In this article, I am going to show you how to use an Excel workbook as your data source and fetch data based on your SQL query. In my first step, I will show you how to write a query using SQL syntax and next, I will show you how to fetch and bind data in your
When I started reading an Excel workbook by writing traditional VBA code I found it's complicated to read even one cell value. After writing successful code too, you may be trapped under the COM memory leakage issue. If your Excel file has a huge amount of data to read and quite a few number of sheets, then you can expect a nice popup window showing up with, "You are running out of virtual memory." Even after killing several objects during this process, you cannot make sure that your object will be released immediately. Finally I found that SQL can reduce code complexity and I can gain performance and there is no memory leakage issue. I am assuming that those of you who are reading this article have basic knowledge of ADO.NET and Microsoft Excel.
Using the Code
Getting data from any data storage using ADO.NET is very simple. Before writing real code, let's create a Windows application (though you can use Web application as well), and add the following line at the top of the target form. I am going to use OLEDB API for accessing Excel data. OLEDB will take help of the JET engine to execute a query and fetch data from Excel.
Please make a note that I am using Office 2003 and Visual Studio 2003.
1. Connection String
First, we will establish connection to our data source. It is very similar to connecting to the SQL Server or Oracle.
OleDbConnection con = new OleDbConnection(
"provider=Microsoft.Jet.OLEDB.4.0;data source=" +
"File Name with Complete Path" +";Extended Properties=Excel 8.0;");
2. Writing Query
Writing an Excel query is as similar as writing a query in any other traditional data storage like SQL Server, Oracle, etc. However there are a few differences. First, you have to specify your sheet name instead of your table name. Next, you have to give starting and end cell references. Watch my following code carefully:
SELECT * FROM [42560035$A1:F500]
- In the above query,
42560035 is my sheet name (consider this to be your table name). In your case, this name may be different. Please do not forget to write your sheet name inside
- Next to the sheet name is my start cell and end cell reference. Your Excel file may contain different cell references. Please make sure that after your sheet name, you are using the dollar sign ($) then your start cell reference and end cell reference separated by colon (:). It is an error if you do not mention your start cell and end cell references in your query.
This was a very simple query. Here is the more complicated one:
SELECT * FROM [42560030$A21:F500]
WHERE [Period_End Date] = #3/2/2007#
ORDER BY [Date_Incurred]
In place of
*, which returns all columns, you can specify exact column(s) name you are looking for. Here is an example:
SELECT [Associate Name] as Associate,[Amount] as Amount
WHERE [Period_End Date] = #3/2/2007# ORDER BY [Date_Incurred]
Please make sure that you are placing your column name inside
 bracket if your column name contains a white space. Otherwise, your JET engine will throw an exception. For consistency, place all column names inside
 bracket. One more interesting point: if you observe the
[Period_End Date] column name carefully, there is an underscore (_) between
End. This is because in my Excel sheet I wrote
Period in one line and
End Date in the next line. Please note that when I wrote the next line, that meant Next Line not next cell. The next step is to build our data adapter.
StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT * FROM [42560035$A1:F500]");
OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.Tostring(),con);
4. Filling DataSet
DataSet dsXLS = new DataSet() adp.Fill(dsXLS);
5. Binding Grid
After filling the data, now is the time is to see the data. For that, we can use
DataGrid or you can write your dataset into an XML file by using the
WriteXml method. In this example, I will be using
DataGrid to display my data.
DataView dvEmp = new DataView(ds.Tables);
dataGrid1.DataSource = dvEml;
That is all.
In my next article, I am going to show you how to post data in an Excel sheet.
- 30th July, 2007: Initial post