Problem about getting a excel datasheet's name with oledb

[Kun Niu]

Dear all,

I'm trying to use oledb to get the content of an excel file.
Now I can use oledb to get the content of a datasheet.
But I should know the name of the datasheet in advance.
How can I use oledb to query the names of all the spreadsheets in the excel
file?

Thanks in advance for any hints and reply.

[Alvin Bruney [ASP.NET MVP]]

The Excel application object contains a sheets collection that you can
iterate to examine sheet.name. Google.com/codesearch should provide
examples.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99
-------------------------------------------------------

"Kun Niu" <Kun...@discussions.microsoft.com> wrote in message
news:B5EA16BF-EBF8-4284...@microsoft.com...

[Ignacio Machin ( .NET/ C# MVP )]

Try this:
static 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++;
}

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

[Kun Niu]

Thanks.
But I'd rather prefer the oleconnection way.:)

[Kun Niu]

Excellent work.
This is what I'm after.
Thanks.

[mario...@gmail.com]

Hi,

You can use Excel Automation or some third party component -- it works
much better. I can recommend you to use GemBox.Spreadsheet .NET
component.

--
Mario
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV) files or export to HTML files from your .NET apps. See
http://www.gemboxsoftware.com/GBSpreadsheet.htm

[Kun Niu]

Thanks.
I'll pay attention to that.:)