MFC输出EXCEL文档的过程总结
来源:互联网 发布:java 内存泄漏 例子 编辑:程序博客网 时间:2024/06/04 19:06
由于目前的工作内容涉及到了几处需要输出EXCEL表格的内容,因此在网上也查找了些资料,在下面总结下,以方便使用;
1、当你输出的内容已经在输出的时候全部知道,并且输出格式已经固定,那么你就可以使用下面的方法输出,用if( (pfile = fopen(strFileName, "wb+")) != NULL)
打开EXCEL,然后在其中用fprintf写入,用\T到下一个单元格,用\n到下一行;适合输出内容固定,格式固定的内容。
void CRqEquipReqView::SaveDxcFileXLS(CString strFileName, BOOL bSaveAsAll){FILE *pfile = NULL;CString strGroupName;CString strMain, strSlave;CString strMainName, strMainCreateTime, strMainDirection, strMainCap, strMainStatus; CString strMainSrc, strMainSrcAccess, strMainSrcConGroup;CString strMainDst, strMainDstAccess, strMainDstConGroup;CString strProName, strProCreateTime, strProDirection, strProCap, strProStatus;CString strProSrc, strProSrcAccess, strProSrcConGroup;CString strProDst, strProDstAccess, strProDstConGroup;strMain.LoadString(IDS_RQNLPCT_MAIN);strSlave.LoadString(IDS_RQNLPCT_SLAVE);if(m_pRqGroup){strGroupName = m_pRqGroup->GetName();}int MainCount = m_lstDxc.GetItemCount();int ProCount = m_lstProtDxc.GetItemCount();//参数中带'b'的话是以二进制文件形式打开文件, 否则是以文本文件打开.//以文本文件形式打开时, 值26就作为EOF了, 前者则不会.//如果不是保存成文本形式的话, 切记加上'b'//"a "模式在添加数据前并不清除EOF标志,添加数据后用MS-DOS的TYPE命令只能显示数据到原来EOF标志,而并不会显示后来添加的. //"a+ "模式会清除EOF标志,添加数据后,用MS-DOS的TYPE命令能显示所有的数据."a+ "模式需要以CTRL+Z EOF标志结束的流输入.if( (pfile = fopen(strFileName, "wb+")) != NULL) {fprintf(pfile, strGroupName + "-" + strMain + "\n");CString strItem;strItem.LoadString(IDS_RQSUBNET_DXCNAME);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_DXCCAP);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_DXCDIR);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_SRCCARD);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_SRCAP);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_SRCCONNECTGROUP);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_DSTCARD);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_DSTAP);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_RQSUBNET_DSTCONNECTGROUP);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_STATUS);fprintf(pfile, strItem + "\t");strItem.LoadString(IDS_DXCCREATETIME);fprintf(pfile, strItem + "\t\n");CRqNeDxc rqNeDxc;for(int mainDxc = 0; mainDxc < m_lstDxc.GetItemCount(); mainDxc++){int iDxcID = m_lstDxc.GetItemData(mainDxc);if (!m_bSingle){if(iDxcID < m_vRqNeDxcBiDsp.size()) {rqNeDxc = m_vRqNeDxcBiDsp[iDxcID];}else{rqNeDxc = m_vRqNeDxcUniDsp[iDxcID - m_vRqNeDxcBiDsp.size()];}}else{rqNeDxc = m_vRqDxc[iDxcID];}if(!bSaveAsAll && !rqNeDxc.GetSelected()){continue;}strMainName = m_lstDxc.GetItemText(mainDxc, NAMECOL);strMainCap = m_lstDxc.GetItemText(mainDxc, CAPCOL);strMainDirection = m_lstDxc.GetItemText(mainDxc, DIRCOL);strMainSrc = m_lstDxc.GetItemText(mainDxc, SRCCARDCOL);strMainSrcAccess = m_lstDxc.GetItemText(mainDxc, SRCTU12COL);strMainSrcAccess = " " + strMainSrcAccess;strMainSrcAccess += " ";strMainSrcConGroup = m_lstDxc.GetItemText(mainDxc, SRCCONGROUPCOL);strMainDst = m_lstDxc.GetItemText(mainDxc, DSTCARDCOL);strMainDstAccess = m_lstDxc.GetItemText(mainDxc, DSTTU12COL);strMainDstAccess = " " + strMainDstAccess;strMainDstAccess += " ";strMainDstConGroup = m_lstDxc.GetItemText(mainDxc, DSTCONGROUPCOL);strMainStatus = m_lstDxc.GetItemText(mainDxc, ENABLE);strMainCreateTime = m_lstDxc.GetItemText(mainDxc, CREATETIME);fprintf(pfile, strMainName + "\t");fprintf(pfile, strMainCap + "\t");fprintf(pfile, strMainDirection + "\t");fprintf(pfile, strMainSrc + "\t");fprintf(pfile, strMainSrcAccess + "\t");fprintf(pfile, strMainSrcConGroup + "\t");fprintf(pfile, strMainDst + "\t");fprintf(pfile, strMainDstAccess + "\t");fprintf(pfile, strMainDstConGroup + "\t");fprintf(pfile, strMainStatus + "\t");fprintf(pfile, strMainCreateTime + "\t\n");}if(ProCount == 0){CString strTmp;strTmp.LoadString(IDS_NOPROTSERVICE);fprintf(pfile, strGroupName + "-" + strSlave + "(" + strTmp + ")" + "\n");}else{fprintf(pfile, strGroupName + "-" + strSlave + "\n");}for(int proDxc = 0; proDxc < m_lstProtDxc.GetItemCount(); proDxc++){int iDxcID = m_lstProtDxc.GetItemData(proDxc);if (!m_bSingle){rqNeDxc = m_vRqNeDxcUniDsp_ForProList[iDxcID];}else{rqNeDxc = m_vRqDxc[iDxcID];}if(!bSaveAsAll && !rqNeDxc.GetSelected()){continue;}strProName = m_lstProtDxc.GetItemText(proDxc, NAMECOL);strProCap = m_lstProtDxc.GetItemText(proDxc, CAPCOL);strProDirection = m_lstProtDxc.GetItemText(proDxc, DIRCOL);strProSrc = m_lstProtDxc.GetItemText(proDxc, SRCCARDCOL);strProSrcAccess = m_lstProtDxc.GetItemText(proDxc, SRCTU12COL);strProSrcAccess = " " + strProSrcAccess;strProSrcAccess += " ";strProSrcConGroup = m_lstProtDxc.GetItemText(proDxc, SRCCONGROUPCOL);strProDst = m_lstProtDxc.GetItemText(proDxc, DSTCARDCOL);strProDstAccess = m_lstProtDxc.GetItemText(proDxc, DSTTU12COL);strProDstAccess = " " + strProDstAccess;strProDstAccess += " ";strProDstConGroup = m_lstProtDxc.GetItemText(proDxc, DSTCONGROUPCOL);strProStatus = m_lstProtDxc.GetItemText(proDxc, ENABLE);strProCreateTime = m_lstProtDxc.GetItemText(proDxc, CREATETIME);fprintf(pfile, strProName + "\t");fprintf(pfile, strProCap + "\t");fprintf(pfile, strProDirection + "\t");fprintf(pfile, strProSrc + "\t");fprintf(pfile, strProSrcAccess + "\t");fprintf(pfile, strProSrcConGroup + "\t");fprintf(pfile, strProDst + "\t");fprintf(pfile, strProDstAccess + "\t");fprintf(pfile, strProDstConGroup + "\t");fprintf(pfile, strProStatus + "\t");fprintf(pfile, strProCreateTime + "\t\n");}fclose(pfile);}else{CString strmsg;strmsg.LoadString(IDS_CLOSEEXCEL);AfxMessageBox(strmsg, 0, 0);}}2、 这种输入方式,适合后台输出,不需要打开EXCEL文件,不需要选择输出路径,只是后台自己默默的记录EXCEL文件。
CString sFile,sPath;CString strChuType = "-";CString strChuStatus = "-"; GetModuleFileName(NULL,sPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH); //获取主程序所在路径,存在sPath中sPath.ReleaseBuffer ();int nPos;nPos=sPath.ReverseFind ('\\');sPath=sPath.Left (nPos);sFile = sPath + "\\ServerOption"; sFile = sFile +"\\ChuStatusRecord";sFile = sFile + "\\" + pGroup->GetNeName() +"(" +TheUser.GetCurrentUserName() + ")"+".xls";HANDLE hFile = CreateFile(sFile, GENERIC_READ, FILE_SHARE_READ, NULL, OPEN_EXISTING, NULL, NULL);if (hFile == INVALID_HANDLE_VALUE){HRESULT hr = GetLastError();if ( ERROR_ACCESS_DENIED == hr || ERROR_SHARING_VIOLATION == hr){return ;}} else{// 文件未被打开CloseHandle(hFile);}CString strSheet, str;CStringArray sampleArray, testRow;CSpreadSheet SS(sFile, "CHU"); //新建Excel文件名及路径,TestSheet为内部表名SS.BeginTransaction();sampleArray.RemoveAll(); str.LoadString(IDS_EQUIPTYPE); //设备类型sampleArray.Add(str);str.LoadString(IDS_NE_NAME);//设备名称sampleArray.Add(str);str.LoadString(IDS_EQP_DLDIP);//设备地址sampleArray.Add(str);str.LoadString(IDS_SLOT);//槽号sampleArray.Add(str);str.LoadString(IDS_CHUNO);//话路编号sampleArray.Add(str);str.LoadString(IDS_CHNTYPE);//话路类型sampleArray.Add(str);str.LoadString(IDS_CHU_STATUS);//话路状态sampleArray.Add(str);str.LoadString(IDS_ALMLISTBEGINTIME);//开始时间sampleArray.Add(str);str.LoadString(IDS_ALMLISTENDTIME);//结束时间sampleArray.Add(str);SS.AddHeaders(sampleArray);CHHCardBase* pCardBase = dynamic_cast<CHHCardBase*>(pGroup->DBase->GetSuperCardBase(ucSystemCardSlot));if( pCardBase != NULL ){BYTE bCount = pParam[5];int OFFSET = 6;for (int index = 0; index < bCount ; index++){BYTE bChuNo = pParam[OFFSET++];BYTE bChuType = pParam[OFFSET++];BYTE bChuStatus = pParam[OFFSET++];CHHPort* pPort = pCardBase->GetPortByType( bChuNo, Port_CHU );if( pPort == NULL )continue;CHHChuIfModule* pModule = dynamic_cast<CHHChuIfModule*>(pPort->GetModule( Mod_ChuIf ));if( pModule == NULL )continue;str.Empty();sampleArray.RemoveAll();str = pGroup->GetSeriesName();sampleArray.Add(str);str = pGroup->GetNeName();sampleArray.Add(str);str = pGroup->GetNeAddressStr();sampleArray.Add(str);str.Format("%d", bSlot+1);sampleArray.Add(str);str.Format("CHU-%d", bChuNo +1);sampleArray.Add(str);strChuType = GetChuTypeString(bChuType);if (bChuType == 0 || bChuType == 4 || bChuType == 5 ||bChuType == 6){strChuStatus = GetChuStatusString(bChuStatus);}else{strChuStatus = "-";}sampleArray.Add(strChuType);sampleArray.Add(strChuStatus);CTime time;time=CTime::GetCurrentTime();str.Format("%d-%d-%d-%d:%d:%d",time.GetYear(),time.GetMonth(),time.GetDay(),time.GetHour(), time.GetMinute(), time.GetSecond());sampleArray.Add(str);sampleArray.Add("-");int iRow = SS.GetTotalRows() + 1;SS.AddRow(sampleArray, iRow, true);int iExcelRow = pModule->GetExcelRow();if (iExcelRow != 0){SS.AddCell(str, 9, iExcelRow );}pModule->SetExcelRow(iRow);}SS.Commit();3、适合用于将界面上的内容所见及所得的输出,可以设置单元格的的合并,大小,字体,颜色等等;
void CFileRecord::OutPutToExcelForMFD(GROUP* pGroup) {// TODO: Add extra validation here//用m_ExlApp对象创建Excel2003进程 if(!m_ExlApp.CreateDispatch("Excel.Application",NULL)) { AfxMessageBox("创建Excel服务失败!"); return; } //设置为可见 m_ExlApp.SetVisible(TRUE); ///////////////////下面得到应用程序所在的路径/////////////////// CString theAppPath,sPath, strSheet; GetModuleFileName(NULL,sPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH);sPath.ReleaseBuffer ();int nPos;nPos=sPath.ReverseFind ('\\');sPath=sPath.Left (nPos);theAppPath = sPath + "\\System\\"; //////////////////////////////////////////////////////////////// CString TempPath=""; TempPath=theAppPath+"Template.xls";//EXCEL模板的路径 m_ExlBooks.AttachDispatch(m_ExlApp.GetWorkbooks(),TRUE); m_ExlBook.AttachDispatch(m_ExlBooks.Add((_variant_t)TempPath),TRUE);//加载EXCEL模板 m_ExlSheets.AttachDispatch(m_ExlBook.GetSheets(),TRUE);//加载Sheet页面 //添加新的Sheet页面 m_ExlSheets.Add(vtMissing,vtMissing,_variant_t((long)1),vtMissing); //删除第二个Sheet页面 m_ExlSheet.AttachDispatch(m_ExlSheets.GetItem(_variant_t((long)2)),TRUE); m_ExlSheet.Delete(); //把第一个Sheet页面的名字改变为TestSheet m_ExlSheet.AttachDispatch(m_ExlSheets.GetItem(_variant_t((long)1)),TRUE); strSheet.LoadString(IDS_CHUFACTORYSETTING); m_ExlSheet.SetName(strSheet); ///////合并第一行单元格A1至D1////// //加载要合并的单元格 m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(_variant_t("A1"),_variant_t("H2")),TRUE); m_ExlRge.Merge(_variant_t((long)0)); ////////设置表格内容//////// m_ExlRge.AttachDispatch(m_ExlSheet.GetCells(),TRUE);//加载所有单元格 m_ExlRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(strSheet)); DBASE *pDBase = pGroup->DBase; if(pDBase == NULL) { return; } int nConfirmNum = pDBase->GetCardCount(); //设备拥有的槽数 if (nConfirmNum == 0) {return; } int nMaxChuCount = 0; //设备单盘拥有的最大话路数 CString strChuType = "-"; CString strChuStatus = "-"; CString str,strCtrl; int iTempCount = 0; for(int iSlot=1; iSlot <= nConfirmNum; iSlot++) { str.LoadString(IDS_STRINGCHANNEL); strCtrl.LoadString(IDS_SLOT); str = strCtrl+ "|" + str; m_ExlRge.SetItem(_variant_t((long)(3)),_variant_t((long)1),_variant_t(str)); str.Format("%d:",iSlot); strCtrl.LoadString(IDS_SLOT); str = strCtrl+str; m_ExlRge.SetItem(_variant_t((long)(3+iSlot)),_variant_t((long)1),_variant_t(str)); CHHCardBase *pCardBaseCtrl = dynamic_cast<CHHCardBase*>( pDBase->GetSuperCardBase(iSlot)); if (pCardBaseCtrl != NULL) { int iSlotCtrl = 0; if (pGroup->GetExtModelID() == MFA) //MFA特殊处理 { switch(iSlot) { case 1: iSlotCtrl = 1; break; case 2: iSlotCtrl = 2; break; case 3: iSlotCtrl = 3; break; case 4: iSlotCtrl = 4; break; case 5: iSlotCtrl = 24; break; case 6: iSlotCtrl = 23; break; case 7: iSlotCtrl = 22; break; case 8: iSlotCtrl = 21; break; case 9: iSlotCtrl = 14; break; case 10:iSlotCtrl = 13; break; case 11:iSlotCtrl = 12; break; case 12:iSlotCtrl = 11; break; case 13:iSlotCtrl = 13; break; case 14:iSlotCtrl = 14; break;} } else { switch(iSlot) { case 1: iSlotCtrl = 1; break; case 2: iSlotCtrl = 2; break; case 3: iSlotCtrl = 3; break; case 4: iSlotCtrl = 24; break; case 5: iSlotCtrl = 23; break; case 6: iSlotCtrl = 22; break; case 7: iSlotCtrl = 21; break; case 8: iSlotCtrl = 14; break; case 9: iSlotCtrl = 13; break; case 10:iSlotCtrl = 12; break; case 11:iSlotCtrl = 11; break; case 12:iSlotCtrl = 12; break; case 13:iSlotCtrl = 13; break; case 14:iSlotCtrl = 14; break; } } strCtrl = pCardBaseCtrl->GetCardTypeName(); str.Format("%d:%s",iSlotCtrl,strCtrl); strCtrl.LoadString(IDS_SLOT); str = strCtrl+str; m_ExlRge.SetItem(_variant_t((long)(3+iSlot)),_variant_t((long)1),_variant_t(str)); } } CHHCardBase *pCardBase = dynamic_cast<CHHCardBase*>( pDBase->GetSuperCardBase(ucSystemCardSlot)); //系统盘 if (pCardBase != NULL) {int iChuCount = pCardBase->GetSpecifyPortCount(Port_CHU); for (int iChuNum = 0 ; iChuNum < iChuCount ; iChuNum++){ int iChuType = 0xff; int iChuStatus = 0xff; CHHPort* pChuPort = pCardBase->GetPortByType( iChuNum, Port_CHU ); if (pChuPort != NULL) { int iChuNumCtrl =0; int iSlotCtrl = 0; if (iChuNum < 30) { iChuNumCtrl = iChuNum%8; iSlotCtrl = 10- iChuNum/8; } else { iChuNumCtrl = (iChuNum+2)%8; iSlotCtrl = 10 - (iChuNum+2)/8 ; } if(!pChuPort->GetPortIsUsedForChu()) { continue; } CHHChuIfModule *pModule = dynamic_cast<CHHChuIfModule*>(pChuPort->GetModule(Mod_ChuIf)); if(pModule == NULL) { continue; } iChuType = pModule->GetChuType(TRUE); str.Format("%d", iChuNumCtrl+1); str = "CH"+ str; m_ExlRge.SetItem(_variant_t((long)3),_variant_t((long)(2+iChuNumCtrl)),_variant_t(str)); str = GetChuTypeString(iChuType); if (pGroup->GetExtModelID() == MFA) //MFA特殊处理 { m_ExlRge.SetItem(_variant_t((long)(5+iSlotCtrl)),_variant_t((long)(2+iChuNumCtrl)),_variant_t(str)); } else {m_ExlRge.SetItem(_variant_t((long)(4+iSlotCtrl)),_variant_t((long)(2+iChuNumCtrl)),_variant_t(str)); } if (iChuNumCtrl >= nMaxChuCount) { nMaxChuCount = iChuNumCtrl +1; } }} } //加载要合并的单元格 switch (nMaxChuCount) { case 8: m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(_variant_t("A1"),_variant_t("I2")),TRUE); m_ExlRge.Merge(_variant_t((long)0)); break; case 10: m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(_variant_t("A1"),_variant_t("K2")),TRUE); m_ExlRge.Merge(_variant_t((long)0)); break; case 16: m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(_variant_t("A1"),_variant_t("Q2")),TRUE); m_ExlRge.Merge(_variant_t((long)0)); break; } ////////设置表格内容//////// m_ExlRge.AttachDispatch(m_ExlSheet.GetUsedRange());//加载已使用的单元格 m_ExlRge.SetWrapText(_variant_t((long)1));//设置单元格内的文本为自动换行 //设置齐方式为水平垂直居中 //水平对齐:默认=1,居中=-4108,左=-4131,右=-4152 //垂直对齐:默认=2,居中=-4108,左=-4160,右=-4107 m_ExlRge.SetHorizontalAlignment(_variant_t((long)-4108)); m_ExlRge.SetVerticalAlignment(_variant_t((long)-4108)); ///////设置整体的字体、字号及颜色////// Font ft; ft.AttachDispatch(m_ExlRge.GetFont()); ft.SetName(_variant_t("宋体"));//字体 ft.SetColorIndex(_variant_t((long)1));//字的颜色 ft.SetSize(_variant_t((long)10));//字号 ///////////设置标题字体及颜色////////// m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(_variant_t("A1"),_variant_t("H2"))); ft.AttachDispatch(m_ExlRge.GetFont()); ft.SetBold(_variant_t((long)1));//粗体 ft.SetSize(_variant_t((long)13)); ft.SetColorIndex(_variant_t((long)1)); CellFormat cf; cf.AttachDispatch(m_ExlRge.GetCells()); // //////////////设置底色///////////////// // // Interior it; // // it.AttachDispatch(m_ExlRge.GetInterior()); // // it.SetColorIndex(_variant_t((long)11));//标题底色 // // ////表格内容的底色//// // // m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(_variant_t("A2"),_variant_t("D5"))); // // it.AttachDispatch(m_ExlRge.GetInterior()); // // it.SetColorIndex(_variant_t((long)15)); //////////////为表格设置边框///////////// Range UnitRge; CString CellName; for(int i=1;i<=nConfirmNum+3;i++) { for(int j=1;j<=nMaxChuCount+1;j++) { CellName.Format("%c%d",j+64,i);//单元格的名称 UnitRge.AttachDispatch(m_ExlRge.GetRange(_variant_t(CellName),_variant_t(CellName)));//加载单元格 //LineStyle=线型 Weight=线宽 ColorIndex=线的颜色(-4105为自动) UnitRge.BorderAround(_variant_t((long)1),_variant_t((long)2),_variant_t((long)-4105),vtMissing);//设置边框 } } //释放对象(相当重要!) m_ExlRge.ReleaseDispatch(); m_ExlSheet.ReleaseDispatch(); m_ExlSheets.ReleaseDispatch(); m_ExlBook.ReleaseDispatch(); m_ExlBooks.ReleaseDispatch(); //m_ExlApp一定要释放,否则程序结束后还会有一个Excel进程驻留在内存中,而且程序重复运行的时候会出错 m_ExlApp.ReleaseDispatch(); //退出程序 m_ExlApp.Quit(); //CDialog::OnOK();}
- MFC输出EXCEL文档的过程总结
- owc输出excel文档
- MFC 单文档程序的运行过程
- Jsp输出excel文档和中文乱码问题的解决
- MFC框架的窗口、文档、视的创建过程
- MFC启动过程&单文档MFC程序的启动详细过程
- MFC启动过程&单文档MFC程序的启动详细过程
- MFC启动过程&单文档MFC程序的启动详细过程
- MFC启动过程&单文档MFC程序的启动详细过程
- MFC 单文档创建过程
- 探索MFC中单文档中m_pMainWnd的初始化过程
- MFC多文档的创建和打开过程
- MFC 的MDI创建空文档调用过程
- 图像输出过程总结
- 使用MFC自动化操作Excel文档
- MFC从数据库里导出excel文档
- MFC从数据库里导出excel文档
- MFC ODBC输出到Excel文件
- 程序员总结:帮助你早些明白一些道理
- win 2008 r2 + iis7,ubuntu server 服务器设置入门
- Qt线程基础
- 红梅
- 一套较完整的技术框架
- MFC输出EXCEL文档的过程总结
- ~/.viminfo权限引起的vim无法记住上次编辑的位置(附vim配置文件)
- pro bash programming学习笔记——第六章
- VC调试方法大全
- SQL Server优化50法
- 利用excel对记事进行时间排序
- 水晶报表异常整合
- 编程珠玑习题1.9分析和我的总结
- Tabhost的使用