Excel Adapter for ADO.NET - CodeProject 12,751,650 members (33,685 online)
70.9K views1.1K downloads56 bookmarked
Tomy Rhymond, 28 Mar 2007
3.91/5 - 8 votesμ 3.91, σa 3.21 [?]
An article on converting a Microsoft Excel Workbook to ADO.NET Dataset using C# and Microsoft Office 2003 Interop Assemblies.	Download source files - 42.2 KB Introduction
On the recent project I worked on, I had a requirement to load data from an Excel spreadsheet to a database on a regular basis. My goal was to load the data from Excel to a ADO.NET Dataset and run my business rules. I couldn't find any articles addressing this problem and so I decided to write one. There are two projects included in the source code package. ExcelADOAdapter builds the library, ExcelADOAdapter.dll. ExcelAdapterTest is a test Windows Form application. In addition, you need to add the following references. Microsoft Excel 11.0 Object Library
All sample codes in this articles are in C#. This component uses Microsoft Office 2003 Interop Assemblies to handle the Excel source and the code is written in C#. The individual worksheets on the Excel workbook will be loaded into separate Datatables on the DataSet. I have a workbook, PurchaseOrder, with 5 worksheets; Customers, Orders, OrderDetails, Shippers and Products. The load process will create a dataset with 5 tables. The name of the dataset will be set to the name of the Excel workbook, in this example PurchaseOrder. The DataTable names will be the name of the worksheets. Background
For more information on the Office 2003 Interop Assemblies, refer MSDN Reference. Using the code
The most important thing to remember when working with Excel is to get a valid range of cells on the worksheet to work with. We certainly don't want all the empty cells to get loaded into the DataTables. The logic that I used to get the valid range is depicted below: /// &lt;summary /&gt;
/// Gets the valid range of cells to work with.
/// &lt;param name="sheet" /&gt;The sheet.&lt;/param /&gt;
/// &lt;returns /&gt;Excel Range&lt;/returns /&gt;
private Microsoft.Office.Interop.Excel.Range getValidRange(Worksheet sheet)
string downAddress = "";
string rightAddress = "";
long indexOld = 0;
Microsoft.Office.Interop.Excel.Range startRange;
Microsoft.Office.Interop.Excel.Range rightRange;
Microsoft.Office.Interop.Excel.Range downRange;
startRange = sheet.get_Range("A1", Missing.Value);
// get the end of values to the right
// (will stop at the first empty cell)
rightRange = startRange.get_End(XlDirection.xlToRight);
/*get_End method scans the sheet in the direction specified
*until it finds an empty cell and returns the previous cell.
*We need to scan all the columns and find the column with
*the highest number of rows (row count).
*Then use the Prefix character on the right cell and the
*row count to determine the address for the valid range.
downRange = rightRange.get_End(XlDirection.xlDown);
downAddress = downRange.get_Address(false, false,
eferenceStyle.xlA1, Type.Missing, Type.Missing);
index = getIndex(downAddress);
if (index &gt;= 65536) index = 0;
if (index &gt; indexOld) indexOld = index;
if (rightRange.Column == 1) break;
rightRange = rightRange.Previous;
rightAddress = rightRange.get_Address(false, false,
ReferenceStyle.xlA1, Type.Missing, Type.Missing);
return sheet.get_Range("A1", getPrefix(rightAddress) + indexOld);
startRange = null;
rightRange = null;
downRange = null;
startRange = sheet.get_Range("A1", Missing.Value); sets the starting cell as A1 which is the top left cell on the Excel worksheet. The get_End method on the Range object scans the sheet in the direction specified until it finds a empty cell and returns the previous cell. But a blank cell can also hold valid values as shown below. To avoid this problem, you need to check all the columns and find the column with the highest number of rows and use that as the boundary for the valid range. But still if all the cells in the row are blank, the get_End method will not find any rows after that row. Once after a valid range is found, we can create a two dimensional array and load the array to a DataTable. See the code segment below. /// &lt;summary /&gt;
/// Loads the data table.
private System.Data.DataTable loadDataTable(Worksheet sheet)
object[] columnValues;
dt.TableName = sheet.Name;
range = getValidRange(sheet);
columnValues = new object[values.GetLength(1)];
if (i == 1 &amp;&amp; _firstRowHeader)
object value = values[i, j];
if (value != null) dt.Columns.Add(value.ToString());
else dt.Columns.Add("");
columnValues[j - 1] = value.ToString();
columnValues[j - 1] = "";
dt.Rows.Add(columnValues);
} I hope you will find the code useful. Thank you. History
28 March 2007 - Updated source
A Faster Copy... PeterMoon24-May-09 15:37
PeterMoon24-May-09 15:37 Hi,Great article. Some time back I have the problem to send large amount of data to Excel, and I found a faster approach that copy all the value from an array to Excel with a simple invoke.Maybe you should check it and try to read the cell using the same approach. It could improve the speed for reading a large amount of cells.http://www.codeproject.com/KB/office/FastExcelExporting.aspxRegards,Pedro
Updating individual cells Member 44670355-Feb-09 20:20
Member 44670355-Feb-09 20:20 Here when i done this.I realize that the cells are updated as it is when it have coloumn Header, but if i want to update cells without giving coloumn header but provide cell number like : F12,F13 etc.
Problem & solution for cells containing dates Ernest_Paez23-Oct-08 5:36
Ernest_Paez23-Oct-08 5:36 Congratulations for your great code.I have had some problem because the Value2 method doesn't recognize date cells. It returns a float value instead of a date value.After looking for a solution, I found that replacing the lineobject[,] values = (object[,])range.Value2;for this otherobject[,] values = (object[,])range.get_Value(XlRangeValueDataType.xlRangeValueDefault);in your loadDataTable method solves the problem.
Re: Problem & solution for cells containing dates tedirgin28-Apr-10 4:20
tedirgin28-Apr-10 4:20 thanks mantedirgin
Works like a charm! joshua_rivers16-Jul-08 17:07
joshua_rivers16-Jul-08 17:07 I just decided I had enough of my expensive, buggy, 3rd party excel component, and after a brief google, found your article.Your code slid into my app as a practical drop-in, and worked perfectly. Thanks for your work!ps. Perhaps you should implement IDisposable to clean up after things. That would allow me to use a using() block to clean up rather than have to remember that call to Dispose();
Dosn't work with Visual Studio.Net 2005 Majdi Jamal17-Nov-07 11:28
Majdi Jamal17-Nov-07 11:28 Nice project .. I really need to use the same idea in recently project I work on.I try to use this code in VS 2005, dosn't work, do u have any idea how to upload the excel sheet into datagrid in VS2005 invironment.Thanks in advance hi everybody
"Old Format or invalid type library" error??? [SK]SpooK30-Oct-07 5:30
[SK]SpooK30-Oct-07 5:30 Hi,I'm loading your "purchaseorder.xls" this error displayed.
Old Format or invalid type library. (Exception from HRESULT:0x80028018(TYPE_E_INVDATAREAD))
Install excel on server.` SinghalManu7-Jun-07 4:57
SinghalManu7-Jun-07 4:57 I want to know fi I use this method to read from an excel file, when deploying my application on the server, am I required to install excel on the server. Please let me know asap.Thanks in advance.Manu Manu
Re: Install excel on server.` fandijunior7-Sep-07 12:42
fandijunior7-Sep-07 12:42 Hidid it work when you deploy it on the server? Thanks.Eimar Re: Install excel on server.` gg423713-Nov-09 0:59
gg423713-Nov-09 0:59 If you want that your code runs independent of Excel application you should try GemBox.Spreadsheet .NET Excel component.Because it implements its own methods to read and write Excel files it is much faster than Excel Interop (about 250 times), and it is very easy to use.Check out this web demo of DataTable export to Excel through ASP.NET stream.
work with web application Nigam SAMir28-Mar-07 19:34
Nigam SAMir28-Mar-07 19:34 It work with web application also? Thanks & Regards,SAMir Nigam,Software Developer,STPL, Lucknow, India.
Re: work with web application jw12329-Mar-07 2:04
jw12329-Mar-07 2:04 Hi, you should consider using OleDbDataAdapter:The example is pretty straightforward, so please don't just copy it...private DataSet LoadFile(string fileName) { DataSet ret = new DataSet(); System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(); //You should consider using "HDR=NO", to get numbered ColumnNames in your DataSet. myConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"", fileName); //Try-Catch-Finally? myConnection.Open(); //Get all Table-Names from the workbook DataTable tbl = myConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in tbl.Rows) { string tableName = row[2].ToString(); System.Data.OleDb.OleDbCommand selectCommand = new System.Data.OleDb.OleDbCommand(string.Format("SELECT * FROM [{0}]", tableName), myConnection); System.Data.OleDb.OleDbDataAdapter myAdapter = new System.Data.OleDb.OleDbDataAdapter(selectCommand); myAdapter.Fill(ret, tableName); } myConnection.Close(); return ret; } Use Odbc Kevin I28-Mar-07 16:57
Kevin I28-Mar-07 16:57 Wow, you should look into ODBC and excel. Just search. It's basically just creating a connection,writing a query to select the fields from the worksheet, then using the standard system.data objects to pull it out. MUCH faster and a lot easier than using the Excel interop dlls. Re: Use Odbc chai9332328-Mar-07 18:45
chai9332328-Mar-07 18:45 I think Koogra Excel Biff Reader much faster.http://sourceforge.net/projects/koogra/You also don't need install excel on local machine. Re: Use Odbc Member 129842756-Feb-17 6:38
Member 129842756-Feb-17 6:38 am planning to use this library in one of the existing project that uses Office Excel. I would appreciate if you guide me as to where can I get some documentation about this library (like the different methods,events etc it contains &amp; their usage). When I download koogra-3.1.5-bin-03232010.zip file, I see 2 dlls &amp; an XML file &amp; no documentation. Because, without some initial documentation about its usage, it makes no sense in using this library.Thank you.
Re: Use Odbc thund3rstruck29-Mar-07 4:52
thund3rstruck29-Mar-07 4:52 I was thinking the exact same thing. The Interops are slow and require the libraries. With the OleDataAdapter you can just run a select statement directly against the spreadsheet... it's really quite simpleHow To Query and Display Excel Data by Using ADO.NET[^]
Last Updated 28 Mar 2007	Article Copyright 2007 by Tomy RhymondEverything else
Excel Adapter for ADO.NET - CodeProject 12,751,650 members (33,685 online)
70.9K views1.1K downloads56 bookmarked
Tomy Rhymond, 28 Mar 2007
3.91/5 - 8 votesμ 3.91, σa 3.21 [?]
An article on converting a Microsoft Excel Workbook to ADO.NET Dataset using C# and Microsoft Office 2003 Interop Assemblies.	Download source files - 42.2 KB Introduction
On the recent project I worked on, I had a requirement to load data from an Excel spreadsheet to a database on a regular basis. My goal was to load the data from Excel to a ADO.NET Dataset and run my business rules. I couldn't find any articles addressing this problem and so I decided to write one. There are two projects included in the source code package. ExcelADOAdapter builds the library, ExcelADOAdapter.dll. ExcelAdapterTest is a test Windows Form application. In addition, you need to add the following references. Microsoft Excel 11.0 Object Library
All sample codes in this articles are in C#. This component uses Microsoft Office 2003 Interop Assemblies to handle the Excel source and the code is written in C#. The individual worksheets on the Excel workbook will be loaded into separate Datatables on the DataSet. I have a workbook, PurchaseOrder, with 5 worksheets; Customers, Orders, OrderDetails, Shippers and Products. The load process will create a dataset with 5 tables. The name of the dataset will be set to the name of the Excel workbook, in this example PurchaseOrder. The DataTable names will be the name of the worksheets. Background
For more information on the Office 2003 Interop Assemblies, refer MSDN Reference. Using the code
The most important thing to remember when working with Excel is to get a valid range of cells on the worksheet to work with. We certainly don't want all the empty cells to get loaded into the DataTables. The logic that I used to get the valid range is depicted below: /// &lt;summary /&gt;
/// Gets the valid range of cells to work with.
/// &lt;param name="sheet" /&gt;The sheet.&lt;/param /&gt;
/// &lt;returns /&gt;Excel Range&lt;/returns /&gt;
private Microsoft.Office.Interop.Excel.Range getValidRange(Worksheet sheet)
string downAddress = "";
string rightAddress = "";
long indexOld = 0;
Microsoft.Office.Interop.Excel.Range startRange;
Microsoft.Office.Interop.Excel.Range rightRange;
Microsoft.Office.Interop.Excel.Range downRange;
startRange = sheet.get_Range("A1", Missing.Value);
// get the end of values to the right
// (will stop at the first empty cell)
rightRange = startRange.get_End(XlDirection.xlToRight);
/*get_End method scans the sheet in the direction specified
*until it finds an empty cell and returns the previous cell.
*We need to scan all the columns and find the column with
*the highest number of rows (row count).
*Then use the Prefix character on the right cell and the
*row count to determine the address for the valid range.
downRange = rightRange.get_End(XlDirection.xlDown);
downAddress = downRange.get_Address(false, false,
eferenceStyle.xlA1, Type.Missing, Type.Missing);
index = getIndex(downAddress);
if (index &gt;= 65536) index = 0;
if (index &gt; indexOld) indexOld = index;
if (rightRange.Column == 1) break;
rightRange = rightRange.Previous;
rightAddress = rightRange.get_Address(false, false,
ReferenceStyle.xlA1, Type.Missing, Type.Missing);
return sheet.get_Range("A1", getPrefix(rightAddress) + indexOld);
startRange = null;
rightRange = null;
downRange = null;
startRange = sheet.get_Range("A1", Missing.Value); sets the starting cell as A1 which is the top left cell on the Excel worksheet. The get_End method on the Range object scans the sheet in the direction specified until it finds a empty cell and returns the previous cell. But a blank cell can also hold valid values as shown below. To avoid this problem, you need to check all the columns and find the column with the highest number of rows and use that as the boundary for the valid range. But still if all the cells in the row are blank, the get_End method will not find any rows after that row. Once after a valid range is found, we can create a two dimensional array and load the array to a DataTable. See the code segment below. /// &lt;summary /&gt;
/// Loads the data table.
private System.Data.DataTable loadDataTable(Worksheet sheet)
object[] columnValues;
dt.TableName = sheet.Name;
range = getValidRange(sheet);
columnValues = new object[values.GetLength(1)];
if (i == 1 &amp;&amp; _firstRowHeader)
object value = values[i, j];
if (value != null) dt.Columns.Add(value.ToString());
else dt.Columns.Add("");
columnValues[j - 1] = value.ToString();
columnValues[j - 1] = "";
dt.Rows.Add(columnValues);
} I hope you will find the code useful. Thank you. History
28 March 2007 - Updated source
A Faster Copy... PeterMoon24-May-09 15:37
PeterMoon24-May-09 15:37 Hi,Great article. Some time back I have the problem to send large amount of data to Excel, and I found a faster approach that copy all the value from an array to Excel with a simple invoke.Maybe you should check it and try to read the cell using the same approach. It could improve the speed for reading a large amount of cells.http://www.codeproject.com/KB/office/FastExcelExporting.aspxRegards,Pedro
Updating individual cells Member 44670355-Feb-09 20:20
Member 44670355-Feb-09 20:20 Here when i done this.I realize that the cells are updated as it is when it have coloumn Header, but if i want to update cells without giving coloumn header but provide cell number like 
: F12,F13 etc.
Problem & solution for cells containing dates Ernest_Paez23-Oct-08 5:36
Ernest_Paez23-Oct-08 5:36 Congratulations for your great code.I have had some problem because the Value2 method doesn't recognize date cells. It returns a float value instead of a date value.After looking for a solution, I found that replacing the lineobject[,] values = (object[,])range.Value2;for this otherobject[,] values = (object[,])range.get_Value(XlRangeValueDataType.xlRangeValueDefault);in your loadDataTable method solves the problem.
Re: Problem & solution for cells containing dates tedirgin28-Apr-10 4:20
tedirgin28-Apr-10 4:20 thanks mantedirgin
Works like a charm! joshua_rivers16-Jul-08 17:07
joshua_rivers16-Jul-08 17:07 I just decided I had enough of my expensive, buggy, 3rd party excel component, and after a brief google, found your article.Your code slid into my app as a practical drop-in, and worked perfectly. Thanks for your work!ps. Perhaps you should implement IDisposable to clean up after things. That would allow me to use a using() block to clean up rather than have to remember that call to Dispose();
Dosn't work with Visual Studio.Net 2005 Majdi Jamal17-Nov-07 11:28
Majdi Jamal17-Nov-07 11:28 Nice project .. I really need to use the same idea in recently project I work on.I try to use this code in VS 2005, dosn't work, do u have any idea how to upload the excel sheet into datagrid in VS2005 invironment.Thanks in advance hi everybody
"Old Format or invalid type library" error??? [SK]SpooK30-Oct-07 5:30
[SK]SpooK30-Oct-07 5:30 Hi,I'm loading your "purchaseorder.xls" this error displayed.
Old Format or invalid type library. (Exception from HRESULT:0x80028018(TYPE_E_INVDATAREAD))
Install excel on server.` SinghalManu7-Jun-07 4:57
SinghalManu7-Jun-07 4:57 I want to know fi I use this method to read from an excel file, when deploying my application on the server, am I required to install excel on the server. Please let me know asap.Thanks in advance.Manu Manu
Re: Install excel on server.` fandijunior7-Sep-07 12:42
fandijunior7-Sep-07 12:42 Hidid it work when you deploy it on the server? Thanks.Eimar Re: Install excel on server.` gg423713-Nov-09 0:59
gg423713-Nov-09 0:59 If you want that your code runs independent of Excel application you should try GemBox.Spreadsheet .NET Excel component.Because it implements its own methods to read and write Excel files it is much faster than Excel Interop (about 250 times), and it is very easy to use.Check out this web demo of DataTable export to Excel through ASP.NET stream.
work with web application Nigam SAMir28-Mar-07 19:34
Nigam SAMir28-Mar-07 19:34 It work with web application also? Thanks & Regards,SAMir Nigam,Software Developer,STPL, Lucknow, India.
Re: work with web application jw12329-Mar-07 2:04
jw12329-Mar-07 2:04 Hi, you should consider using OleDbDataAdapter:The example is pretty straightforward, so please don't just copy it...private DataSet LoadFile(string fileName) { DataSet ret = new DataSet(); System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(); //You should consider using "HDR=NO", to get numbered ColumnNames in your DataSet. myConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"", fileName); //Try-Catch-Finally? myConnection.Open(); //Get all Table-Names from the workbook DataTable tbl = myConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); foreach (DataRow row in tbl.Rows) { string tableName = row[2].ToString(); System.Data.OleDb.OleDbCommand selectCommand = new System.Data.OleDb.OleDbCommand(string.Format("SELECT * FROM [{0}]", tableName), myConnection); System.Data.OleDb.OleDbDataAdapter myAdapter = new System.Data.OleDb.OleDbDataAdapter(selectCommand); myAdapter.Fill(ret, tableName); } myConnection.Close(); return ret; } Use Odbc Kevin I28-Mar-07 16:57
Kevin I28-Mar-07 16:57 Wow, you should look into ODBC and excel. Just search. It's basically just creating a connection,writing a query to select the fields from the worksheet, then using the standard system.data objects to pull it out. MUCH faster and a lot easier than using the Excel interop dlls. Re: Use Odbc chai9332328-Mar-07 18:45
chai9332328-Mar-07 18:45 I think Koogra Excel Biff Reader much faster.http://sourceforge.net/projects/koogra/You also don't need install excel on local machine. Re: Use Odbc Member 129842756-Feb-17 6:38
Member 129842756-Feb-17 6:38 am planning to use this library in one of the existing project that uses Office Excel. I would appreciate if you guide me as to where can I get some documentation about this library (like the different methods,events etc it contains &amp; their usage). When I download koogra-3.1.5-bin-03232010.zip file, I see 2 dlls &amp; an XML file &amp; no documentation. Because, without some initial documentation about its usage, it makes no sense in using this library.Thank you.
Re: Use Odbc thund3rstruck29-Mar-07 4:52
thund3rstruck29-Mar-07 4:52 I was thinking the exact same thing. The Interops are slow and require the libraries. With the OleDataAdapter you can just run a select statement directly against the spreadsheet... it's really quite simpleHow To Query and Display Excel Data by Using ADO.NET[^]
Last Updated 28 Mar 2007	Article Copyright 2007 by Tomy RhymondEverything else