Asp.net Excel file operation instance

Share an article about. net connection operations for excel databases and data reading operations. If you have any need, refer to this example.

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 indicates the version number. Microsoft Jet 4.0 ole db Provider supports the Excel 3.0, 4.0, 5.0, and 8.0 database types. That is to say, it cannot connect to an Excel 2007 file. For an Excel 2007 file, you can save it as 97-2003.

HDR = NO indicates that the Excel area does not contain the title, that is, the first row of the area is the data row. In this case, F1 indicates the first field, F2 indicates the second field, and so on.

There are still many details about using this database-like method to Operate Excel, which will be described in the following articles.

Note:

Excel is also an attribute file-type database. release resources after use.

Instance operations

 

The 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 the Data Type

If no data type is specified for the cell in Excel, OLEDB obtains the Data Type of the current cell by guessing. By default, the Data Type of a cell is predicted by scanning the first 8 rows of the current row. To change the default number of scanned rows, specify the corresponding value for MAXSCANROWS in the connection string extension attribute.

If we specify the cell data type as a number, the data is automatically converted to the number type after being written to Excel, even if it is inserted as a string in an SQL statement, for example: set F18 = '000000 '.