Problem about getting a excel datasheet's name with oledb

  • Thread starter Thread starter Kun Niu
  • Start date Start date
K

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.
 
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
 
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.
Click to expand...

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 = 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();
}
}
 
Thanks.
But I'd rather prefer the oleconnection way.:)

Alvin Bruney said:
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 said:
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.
Click to expand...
Click to expand...
 
Excellent work.
This is what I'm after.
Thanks.

Ignacio Machin ( .NET/ C# MVP ) said:
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.
Click to expand...

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 = 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();
}
}
Click to expand...
 
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.
Click to expand...

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.
 
Back
Top