mfc 通过excel驱动读取excel文件

CString CXXXXX::GetExcelDriver(){char szBuf[2001];WORD cbBufMax = 2000;WORD cbBufOut;char *pszBuf = szBuf;CString sDriver;// 获取已安装驱动的名称(涵数在odbcinst.h里)if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))return "";// 检索已安装的驱动是否有Excel...do{if (strstr(pszBuf, "Excel") != 0){//发现 !sDriver = CString(pszBuf);break;}pszBuf = strchr(pszBuf, '\0') + 1;}while (pszBuf[1] != '\0');return sDriver;}void CXXXXX::ReadExcelFile( const CString &strFilePath ){CDatabase database;CString sSql;CString sCode, sName;CString sDriver;CString sDsn;int nBegin = strFilePath.ReverseFind('\\');int nEnd = strFilePath.ReverseFind('.');CString strFileName = strFilePath.Mid(nBegin+1, nEnd-nBegin-1);// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)" sDriver = GetExcelDriver();if (sDriver.IsEmpty()){// 没有发现Excel驱动AfxMessageBox("没有安装Excel驱动!");return;}// 创建进行存取的字符串sDsn.Format("ODBC;DRIVER={%s};DSN='''';DBQ=%s", sDriver, strFilePath);TRY{// 打开数据库(既Excel文件)database.Open(NULL, false, false, sDsn);CRecordset recset(&database);CStringList strTableList;GetExcelAllTableNames(strFilePath, strTableList);CString strTableName = strTableList.GetAt(strTableList.FindIndex(0));// 设置读取的查询语句.//sSql.Format("SELECT %s, %s FROM [%s]", ZQBM, ZQMC, strTableName);sSql.Format("SELECT * FROM [%s]", strTableName);// 执行查询语句recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);// 获取查询结果while (!recset.IsEOF()){//读取Excel内部数值//recset.GetFieldValue(ZQBM, sCode);//recset.GetFieldValue(ZQMC, sName);recset.GetFieldValue(short(5), sCode);recset.GetFieldValue(short(6), sName);// 移到下一行recset.MoveNext();}// 关闭数据库database.Close();}CATCH(CDBException, e){// 数据库操作产生异常时...AfxMessageBox("数据库错误: " + e->m_strError);}END_CATCH;}BOOL CXXXXXX::GetExcelAllTableNames( const CString &sExcelFile, CStringList &slTableName ){char strDsn[MAX_PATH];CString sDsn;memset(strDsn,0,MAX_PATH);sDsn.Format("DBQ=%s;Driver={Microsoft Excel Driver (*.xls)};",sExcelFile);strcpy(strDsn,sDsn);//分配环境句柄SQLHENV m_henv;if(SQLAllocEnv(&m_henv) != SQL_SUCCESS){return FALSE;}//分配连接句柄SQLHDBC m_hdbc;if(SQLAllocConnect(m_henv,&m_hdbc) != SQL_SUCCESS){return FALSE;}// 连接数据源UCHAR szConnectOutput[512];SWORD nResult;if(SQLDriverConnect( m_hdbc,NULL,(UCHAR*)strDsn,SQL_NTS,szConnectOutput,sizeof(szConnectOutput),&nResult,SQL_DRIVER_COMPLETE ) != SQL_SUCCESS){return FALSE;}SQLHSTMT hstmt = NULL;if(SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hstmt) != SQL_SUCCESS){return FALSE;}/*也可对其进行限制，如 if(SQLTables( hstmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR*)"TABLE", strlen("TABLE")) != SQL_SUCCESS) 此名则只获取类型为TABLE的表*///此是获取所有表名，包括名字后带有$符号的系统表if(SQLTables( hstmt, NULL, 0, NULL, 0, NULL, 0, 0, 0) != SQL_SUCCESS ){return FALSE;}SDWORD cb;char szTable[255];char szTableType[255];SQLBindCol( hstmt, 3, SQL_C_CHAR, szTable, 255, &cb );SQLBindCol( hstmt, 4, SQL_C_CHAR, szTableType, 255, &cb );while(SQLFetch(hstmt) == SQL_SUCCESS){if(!slTableName.Find(szTable)){slTableName.AddTail(szTable);}}SQLFreeHandle( SQL_HANDLE_STMT, hstmt );SQLDisconnect( m_hdbc ); SQLFreeHandle( SQL_HANDLE_ENV, m_henv );return TRUE;}