Here I am explaining how to read excel sheet and import it using ADO.Net and C#. Excel Workbook is just like database with sheets corresponding to tables. See the mapping below.
Database <—————> Excel Workbook
Sheet <—————-> Table
Connection String for Excel 97-2003 Format (.XLS)
For Excel 97-2003 format Microsoft Jet OLEDB Driver 4.0 is used. A sample connection string as follows.
Connection String for Excel 2007 Format (.XLSX)
For Excel 2007 format the new Microsoft Ace OLEDB Driver 4.0 is used. A sample connection string as follows.
Rest everything is same for both versions. One thing to note Microsoft Ace OLEDB Driver 12.0 works for both Excel 2003 and Excel 2007 formats.
You can specify whether your Excel file has Headers or not using the HDR property.
When HDR is set to Yes the First Row is considered as the Header of the Excel file.
For this tutorial I have used the following Excel Sheet
Establish a Connection
The dtExcelSchema contains all the Sheets present in your Excel Workbook
You access them in the following way
This will give the name of the first sheet. i.e. Sheet1$
Running a Select Query by Specifying Column Names
Note the above query will only work when Header Row is present in the Excel Sheet.
XML Output of the Query
Running a Query without Specifying Column Names
Above query will work in both cases if Header row is specified and when not specified
Output of the Above Query with Header = Yes
Output of the Above Query with Header = No
Running a Query on a Range of Cells
Many times you need to select all data from a range of Cells. The above query selects all the data within the cell range A3:B5
Output of the Above Query
You can download the sample source code here.