[ Team LiB ]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;

// . . .


"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.

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.
[ Team LiB ]