VC中调用EXECL模板生成报表

最新推荐文章于 2020-11-24 01:41:10 发布

firy_lau1982

最新推荐文章于 2020-11-24 01:41:10 发布

阅读量940

点赞数

文章标签：报表 excel application path mfc office

本文链接：https://blog.csdn.net/firy_lau1982/article/details/1418576

版权

●新建Excel模板文件tmp.xlt

●准备
新建一对话框工程,打开添加类向导,选择“类型库中的MFC类”

选择Office安装文件夹下面的Excel9.OLB文件，然后选择_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range，加入新类，分别为_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range

将excel9.h excel9.cpp拷贝到工程目录并加入工程

●编码实现：
先保护两个头文件


#include "comdef.h"  
#include "Excel9.h"

添加一按钮，加入下列代码：


      _Application ExcelApp;  
      Workbooks wbsMyBooks;  
      _Workbook wbMyBook;  
      Worksheets wssMysheets;  
      _Worksheet wsMysheet;  
      Range rgMyRge;  

      //创建Excel 2000服务器(启动Excel)  
      if (!ExcelApp.CreateDispatch("Excel.Application",NULL))  
      {  
            AfxMessageBox("创建Excel服务失败!");  
            return;  
      }  
      ExcelApp.SetVisible(false);  

      //利用模板文件建立新文档  
      char path[MAX_PATH]; 
      GetCurrentDirectory(MAX_PATH,path); 
      CString strPath = path; 
      strPath += "/tmp"; 
      wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);  
      wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t(strPath))); 

      //得到Worksheets  
      wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true); 

      //得到sheet1  
      wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true); 

      //添加模板个数 
      for( int i=0; i<4; i++) 
      { 
            wsMysheet.Copy(vtMissing,_variant_t(wsMysheet)); 
      } 
      CString str1; 
      wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true); 
      str1 = "第1页"; 
      wsMysheet.SetName(str1); 
      for(i=0;i<wssMysheets.GetCount()-1;i++){ 
            wsMysheet = wsMysheet.GetNext(); 
            str1.Format("第%d页",i+2); 
            wsMysheet.SetName(str1); 
      } 
      wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("第1页")),true); 

      //得到全部Cells，此时,rgMyRge是cells的集合  
      rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);  

      //设置1行1列的单元的值  
      CString str=_TEXT("22:12"); 
      rgMyRge.SetItem(_variant_t((long)2),_variant_t((long)1),_variant_t("0001"));  
      rgMyRge.SetItem(_variant_t((long)2),_variant_t((long)2),_variant_t("哈哈")); 
      rgMyRge.SetItem(_variant_t((long)2),_variant_t((long)3),_variant_t("20"));  
      rgMyRge.SetItem(_variant_t((long)2),_variant_t((long)4),_variant_t("男"));  

      rgMyRge.SetItem(_variant_t((long)3),_variant_t((long)1),_variant_t("0002"));  
      rgMyRge.SetItem(_variant_t((long)3),_variant_t((long)2),_variant_t("我我")); 
      rgMyRge.SetItem(_variant_t((long)3),_variant_t((long)3),_variant_t("66"));  
      rgMyRge.SetItem(_variant_t((long)3),_variant_t((long)4),_variant_t("女"));  

      //将表格保存 
      strPath = path; 
      strPath += "/result.xls"; 
      DeleteFile(strPath); 
      wsMysheet.SaveAs(strPath,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing); 
      ExcelApp.SetVisible(true); 
       
      wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t(strPath))); 
      wbMyBook.PrintPreview(_variant_t(false));  
      ExcelApp.Quit(); 

      //释放对象  
      rgMyRge.ReleaseDispatch();  
      wsMysheet.ReleaseDispatch();  
      wssMysheets.ReleaseDispatch();  
      wbMyBook.ReleaseDispatch();  
      wbsMyBooks.ReleaseDispatch();  
      ExcelApp.ReleaseDispatch();  


}