Click here to Skip to main content
13,358,244 members (56,505 online)
Click here to Skip to main content
Add your own
alternative version


27 bookmarked
Posted 25 Mar 2013

Work with MS Excel and ADO.NET

, 25 Mar 2013
Rate this:
Please Sign up or sign in to vote.
This article will guide the developers on how you can connect to excel files using ADO.NET and modify the sheets


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 OledbConnection object.

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:

  • Use 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 InsertCommand
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 DELETE operations.

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,

The '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 you have to use x86 in project properties and Microsoft.ACE.OLEDB.12.0.

// this code is for reference and consider changing the code for your need
OleDbConnection excelConnection=null;
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();

    //to get the schema of the workbook.
    dtTables = excelConnection.GetSchema();

    //get the tables in the workbook
    dtTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
    String[] excelSheets = null;
        excelSheets = new String[dtTables.Rows.Count];
        int i = 0;

        // Add the sheet name to the string array.
        foreach(DataRow row in dtTables.Rows)
            excelSheets[i] = row["TABLE_NAME"].ToString();
    DataSet ds = new DataSet();
    //prepare dataset from the tables in the workbook
    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;
        // adapter.FillSchema(ds




This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Rajendra Kondapalli
Software Developer (Senior)
India India
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Marco Tenuti7-Oct-15 10:41
memberMarco Tenuti7-Oct-15 10:41 
QuestionThis might be interesting here: Pin
Dietmar Schoder29-Jul-14 11:34
professionalDietmar Schoder29-Jul-14 11:34 
QuestionGreat article! Pin
Mr. Truong Pham2-Apr-13 5:34
memberMr. Truong Pham2-Apr-13 5:34 
I like the idea that the data in Excel files can be modified directly from apps. Great article! Thank you.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 25 Mar 2013
Article Copyright 2013 by Rajendra Kondapalli
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid