在VC中导出excel表格（两种方法）

相关资料可以参考http://blog.csdn.net/Augusdi/article/category/704352

1.利用ODBC

void CGroupPanel::OnExportChannelinfo(){CGroupManager * pGroupMgr = CGroupManager::Instance();CDatabase chDB;CString csDBDriver;char path[MAX_PATH];       //获取路径用的数组  CString csDir;                      //包括EXCEL文件名在内的路径名.  CString csSql;                    //SQL命令语句,用m_db可直接执行.CTime curTime = CTime::GetCurrentTime();CString csTime = curTime.Format("%Y年%m月%d号%H时%M分%S秒");csDBDriver = "MICROSOFT EXCEL DRIVER (*.XLS)";GetCurrentDirectory(MAX_PATH,path); csDir = path; csDir = csDir + "\\通道信息" + csTime + ".xls";csSql.Format("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",csDBDriver,csDir,csDir);TRY{ if (chDB.OpenEx(csSql,CDatabase::noOdbcDialog)){GroupInfo * pGroupInfo;CString csGroupName;pGroupInfo = pGroupMgr->findFirstGroupInfo();csGroupName = pGroupInfo->_sGroupName.c_str();for(pGroupInfo = pGroupMgr->findFirstChildGroup(pGroupInfo); pGroupInfo != NULL; pGroupInfo = pGroupMgr->findNextGroupInfo(csGroupName)){csGroupName = pGroupInfo->_sGroupName.c_str();csSql = "Create Table " + csGroupName + "(通道名称 Text,IP地址 Text,派出所 Text,是否在线 Text)";chDB.ExecuteSQL(csSql);GroupInfo * pGroupInfo2;CString csGroupName2;for(pGroupInfo2 = pGroupMgr->findFirstChildGroup(pGroupInfo);pGroupInfo2 != NULL; pGroupInfo2 = pGroupMgr->findNextLeafGroupInfo(pGroupInfo2)){csGroupName2 = pGroupInfo2->_sGroupName.c_str();vector<string>::iterator it = pGroupInfo2->_vecVidiSBBH.begin();for(;it!=pGroupInfo2->_vecVidiSBBH.end();it++){string sCamBH = (*it);lpVIDEOCHNODE lpCh = CVideoChannelManager::Instance()->GetVideoChannelByCamID(sCamBH.c_str());lpENCODERNODE lpEn = CEncodeManager::Instance()->GetEncodeByID(lpCh->encChildInfo.nParentID);CString csChName = lpCh->encChildInfo.csName;CString csIP = lpEn->encInfo.csIP;CString csStatus = (lpCh->nChState.iStateData.bOnLine == 0) ? "不在线"  : "在线";csSql = "Insert Into " + csGroupName + "(通道名称,IP地址,派出所,是否在线) Values('" + csChName + "','" + csIP + "','" + csGroupName2 + "','" + csStatus + "')";chDB.ExecuteSQL(csSql);}}}chDB.Close();}}CATCH_ALL(e){// e->ReportError();chDB.Close();return;}END_CATCH_ALL;}

2.利用COM

void CGroupPanel::OnExportChannelinfo(){CTime curTime = CTime::GetCurrentTime();CString csTime = curTime.Format("%Y年%m月%d号%H时%M分%S秒");char path[MAX_PATH];GetCurrentDirectory(MAX_PATH,path);CString strFile = path;COleVariantcovTrue((short)TRUE),covFalse((short)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND,   VT_ERROR);CApplication app;CWorkbook book;CWorkbooks books;CWorksheet sheet;CWorksheets sheets;CRange range;CFont0 font;LPDISPATCH lpDisp = NULL;//创建Excel服务器(启动Excel)if (!app.CreateDispatch(_T("Excel.Application"))){MessageBox(_T("启动Excel失败!"));return;}//判断当前Excel的版本CString csExcelVersion = app.get_Version();int iStart = 0;  csExcelVersion = csExcelVersion.Tokenize(_T("."), iStart);  if (_T("11") == csExcelVersion)  {  //当前Excel的版本是2003 strFile = strFile + "\\通道信息" + csTime + ".xls";}  else if (_T("12") == csExcelVersion)  {  //当前Excel的版本是2007strFile = strFile + "\\通道信息" + csTime + ".xlsx";}  else  {  //当前Excel的版本是其他版本strFile = strFile + "\\通道信息" + csTime + ".xlsx";} books.AttachDispatch(app.get_Workbooks(),TRUE);lpDisp = books.Add(vtMissing);book.AttachDispatch(lpDisp);//加载EXCEL模板 sheets.AttachDispatch(book.get_Sheets(),TRUE);//加载Sheet页面 CGroupManager * pGroupMgr = CGroupManager::Instance();GroupInfo * pGroupInfo;CString csGroupName;pGroupInfo = pGroupMgr->findFirstGroupInfo();csGroupName = pGroupInfo->_sGroupName.c_str();int sheetIndex = 1;for(pGroupInfo = pGroupMgr->findFirstChildGroup(pGroupInfo); pGroupInfo != NULL; pGroupInfo = pGroupMgr->findNextGroupInfo(csGroupName),sheetIndex++){csGroupName = pGroupInfo->_sGroupName.c_str();sheets.Add(vtMissing,vtMissing,_variant_t((long)1),vtMissing);//把第一个Sheet页面的名字改变为csGroupName sheet.AttachDispatch(sheets.get_Item(_variant_t((long)1)),TRUE); sheet.put_Name(csGroupName); range.AttachDispatch(sheet.get_Cells(),TRUE);//加载所有单元格range.put_Item(_variant_t((long)1),_variant_t((long)1),_variant_t("通道名称"));range.put_Item(_variant_t((long)1),_variant_t((long)2),_variant_t("IP地址")); range.put_Item(_variant_t((long)1),_variant_t((long)3),_variant_t("派出所")); range.put_Item(_variant_t((long)1),_variant_t((long)4),_variant_t("是否在线"));GroupInfo * pGroupInfo2;CString csGroupName2;int lineIndex = 2;for(pGroupInfo2 = pGroupMgr->findFirstChildGroup(pGroupInfo);pGroupInfo2 != NULL; pGroupInfo2 = pGroupMgr->findNextLeafGroupInfo(pGroupInfo2)){csGroupName2 = pGroupInfo2->_sGroupName.c_str();vector<string>::iterator it = pGroupInfo2->_vecVidiSBBH.begin();for(;it!=pGroupInfo2->_vecVidiSBBH.end();it++,lineIndex++){string sCamBH = (*it);lpVIDEOCHNODE lpCh = CVideoChannelManager::Instance()->GetVideoChannelByCamID(sCamBH.c_str());lpENCODERNODE lpEn = CEncodeManager::Instance()->GetEncodeByID(lpCh->encChildInfo.nParentID);CString csChName = lpCh->encChildInfo.csName;CString csIP = lpEn->encInfo.csIP;CString csStatus = (lpCh->nChState.iStateData.bOnLine == 0) ? "不在线"  : "在线";range.put_Item(_variant_t((long)lineIndex),_variant_t((long)1),_variant_t(csChName));range.put_Item(_variant_t((long)lineIndex),_variant_t((long)2),_variant_t(csIP)); range.put_Item(_variant_t((long)lineIndex),_variant_t((long)3),_variant_t(csGroupName2)); range.put_Item(_variant_t((long)lineIndex),_variant_t((long)4),_variant_t(csStatus));}}range = range.get_EntireColumn();range.AutoFit();range = sheet.get_Range(COleVariant("A1"), COleVariant("D1"));range.put_RowHeight(COleVariant((short)30)); font = range.get_Font();font.put_Bold(COleVariant((short)TRUE));font.put_Size(COleVariant((short)10));}//删除默认的sheet1,sheet2,sheet3sheet.AttachDispatch(sheets.get_Item(_variant_t((long)sheetIndex)),TRUE); sheet.Delete();  sheet.AttachDispatch(sheets.get_Item(_variant_t((long)sheetIndex)),TRUE);  sheet.Delete();  sheet.AttachDispatch(sheets.get_Item(_variant_t((long)sheetIndex)),TRUE);  sheet.Delete(); book.SaveCopyAs(COleVariant(strFile));book.put_Saved(true);books.Close();//释放对象（相当重要！） range.ReleaseDispatch(); sheet.ReleaseDispatch(); sheets.ReleaseDispatch(); book.ReleaseDispatch(); books.ReleaseDispatch(); //app一定要释放，否则程序结束后还会有一个Excel进程驻留在内存中，而且程序重复运行的时候会出错 app.ReleaseDispatch(); app.Quit();}