How to read EXCEL documents using ADO. NET

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 possible is as follows:

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

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

The Code is as follows:

Copy code

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

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. In x, only OleDb provides GetOleDbSchemaTable
Method, while SqlClient or OrcaleClient does not have a corresponding method, because the corresponding database has provided similar functions for access by stored procedures or system table supply applications, such
On 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.