ADO.NET Cookbook

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