c++ 读写Excel及数据导入SQLServer
C++操作Excel ,网上的资料还是比较多的，写这篇文章也是分享给初学者一些经验。本人 觉得CSpreadSheet.h这个类封装的还不错。下面我就如何使用这个类介绍一下，
main.cpp/#include#include #include #include "CSpreadSheet.h"using std::string;#pragma warning(disable:4146)#pragma warning(disable:4786)#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF")//插入到数据库bool InsertExcel(CString str1,CString str2){ try { CDatabase m_db; if (!m_db.IsOpen()) { m_db.OpenEx("Dsn=MyDatabase;uid=Administrator;trusted_connection=Yes;app=Microsoft? Visual Studio? 2005;wsid=LIYU\SQLEXPRESS;database=MyDdatabase",0); } CString sql("insert into Students(myname,age) values('"+ str1+"','"+str2+"')"); m_db.ExecuteSQL(sql); if(m_db.IsOpen()) { m_db.Close(); } return true; } catch(_com_error e) { string ErrorMessage("数据库连接关闭失败:"),Description,Source; Description=e.Description(); Source=e.Source(); ErrorMessage+=e.ErrorMessage(); ErrorMessage=ErrorMessage+"\r\n"+Source+"\r\n"+Description; ::MessageBox(NULL,ErrorMessage.c_str(),"错误",MB_OK); return false; } }//获取路径CString GetAddr(){ CString sFile,sPath; //获取主程序所在路径,存在sPath中 GetModuleFileName(NULL,sPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH); sPath.ReleaseBuffer (); int nPos; nPos=sPath.ReverseFind ('\\'); sPath=sPath.Left (nPos); sFile = sPath + "\\Demo.xls"; return sFile;}
//得到驱动CString 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;}
//读取Excelvoid ReadFromExcel() { TRY { CString str=GetAddr(); if(str.IsEmpty()) ::MessageBox(NULL,"无法获取当前路径",NULL,MB_OK); else { CSpreadSheet SS(str,"Students"); CStringArray Rows, Column; CString strContents = ""; CString sItem[3]={"0"}; for (int i = 2; i < SS.GetTotalRows()+1; i++) { // 读取一行 SS.ReadRow(Rows, i); strContents.Empty(); for (int j = 0; j < Rows.GetSize(); j++) { strContents = Rows.GetAt(j); sItem[j]=strContents; printf("%s\t",sItem[j]); } printf("\n"); if(!InsertExcel(sItem[1],sItem[2])) { ::MessageBox(NULL,"导入数据出错","错误",MB_OK); return; } } } } CATCH(CDBException, e) { // 数据库操作产生异常时... AfxMessageBox("数据库错误: " + e->m_strError); } END_CATCH;}//写Excelvoid WriteFromExcel(int num,CString str1,CString str2,CString str3) { CString path=GetAddr(); if(path.IsEmpty()) ::MessageBox(NULL,"获取路径错误",NULL,MB_OK); else { // 新建Excel文件名及路径，TestSheet为内部表名 CSpreadSheet SS(path,"StudentsOut"); CStringArray sampleArray, testRow; SS.BeginTransaction(); // 加入标题 sampleArray.RemoveAll(); sampleArray.Add("ID"); sampleArray.Add("myname"); sampleArray.Add("age"); SS.AddHeaders(sampleArray); testRow.Add(str1); testRow.Add(str2); testRow.Add(str3); SS.AddRow(testRow,num,true); SS.Commit(); }}//查询bool selectExcel(){ _ConnectionPtr m_pConnection; //connection object's pointer _CommandPtr m_pCommand; //command object's pointer _ParameterPtr m_pParameter; //Parameter object's pointer _RecordsetPtr m_pRecordset; HRESULT hr; try { // 创建连接对象 hr=m_pConnection.CreateInstance(__uuidof(Connection)); m_pRecordset.CreateInstance(__uuidof(Recordset)); if(!SUCCEEDED(hr)) return FALSE; // 连接数据库 m_pConnection->ConnectionString="File Name=LinkDatabase.udl"; m_pConnection->ConnectionTimeout=20;//等待连接的时间为20s hr=m_pConnection->Open("","","",adModeUnknown); if(!SUCCEEDED(hr)) return FALSE; // 查询数据库 _variant_t RecordsAffected; std::string sql= "select * from Students"; char * str=(char*)sql.c_str(); m_pRecordset=m_pConnection->Execute(str,&RecordsAffected,adCmdText); //m_pRecordset-> Open(str, _variant_t((IDispatch *)m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText); int i=2; // 遍历查询结果 while (!m_pRecordset->adoEOF) { printf("%s\t",(char*)(_bstr_t)m_pRecordset->GetCollect("ID")); printf("%s\t",(char*)(_bstr_t)m_pRecordset->GetCollect("myname")); printf("%s\n",(char*)(_bstr_t)m_pRecordset->GetCollect("age")); CString str1=(CString)m_pRecordset->GetCollect("ID"); CString str2=(CString)m_pRecordset->GetCollect("myname"); CString str3=(CString)m_pRecordset->GetCollect("age"); WriteFromExcel(i++,str1,str2,str3); m_pRecordset->MoveNext(); } m_pRecordset->Close(); // 关闭数据库连接 if(m_pConnection!=NULL) { m_pConnection->Close(); m_pConnection = NULL ; } } catch(_com_error e) { string ErrorMessage("数据库连接关闭失败:"),Description,Source; Description=e.Description(); Source=e.Source(); ErrorMessage+=e.ErrorMessage(); ErrorMessage=ErrorMessage+"\r\n"+Source+"\r\n"+Description; ::MessageBox(NULL,ErrorMessage.c_str(),"错误",MB_OK); return FALSE; } }int main(){ CoInitialize(NULL);// ReadFromExcel();//读取Excel到数据库 selectExcel(); CoUninitialize( ); system("pause"); return 0;}mian.cpp