I am using OBDC to import data from some excel file using CDatabase and and excel drivers. Code is something like this:
CString GetExcelDriver()
{
TCHAR szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
TCHAR *pszBuf = szBuf;
CString sDriver;
// Get the names of the installed drivers ("odbcinst.h" has to be included )
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
return _T("");
// Search for the driver...
do
{
if(_tcsstr( pszBuf, _T("Excel")) != 0 )
{
// Found !
return pszBuf;
break;
}pszBuf = _tcschr( pszBuf, _T('\0')) + 1;
}while( pszBuf[1] != _T('\0'));
return _T("");
}
void ImportData()
{
CString strExcelPath = _T("excel_file.xls");
CString strSQL;
CString strValue;
strSQL.Format(_T("DRIVER={%s};DSN='';DBQ=%s")
, GetExcelDriver()
, strExcelPath);
if(dbFile.Open(NULL,false,false,strSQL))
{
CRecordset rs(&dbFile);
rs.Open(CRecordset::forwardOnly, _T("SELECT * FROM table_name"), CRecordset::readOnly);
int nColumnCount = rs.m_nResultCols;
while(!rs.IsEOF())
{
try
{
rs.GetFieldValue((short)0, strValue);
rs.GetFieldValue((short)1, strValue);
/*
rs.GetFieldValue((short)2, strValue);
rs.GetFieldValue((short)3, strValue);
rs.GetFieldValue((short)4, strValue);
.
.
.
*/
}
catch(CException* pEx)
{
// DB Exception
}
rs.MoveNext();
}
}
}
But to do so, I need the excel sheet name(table_name). As long as I keep that hard-coded, it is fine but in case of user file, it will not work. After long hours of googling, not is found that could get me the sheet name. I would appretiate if any one may suggest a way to get the sheet name from the excel file.