Java & Netbeans

Connection strings for Excel
Providers for Excel

» Microsoft Jet OLE DB 4.0 » ACE OLEDB 12.0 » .NET Framework Data Provider for OLE DB (OleDbConnection) » Microsoft Excel ODBC Driver » .NET Framework Data Provider for ODBC (OdbcConnection) » .NET xlReader for Microsoft Excel (ExcelConnection)

Microsoft Jet OLE DB 4.0
Type: OLE DB Provider
Usage: Provider=Microsoft.Jet.OLEDB.4.0
Manufacturer: Microsoft

Standard
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
How to Use JET in 64 bit environments >>

Standard alternative
Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.
OLEDB;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 [Column Name One], [Column Name Two] FROM [Sheet One$]". 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. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

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."
A workaround for the "could not decrypt file" problem

ACE OLEDB 12.0
Type: OLE DB Provider
Usage: Provider=Microsoft.ACE.OLEDB.12.0
Manufacturer: Microsoft

Excel 97-2003 Xls files with ACE OLEDB 12.0
You can use this connection string to use the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties="Excel 12.0;HDR=YES";
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

.NET Framework Data Provider for OLE DB
Type: .NET Framework Wrapper Class Library
Usage: System.Data.OleDb.OleDbConnection
Manufacturer: Microsoft

Bridging to Jet OLE DB 4.0
This is just one connection string sample for the wrapping OleDbConnection class that calls the underlying OLEDB provider. See respective OLE DB provider for more connection strings to use with this class.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";


Microsoft Excel ODBC Driver
Type: ODBC Driver
Usage: Driver={Microsoft Excel Driver (*.xls)}
Manufacturer: Microsoft

Standard
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

Specify ReadOnly
[Microsoft][ODBC Excel Driver] Operation must use an updateable query. Use this connection string to avoid the error.
Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\MyExcel.xls;ReadOnly=0;
ReadOnly = 0 specifies the connection to be updateable.

Loading ad...
.NET Framework Data Provider for ODBC
Type: .NET Framework Wrapper Class Library
Usage: System.Data.Odbc.OdbcConnection
Manufacturer: Microsoft

Bridging to Microsoft Excel ODBC Driver
This is just one connection string sample for the wrapping OdbcConnection class that calls the underlying ODBC Driver. See respective ODBC driver for more connection strings to use with this class.
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;


.NET xlReader for Microsoft Excel
Type: .NET Framework Class Library
Usage: VM.xPort.ExcelClient.ExcelConnection
Manufacturer: xPortTools

Excel file with header row
Data Source =c:\myExcelFile.xls;HDR=yes;Format=xls;


Excel file without header row
Data Source =c:\myExcelFile.xls;HDR=no;Format=xls;