Name of the Excel Worksheet using ASP

[deeparajendran]

Hello,

I am looking to open an Excel spreadsheet in ADO.NET using OLEDB's Jet engine. I have a conn string as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.xls;Extended Properties=Excel 8.0;

I am attempting to put together the SQL to query this spreadsheet. The examples I have found all look like:

Select * From [Sheet1$]

but this explicitly names the worksheet in the spreadsheet. Is there a way to indicate the worksheet by index instead of name? I am attempting to build an automatic parser for an excel spreadsheet and am looking to query the first worksheet but its name could vary from spreadsheet to spreadsheet. How should I do this? If it is not possible to reference the worksheet within the spreadsheet by index in the SQL Select statement, then is there a way to discover the name of the worksheet at run time without starting an instance of Excel? Thanks for your help!

[Jatinder Bahl]

Using following code you can iterate through all the sheets in Excel file:

private String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;

try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables , null);

if(dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}

// Loop through all of the sheets if you want too...
for(int j=0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
}

return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}

[zensunni]

Is that ASP code? Looks like C#. How can we do it in ASP?