asp.net Excel file action instance

The code is as follows	Copy Code
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=excel file location; Extended properties= ' Excel 8.0; Hdr=no; '

In Extended Properties, Excel 8.0 is represented as the version number, and Microsoft Jet 4.0 OLE DB Provider supports the Excel 3.0,4.0,5.0,8.0 database type. That means it can't connect to Excel 2007 files, and for Excel 2007 files, you can save them in 97-2003 format.

Hdr=no indicates that the Excel area does not contain a caption, that is, the first row of the range is the data row, where F1 is used to represent the first field, the second field with F2, and so on.

Using this database-like approach to Excel also has a number of detailed techniques that will be covered in later articles.

Attention

Excel also attributes a file-style database, paying attention to releasing resources after use.

Instance operations

code is as follows

copy code

String sql = @ "SELECT * FROM [sheet1$]"; OleDbConnection conn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath ("Foo.xls") + ";" + "Extended properties= ' Excel 8.0;" Hdr=no; ' "); Conn. Open (); OleDbCommand cmd = new OleDbCommand (SQL, conn); OleDbDataReader reader = cmd. ExecuteReader (); while (reader. Read ()) { Response.Write (reader. GetString (0) + "<br/>"); } Reader. Close (); Reader. Dispose (); Cmd. Dispose (); Conn. Close (); Conn. Dispose ();

Note Data types

If you do not specify the type of data for the cell in Excel, then OLE DB guesses to get the data type of the current cell. The default is to scan the 8 rows before and after the current row to guess the data type of the cell. To change the default number of scan rows, specify the corresponding value for MAXSCANROWS in the connection string extension property.

If we specify the type of cell data as a number, it automatically becomes a numeric type when written to Excel, even if it is inserted as a string in an SQL statement, such as: Set f18= ' 832 '.