471,356 Members | 1,636 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,356 software developers and data experts.

Problem about getting a excel datasheet's name with oledb

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.
Jun 27 '08 #1
6 2024
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" <Ku****@discussions.microsoft.comwrote in message
news:B5**********************************@microsof t.com...
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.
Jun 27 '08 #2
On May 5, 12:06*pm, Kun Niu <Kun...@discussions.microsoft.comwrote:
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.
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();
}
}

Jun 27 '08 #3
Thanks.
But I'd rather prefer the oleconnection way.:)

"Alvin Bruney [ASP.NET MVP]" wrote:
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" <Ku****@discussions.microsoft.comwrote in message
news:B5**********************************@microsof t.com...
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.
Jun 27 '08 #4
Excellent work.
This is what I'm after.
Thanks.

"Ignacio Machin ( .NET/ C# MVP )" wrote:
On May 5, 12:06 pm, Kun Niu <Kun...@discussions.microsoft.comwrote:
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.

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();
}
}

Jun 27 '08 #5
On May 5, 6:06 pm, Kun Niu <Kun...@discussions.microsoft.comwrote:
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.
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
Jun 27 '08 #6
Thanks.
I'll pay attention to that.:)

"ma*********@gmail.com" wrote:
On May 5, 6:06 pm, Kun Niu <Kun...@discussions.microsoft.comwrote:
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.

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
Jun 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Chris | last post: by
2 posts views Thread by jeffgeorge | last post: by
22 posts views Thread by PeteCresswell | last post: by
3 posts views Thread by hkappleorange | last post: by
9 posts views Thread by dba123 | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.