ADO.net and Excel

I am trying to open an excel file from a asp.net page (C#) using ADO.net. all works good...but if the the excel worksheet has a name different from [Sheet1$] the code goes in error...

Any idea how to solve this problem... how can i discover the name of the worksheet

Here my code:

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";

sConnectionString += "Data Source=" + DirFileName + ";";

sConnectionString += "Extended Properties=\"Excel 8.0;HDR=YES;\";

OleDbConnection objConn = new OleDbConnection(sConnectionString);

try

{

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

OleDbDataAdapter objAdapter = new OleDbDataAdapter();

objAdapter.SelectCommand = objCmdSelect;

DataTable myExcelTable = new DataTable();

//DataSet myExcelDataSet = new DataSet();

//objAdapter.Fill(myExcelDataSet);

objAdapter.Fill(myExcelTable);

objConn.Close();

}

.NET Development21

Hi, mcrisf

As you code:OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

So It read the [Sheet1$] of the excel file, of course.

If you change the name of the sheet in the file, you must change the corresponding code.

Thank you.

I know that i need to change the name of the worksheet...

My question was....is there a way to refer to the first worksheet (WITHOUT using the name of the worksheet) in a general way

I dont know what will be the name of the worksheet.it can change every time...so i need to use "something" (ex. a number ) to intercept the first worksheet...

any idea

thank you

mcrisf

You could use Excel automation objects. The function below shows how you can get the name of the first sheet.

Some notes:

There's a problem, you need to have Excel installed on the server that'll run this code.
Add the reference to the Excel API in your project.
I don't remember if Workboos.Worksheets is a zero-based index or one-based . If get_Item(1) gets the second sheet, use 0.

public string GetWorkSheetName(string FileName)
{
Excel.Application excelApp = new Excel.ApplicationClass();
Excel.WorkbookClass wb = (Excel.WorkbookClass) excelApp.Workbooks.Open(
FileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet) wb.Worksheets.get_Item(1);
string sName= ws.Name;
wb.Close();
excelApp.Quit();
return sName;
}

thank you for the answer...but i dont want to use the excel API or library...I would like to avoid these solutions....any other idea thx

mcrisf

i found out the answer

read this article

http://davidhayden.com/blog/dave/archive/2006/05/31/2975.aspx

Hi, mcrisf

You can use GetOleDbSchemaTable to get the sheet name first and then access the sheet data.

Following example show a method return the first sheet name of a specific excel file:

public string ExcelSheetNames(String excelFile)
{
DataTable dt;
string connString = ExcelExtendedConn(excelFile);
using (OleDbConnection objConn = new
OleDbConnection(connString))
{
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
}
string name = dt.Rows[0]["TABLE_NAME"].ToString();
if(name[0] == '\'')
{
//numeric sheetnames get single quotes around them in the schema.
//remove them here

{
name = name.Substring(1, name.Length - 2);
}
}
}
}

Thank you