MFC输出EXCEL过程总结.docx

由于目前的工作内容涉及到了几处需要输出EXCEL表格的内容，因此在网上也查找了些资料，在下面总结下，以方便使用；1、当你输出的内容已经在输出的时候全部知道，并且输出格式已经固定，那么你就可以使用下面的方法输出，用if( (pfile = fopen(strFileName, wb+) != NULL)打开EXCEL，然后在其中用fprintf写入，用T到下一个单元格，用n到下一行；适合输出内容固定，格式固定的内容。cppvoid 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 + tn); 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_vRqNeDxcBiDspiDxcID; else rqNeDxc = m_vRqNeDxcUniDspiDxcID - m_vRqNeDxcBiDsp.size(); else rqNeDxc = m_vRqDxciDxcID; 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 + tn); 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 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(pGroup-DBase-GetSuperCardBase(ucSystemCardSlot);if( pCardBase != NULL ) BYTE bCount = pParam5; int OFFSET = 6; for (int index = 0; index GetPortByType( bChuNo, Port_CHU ); if( pPort = NULL ) continue; CHHChuIfModule* pModule = dynamic_cast(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、适合用于将界面上的内容所见及所得的输出，可以设置单元格的的合并，大小，字体，颜色等等；cppvoid 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( 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.