Work with MS Excel and ADO.NET

Introduction

This article will guide the developers on how you can connect to excel files using ADO.NET and modify the sheets.

Background

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 process.so 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;
try
{
    excelConnection = new OleDbConnection();
    excelConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\DbBackup.xlsx;Extended Properties='Excel 8.0;HDR=YES;'";
    excelConnection.Open();
    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;
    if((dtTables!=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();
            i++;
        }
    }  
    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
        adapter.Fill(dtItems);
        ds.Tables.Add(dtItems);
    }
    
    finally
    {
        adapter.Dispose();
        excelConnection.Dispose();
    }

History

Version 1.0.0.0