Asp. Net read Execl FAQ collection

It is often necessary to export data between the database and Execl. In the 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 possibilities are as follows: (asp.net environment)
[Csharp]
// 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 ();

Everything is like operating a database, but note the following:
1. When the data provider uses Jet, you must specify the Extended Properties keyword to set specific Excel Properties. Different Versions of Excel correspond to different attribute values: the valid Excel version for the Extended 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/vbtskcodeexamplereadingexceldata1_dataset. 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 reference the fully used worksheet range, specify the worksheet name followed by the dollar sign. For example:
Select * from [Sheet1 $]
To reference a specific address range on a worksheet, specify the worksheet name that follows the dollar sign and the range. For example:
Select * from [Sheet1 $ A1: B10]
To reference a 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/vbtskcodeexamplereadingexceldata1_dataset. asp
Note:
You can reference three objects in an Excel Workbook:
• Full Worksheet: [Sheet1 $], and Sheet1 is the worksheet name
• Named cell area on a worksheet: [MyNamedRange] (you do not need to specify a worksheet because the entire xls naming area can only be unique)
XLS naming method: select the cell range, insert, name, and definition
• Untitled cell area on the worksheet: [Sheet1 $ A1: B10]
(Among various objects provided by relational databases (tables, views, stored procedures, etc.), Excel Data sources only provide objects equivalent to tables, it consists of the worksheet in the specified workbook and the defined naming area. The naming area is regarded as a "table", while the worksheet is regarded as a "system table ")
Note:
• [] (Square brackets) must be used. If not, the following message is displayed:
FROM clause syntax error
• It must be followed by $ (dollar sign); otherwise, it is reported as follows:
The Microsoft Jet Database Engine cannot find the object 'sheet2 '. Check whether the object exists and write out its name and path correctly.
• If the worksheet name is incorrect or does not exist, the following message is displayed:
'Sheet2 $ 'is not a valid name. Make sure it does not contain invalid characters or punctuation and the name is not too long.
• Mentioned in how to use ADO in Visual Basic or VBA to process Excel Data
~ And '(wavy lines and single quotes) instead of [], use ADO. NET test failed, report:
FROM clause syntax error
• When the worksheet name ([Sheet1 $]) is referenced, the data provider determines that the data table starts from a non-empty cell at the top left of the specified worksheet. For example, if the worksheet is empty from row 3rd, column C, row 3rd, column C, and all rows 1st and 2, only data starting from row 3rd and column C is displayed; end with a non-empty unit in the maximum range of the last table;
• To precisely read the range, use the namespace [NamedRange] or specify the address [Sheet1 $ A1: C10].
4. How do I reference a column name?
• 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;
• If you want the first row to be displayed as data rather than column names, you can specify: HDR = NO in the Extended Properties attribute of the connection string.
The default value is HDR = NO. format:
String connStr = "Provider = Microsoft. Jet. OLEDB.4.0;" +
"Extended Properties =" "Excel 8.0; HDR = NO" ";" +
"Data source =" + xlsPath;
Note: For Excel 8.0; HDR = NO, double quotation marks are required (here, the backslashes are escape characters in C)
Ref: ms-help: // MS. VSCC. v80/MS. MSDN. v80/MS. visual Studio. v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm in "connect to Excel" section (Note: In my own MSDN, it uses two double quotation marks as an error. The test failed, the original Article says:
Note: Double quotation marks required for Extended Properties must also be added.
)
In this case, all column names start with F and then follow the index, starting with F1, F2, F3 .......
5. Why is the valid cell data not displayed?
The possible cause of this situation is that, in the default connection, the data provider infers the Data Types of subsequent units based on the previous cells.
You can specify IMEX = 1 in Extended Properties.
"IMEX = 1;" notifies the driver to always read the "hybrid" data column as text
Ref: Same as 4
PS: in the case of baidu, some netizens said that adding quotation marks to each unit is certainly a good solution, but the workload is huge, and it is not too busy, I am glad to find the cure prescription.
 
More ref:
How to use ADO in Visual Basic or VBA to process Excel Data
Http://dotnet.aspx.cc/ShowDetail.aspx? C673E2CD-3F1E-4919-8CE0-D69B894A0599
Note:
After adding the HDR or IMEX attribute values, many people on the Internet will report the error "unable to find the installable ISAM. The solution is simple:
The connection string is written as follows:
Provider = Microsoft. Jet. Oledb.4.0; Data Source = {0}; Extended Properties = 'excel 8.0; HDR = 1; IMEX = 1; '(note the single quotation marks)
Instead of writing it like this:
Provider = Microsoft. Jet. Oledb.4.0; Data Source = {0}; Extended Properties = Excel 8.0; HDR = 1; IMEX = 1;