The Problem
Many apps offer an export function. So wouldn�t it be nice to be able to easily save that result as an Excel sheet?
ODBC does make this possible, but there�s one little drawback: Using ODBC the usual way there has to be a registered datasource (DSN) in the ODBC manager.
This is not very useful because you�d have to install that DSN locally on every machine that should support your export function.
The Solution
Omiting the DSN tag in the connect string of CDatabase::OpenEx()
gives us the opportunity to refer the ODBC-Driver directly using its name so we don�t have to have a DSN registered.
This, of course, implies that the name of the ODBC-Driver is exactly known.
If you just want to test if a certain driver is present (to show the supported extensions in the CFileOpenDlg
for example) just try to CDatabase::OpenEx()
it.
If it isn�t installed an exception gets thrown.
To create and write to that Excel sheet you simply use SQL as shown in the code sample below.
What is Needed
In order to get the code below going you have to
- have included
- have an installed ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)"
The Source code
void MyDemo::Put2Excel()
{
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)";
CString sExcelFile = "c:\\demo.xls";
CString sSql;
TRY
{
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
sDriver, sExcelFile, sExcelFile);
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
sSql = "CREATE TABLE demo (Name TEXT,Age NUMBER)";
database.ExecuteSQL(sSql);
sSql = "INSERT INTO demo (Name,Age) VALUES ('Bruno Brutalinsky',45)";
database.ExecuteSQL(sSql);
sSql = "INSERT INTO demo (Name,Age) VALUES ('Fritz Pappenheimer',30)";
database.ExecuteSQL(sSql);
sSql = "INSERT INTO demo (Name,Age) VALUES ('Hella Wahnsinn',28)";
database.ExecuteSQL(sSql);
}
database.Close();
}
CATCH_ALL(e)
{
TRACE1("Driver not installed: %s",sDriver);
}
END_CATCH_ALL;
}