Use oledb to Operate Excel

This document describes how to use oledb to Operate Excel. This document is intended for developers.

Overview

Using oledb to Operate Excel is better than using Excel directly, and the program is highly readable. When using oledb to Operate Excel, Excel is like a database, while every Excel table is like a table in a database.

 

Rules

Some operations on Excel using oledb are different from those on general databases. For example:

1. Table name. If table sheet1 is operated, it should be written as [sheet1 $]

2. oledb is not recommended for deleting Excel Data.

 

Configure connection string

When configuring the connection string as follows, note that the extended properties value varies with the Excel version. The following value is excel2003; "HDR = yes; "indicates that the first row contains the column name, but does not contain data. If the first row of Excel is not set as the column name, it is set to No. At this time, the column is automatically generated;" IMEX = 1; "indicates that the driver always reads the" intermixed "data column as text.

Oledbconnection conn = new oledbconnection ();

Conn. connectionstring = "provider = Microsoft. Jet.Oledb. 4.0; Data Source = "+ Excel file path +"; extended properties ='Excel8.0; HDR = yes; IMEX = 1 '";

 

Query data

L read data from the entire table

String SQL = "select * from [sheet1 $]"

L for non-standard Excel tables, you can specify the sheet range in Excel

String SQL = "select * from [sheet1 $ A3: F20]";

Update Data

L for standardized tables

String SQL = "Update [sheet1 $] Set fieldname1 = '000000' where fieldname2 = 'b3 '";

L for non-standard tables

String SQL = "Update [sheet1 $ A9: F15] Set fieldname = '000000' where anotherfieldname = 'b3 '";

Insert data

L for standardized tables

String SQL = "insert into [sheet1 $] (fieldname1, fieldname2 ,...) Values ('A', 'B ',...) ";

L for non-standard tables

String SQL = "insert into [sheet1 $ A9: F15] (fieldname1, fieldname2 ,...) Values ('A', 'B ',...) ";

 

Common Errors

If the "operation must use an updatable query" error occurs, the "field" reference in the Excel file may be incorrect in the SQL statement, or the Excel file does not have the "modify" permission;

If the error "the selected range cannot be expanded" appears, it may be that the "range" referenced by the Excel file has an error.