我用ODBC操作Excel文件,因为要需要用户输入Excel文件路径,所以使用了CFileDialog,,但是在后面打开数据库时候就失败了,产生异常直接转跳到
CATCH中,void CCompareRegisters::OnButtonBrowse() //界面上的一个按扭用来输入文件的路径
{
#if 0//我采用这种方式就OK,但是这种方式不能限制文件的后缀名,在选择文件的时候很烦琐,不方便
CString m_strPath;
TCHAR lpDIR[MAX_PATH];
LPITEMIDLIST il;
BROWSEINFO bi;
bi.hwndOwner =m_hWnd;
bi.pidlRoot=NULL;
bi.pszDisplayName=lpDIR;
bi.lpszTitle=_T("please select directory");
bi.ulFlags=BIF_BROWSEINCLUDEFILES ;
bi.lpfn=NULL;
bi.lParam=0;
bi.iImage=0;
il=SHBrowseForFolder(&bi);
int k=MAX_PATH;
if(SHGetPathFromIDList(il,lpDIR) == TRUE)
{
m_strPath = lpDIR;
GetDlgItem(IDC_EDIT_REG_FILEPATH)->SetWindowText(m_strPath);
}
#else //采用CFileDialog及失败
CString m_strPath;
CFileDialog dlg(TRUE,NULL,NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,"(*.xls)|*.xls",NULL);
if(dlg.DoModal() == IDOK)
{
m_strPath = dlg.GetPathName();
}
((CEdit *)GetDlgItem(IDC_EDIT_REG_FILEPATH))->SetWindowText(m_strPath);
#endif
}//pFileName指想操作文件路径,并读出excel文件通过参数pDataBuffer传回.
BOOL CCompareRegisters::ReadExcelFileContent(char *pFileName, char *pDataBuffer)
{
BOOL Flag = FALSE;
CDatabase database;
CString sSql;
CString sItem1, sItem2 , sItem3;
CString sDriver;
CString sDsn;
CString sFile,sPath;
//get main program driver for excel,save in sPath
GetModuleFileName(NULL,sPath.GetBufferSetLength(MAX_PATH+1),MAX_PATH);
sPath.ReleaseBuffer();
int nPos;
nPos = sPath.ReverseFind ('\\');
sPath = sPath.Left (nPos); sFile = pFileName; //to be read file name // check excel driver installed status "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();//取的系统驱动名,函数原形没有帖出来
if (sDriver.IsEmpty())
{
// can't find out Excel driver
AfxMessageBox("Excel driver has not been installed yet!");
return FALSE;
}
// create string be used to saved characters
sDsn.Format("ODBC;DRIVER={%s};DSN=;DBQ=%s", sDriver, sFile); TRY
{
// open excel file
database.Open(NULL, false, false, sDsn);//使用了上面的CFileDialog 这里每次都失败,直接跳到CATCH中
CRecordset recset(&database);
// setting Query condition,
sSql = "SELECT AddressName,Value,Description "
"FROM Table1 " ;
//"ORDER BY AddressName ";
// perform Query condition
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly); // get the result
strcpy(pDataBuffer,"");
while (!recset.IsEOF())
{
//read Excel data
recset.GetFieldValue("AddressName", sItem1);
recset.GetFieldValue("Value", sItem2);
recset.GetFieldValue("Description", sItem3); for(int i=GetStrLen(sItem1); i<25; i++)
{
sItem1 += " ";
}
strcat(pDataBuffer,sItem1);
strcat(pDataBuffer,"\n");
strcat(pDataBuffer,sItem2);
strcat(pDataBuffer,"\n"); strcat(pDataBuffer,sItem3);
strcat(pDataBuffer,"\n"); //move to next line
recset.MoveNext();
} // close database
database.Close();
}
CATCH(CDBException, e)
{
// if error,give error info ...
AfxMessageBox("DataBase Error: " + e->m_strError);
}
END_CATCH; return TRUE;
}
CATCH中,void CCompareRegisters::OnButtonBrowse() //界面上的一个按扭用来输入文件的路径
{
#if 0//我采用这种方式就OK,但是这种方式不能限制文件的后缀名,在选择文件的时候很烦琐,不方便
CString m_strPath;
TCHAR lpDIR[MAX_PATH];
LPITEMIDLIST il;
BROWSEINFO bi;
bi.hwndOwner =m_hWnd;
bi.pidlRoot=NULL;
bi.pszDisplayName=lpDIR;
bi.lpszTitle=_T("please select directory");
bi.ulFlags=BIF_BROWSEINCLUDEFILES ;
bi.lpfn=NULL;
bi.lParam=0;
bi.iImage=0;
il=SHBrowseForFolder(&bi);
int k=MAX_PATH;
if(SHGetPathFromIDList(il,lpDIR) == TRUE)
{
m_strPath = lpDIR;
GetDlgItem(IDC_EDIT_REG_FILEPATH)->SetWindowText(m_strPath);
}
#else //采用CFileDialog及失败
CString m_strPath;
CFileDialog dlg(TRUE,NULL,NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,"(*.xls)|*.xls",NULL);
if(dlg.DoModal() == IDOK)
{
m_strPath = dlg.GetPathName();
}
((CEdit *)GetDlgItem(IDC_EDIT_REG_FILEPATH))->SetWindowText(m_strPath);
#endif
}//pFileName指想操作文件路径,并读出excel文件通过参数pDataBuffer传回.
BOOL CCompareRegisters::ReadExcelFileContent(char *pFileName, char *pDataBuffer)
{
BOOL Flag = FALSE;
CDatabase database;
CString sSql;
CString sItem1, sItem2 , sItem3;
CString sDriver;
CString sDsn;
CString sFile,sPath;
//get main program driver for excel,save in sPath
GetModuleFileName(NULL,sPath.GetBufferSetLength(MAX_PATH+1),MAX_PATH);
sPath.ReleaseBuffer();
int nPos;
nPos = sPath.ReverseFind ('\\');
sPath = sPath.Left (nPos); sFile = pFileName; //to be read file name // check excel driver installed status "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();//取的系统驱动名,函数原形没有帖出来
if (sDriver.IsEmpty())
{
// can't find out Excel driver
AfxMessageBox("Excel driver has not been installed yet!");
return FALSE;
}
// create string be used to saved characters
sDsn.Format("ODBC;DRIVER={%s};DSN=;DBQ=%s", sDriver, sFile); TRY
{
// open excel file
database.Open(NULL, false, false, sDsn);//使用了上面的CFileDialog 这里每次都失败,直接跳到CATCH中
CRecordset recset(&database);
// setting Query condition,
sSql = "SELECT AddressName,Value,Description "
"FROM Table1 " ;
//"ORDER BY AddressName ";
// perform Query condition
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly); // get the result
strcpy(pDataBuffer,"");
while (!recset.IsEOF())
{
//read Excel data
recset.GetFieldValue("AddressName", sItem1);
recset.GetFieldValue("Value", sItem2);
recset.GetFieldValue("Description", sItem3); for(int i=GetStrLen(sItem1); i<25; i++)
{
sItem1 += " ";
}
strcat(pDataBuffer,sItem1);
strcat(pDataBuffer,"\n");
strcat(pDataBuffer,sItem2);
strcat(pDataBuffer,"\n"); strcat(pDataBuffer,sItem3);
strcat(pDataBuffer,"\n"); //move to next line
recset.MoveNext();
} // close database
database.Close();
}
CATCH(CDBException, e)
{
// if error,give error info ...
AfxMessageBox("DataBase Error: " + e->m_strError);
}
END_CATCH; return TRUE;
}
database.Open(NULL, false, false, sDsn);//
CFileDialog dlg(TRUE,NULL,NULL,OFN_HIDEREADONLY ¦ OFN_OVERWRITEPROMPT,"(*.xls) ¦*.xls",NULL);
可以改为:
CFileDialog dlg(TRUE,NULL,NULL,OFN_HIDEREADONLY ¦ OFN_OVERWRITEPROMPT|OFN_NOCHANGEDIR,"(*.xls) ¦*.xls",NULL);
增加OFN_NOCHANGEDIR标识。另外的方法:
TCHAR szDir[MAX_PATH];
::GetCurrentDirectory(MAX_PATH, szDir);
//接下来是与路径有关的操作(可能改变当前路径的操作)
.....
最后恢复当前路径:
if(!::SetCurrentDirectory(szDir))
{
MessageBox("ÖØÐÂÉèÖõ±Ç°Ä¿Â¼Ê§°Ü!");
}