Introduction
Sometimes software developers need to export some data to Excel format or read some cells from Excel file. One way to do it without Excel automation is interaction with ADO. In this case Excel files are treated as database. This method doesn't require Microsoft Excel and quickly enough, but it doesn't support formatting and formulas.
Connection strings
There are two types of connection string. First for binary format (xls):
Provider=Microsoft.JET.OLEDB.4.0;Data Source=data.xls;Extended Properties="Excel 8.0"
Second for xml format (xlsx):
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=data.xlsx;Extended Properties="Excel 12.0 Xml"
If input file doesn't have a header with column names then add ";HDR=NO" in extended properties.
Writing
First create a Connection object:
TESTHR(pCon.CreateInstance(__uuidof(Connection)));
TESTHR(pCon->Open(connStr, "", "", NULL));
Afterwards create Command object and table. Note that name of table is name of sheet:
Read more: Codeproject
Sometimes software developers need to export some data to Excel format or read some cells from Excel file. One way to do it without Excel automation is interaction with ADO. In this case Excel files are treated as database. This method doesn't require Microsoft Excel and quickly enough, but it doesn't support formatting and formulas.
Connection strings
There are two types of connection string. First for binary format (xls):
Provider=Microsoft.JET.OLEDB.4.0;Data Source=data.xls;Extended Properties="Excel 8.0"
Second for xml format (xlsx):
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=data.xlsx;Extended Properties="Excel 12.0 Xml"
If input file doesn't have a header with column names then add ";HDR=NO" in extended properties.
Writing
First create a Connection object:
TESTHR(pCon.CreateInstance(__uuidof(Connection)));
TESTHR(pCon->Open(connStr, "", "", NULL));
Afterwards create Command object and table. Note that name of table is name of sheet:
Read more: Codeproject