This article will guide the developers on how you can connect to excel files using ADO.NET and modify the sheets.
Many time developers have to export the data to other applications, this may be due to the requirement that customer have some data to be migrated from other systems. This requirement may come when the data in important and it can be migrated easily (say around few thousands of records). Sometimes you have change this data in excel files i.e. either two data sheets have to be merged or pick some data from other source and add it to spread sheet.
Using the Code
The Microsoft Jet database engine can access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. To access Excel workbooks with ADO.NET, you can use the Jet OLE DB provider. Excel file can be connected using
To access an Excel workbook by using the Jet OLE DB Provider, use a connection string that has the following syntax:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\WorkBook1.xls;Extended Properties="Excel 8.0;HDR=YES;"
If you specify
HDR=NO in the connection string, the
Jet OLE DB provider automatically names the fields for you (F1 represents the first field, F2 represents the second field, and so on).
In the connection string, specify the full path and file name for the workbook in the Data Source parameter. The Extended Properties parameter may contain two properties: a property for the ISAM version and a property to indicate whether or not the table(s) include headers.
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans eight rows in a column to guess the data type for the field. You can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.
How you refer the data in excel: There are several way you can reference a table (range) ,
- Use sheet name followed by $. Eg:Sheet1$
- Use a range with defined name. Eg:[MyNamedRange]
- Use a range with a specific address Eg:Sheet1$A1:B10
Note: $ means table exists so when you are creating new excel table no need to include $ sign.
Create Tables: To create a table in an Excel workbook,
CREATE TABLE Sheet1 (F1 char(255), F2 char(255))
Add and Update: With ADO.NET, you can insert and update records in a workbook in one of three ways:
OLEDbCommand and set its
CommandText and then call the
ExecuteNonQuery method. INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC')
UPDATE [Sheet1$] SET F2 = 'F2Value' WHERE F1 = 'F1Value'
- Make changes to a
DataSet that you have filled with a table/query from an Excel workbook and then call the Update method of the
DataAdapter to resolve changes from the
DataSet back to the workbook. However, to use the
Update method for change resolution you must set parameterized commands for the DataAdapter's
INSERT INTO [Sheet1$] (F1, F2) values (?,?)
UPDATE [Sheet1$] SET F2 = ? WHERE F1 =?.
commands are required because the
OleDbDataAdapter does not supply key/index information for Excel workbooks; without key/index fields, the
CommandBuilder cannot automatically generate the commands for you.
- Data from other files can be imported to excel from other data sources which supports
Jet OLE DB provider with single insert command.
Eg:Text files, Microsoft Access databases and of course Excel Workbooks.
INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"
Delete Records: Although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow
Note: if you are trying to connect to excel on 64 bit machine, you might get the following error with
Microsoft.Jet.OLEDB provider. Like below error,
Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine.
There is no 64 bit driver, so you need to run it as a 32 bit process.so you have to use x86 in project properties and
OleDbDataAdapter adapter= null;
excelConnection = new OleDbConnection();
excelConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\DbBackup.xlsx;Extended Properties='Excel 8.0;HDR=YES;'";
DataTable dtTables = new DataTable();
dtTables = excelConnection.GetSchema();
dtTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
String excelSheets = null;
excelSheets = new String[dtTables.Rows.Count];
int i = 0;
foreach(DataRow row in dtTables.Rows)
excelSheets[i] = row["TABLE_NAME"].ToString();
DataSet ds = new DataSet();
foreach (string sheet in excelSheets)
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = excelConnection;
cmd.CommandText = "Select * from ["+sheet+"]";
DataTable dtItems = new DataTable();
dtItems.TableName = sheet;
adapter = new OleDbDataAdapter();
adapter.SelectCommand = cmd;