锐英源精品开源心得，转载请注明：“锐英源www.wisestudy.cn，孙老师作品，电话13803810136。”需要全文内容也请联系孙老师。

Automating Excel 2007 and creating charts using C++ MFC application in Visual Studio 2008

在Visual Studio 2008中使用C++MFC应用程序自动化Excel 2007并创建图表

Introduction

This article describes how to automate Excel 2007 using a C++ MFC application. The solution also works for Excel 2010. I have also highlighted some of the issues faced while accomplishing this task. In this article I describe how to open up the Excel Application, how to create a worksheet and enter data into the worksheet and to create charts using the data in the worksheet. I used Visual studio 2008 for this project but I believe that it should work with VS 2010 as well.

介绍

本文介绍如何使用C++ MFC应用程序自动化Excel 2007。该解决方案也适用于Excel 2010。我也强调了一些完成这一任务所面临的问题。在本文中，我将介绍如何打开Excel应用程序，如何创建一个工作表，输入数据到工作表，并使用工作表中的数据创建图表。我用Visual Studio 2008讲解这个项目，但我认为，它应该使用于VS 2010。

Using the code

Assuming you have Microsoft Excel 2007 installed, open visual studio and create a new MFC application named as AutomateExcel.

使用代码

假设您已经安装Microsoft Excel 2007，打开Visual Studio，并创建一个名为AutomateExcel的新的MFC应用程序。

Chose the application type as Dialog based and click on Finish.

选择基于对话框的应用程序类型，然后单击Finish（完成）。

Click on Add Class from the Project menu and select MFC Class From TypeLib.

从项目菜单点击添加类，然后从类型库选择MFC类

In the Add Class From Typelib Wizard select the Registry option and select Microsoft Excel 12.0 Object Library<1.6> from the dropdown list.

Select the following interfaces:

_Application

_Chart

_Workbook

_Worksheet

Charts

Font

Range

Workbooks

Worksheets

从类型库添加类向导中选择注册表选项，从下拉列表中选择Microsoft Excel 12.0对象库<1.6>。

选择以下接口：

_Application

_Chart

_Workbook

_Worksheet

图表

字体

范围

工作簿

工作表

On clicking Finish, all the related header files will be created. Open AutomateExcelDlg.cpp and include all these header files.

单击完成，所有相关的头文件将被创建。打开AutomateExcelDlg.cpp，包括所有这些头文件。

#include "CApplication.h"
#include "CFont0.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
#include "CChart.h"
#include "CCharts.h" 

Try building your project at this point. You will get a large number of errors in excel.tlh file. In order to get rid of these errors you will need to comment out all the #import statements in all the header files that were created by Visual Studio. You can do a find all for "#import" and quickly go to each of the header files and comment those lines.

在这一点上尝试建立您的项目。你会在excel.tlh文件中得到大量的错误。为了摆脱这些错误，你将需要注释掉所有的＃import语句由Visual Studio创建的头文件。你可以做一个发现所有的“＃import”，迅速进入各头文件并注释这些行。

//#import "C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" no_namespace
...

Once you have commented out all the #import statements and saved all those files, try building your project again. You will get some syntax error in the file crange.h on the line "VARIANT DialogBox()". The key to resolving this error is to put an underscore in front of DialogBox().

一旦你已经注释掉所有的#import语句，并保存所有这些文件，请尝试重新建立你的项目。您将在crange.h 文件“VARIANT DialogBox的（）”行得到的一些语法错误。解决这个错误的关键是在DialogBox（）的前面放置下划线。

VARIANT _DialogBox()
...

Build again to make sure that your build succeeds. Now we are ready to write some code Smile | :) Open the AutomateExcel.cpp file and inside the InitInstance function add the following code:

再建立以确保您的构建成功。现在我们准备编写一些代码) 打开AutomateExcel.cpp文件和InitInstance函数里面添加以下代码：

Hide   Copy Code
if(!AfxOleInit())
{
AfxMessageBox(_T("Cannot initialize COM dll"));
return FALSE;
}
...      

AfxEnablAfxEnableControlContainer(); From your solution explorer expand Resource Files and double click on AutomateExcel.rc. In the Resource View expand the Dialog folder and double click on IDD_AUTOMATEEXCEL_DIALOG to open the dialog page of your application. Delete the initial label and also delete the Cancel button. Change the caption of the OK button to Run and rename the ID as IDRUN. Double click the Run button to create the OnBnClickedRun() event handler. Add the following code into this function:

从您的解决方案资源管理器中展开资源文件并双击AutomateExcel.rc。在资源视图扩大IDD_AUTOMATEEXCEL_DIALOG对话框文件夹，双击打开应用程序的对话框页面。删除最初的标签，同时删除取消按钮。改变OK按钮的标题来运行，并重新命名ID为IDRUN。双击运行按钮来创建OnBnClickedRun（）事件处理程序。将下面的代码添加到该函数：

void CAutomateExcelDlg::OnBnClickedRun()
{
// Commonly used OLE variants.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CApplication app;
// Start Excel and get an Application object.
if(!app.CreateDispatch(TEXT("Excel.Application")))
{
AfxMessageBox(TEXT("Couldn't start Excel and get Application object."));
return;
}
app.put_Visible(TRUE);
app.put_UserControl(TRUE);
}

Build the solution and run it. Click on the Run button and notice the Excel application open up. Congratulations! you have successfully completed your first automation task - Opening up the Excel application. Now lets add a workbook in it and add some information in the first sheet of the workbook.

构建解决方案，并运行它。点击运行按钮，注意Excel应用程序打开。恭喜！您已经成功地完成了第一个自动化任务 - 打开了Excel应用程序。现在，让我们在里面添加一个工作簿，并在工作簿的第一页添加一些信息。

if(!app.CreateDispatch(TEXT("Excel.Application")))
{
AfxMessageBox(TEXT("Couldn't start Excel and get Application object."));
return;
}.
CWorkbooks books;
CWorkbook book;
CWorksheets sheets;
CWorksheet sheet;
CRange range;
CFont0 font;
books = app.get_Workbooks();
book = books.Add (covOptional);
//Get the first sheet.
sheets =book.get_Sheets();
sheet = sheets.get_Item(COleVariant((short)1));
 
range = sheet.get_Range(COleVariant(TEXT("A1")),COleVariant(TEXT("A1")));
range.put_Value2(COleVariant(TEXT("Average precipation (mm)")));
range = sheet.get_Range(COleVariant(TEXT("A1")),COleVariant(TEXT("C1")));
range.Merge(covOptional);
range = sheet.get_Range(COleVariant(TEXT("B2")),COleVariant(TEXT("B2")));
range.put_Value2(COleVariant(TEXT("Acapulco")));
range = sheet.get_Range(COleVariant(TEXT("C2")),COleVariant(TEXT("C2")));
range.put_Value2(COleVariant(TEXT("Amsterdam")));
 
//Fill A3:A6 with an array of values (Months).
COleSafeArray saRet;
DWORD numElements[]={4,1};   //4x1 element array
saRet.Create(VT_BSTR, 2, numElements);
FillSafeArray(L"January", 0, 0, &saRet);
FillSafeArray(L"April", 1, 0, &saRet);
FillSafeArray(L"July", 2, 0, &saRet);
FillSafeArray(L"October", 3, 0, &saRet);
  
range = sheet.get_Range(COleVariant(TEXT("A3")), COleVariant(TEXT("A6")));
range.put_Value2(COleVariant(saRet));
saRet.Detach();
 
//Fill B3:C6 with values
range = sheet.get_Range(COleVariant(TEXT("B3")),COleVariant(TEXT("B3")));
range.put_Value2(COleVariant(short(10)));
range = sheet.get_Range(COleVariant(TEXT("B4")),COleVariant(TEXT("B4")));
range.put_Value2(COleVariant(short(69)));
range = sheet.get_Range(COleVariant(TEXT("B5")),COleVariant(TEXT("B5")));
range.put_Value2(COleVariant(short(5)));
range = sheet.get_Range(COleVariant(TEXT("B6")),COleVariant(TEXT("B6")));
range.put_Value2(COleVariant(short(53)));
range = sheet.get_Range(COleVariant(TEXT("C3")),COleVariant(TEXT("C3")));
range.put_Value2(COleVariant(short(208)));
range = sheet.get_Range(COleVariant(TEXT("C4")),COleVariant(TEXT("C4")));
range.put_Value2(COleVariant(short(76)));
range = sheet.get_Range(COleVariant(TEXT("C5")),COleVariant(TEXT("C5")));
range.put_Value2(COleVariant(short(145)));
range = sheet.get_Range(COleVariant(TEXT("C6")),COleVariant(TEXT("C6")));
range.put_Value2(COleVariant(short(74)));  
//Format A1:C1 as bold, vertical alignment = center.
range = sheet.get_Range(COleVariant(TEXT("A1")), COleVariant(TEXT("C1")));
font = range.get_Font();
font.put_Bold(covTrue);
range.put_VerticalAlignment(COleVariant((short)-4108));   //xlVAlignCenter = -4108
//AutoFit columns A:D.
range = sheet.get_Range(COleVariant(TEXT("A1")), COleVariant(TEXT("D1")));
CRange cols;
cols = range.get_EntireColumn();
cols.AutoFit();
.app.put_Visible(TRUE);
app.put_UserControl(TRUE);

Here we grab the first sheet of the workbook and enter data into it by using ranges. The FillSafeArray function is below

在这里，我们抓住工作簿的第一个工作表，并通过使用范围数据输入到它。该FillSafeArray功能如下

void FillSafeArray(OLECHAR FAR* sz, int iRow, int iCol,
COleSafeArray* sa)
{
VARIANT v;
long index[2];
index[0] = iRow;
index[1] = iCol;
VariantInit(&v);
v.vt = VT_BSTR;
v.bstrVal = SysAllocString(sz);
sa->PutElement(index, v.bstrVal);
SysFreeString(v.bstrVal);
VariantClear(&v);
}

Let's now add a chart real quick.

现在让我们真正快捷的添加图表

CCharts charts;
CChart chart;
charts = book.get_Charts();
chart = charts.Add(covOptional, covOptional, covOptional);

Build and run the project. Click on the Run Button. Here's how it looks for Excel 2007.

生成并运行该项目。点击运行按钮。下面是Excel 2007的外观。

The charts are created using the data from the currently active sheet by Excel 2007 and 2010. This sheet is the one we just created. There is a lot more that can be done with charts specially when you want to create your own charts choosing your series and axes. But for this article, I am going to keep it simple and end here. I will try to cover more things in a future article.

该图表是使用Excel 2007和2010当前活动工作表中数据创建的。这个数据表示我们刚刚创建的。还有很多可以通过图表完成，特别是当你想创建自己的图表选择您的系列和轴。但是对于这篇文章，我要保持简单，在这里结束。我会在以后的文章尽量覆盖更多的事情。

History

Version 1.0 历史 1.0版

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

许可证 这篇文章，以及任何相关的源代码和文件，是根据代码项目开放许可（CPOL）

About the Author

关于作者 abhinavsly