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 theDataAdapter
. ADataTable
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 theForm.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); }
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:
The following subsections discuss how to use Excel as an ADO.NET data source.
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)
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');
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$]
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.