[MFC]MFC输出到EXCEL表格并画图表

MSDN 上只有VBA的代码，vc的资料都是别人写的代码做参考

Excel VBA 参考

这些.h文件是通过excel.exe导入到项目中的，我的office是2016 目录是

C:Program Files (x86)Microsoft Office ootOffice16excel.exe

导入方法可以参考 MFC vc++ 写 word 文件，使用MSWORD.OLB 导入方法

向excel中写数据并画图

  1 #include "CApplication.h"
  2 #include "CChart.h"
  3 #include "CChartObject.h"
  4 #include "CChartObjects.h"
  5 #include "CCharts.h"
  6 #include "CRange.h"
  7 #include "CWorkbook.h"
  8 #include "CWorkbooks.h"
  9 #include "CWorksheet.h"
 10 #include "CWorksheets.h"
 11 
 12 void CMFCApplication1Dlg::OnBnClickedOk()
 13 {
 14     COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
 15     CApplication app;
 16     if (!app.CreateDispatch(_T("Excel.Application"), NULL))
 17     {
 18         AfxMessageBox(_T("创建Excel服务失败!"));
 19         return;
 20     }
 21     app.put_Visible(FALSE);//不显示EXCEL表
 22 
 23     CWorkbooks books;
 24     CWorkbook book;
 25     books = app.get_Workbooks();//获取一个工作簿集合
 26     book = books.Add(covOptional);//添加一个工作簿
 27 
 28     CWorksheets sheets;
 29     CWorksheet sheet;
 30     sheets = book.get_Sheets();//获取一个工作表集合
 31     sheet = sheets.get_Item(COleVariant((short)1));//获取一个工作表
 32 
 33     CRange range;
 34     range.AttachDispatch(sheet.get_Cells(), TRUE);//加载所有单元格
 35     range.put_Item(_variant_t((long)1), _variant_t((long)1), _variant_t("失败测试项"));//横坐标 纵坐标 内容
 36     range.put_ColumnWidth(_variant_t((long)10));//所有列的宽度
 37     range.put_Item(_variant_t((long)1), _variant_t((long)2), _variant_t("频次"));
 38     range.put_Item(_variant_t((long)1), _variant_t((long)3), _variant_t("累计百分比"));
 39     range.put_Item(_variant_t((long)1), _variant_t((long)4), _variant_t("频率"));
 40 
 41     range.put_Item(_variant_t((long)2), _variant_t((long)1), _variant_t("失败1"));
 42     range.put_Item(_variant_t((long)3), _variant_t((long)1), _variant_t("失败2"));
 43     range.put_Item(_variant_t((long)4), _variant_t((long)1), _variant_t("失败3"));
 44     range.put_Item(_variant_t((long)5), _variant_t((long)1), _variant_t("失败4"));
 45     range.put_Item(_variant_t((long)6), _variant_t((long)1), _variant_t("失败5"));
 46 
 47     range.put_Item(_variant_t((long)2), _variant_t((long)2), _variant_t(20));
 48     range.put_Item(_variant_t((long)3), _variant_t((long)2), _variant_t(20));
 49     range.put_Item(_variant_t((long)4), _variant_t((long)2), _variant_t(20));
 50     range.put_Item(_variant_t((long)5), _variant_t((long)2), _variant_t(20));
 51     range.put_Item(_variant_t((long)6), _variant_t((long)2), _variant_t(20));
 52 
 53     range.put_Item(_variant_t((long)2), _variant_t((long)3), _variant_t("20%"));
 54     range.put_Item(_variant_t((long)3), _variant_t((long)3), _variant_t("40%"));
 55     range.put_Item(_variant_t((long)4), _variant_t((long)3), _variant_t("60%"));
 56     range.put_Item(_variant_t((long)5), _variant_t((long)3), _variant_t("80%"));
 57     range.put_Item(_variant_t((long)6), _variant_t((long)3), _variant_t("100%"));
 58 
 59     range.put_Item(_variant_t((long)2), _variant_t((long)4), _variant_t("20%"));
 60     range.put_Item(_variant_t((long)3), _variant_t((long)4), _variant_t("20%"));
 61     range.put_Item(_variant_t((long)4), _variant_t((long)4), _variant_t("20%"));
 62     range.put_Item(_variant_t((long)5), _variant_t((long)4), _variant_t("20%"));
 63     range.put_Item(_variant_t((long)6), _variant_t((long)4), _variant_t("20%"));
 64 
 65     long left, top, width, height;
 66     left = 100;
 67     top = 10;
 68     width = 350;
 69     height = 250;
 70 
 71     CChartObjects excel_charts;
 72     CChartObject excle_chart;
 73     CChart chart;
 74     LPDISPATCH lpDisp;
 75     lpDisp = sheet.ChartObjects(covOptional);
 76     ASSERT(lpDisp);
 77     excel_charts.AttachDispatch(lpDisp);
 78 
 79     excle_chart = excel_charts.Add(left, top, width, height);
 80     chart.AttachDispatch(excle_chart.get_Chart());
 81 
 82     lpDisp = sheet.get_Range(_variant_t("A1"), _variant_t("C6"));//图标数据范围
 83     ASSERT(lpDisp);
 84     range.AttachDispatch(lpDisp);
 85 
 86     VARIANT var;
 87     var.vt = VT_DISPATCH;
 88     var.pdispVal = lpDisp;
 89 
 90     chart.ChartWizard(var,
 91         COleVariant((short)65),//图表类型折线图
 92         covOptional,//不知道有啥用
 93         COleVariant((short)2),
 94         COleVariant((short)1),
 95         COleVariant((short)1),//"系列1"用的个数
 96         COleVariant((short)TRUE),
 97         _variant_t("失败项柏拉图"),//图表标题
 98         _variant_t(""),//横坐标标题
 99         _variant_t(""),//纵坐标标题
100         _variant_t("")//此纵坐标标题
101     );
102     //保存
103     CString strExcelPath = _T("柏拉图.xlsx");//创建在我的文档中
104     book.SaveAs(_variant_t(strExcelPath), vtMissing, vtMissing, vtMissing, vtMissing,
105         vtMissing, 0, vtMissing, vtMissing, vtMissing,
106         vtMissing, vtMissing);
107 
        chart.ReleaseDispatch();
        chartObject.ReleaseDispatch();
108     range.ReleaseDispatch();
109     sheet.ReleaseDispatch();
110     sheets.ReleaseDispatch();
111     book.ReleaseDispatch();
        books.Close();
112     books.ReleaseDispatch();
113     app.Quit();
114     app.ReleaseDispatch();
115     //退出程序
116 }

这时生成的excel.xlsx文件是这样的

但此时这个图离要求还要调整图表

1.“累计百分比”的坐标轴，次坐标

2.设置“频数”的图表类型为柱形图

3.设置图表显示数据标签

4.移动图表位置

此时，用office excel 的视图 ->录制宏，生成的各个步骤的VBA代码是

 1 Sub 宏1()
 2 '
 3 ' 宏1 宏
 4 '
 5 
 6 '
 7     ActiveSheet.ChartObjects("图表 1").Activate
 8     ActiveChart.FullSeriesCollection(2).AxisGroup = 2
 9 End Sub
10 
11 Sub 宏2()
12 '
13 ' 宏2 宏
14 '
15 
16 '
17     ActiveSheet.ChartObjects("图表 1").Activate
18     ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
19 End Sub
20 Sub 宏3()
21 '
22 ' 宏3 宏
23 '
24 
25 '
26     ActiveChart.SetElement (msoElementDataLabelShow)
27 End Sub
28 Sub 宏4()
29 '
30 ' 宏4 宏
31 '
32 
33 '
34     ActiveChart.ChartArea.Select
35     ActiveSheet.Shapes("图表 1").IncrementLeft 155
36     ActiveSheet.Shapes("图表 1").IncrementTop -7
37 End Sub

这个代码转换成vc 的代码就ok了，但是查了半天MSDN 一些步骤找不到对应的函数，所以这个代码画简单图还是有点用，复杂的查msdn太煎熬了

这时又有另一个方法是

1.先把.xlsx文件做出来，图画好如图

2.写代码修改单元格的数据，再修改图表对应的单元格范围这样就可以比较灵活的生成一个柏拉图了

 1 void CMFCApplication2Dlg::OnBnClickedOk()
 2 {
 3     COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
 4     CApplication excel_app; //Excel应用程序
 5     if (!excel_app.CreateDispatch(_T("Excel.Application"), NULL))
 6     {
 7         AfxMessageBox(_T("创建Excel服务失败!"));
 8         return;
 9     }
10     excel_app.put_Visible(FALSE);//不显示EXCEL表
11     excel_app.put_UserControl(FALSE);//不允许其他用户控制Excel
12     //得到工作簿容器
13     CWorkbooks books;
14     books.AttachDispatch(excel_app.get_Workbooks());
15     //打开一个工作簿，如不存在，则新增一个工作簿
16     CWorkbook book;
17     LPDISPATCH lpDisp;
18     lpDisp = books.Open(_T("柏拉图Format.xlsx"),
19     vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,
20     vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,
21     vtMissing, vtMissing, vtMissing, vtMissing);
22     book.AttachDispatch(lpDisp);
23 
24     //得到工作簿中的Sheet的容器
25     CWorksheets sheets;
26     CWorksheet sheet;
27     sheets.AttachDispatch(book.get_Sheets());
28     sheet = sheets.get_Item(_variant_t((long)1));//获取一个工作表
29 
30     //修改一个单元格表示正确打开了这个文件
31     CRange range;
32     range.AttachDispatch(sheet.get_Cells(), TRUE);//加载所有单元格
33     range.put_Item(_variant_t((long)7), _variant_t((long)1), _variant_t("失败6"));//横坐标 纵坐标 内容
34     range.put_Item(_variant_t((long)7), _variant_t((long)2), _variant_t("10"));//横坐标 纵坐标 内容
35     range.put_Item(_variant_t((long)7), _variant_t((long)3), _variant_t("110%"));//横坐标 纵坐标 内容
36     range.put_Item(_variant_t((long)7), _variant_t((long)4), _variant_t("10%"));//横坐标 纵坐标 内容
37     CChartObject chartObject;
38     CChart chart;
39     lpDisp = sheet.ChartObjects(_variant_t((long)1));//第一个图表
40     ASSERT(lpDisp);
41     chartObject.AttachDispatch(lpDisp);
42     chart = chartObject.get_Chart();
43     LPDISPATCH lpRange;
44     lpRange = sheet.get_Range(_variant_t("A1"), _variant_t("C7"));//图标数据范围
45 
46     chart.SetSourceData(lpRange, _variant_t((long)2));
47 
48     //保存
49     //book.Save();
50     //另存为
51     CString strExcelPath = _T("柏拉图1.xlsx");//创建在我的文档中
52     book.SaveAs(_variant_t(strExcelPath), vtMissing, vtMissing, vtMissing, vtMissing,
53         vtMissing, 0, vtMissing, vtMissing, vtMissing,
54         vtMissing, vtMissing);
55     //关闭文件释放资源
       chart.ReleaseDispatch();
       lpRange->Release();//这个东西要释放掉
       chartObject.ReleaseDispatch();
56     range.ReleaseDispatch();
57     sheet.ReleaseDispatch();
58     sheets.ReleaseDispatch();
59     book.ReleaseDispatch();
       books.Close();
60     books.ReleaseDispatch();
61     excel_app.Quit();
62     excel_app.ReleaseDispatch();
63 }

结果如图

这个方法的缺点是图表的2个“系列”频次和累计百分比的单元格必须是挨着的2列数据，不然需要再从图表类得到“系列”的类，再修改“系列”的数据范围，那就不知道能不能行的通了

这里只对如何画柏拉图做讨论，不对举例的数据合理性做讨论，参考【工具】柏拉图的正确制作方法 Excel柏拉图怎么制作

延申问题：

1.在线程中写excel文件，Range::put_Item这个函数有中断，不知道啥原因，那就不要用了吧

2.可以在表格中写公式例如，range.put_Item(_variant_t((long)2), _variant_t((long)1), _variant_t("=C2"));

结果如图

设置字体和对齐方式

CFont0 excelFont;
CRange rangeFont;

1                 strRange.Format(_T("%s%d"), strTemp, stuTempFormat.iRow);// A1
2                 rangeFont.AttachDispatch(sheet.get_Range(_variant_t(strRange), _variant_t(strRange)), TRUE);//这里可以用一个范围
3                 excelFont.AttachDispatch(rangeFont.get_Font());
4                 excelFont.put_Name(_variant_t(strFontName)); //字体
5                 excelFont.put_Size(_variant_t((long)m_iFontSize)); //大小
6                 rangeFont.put_HorizontalAlignment(_variant_t((long)m_ExcelAlignment));//对齐方式

获取行数列数

在MFC中怎么获得Excel文档中已经使用了的行数和列数

    CRange range;
    range = sheet.get_UsedRange();
    range = range.get_Rows();
    long lCountRow = range.get_Count();
    range = range.get_Columns();
    long lCountCol = range.get_Count();