Recipe 1.2 Connecting to a Microsoft Excel Workbook

Problem

You want to access data stored in a Microsoft Excel workbook.

Solution

Use the OLE DB Jet provider to create, access, and modify data stored in an Excel workbook.

The sample code contains two event handlers:

Form.Load

Creates an OleDbDataAdapter that uses the Jet OLE DB provider to access an Excel workbook. Custom insert and update logic is created for the DataAdapter. A DataTable is filled from the first worksheet, Sheet1, in the Excel workbook and the default view of the table is bound to a data grid on the form.

Update Button.Click

Uses the DataAdapter created in the Form.Load event handler to update the Excel workbook with the programmatic changes.

The C# code is shown in Example 1-2.

Example 1-2. File: ExcelForm.cs

// Namespaces, Variables, and Constants
using System;
using System.Configuration;
using System.Data;

private OleDbDataAdapter da;
private DataTable dt;

//  . . . 

private void ExcelForm_Load(object sender, System.EventArgs e)
{
    // Create the DataAdapter.
    da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",
        ConfigurationSettings.AppSettings["Excel_0115_ConnectString"]);

    // Create the insert command.
    String insertSql = "INSERT INTO [Sheet1$] " +
        "(CategoryID, CategoryName, Description) " +
        "VALUES (?, ?, ?)";
    da.InsertCommand =
        new OleDbCommand(insertSql, da.SelectCommand.Connection);
    da.InsertCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
        "CategoryID");
    da.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
        "CategoryName");
    da.InsertCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
        "Description");

    // Create the update command.
    String updateSql = "UPDATE [Sheet1$] " +
        "SET CategoryName=?, Description=? " +
        "WHERE CategoryID=?";
    da.UpdateCommand =
        new OleDbCommand(updateSql, da.SelectCommand.Connection);
    da.UpdateCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
        "CategoryName");
    da.UpdateCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
        "Description");
    da.UpdateCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
        "CategoryID");

    // Fill the table from the Excel spreadsheet.
    dt = new DataTable( );
    da.Fill(dt);
    // Define the primary key.
    dt.PrimaryKey = new DataColumn[] {dt.Columns[0]};

    // Records can only be inserted using this technique.
    dt.DefaultView.AllowDelete = false;
    dt.DefaultView.AllowEdit = true;
    dt.DefaultView.AllowNew = true;
    // Bind the default view of the table to the grid.
    dataGrid.DataSource = dt.DefaultView;
}
private void updateButton_Click(object sender, System.EventArgs e)
{
    da.Update(dt);
}

Discussion

You can use the Jet OLE DB provider to access Microsoft Excel as a data source. The Jet database engine can access other database file formats through Indexed Sequential Access Method (ISAM) drivers specified in the Extended Properties attribute of the connection. Excel 2000 and 2002 are supported with the Excel 8.0 source database type as shown in the following example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myBook.xls;
    Extended Properties="Excel 8.0;HDR=YES";

The Extended Properties attribute can, in addition to the ISAM version property, specify whether or not tables include headers as field names in the first row of a range using an HDR attribute.

There are three ways in which you can reference Excel workbook data within a SQL statement:

• Specify the worksheet name followed by a dollar sign to access the entire range used in the worksheet:

  SELECT * FROM [MySheet$]

• Specify a range explicitly using cells:

  SELECT * FROM [MySheet$A1:E5]

• Specify a range with a defined name, as shown in the solution:

  SELECT * FROM MyRange

The following subsections discuss how to use Excel as an ADO.NET data source.

Create table

The CREATE TABLE command will create a table in an Excel workbook. The workbook for the connection will be created if it does not exist. For example:

CREATE TABLE MySheet (Field1 char(10), Field2 float, Field3 date)

Create data

You can use the INSERT command, either static or parameterized, to insert data into a worksheet or range:

INSERT INTO [MySheet$]  (Field1, Field2, Field3)
VALUES ('testdata', 1.234, '09/28/1979');

Retrieve data

Use either a DataAdapter or a DataReader to retrieve data from an Excel workbook. Create a SQL SELECT statement referencing a worksheet or a range in an Excel workbook and execute the statement to fill a DataSet using a DataAdapter or to create a DataReader. For example:

SELECT * FROM [MySheet$]

Update data

The UPDATE command, either static or parameterized, can update data in a worksheet or range. For example:

UPDATE [MySheet$] 
SET Field2 = '2.345',
    Field3 = '10/18/1964'
WHERE
    Field1 = 'testdata'

Delete data

The Jet OLE DB provider does not allow DELETE operations. An error will be raised if an attempt is made to execute a DELETE statement affecting one or more records.