ADO read Excel's implementation code ((C #))

Connection string

// 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;
          // Check for phrases
          string sql = "SELECT * FROM [Sheet1 $]";
          DataSet ds = new DataSet ();
          OleDbDataAdapter da = new OleDbDataAdapter (sql, connStr);
          da.Fill (ds); // Fill the DataSet

          // Operate the data in the DataSet here

          // Output, bind data
          GridView1.DataSource = ds.Tables [0];
          GridView1.DataBind ();

Is it simple?! It's like manipulating databases, just to be aware that:
1. The data provider uses jet and needs to specify the Extended properties keyword to set Excel-specific attributes, and different versions of Excel correspond to different property values: A valid version of Excel for Extended the 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/ Vbtskcodeexamplereadingexceldataintodataset.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 refer to the scope of a fully used worksheet, specify a sheet name followed by a dollar sign. For example:

SELECT * FROM [sheet1$]
To refer to a specific range of addresses on a worksheet, specify the name of the sheet followed by the dollar sign and the range. For example:

SELECT * FROM [SHEET1$A1:B10]
To reference the 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/ Vbtskcodeexamplereadingexceldataintodataset.asp
Description
You can refer to three types of objects in an Excel workbook:
• Entire sheet: [sheet1$], Sheet1 is the name of the worksheet
• Named cells range on worksheet: [Mynamedrange] (no worksheet required because the named range can only be unique in the entire XLS)
XLS naming method: Select range of cells insert name definition
• Unnamed range of cells on a worksheet: [SHEET1$A1:B10]
(in a variety of objects provided by a relational database (tables, views, stored procedures, and so on), the Excel data source provides only objects that are equivalent to tables, consisting of worksheets in the specified workbook and named ranges defined. Named ranges are considered "tables" and worksheets are considered "system tables"

Attention:
• must use [] (square brackets), no will be reported:
FROM clause syntax error
• Must be followed by $ (dollar sign), otherwise reported:
The Microsoft Jet database engine could not find the object ' sheet2′. Please determine if the object exists and write out its name and path correctly.
• If the worksheet name is incorrect or does not exist, it will be reported:
' sheet2$ ' is not a valid name. Make sure that it does not contain invalid characters or punctuation, and that the name is not too long.
• In how to use ADO in Visual Basic or VBA to work with Excel data, mention that you can use
~ and ' (wavy lines and single quotes) instead of [], using ADO. NET test did not succeed, reported:
FROM clause syntax error
• When referencing the work name ([sheet1$]), the data provider considers the data table to start with a non-empty cell at the top left of the specified sheet. For example, the worksheet starts at line 3rd, column C, line 3rd, column C, and 1th and 2 are all empty, only the data starting at row 3rd and column C will be displayed, ending with a non-empty cell in the maximum range of the final table;
• Therefore, you should use a named range [NamedRange] or specify an address if you need to read the scope exactly: [SHEET1$A1:C10]

4. How do I reference a column name?
• Depending on the default connection string, the data provider will use the first row in the valid range as the column name, or F1, F2 if the row is empty, where the ordinal is consistent with the cell position, starting with 1;
• If you want the first row to appear as data instead of column names, you can specify the Extended properties property on the connection string: Hdr=no
The default value is: The Hdr=no format is as follows:

String connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended properties=\" Excel 8.0; Hdr=no\ ";" +
"Data source=" + Xlspath;
Note: Excel 8.0; Hdr=no need to use double quotes (here's the backslash, which is escaped in C #)

Ref
Ms-help://ms. Vscc.v80/ms. Msdn.v80/ms. Visualstudio.v80.chs/wd_adonet/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm
In the "Connecting to Excel" section (Note: In my own MSDN, it's an example using two double quotes is wrong, the test did not pass, the original said:

Note that the double quotes required for the Extended Properties must also be enclosed in double quotes.
）

In this case, all the column names start with F, followed by the index, starting with F1, f2,f3 ....

5. Why does the valid cell data not show up?
This may occur because, in the default connection, the data provider infers the data type of subsequent cells based on the preceding cell.
Imex=1 can be specified through the Extended Properties

"Imex=1;" Notifies the driver that the "mixed" data column is always read as text
Ref: Same 4

PS: In the Baidu this issue, there are netizens said, will each unit with quotation marks, this is a lattice program, but how much work, but not a job, glad to find a "cure remedy"

More ref:
How to use ADO in Visual Basic or VBA to work with Excel data
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599

Should
The program often requires data interaction with Excel, which is explained by the ADO-
Basic methods and techniques for reading Excel. Now to introduce how to read the Excel data dynamically, here is the dynamic refers to the first do not know the Excel file is what kind of structure, or can not pre-
such as how many sheet are in an. xls file, and the structure of each sheet may be different and so on.
In fact, we can get the "schema information" of Excel to dynamically
Constructs a query statement. The "schema information" here has the same meaning as "database schema information" in the database domain (also known as "metadata"), and for the entire database, these "metadata" typically includes a database or a data
Data sources in a database, tables and views, and constraints, and for tables in a database, schema information includes primary keys, columns, and AutoNumber fields.
In the above mentioned

In various objects provided by a relational database (tables, views, stored procedures, and so on), the Excel data source provides only objects that are equivalent to tables, which consist of worksheets and defined named ranges in the specified workbook. Named ranges are considered "tables" and worksheets are considered "system tables"

Here we treat Excel as a "database" and then use the OleDbConnection.GetOleDbSchemaTable method
To obtain the required schema information, the method obtains schema information that is compatible with ANSI SQl-92:

Note
Meaning: For those unfamiliar with the OLE DB schema rowsets, they are essentially ANSI SQL-92
Defines a standardized schema for database construction. Each schema rowset has a set of columns that provide definition metadata for the specified construct, called. NET
Limit columns in the document). That way, if you request schema information (for example, the schema information for a column or the schema information for a collation), you know exactly what kind of data you can get. If you would like more information
Please visit Appendix B:schema rowsets.
Ref:http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true

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

// Read Excel data and fill 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 extended properties as
 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002), and the first line 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
         conn = new OleDbConnection (connStr);
         conn.Open ();

         // Get the table definition metadata of the data source
         // tblSchema = conn.GetSchema ("Tables");
         tblSchema = conn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object [] {null, null, null, "TABLE"});

         GridView1.DataSource = tblSchema;
         GridView1.DataBind ();

         // close the connection
         conn.Close ();
 For a detailed description of the GetOleDbSchemaTable method, please refer to:
 http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

 Next is a program fragment that uses "architecture information" to dynamically read the form or named area defined in Excel:

         // Read Excel data and fill 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 extended properties as
 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002), and the first line 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
         conn = new OleDbConnection (connStr);
         conn.Open ();

         // Get the table definition metadata of the data source
         // tblSchema = conn.GetSchema ("Tables");
         tblSchema = conn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object [] {null, null, 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, 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
         // only display here
         GridView1.DataSource = ds.Tables [0];
         GridView1.DataBind ();

         GridView2.DataSource = ds.Tables [1];
         GridView2.DataBind ();

Here we do not need to "hard code" the Selec statement, you can dynamically construct the "table name" from the sentence as needed.

Not only can you get an indication that you can also get information about the field name, field type, and so on in each table:

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

At ADO
At 1.x, only OLE DB provides getoledbschematable
method, and SqlClient or orcaleclient do not have a corresponding method, because the corresponding database has provided a similar function of the stored procedures or system tables for application access, such as the
For SQL Server:

SELECT *
From Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = N ' Customers '

In ADO 2.0, each Xxxconnenction implements the Getschemal method of the base class System.Data.Common.DbConnection.
To obtain schema information for the data source.
Http://msdn2.microsoft.com/zh-cn/library/system.data.common.dbconnection.getschema.aspx

//---------------------------------------------------------------------------
IMEX the right way to write
String connexcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + FilePath + "; Extended properties= ' Excel 8.0;imex=1; Hdr=no;imex=1

using System;
 using System.Data;
 using System.Configuration;
 using System.Web;
 using System.Web.Security;
 using System.Web.UI;
 using System.Web.UI.WebControls;
 using System.Web.UI.WebControls.WebParts;
 using System.Web.UI.HtmlControls;

 using System.Data.SqlClient;
 using System.Data.OleDb;

 public partial class _Default : System.Web.UI.Page 
 {
     protected void Page_Load(object sender, EventArgs e)
     {

     }
     protected void Button1_Click(object sender, EventArgs e)
     {

         string strconn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"D:/last.xls" + ";Extended Properties=Excel 8.0;"; //HDR=no";//IMEX=1
         OleDbConnection conn = new OleDbConnection(strconn);
         DataSet myset = new DataSet();
         try
         {
             conn.Open();

 
             string mysql = "select * from [Sheet1$] ";//where chs <> ''

             OleDbDataAdapter aper = new OleDbDataAdapter(mysql, conn);

             myset.Tables.Clear();
             aper.Fill(myset, "book");

             conn.Close();

             GridView1.DataSource = myset.Tables["book"];
             GridView1.DataBind();
         }
         catch (Exception ex)
         {
             conn.Close();
             this.lb_msg.Text = ex.Message;
             return;
             // return ex.Message;
         }
     }
 }