2009-07-03

Excel Sheet Names using ODBC

I recently needed to import data from excel into a c# application after doing some searching I found the 2007 Office System Driver: Data Connectivity Components there is a lot of good info on the net so I quickly got it up and running. There was only one problem nowhere I looked could I find out how to get the sheet names. I stared putting various stuff into a data grid until I cam up with the code below.
string filename = "your filename";
string connectionString;
connectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};dbq=" + filename + ";fil=excel 12.0;readonly=0;usercommitsync=Yes";

OdbcConnection myConnection = new OdbcConnection(connectionString);

myConnection.Open();
DataTable data = myConnection.GetSchema("Tables");
myConnection.Close();

List sheets = new List();
foreach (DataRow sheet in data.Rows)
{
    sheets.Add(sheet["TABLE_NAME"].ToString().Trim('\'').TrimEnd('$'));
}

P.S. I have built a whole static class library to read and write excel sheets I will post it here when I have some time to comment it.

No comments:

Post a Comment