How To Connect to Excel

2年前 (2022) 程序员胖胖胖虎阿
186 0 0

 

 

How To Connect to Excel Excel  
This is a compiled connection strings reference list on how to connect to Excel.

ODBC

 
Standard
   
 
How To Connect to ExcelDriver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

 

OLE DB
 
Standard
   
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++   \"
VB6, VBScript   ""
xml (web.config etc)   "
or maybe use a single quota '.
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

Sarah Ding Note:

How To Connect to Excel"IMEX=1;" tells the driver to always read "intermixed" data columns as text 

 

版权声明:程序员胖胖胖虎阿 发表于 2022年9月16日 上午11:00。
转载请注明:How To Connect to Excel | 胖虎的工具箱-编程导航

相关文章

暂无评论

暂无评论...