C # FAQs and solutions for accessing EXCEL files through database operations

In addition to common databases such as SQL server and access, Excel files can also be accessed as databases.
Ado.net:
OleDbConnection cn = new OleDbConnection ();
Cn. ConnectionString = @ "provider = Microsoft. jet. oledb.4.0; data source = [excel file path]; Extended Properties =" "Excel 8.0; HDR = YES ;""";
Cn. Open ();
OleDbDataAdapter oda = new OleDbDataAdapter ("select * from [Sheet1 $]", cn );
DtCode = new DataSet ();
Oda. Fill (dtCode );
DataGridView1.DataSource = dtCode. Tables [0]. DefaultView;
Note:
1. The workbook name in EXCEL is used as the table name. For example, if Sheet1 is written as [Sheet1 $], [] and $, neither can be omitted.
2. The access mode is oledb, and provider = Microsoft. jet. oledb.4.0, especially Extended Properties = "" Excel 8.0; HDR = YES; "" cannot be omitted. If this mode is omitted, an error occurs. HDR = YES indicates that the first row of data in an excel table is used as the field name of the table. The actual data starts from the second row.
I encountered two problems when I was doing this for the first time.
(1) The message "unable to find the installable ISAM" is displayed"
This is because the connection string lacks Extended Properties = "" Excel 8.0; HDR = YES.
(2) The message "the format of the initialization string does not conform to the specification at the beginning of index 103"
This is caused by incorrect connection string writing. Please refer to the correct format. The link string corresponding to the error prompt is as follows:
"Provider = Microsoft. jet. oledb.4.0; data source = [excel file path]; Extended Properties =" "Excel 8.0; HDR = YES ;"
The error is displayed because the value of Extended Properties is enclosed by "", and the parentheses on the right are lost, resulting in non-compliance.