How to read excel from ado.net

How to read excel from ado.net

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. When the data provider uses Jet, you must specify the Extended Properties keyword to set specific Excel Properties. Different Versions of Excel correspond to different attribute values: the 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:

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: You can reference three objects in an Excel Workbook:

* Whole 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, the following message is reported:

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.

* You can use ADO to process Excel Data in Visual Basic or VBA.

~ And '(wavy lines and single quotes) instead of [], use ADO. NET test failed, report:

FROM clause syntax error

* When the worksheet name is [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, if you need 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 data, rather than the column name, 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

Applications often need to interact with Excel data. The above describes the basic methods and skills 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.

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 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.

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 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:

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 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 ();

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.