Excel is used in ASP. NET: 12) Other methods for reading Excel Data (complete)

In addition to directly accessing the cell to read data, you can also use OleDBAdapter to read Excel worksheet data. Note that it is worksheet data. This method can be used to obtain all worksheet datasets at a time.

When using Excel 2003, note the single quotation marks in the connection string:

using System.Data;using System.Data.OleDb;...OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + sheetname + "$]",                                                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile +                                                 ";Extended Properties='Excel 8.0;HDR=NO;IMAX=1;';");System.Data.DataTable table = new System.Data.DataTable();adapter.Fill(table);

Use Excel 2007:

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + sheetname + "$]",                                                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";" +                                                "Extended Properties=/"Excel 12.0;HDR=NO;IMEX=1;/"");DataSet ds = new DataSet();adapter.Fill(ds);

The HDR in the connection string indicates that the first line is displayed as data, not the column name.
Column name definition:

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

You generally do not want to see this column name drop!

For more detailed instructions on using OleDB, refer to the following link for more details:

Http://bbs.51cto.com/archiver/tid-444055.html

OK, my work is finished, and the rest is yours. To encourage me to write better articles, please give a comment. You can also write to igame2000@hotmail.com to contact me. I'm igame.