Query an Excel Worksheet Without using Interop

by William Ryan

Many years back when I was first starting programming, my main duties involved  data mining and reporting.  Crystal Reports could usually make your reports pretty in a short amount of time, but Excel has a lot more power in many regards even though it's not supposed to be a report writer.  Anyway, it wasn't long before I did a lot with Excel and Automation.  When .NET came out, I was working on a project where I had to read in some data from an Excel sheet and peg a few variable to the corresponding fields in the worksheet.  Out of impulse, I ran to Interop and a few hours later, my work was done.  However, it took quite  a bit of code just to extract a few variables.  After all, I had to create an Excel application and interate through it.  This invovled two loops (one nested one) which would move the position to each column, then down a row until its done.  It worked, but I never really liked that approach.

Then comes my friend ADO.NET.  About a week after I wrote that code, I saw that you can everythign short of Enron stock as a data source.  Text files?  You bet.  XML Files?  You bet.  Excel Files, YOU BET!

Here's how to do it... (and the best part is that you can use Standard SQL Queries to grab your data)

string ExcelFile = @"C:\SampleDictationLog.XLS";    if(! File.Exists(ExcelFile))        {       //A better method would be to notify the user and pop up an Open File      //Dialog for them to try another file if they want to continue... but      //I'm feeling a little lazy today.       MessageBox.Show(String.Format("File {0} does not Exist", ExcelFile));    }             string ConnectString;     ConnectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +          ExcelFile + ";ExtendedProperties=Excel 8.0;";    string SheetName="08_17_2003"; //Name of the sheet you want to query from    OleDbConnection Connection = new OleDbConnection();        Connection.ConnectionString = ConnectString;    try   {        Connection.Open();    }    catch(OleDbException ex)    {       Debug.Assert(false, String.Format("Error {0},           StackTrack {1}",ex.Message, ex.StackTrace.ToString()));    }            //I haven't figured out how to use a Parameter here, so I wimped out   //and used Dynamic SQL.  I'll try to find out how to do this corectly   //and Post it    OleDbDataAdapter Command = new OleDbDataAdapter("SELECT * FROM           [" + SheetName + "$]", Connection);     DataSet ExcelData = new DataSet();    try   {           Command.Fill(ExcelData);    }    catch(OleDbException ex)    {       Debug.Assert(false, String.Format("Error {0}, StackTrace {1}"             , ex.Message, ex.StackTrace.ToString()));    }    finally   {       if(Connection.State != ConnectionState.Closed){Connection.Close();}       }     Debug.WriteLine(ExcelData.Tables[0].Rows.Count.ToString());

Notice that this code may look a bit verbose, but that's mostly due to error handlers and clarity.  Don't let that obscure how absolutely simple this method is.

Anyway, I tried to comment what's going on but it's really quite simple. All I'm really doing is setting Excel as a datasource and firing a query off of it.  As I commented, I used dynamic SQL which I avoid
like the plague, but it's only because I couldn't figure out how to get a paramater to work and I'm sure there's a work around.  In my nex article, I'll do the same using a Delimmited file.