Download demo project - 20 Kb
The Problem
After contributing that article about writing into an Excel file I got tons of requests about how to read from one. Well, you asked for it...
The main problem is that you can´t read an Excel file without previously having some formatting done. Microsoft refers to this in one of their KB papers. If somewhere out there finds there´s a way to do the reading whithout the formatting, please let me know...
Another problem is the DSN you need to have installed in your ODBC Admin. This is not very useful because you don´t always know the name of the Excel file from the start.
The last problem I´m dealing with here is generally doing ODBC reading using CRecordset
without deriving from it. That is because if I always have to create a class for every single table I want to use, I´ll end up with lots of rather unnecessary code enlarging my app´s exe.
The Solution
- According to Microsoft, an Excel sheet of version 4.x and later can only be read by ODBC if a database range is defined. Unfortunately they don´t state how to do this exactly. One way to let ODBC know what data is in there is to name a range of data on a worksheet using "Insert/Names" from Excel´s menu. There can be more than one "table" on a worksheet. This means that a sheet isn´t necessarily the same as a table in a "real" database. If you open "ReadExcel.xls" from the attached demo project and look up the names, you´ll see what I mean...
- Omiting the DSN tag in the connect string of CDatabase:Open() gives 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 it isn´t, a call to
SQLGetInstalledDrivers()
will show all the installed drivers. For an example see CReadExcelDlg::GetExcelDriver()
below. - To use
CRecordset
the plain way you have to use a readonly, foreward only recordset. The data to get is defined by the SQL statement you put into CRecordset::Open()
. Reading out the result is done by CRecordset::GetFieldValue()
. For an example see the code below.
What is needed
In order to get the code below going you have to
include <afxdb.h> include <odbcinst.h> install an ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)" (or something like that) You must use an ODBC Admin version 3.5 or higher
Drawbacks
Using a pseudo DSN only works with ODBC Admin V3.51 and higher. Earlier versions will not be able to use a DSN that actually isn´t installed. The result of an attempt to do so is some mumbling about missing registry keys.
If using an underived CRecordset
it needs to be readonly, foreward only. So any attempts to change the data or to move back will fail horribly. If you need to do something like that you´re bound to use CRecordset
the "usual" way. Another drawback is that the tremendous overhead of CRecordset
does in fact make it rather slow. A solution to this would be using the class CSQLDirect
contributed by Dave Merner at http://www.codeguru.com/mfc_database/direct_sql_with_odbc.shtml.
There´s still work to do
One unsolved mystery in reading those files is how to get the data WITHOUT having a name defined for it. That means how can the structure of the data be retrieved, how many "tables" are in there, and so on. If you have any idea about that I´d be glad to read it under almikula@EUnet.at (please make a CC to alexander.mikula@siemens.at).
The Source Code
void CReadExcelDlg::OnButton1()
{
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "ReadExcel.xls";
m_ctrlList.ResetContent();
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
AfxMessageBox("No Excel ODBC driver found");
return;
}
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, sFile);
TRY
{
database.Open(NULL, false, false, sDsn);
CRecordset recset(&database);
sSql = "SELECT field_1, field_2 "
"FROM demo_table "
"ORDER BY field_1";
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
while (!recset.IsEOF())
{
recset.GetFieldValue("field_1", sItem1);
recset.GetFieldValue("field_2", sItem2);
m_ctrlList.AddString(sItem1 + " --> "+sItem2);
recset.MoveNext();
}
database.Close();
}
CATCH(CDBException, e)
{
AfxMessageBox("Database error: " + e->m_strError);
}
END_CATCH;
}
CString CReadExcelDlg::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
do
{
if (strstr(pszBuf, "Excel") != 0)
{
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0') + 1;
}
while (pszBuf[1] != '\0');
return sDriver;
}
Please refer the demo project (ReadExcelDlg.cpp) for more details.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.