Summary of FAQs about reading Excel from ADO. net

From: http://www.zzchn.com/edu/20080220/78435.shtml

It is often necessary to export data between the database and execl. In the Net Era, ADO. Net can use Microsoft. Jet. oledb to access excel. There are already many similar resources on the Internet. The most typical and simplest possibilities are as follows: (Asp.net environment)

// Connection string
String xlspath = server. mappath ("~ /App_data/somefile.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

// Operate the data in Dataset

// Output, bind data
Gridview1.datasource = Ds. Tables [0];
Gridview1.databind ();
Is it easy ?! Everything is like operating a database, but note the following:
1. Data Provision Program When using 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 extended properties values.
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. The Data Source Path uses the physical absolute path (same as access)

3. How do I reference a table name?
A valid reference to a table (or range) in an Excel Workbook.
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
Note:
You can reference three objects in an Excel Workbook:
• Full Worksheet: [sheet1 $], and sheet1 is the worksheet name
• Named cell area on a worksheet: [mynamedrange] (you do not need to specify a worksheet because the entire XLS naming area can only be unique)
XLS naming method: select the cell range, insert, name, and definition
• Untitled cell area on the worksheet: [sheet1 $ A1: B10]
(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 a "table", while the worksheet is regarded as a "system table ")

Note:
• [] (Square brackets) must be used. If not, the following message is displayed:
From clause syntax error
• It must be followed by $ (dollar sign); otherwise, it is reported as follows:
The Microsoft Jet Database Engine cannot find the object 'sheet2 '. Check whether the object exists and write out its name and path correctly.
• If the worksheet name is incorrect or does not exist, the following message is displayed:
'Sheet2 $ 'is not a valid name. Make sure it does not contain invalid characters or punctuation and the name is not too long.
• Mentioned in how to use ADO in Visual Basic or VBA to process Excel Data
~ And '(wavy lines and single quotes) instead of [], use ADO. Net test failed, report:
From clause syntax error
• When the worksheet name ([sheet1 $]) is referenced, 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;
• 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 the first row to be displayed as data rather than column names, you can specify: HDR = no in the extended properties attribute of the connection string.
The default value is HDR = No. format:

String connstr = "provider = Microsoft. Jet. oledb.4.0;" +
"Extended properties =" "Excel 8.0; HDR = no" ";" +
"Data Source =" + xlspath;
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. visual Studio. v80.chs/wd_adonet/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm in "connect to excel" section (Note: In my own msdn, it uses two double quotation marks as an error. The test failed, the original Article says:

Note: Double quotation marks required for extended properties must also be added.
)

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

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;" notifies the driver to always read the "hybrid" data column as text
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, I am glad to find the cure prescription.

More Ref:
How to use ADO in Visual Basic or VBA to process Excel Data
Http://dotnet.aspx.cc/ShowDetail.aspx? C673E2CD-3F1E-4919-8CE0-D69B894A0599

Note:
After adding the HDR or IMEX attribute values, many people on the Internet will report the error "unable to find the installable isam. The solution is simple:
The connection string is written as follows:

Provider = Microsoft. Jet. oledb.4.0; Data Source = {0}; extended properties = 'excel 8.0; HDR = 1; IMEX = 1; '(note the single quotation marks marked in red)

Instead of writing it like this:

Provider = Microsoft. Jet. oledb.4.0; Data Source = {0}; extended properties = Excel 8.0; HDR = 1; IMEX = 1;

Applications often need to interact with Excel Data. Article Ado. Net: how to read Excel (I) describes the basic methods and techniques for reading Excel Based on ADO. net. The number of sheets in the sheet 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.
In ADO. net, how to read data from Excel (I) mentioned

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 a "table", while the worksheet is regarded as a "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:

Note: For those unfamiliar with the ole db architecture row set, they are basically a standardized architecture built by the database defined by the ANSI SQL-92. Each schema row set has a set of columns that provide the definition metadata for the specified structure (referred to as the "restriction column" in the. NET document "). In this way, if the Request Architecture Information (for example, the architecture information of the column or the Architecture Information of the sorting rule), you will know the type of data that can be obtained. For more information, visit Appendix B: schema rowsets.
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
// Connection string
String xlspath = server. mappath ("~ /App_data/somefile.xls ");
String connstr = "provider = Microsoft. Jet. oledb.4.0;" +
"Extended properties =" "Excel 8.0; HDR = no; IMEX = 1" ";" + // specify the extension property as Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002), and the first row is returned as data and read as text
"Data Source =" + xlspath;
String SQL _f = "select * from [{0}]";

Oledbconnection conn = NULL;
Oledbdataadapter da = NULL;
Datatable tblschema = NULL;
Ilist <string> tblnames = NULL;

// Initialize the connection and open it
Conn = new oledbconnection (connstr );
Conn. open ();

// Obtain the table definition metadata of the Data Source
// Tblschema = conn. getschema ("tables ");
Tblschema = conn. getoledbschematable (oledbschemaguid. Tables, new object [] {null, "table "});

Gridview1.datasource = tblschema;
Gridview1.databind ();

// Close the connection
Conn. Close ();
For details about the getoledbschematable method, refer:
Http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

Next is a piece of program that uses the "Architecture Information" to dynamically read the forms or named areas defined in Excel:


// Read Excel Data and fill in Dataset
// Connection string
String xlspath = server. mappath ("~ /App_data/somefile.xls ");
String connstr = "provider = Microsoft. Jet. oledb.4.0;" +
"Extended properties =" "Excel 8.0; HDR = no; IMEX = 1" ";" + // specify the extension property as Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002), and the first row is returned as data and read as text
"Data Source =" + xlspath;
String SQL _f = "select * from [{0}]";

Oledbconnection conn = NULL;
Oledbdataadapter da = NULL;
Datatable tblschema = NULL;
Ilist <string> tblnames = NULL;

// Initialize the connection and open it
Conn = new oledbconnection (connstr );
Conn. open ();

// Obtain the table definition metadata of the Data Source
// Tblschema = conn. getschema ("tables ");
Tblschema = conn. getoledbschematable (oledbschemaguid. Tables, new object [] {null, "table "});

// Gridview1.datasource = tblschema;
// Gridview1.databind ();

// Close the connection
// Conn. Close ();

Tblnames = new list <string> ();
Foreach (datarow row in tblschema. Rows ){
Tblnames. Add (string) Row ["table_name"]); // read Table Name
}

// Initialize the adapter
DA = new oledbdataadapter ();
// Prepare data and import Dataset
Dataset DS = new dataset ();

Foreach (string tblname in tblnames ){
Da. selectcommand = new oledbcommand (string. Format (SQL _f, tblname), Conn );
Try {
Da. Fill (DS, tblname );
}
Catch {
// Close the connection
If (conn. State = connectionstate. Open ){
Conn. Close ();
}
Throw;
}
}

// Close the connection
If (conn. State = connectionstate. Open ){
Conn. Close ();
}

// Process each sheet imported into the dataset
// Display only
Gridview1.datasource = Ds. Tables [0];
Gridview1.databind ();

Gridview2.datasource = Ds. Tables [1];
Gridview2.databind ();

// More codes
//.

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 also obtain the field names and Field Types in 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.
Http://msdn2.microsoft.com/zh-cn/library/system.data.common.dbconnection.getschema.aspx