ADO. NET Access EXCEL (learning notes)

// 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 $]";

 

 

 

1. When the data provider uses Jet, you must specify the Extended Properties keyword to set specific Excel attributes. Different Versions of Excel correspond to different attribute values.
2. Use the physical absolute path for the Data Source Path
3. How to 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]