Restrictions on ADO. NET Excel operations

Restrictions on ADO. NET Excel operations

Restrictions on ADO. NET Excel operations:

1. The cell format cannot be set. inserting a new row will automatically inherit the format of the previous row.

? 2. You cannot use ADO. NET to insert formulas into cells.

?

3. the Jet ole db provider cannot provide primary key/index information for tables in the Excel Workbook. Therefore, you cannot use CommandBuilder to automatically update and insert records in an Excel Workbook.

// Connection string

String xlsPath = @ "d: \ Path \ FileName. XLS"; // absolute physical Path

String connStr = "Provider = Microsoft. Jet. OLEDB.4.0;" + "Extended Properties = Excel 8.0;" + "data source =" + xlsPath;

// Query statement

String SQL = "SELECT * FROM [Sheet1 $]";

DataSet ds = new DataSet ();

OleDbDataAdapter da = new OleDbDataAdapter (SQL, connStr );

Da. Fill (ds); // Fill in DataSet

Note that:

1. When the data provider uses Jet, you must specify the Extended Properties keyword to set a specific Excel attribute. Different Versions of Excel correspond to different attribute values: a valid Excel version for the Extended Properties value.

For Microsoft Excel 8.0 (97), 9.0 (2000), and 10.0 (2002) workbooks, use Excel 8.0.

For Microsoft Excel 5.0 and 7.0 (95) workbooks, use Excel 5.0.

For Microsoft Excel 4.0 workbooks, use Excel 4.0.

For Microsoft Excel 3.0 workbooks, use Excel 3.0.

Ref: http://msdn.microsoft.com/library/chs/default.asp? Url =/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldata1_dataset. asp

2. Use the physical absolute path (same as Access) for the Data Source Path)

3. How to reference a table name?

To reference the fully used worksheet range, specify the worksheet name followed by the dollar sign. For example:

Select * from [Sheet1 $]

To reference a specific address range on a worksheet, specify the worksheet name that follows the dollar sign and the range. For example:

Select * from [Sheet1 $ A1: B10]

To reference a specified range, use the name of the range. For example:

Select * from [MyNamedRange]

Ref: http://msdn.microsoft.com/library/chs/default.asp? Url =/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldata1_dataset. asp

(Among various objects provided by relational databases (tables, views, stored procedures, etc.), Excel Data sources only provide objects equivalent to tables, it consists of the worksheet in the specified workbook and the defined naming area. The naming area is regarded as "table", while the worksheet is regarded as "system table ")

When you reference a worksheet name ([Sheet1 $]), the data provider determines that the data table starts from a non-empty cell at the top left of the specified worksheet. For example, if the worksheet is empty from row 3rd, column C, row 3rd, column C, and all rows 1st and 2, only data starting from row 3rd and column C is displayed; end with a non-empty unit in the maximum range of the last table. Therefore, to precisely read the range, use the namespace [NamedRange] or specify the address [Sheet1 $ A1: C10].

4. How do I reference a column name?

Based on the default connection string, the data provider uses the first row in the valid region as the column name. If a cell in this row is empty, it is expressed by F1 and F2, where the ordinal number, consistent with the cell position, starting from 1;

If you want to display the first row as the data, rather than the column name, you can specify: HDR = NO in the Extended Properties attribute of the connection string.

The format is as follows:

String connStr = "Provider = Microsoft. Jet. OLEDB.4.0;" +

"Extended Properties =" Excel 8.0; HDR = NO ";" + "data source =" + xlsPath;

In this case, all column names start with F and then follow the index, starting with F1, F2, F3 ......

Note: For Excel 8.0; HDR = NO, double quotation marks are required (here, the backslashes are escape characters in C)

Ref: ms-help: // MS. VSCC. v80/MS. MSDN. v80/MS. VisualStudio. v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm

5. Why is the valid cell data not displayed?

The possible cause of this situation is that, in the default connection, the data provider infers the Data Types of subsequent units based on the previous cells.

You can specify IMEX = 1 in Extended Properties.

"IMEX = 1;" Notification drivers always read "hybrid" Data columns as text

The format is as follows:

String connStr = "Provider = Microsoft. Jet. OLEDB.4.0;" +

"Extended Properties =" Excel 8.0; HDR = NO; IMEX = 1 ";" + "data source =" + xlsPath;

Ref: Same as 4

PS: in the case of baidu, some netizens said that adding quotation marks to each unit is certainly a good solution, but the workload is huge, and it is not too busy, fortunately, I found the cure:

More ref:

How to use ADO in Visual Basic or VBA to process Excel Data

Applications often need to interact with Excel data. The above describes the basic methods and skills for reading Excel Based on ADO. NET. Now we want to introduce how to dynamically read Excel data. Here, the dynamic content refers to the structure of an Excel file that we don't know in advance, or that it cannot be predicted, such as one. The number of sheets in the xls file, and the structure of each sheet may be different.

In fact, we can dynamically construct query statements by obtaining the "Architecture Information" of Excel. The "Architecture Information" here is the same as the "database architecture information" in the database field (also known as "metadata"). For the entire database, these "metadata" usually includes directories and existing constraints obtained from databases or data sources, tables, and views in the database. For tables in the database, the architecture information includes the primary key, column, and automatic number fields.

Mentioned above

Among the various objects provided by relational databases (tables, views, stored procedures, etc.), the Excel Data Source only provides objects equivalent to tables, which are composed of worksheets in the specified workbook and named areas defined. The naming area is regarded as "table", while the worksheet is regarded as "system table ")

Here we treat Excel as a "Database", and then use OleDbConnection. GetOleDbSchemaTable Method

To get the required schema information, the schema information obtained by this method is compatible with the ANSI SQl-92: ref: http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx? Mfr = true

The following is a program snippet that reads the "table" definition metadata in the Excel file and displays it:

// Read Excel Data and fill in DataSet

String SQL _F = "SELECT * FROM [{0}]";

OleDbConnection conn = null;

OleDbDataAdapter da = null;

DataTable tblSchema = null;

IList <string> tblNames = null;

Conn = new OleDbConnection (connStr); // initialize the connection

Conn. Open (); // Open

TblSchema = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, new object [] {null, "TABLE"}); // get the TABLE definition metadata of the Data Source

GetOleDbSchemaTable

TblNames = new List <string> ();

Foreach (DataRow row in tblSchema. Rows ){

TblNames. Add (string) row ["TABLE_NAME"]); // read Table Name

}

Da = new OleDbDataAdapter (); // initialize the adapter

DataSet ds = new DataSet (); // prepare data and import DataSet

Foreach (string tblName in tblNames ){

Da. SelectCommand = new OleDbCommand (String. Format (SQL _F, tblName), conn );

Try {

Da. Fill (ds, tblName );

}

Catch {

If (conn. State = ConnectionState. Open ){

Conn. Close (); // Close the connection

}

Throw;

}

}

If (conn. State = ConnectionState. Open ){

Conn. Close (); // Close the connection

}

// Process (Omitted) each sheet imported into the DataSet)

Here, we do not need to perform "Hard encoding" on the SELEC statement. We can dynamically construct the "table name" of the FROM statement as needed ".

You can obtain not only the table name, but also the field name and field type of each table:

TblSchema = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Columns, new object [] {null, null });

In ADO. nET 1.x only OleDb provides the GetOleDbSchemaTable method, while SqlClient or OrcaleClient does not have a corresponding method, because the corresponding database already provides similar functions for access by stored procedures or system table supply programs, for example, for SQL Server:

SELECT * FROM Northwind. INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = N 'customer'

In ADO. NET 2.0, each xxxConnenction implements the GetSchemal method of the base class System. Data. Common. DbConnection.

To obtain the schema information of the data source.

Asp.net/c?#oledboperation excelfile [BugEyes published on 15:58:00]

Recommendation

1. Environment configuration: Add using System. OleDb

2. Compile common functions for connecting and operating excel files

Protected void DoOleSql (string SQL, string database)

{

OleDbConnection conn = new OleDbConnection ();

Conn. connectionString = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ Server. mapPath ("\") + database + "; Extended Properties = 'excel 8.0; HDR = no; IMEX = 0 '";

Try

{// Open the connection

Conn. Open ();

}

Catch (Exception e)

{

Response. Write (e. ToString ());

}

OleDbCommand olecommand = new OleDbCommand (SQL, conn );

Try

{// Execute the statement

Olecommand. ExecuteNonQuery ();

}

Catch (Exception eee)

{

Response. Write (eee. ToString ());

Conn. Close ();

}

Finally

{

Conn. Close (); // Close the database

}

Conn. Close ();

}

Note: 1) when using an Excel Workbook, the first row in the area is the title line (or field name) by default ). If the first region does not contain a title, you can specify HDR = NO in the extended attributes of the connection string. if you specify HDR = NO in the connection string, the Jet ole db Provider will automatically name the field for you (F1 indicates the first field, F2 indicates the second field, and so on ); 2) IMEX = 1 read all data as characters. For other values (0, 2), see the relevant help documentation. 3) if the error "unable to find the installable isam" occurs, generally, the connection string is incorrect.

3. read data from an excel file

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

DoOleSql (SQL, "test.xls ");

4. Update data in an excel file

String SQL = "update [sheet1 $] set FieldName1 = '000000' where FieldName2 = 'b3 '";

DoOleSql (SQL, "test.xls ");

5. Insert data into an excel file

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

DoOleSql (SQL, "test.xls ");

6. delete data in an excel file: This method is not recommended.

7. For non-standard excel tables, you can specify the sheet range in excel.

1) read data: string SQL = "select * from [sheet1 $ A3: F20]";

2) update data: string SQL = "update [sheet1 $ A9: F15] set FieldName = '000000' where AnotherFieldName = 'b3 '";

3) insert data: string SQL = "insert into [sheet1 $ A9: F15] (FieldName1, FieldName2 ,...) Values ('A', 'B ',...) ";

4) delete data: Not recommended

Note: 1) the code can be modified as needed; 2) 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. 3) if the "cannot expand the selected range" error occurs, it may be that the "range" referenced by the excel file is incorrect.