Hi All!
I'm making Excel ODBC program using in List control.
When I run the program, I opened the excel file.
One message box showed to me.
It said that "failed to log in ODBC Excel Driver".
I think I make a mistake in file name (.xls), or (.xlsx).
I write down this form in my code. What;s wrong with my code?
Please, give any advices and tips to me.
<pre>
void CFormSecond::OnChange()
{
CFormThird file;
CString strSheet = DEFAULT_SHEET;
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "*.xlsx";
m_List.DeleteAllItems();
sDriver = GetExcelDriver();
if( sDriver.IsEmpty() )
{
AfxMessageBox("No Excel ODBC driver found");
return;
}
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);
TRY
{
CRecordset recset( &database );
sSql.Format(_T("SELECT * FROM [%s$A1:IV65535]"), strSheet);
int nFieldCount = recset.GetODBCFieldCount();
int nRowCount =0;
int nIndex = 0;
CDBVariant m_DBVariant;
while( !recset.IsEOF() )
{
CString strFieldSum;
CString Add, Field,Value;
recset.GetFieldValue((short)0, Add);
recset.GetFieldValue((short)1, Field);
recset.GetFieldValue((short)2, Value);
if(Value.Right(2) == _T(".0"))
Value = Value.Left(Value.GetLength() - 2);
InsertRow(Add, Field,Value);
recset.MoveNext();
}
}
CATCH(CDBException, e)
{
database.Close();
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;
}
CString CFormSecond::GetExcelDriver()<br />
{<br />
TCHAR szBuf[2048]={0,};<br />
WORD cbBufMax = 2047;<br />
WORD cbBufOut;<br />
LPCTSTR pszBuf = szBuf;<br />
CString strExcelDriver;<br />
<br />
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))<br />
return _T("");<br />
<br />
do<br />
{<br />
if( _tcsstr(pszBuf, _T("Excel") ) != 0 )<br />
{<br />
strExcelDriver = CString( pszBuf );<br />
break;<br />
}<br />
pszBuf = _tcschr( pszBuf, _T('\0') ) + 1;<br />
}<br />
while( pszBuf[1] != _T('\0') );<br />
<br />
return strExcelDriver;<br />
}
This is my open and save code
void CFormThird::OnXlSave()
{
//BOOL bRead;
int mrowNum, mcolNum;
int rowNum = 1;
int colNum = 1;
char temp [10];
CXLEzAutomation XL(FALSE);
//m_SFileName ="MODBUS DATA FILE";
//칼럼 데이터
XL.SetCellValue(colNum++, 1, "Address");
XL.SetCellValue(colNum++, 1, "Field");
XL.SetCellValue(colNum++, 1, "Value");
rowNum= m_cList.GetItemCount();
// colNum = m_cList.GetItemCount();
colNum = 3;
// XL.SetCellValue(mcolNum+1,mrowNum+2,m_cList.GetItemText(mcolNum,mrowNum));
for (mrowNum=0; mrowNum<rowNum ; mrowNum++)
{
for(mcolNum=0; mcolNum<colNum; mcolNum++)
{
CString mText;
mText = m_cList.GetItemText(mrowNum,mcolNum);
XL.SetCellValue(mcolNum+1,mrowNum+2,mText);//m_cList.GetItemText(mcolNum,mrowNum));
}
}
char szFilter[] = "Excel 2003 File (*.xls)|*.xls| Excel 2010 File (*.xlsx)|*.xlsx| All Files (*.*)|*.*||";
//cel File(*.xls) |*.xls| (*.xlsx) |*.xlsx | 모든파일(*.*) |*.*| ";
CFileDialog DataWrite(FALSE, "Excel File(*.xlsx)", "*.xlsx", OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter, NULL);
// 저장 창을 Modal()로 띄움
if(DataWrite.DoModal() == IDOK)
{
//XL.SaveFileAs(DataWrite.GetPathName());
m_SFileName = DataWrite.GetPathName();
//Use Excel SaveFile function to save worksheet
XL.SaveFileAs(m_SFileName);
XL.ReleaseExcel();
}
}
void CFormThird::OnXlOpen()
{
char chThisPath[256];
CString strThisPath ;
char szFilter[] = "Excel 2003 File (*.xls)|*.xls| Excel 2010 File (*.xlsx)|*.xlsx| All Files (*.*)|*.*||";
CFileDialog DataRead(TRUE, "Excel File(*.xlsx)",m_SFileName, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter, NULL);
UpdateData(TRUE);
GetCurrentDirectory(256, chThisPath);
strThisPath.Format("%s\\%s.xlsx",chThisPath,m_SFileName);
GetModuleFileName( NULL, chThisPath, 256);
int nFileRead = DataRead.DoModal();
if(IDOK == nFileRead)
{
CXLEzAutomation XL;
CString sFile = DataRead.GetPathName();
CString address, field, value;
for(int i=1; ; i++)
{
address=XL.GetCellValue(1,i+1);
field=XL.GetCellValue(2,i+1);
value=XL.GetCellValue(3,i+1);
if ( address == "")
break;
m_cList.InsertItem(i-1,address);
m_cList.SetItemText(i-1,1,field);
m_cList.SetItemText(i-1,2,value);
}
if(!XL.OpenExcelFile(sFile))
{
// XL.SaveFileAs(strThisPath);
// m_cList.DeleteAllItems();
XL.ReleaseExcel();
return;
}
}
}