MFC输出EXCEL文档的过程总结
由于目前的工作内容涉及到了几处需要输出EXCEL表格的内容，因此在网上也查找了些资料，在下面总结下，以方便使用；
1、当你输出的内容已经在输出的时候全部知道，并且输出格式已经固定，那么你就可以使用下面的方法输出，用if( (pfile = fopen(strFileName, "wb+")) != NULL)
打开EXCEL，然后在其中用fprintf写入，用/T到下一个单元格，用/n到下一行；适合输出内容固定，格式固定的内容。
[cpp]
</pre><pre name="code" class="cpp">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文件。
[cpp]
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、适合用于将界面上的内容所见及所得的输出，可以设置单元格的的合并，大小，字体，颜色等等；
[cpp]
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();
}
摘自 开心
