写在前面:
最近练习写一个小程序的时候需要编程实现对Excel的操作,由于知识不足,能力有限,只好去百度寻求帮助,可惜没搜到我现在的水平可以解决的方案。后来使用Microsoft Visual Studio 2010 文档,终于搜到了这篇文章,一步步的教你实现Excel操作,顺着它,很容易就解决掉了这个问题。想想自己学习看英文资料也有段时间了,于是决定尝试翻译一下这篇文章与大家分享,顺便锻炼一下自己的阅读能力。
原文网址:http://www.codeproject.com/Articles/28083/Use-Visual-Studio-2008-C-CLI-to-Automate-Excel
参考资料:ASP.NET导入、导出EXCEL中的一些问题汇总(结束EXCEL进程)
闲话不提,书归正传.
项目概述
这篇文章的目的主要是告诉你如何在一个Windows窗体应用程序中使用MC++来操作Excel,因此我不会让这个例子变得很复杂。项目使用的数据都是虚构的。我们会建立一个.NET Windows窗体应用程序,然后放置一个Button控件去运行Excel并显示一个具有3个工作表的工作薄。我会教你一个在工作薄中增加和删除工作表的方法,然后告诉你一个建立并放置柱形图和折线图的方法,并说明如何设置它们的数据区域。下图是此项目在Office 2003下的最终效果图:
如果你使用Office 2007,它看起来会是这样(在接下来的例子中我都会使用Office2007):
创建项目
1.我使用VS2008和.NET Framework 2.0。在VS中新建一个项目。我选择Visual C++ CLR作为项目类型,使用Windows 窗体应用程序作为模板。
2.为了操作Excel,你需要在你的引用中添加一个Office主互操作程序集(PIA).打开你的项目属性页,然后点击"添加新引用"按钮,你就可以找到这个程序集。在.Net选项卡中,向下滚支直到找到"Microsoft.OfficeInterop.Excel".在我的电脑上有两个不同版本的程序集,11.0.0.0 和 12.0.0.0,我使用可以支持Office2003和Office2007的11.0.0.0版本。我没有试过12.0.0.0,对我来说,11.0.0.0已经足够好了。现在就选择一个你希望尝试的,然后点击"确定"吧。
3.在你打开项目属性页时,在左边的窗口中选中"配置属性",然后在"公共语言运行时支持"选项中选择"安全 MSIL 公共语言运行时支持(/clr:safe)"。
4.在你的头文件(Form1.h)中添加如下代码:
using namespace Microsoft::Office::Interop::Excel;
5.我在头文件中也会加上下面一行代码:
#define Excel Microsoft::Office::Interop::Excel
这样一来,在引用Excel方法或属性时就不用输入"Microsoft::Office::Interop::Excel"这么一长串了。
6.为避免编译程序在系统应用程序和Excel应用程序中产生迷惑,你需要把Automate_Excel.cpp里的main方法中的下面语句:
// Create the main window and run it
Application::Run(gcnew Form1());
return 0;
改成:
System::Windows::Forms::Application::EnableVisualStyles();
System::Windows::Forms::Application::SetCompatibleTextRenderingDefault(false);
// Create the main window and run it
System::Windows::Forms::Application::Run(gcnew Form1());
return 0;
7.在Form1中添加一个"Button"控件。我把它命名为 butExcel ,把它的文本属性设为"Run Excel".
8.双击这个控件以在Form1.h中建立一个事件处理程序:
private: System::Void butExcel_Click(System::Object^ sender, System::EventArgs^ e) {
}
现在,如果你编译并运行,你将会看到:
接下来我们将建立一个方法去运行Excel,并在 butExcel_Click 事件处理程序中调用这一方法。
运行Excel的代码
1.在你的项目中添加一个能够建立并运行一个Excel应用程序的方法。
void Form1::RunExcel()
{
//1. Create a new Excel application with 3-sheet Workbook
Excel::Application^ exApp = gcnew Excel::ApplicationClass();
//2. Add a workbook (comes with three Worksheets)
Workbook^ exWb = exApp->Workbooks->Add(Type::Missing);
.
.
.
// Show the Workbook
exApp->Visible = true;
}
注意:如果你没有在头文件中添加第5步所添加的#define,你将不得不输入“Microsoft::Office::Interop::Excel::Application”,而不是简短的“Excel::Application”。
2.在butExcel的事件处理程序中调用这个方法:private: System::Void butExcel_Click(System::Object^ sender, System::EventArgs^ e) {
RunExcel();
}
3.现在编译和运行。点击Run Excel按钮后,将会启动Excel并打开一个拥有3个空工作表的工作薄。
删除,重命名,选择和添加一个工作表
1.假如你只需要两个工作表,那么你可以通过引用一个工作表所在的次序去删除这个工作表。注意,工作表的编号是从1而不是0开始的。下面的代码将会删除第二个(Sheet2)工作表。safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[2])->Delete();
2.尽管我没有在这个例子中这样做,但是你可以参考下面的向工作薄中添加2个工作表的代码去自由添加一个或多个工作表。
exWb->Worksheets->Add(Type::Missing,Type::Missing,2,Type::Missing);
3.如果你想在若干个工作表中使用特定的一个,你需要把这个工作表设为活动工作表。你可以参考下面这行把第二个工作表设为活动的代码.
safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[2])->Select(Type::Missing);
4.你可以创建一个引用活动工作表的跟踪句柄的变量,这样你就能够很容易的重命名工作表,并把它传递给创建图表的方法。创建工作薄后,第一个工作表就是活动工作表。
Worksheet^ exWs = safe_cast<Worksheet^>(exApp->ActiveSheet);
5.重命名活动工作表:
exWs->Name = "Charts";
控制的方法
我使用RunExcel()方法去创建和管理Excel.下面是相关代码:void Form1::RunExcel()
{
//1. Create a new Excel application
Excel::Application^ exApp = gcnew Excel::ApplicationClass();
//2. Add a workbook (comes with three Worksheets)
Workbook^ exWb = exApp->Workbooks->Add(Type::Missing);
//3. Delete the last two worksheets
safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[3])->Delete();
safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[2])->Delete();
//4. Create a variable for the active Worksheet's tracking handle
// (first Worksheet is the default active one)
Worksheet^ exWs = safe_cast<Worksheet^>(exApp->ActiveSheet);
//5. Rename the active worksheet
exWs->Name = "Charts";
//6. Load the data
LoadData();
//7. Make the bar chart
MakeBarChart(exWs, 2, 1);
//8. Make a line chart
MakeLineChart(exWs, 2, 8);
// Show the Workbook
exApp->Visible = true;
}
下面是每一步所做的事:
1.这一步创建了一个应用。
2.第一步所创建的应用是空的,里面没有工作薄。这一步创建了一个工作薄,里面包含三个自动添加的工作表。
3.因为我只计划使用一个表格,因此我删除了其它的两个。首先删除最后一个,这样会比较好一些。
4.我会向创建图表的方法中传递一个工作表的引用。因此这里我创建了一个指向活动活动工作表的引用。当你在一个应用中添加一个工作薄时,第一个工作表,Sheet1,将会是默认的活动工作表。你可以使用下面的代码来把另一个工作表设为活动的:
safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets->Item[3])->Select(Type::Missing);
这样第三个工作表就会变为活动的了。
5.这一行重命名了活动工作表。
6.调用加载数据的方法。
7.调用一个创建柱形图的方法。我向它传递了3个参数,一个是指向图表和数据所存放的工作表的引用,另外两个分别是使用的数据起始位置的行和列的编号。
8.调用一个创建拆线图的方法。相关信息同第7步。
9.所有工作做完后,把应用设为可见的。
加载数据
因为这篇文章讨论的是Excel,因此我准备在方法LoadData()中虚构相关数据。我把港口名称和材料吨位存放到一个用来生成柱形图的SortedList中。我同时创建了另外两个SortedList以用来创建折线图,一个是计划吨位,一个是实际吨位。下面是代码:void Form1::LoadData()
{
slTonsRcvd = gcnew SortedList(); //Global, declared in Form1.h
slByDayNYProjected = gcnew SortedList(); //Global, declared in Form1.h
slByDayNYActual = gcnew SortedList(); //Global, declared in Form1.h
slTonsRcvd->Add("New York", 46.826);
slTonsRcvd->Add("New Jersey", 21.865);
slTonsRcvd->Add("Boston", 4.8);
slTonsRcvd->Add("Los Angles", 30.87);
slTonsRcvd->Add("Portland", 16.4876);
slByDayNYProjected->Add(1, 2.0);
slByDayNYProjected->Add(2, 11.5);
slByDayNYProjected->Add(3, 7.5);
slByDayNYProjected->Add(4, 5);
slByDayNYProjected->Add(5, 10);
slByDayNYProjected->Add(6, 6.5);
slByDayNYProjected->Add(7, .5);
slByDayNYActual->Add(1, 2.3);
slByDayNYActual->Add(2, 12.345);
slByDayNYActual->Add(3, 8.331);
slByDayNYActual->Add(4, 5.702);
slByDayNYActual->Add(5, 10.45);
slByDayNYActual->Add(6, 6.718);
slByDayNYActual->Add(7, .98);
}
制作一个柱形图
下图显示的是我想制作的柱状图,图形大小,使用的数据来源,以及我想让它出现的位置。这个图显示了一个虚构的不同港口的货物接收数量:
我想把数据放在工作表的前两列,图表紧贴着数据。我想把吨位列的格式设置为保留两位小数,但是在图表中显示的是整型。我需要图表标题,而且X轴和Y轴均有标题。
下面是相关代码,代码后面是解释:
void Form1::MakeBarChart(Worksheet ^ws, int row, int col)
{
int xPos = (col+2)*48; //Col width 48 points. Chart starts in 3rd col
int yPos = row*9; //Row height = 9, Chart starts in 2nd row
double tons = 0;
String^ port;
//1. Format a Worksheet column to 2 decimal places for chart data
ws->Range["B1", Type::Missing]->EntireColumn->NumberFormat = "#,##0.00";
//2. Set all Worksheet column widths to 12 to fit column titles and data
safe_cast<Range^>(ws->Columns)->ColumnWidth = 12;
//3. Extract Tons Received data from the SortedList and place on the chart
IDictionaryEnumerator^ ide = slTonsRcvd->GetEnumerator();
while (ide->MoveNext()) {
port = ide->Key->ToString();
tons = Convert::ToDouble(ide->Value);
ws->Cells[row, col] = port;
ws->Cells[row, col+1] = tons;
row++;
}
//4. Create a ChartObjects Collection for the Worksheet
ChartObjects^ chObjs = safe_cast<ChartObjects^>(ws->ChartObjects(Type::Missing));
//5. Add a ChartObject to the collection at(x, y, width, height) in points
ChartObject^ chObj = chObjs->Add(xPos, yPos, 300, 300);
//6. Create a chart from the ChartObject
Chart^ ch = chObj->Chart;
//7. Create a Range object & set the data range.
Range^ rn = ws->Range["A2:B6", Type::Missing];
//8. Do the chart using ChartWizard
ch->ChartWizard(rn->CurrentRegion, //Source
Constants::xlColumn, //Gallery
Type::Missing, //Format
XlRowCol::xlColumns, //Plot by
1, //Category Labels
Type::Missing, //Series Labels
false, //Has Legend
"Weekly Tons Received by Port", //Title
"Port", //Category Title (X)
"Tons", //Value Title (Y)
Type::Missing); //Extra Title
//9. Format the x-axis of the Cargo graph
safe_cast<Axis^>(ch->Axes(XlAxisType::xlValue, XlAxisGroup::xlPrimary))-> \
TickLabels->NumberFormat = "#,##0";
}
我使用一些简单的变量让代码变得容易讨论。下面做一些简单的解释:
1.我想在显示吨位数据时保留两位小数,因此在第一步中,对于整列都使用了数值格式。如果你不想让整列都格式化,你可以指定一个范围,比如仅格式化1-10行,你可以用"B1:B10"来替换"B1".假如你不想显示小数部分,你可以像我在第9步做的那样,使用"#,##0"做为格式化字符串.
2.我把整个工作表的列宽设置为12.如果你想调整一列的宽度,你可以这样做:
safe_cast<Range^>(ws->Columns["B1",Type::Missing])->EntireColumn->ColumnWidth = 12;
5.这里我们添加了一个Chart对象,并指定了它的位置和大小。所有参数都是整型的:X坐标,Y坐标,宽和高。
8.这是一个Chart向导方法。你可以在这里找到它的详细描述。
如果你现在运行程序并点击"Run Excel"按钮,你将会看到下图内容(使用Office2007,Office2003应该会有相似的结果):
制做一个折线图
折线图用来比较计划运抵的吨位数和七天内实际运抵的吨位数。数据列都有标题,而且在底部有图例来区分各条线。我将修改线条的颜色和厚度并且重新定义图例。
下面是相关代码:
void Form1::MakeLineChart(Worksheet ^ws, int row, int col)
{
int xPos = (col+5)*48; //Col width 48 points. Chart starts in 3rd col
int yPos = row*9; //Row height = 9, Chart starts in 2nd row
double tonsA = 0; //Actual tons
double tonsP = 0; //Projected tons
String^ day; //Day being plotted
String^ title = "Tons Received at NY port by day";
//1. Format two Worksheet columns to two decimal places for chart data
ws->Range["I1:J1", Type::Missing]->EntireColumn->NumberFormat = "#,##0.00";
//2. Reset the three Worksheet data column widths to better fit data
ws->Range["H1", Type::Missing]->EntireColumn->ColumnWidth = 5;
ws->Range["I1:J1", Type::Missing]->EntireColumn->ColumnWidth = 9;
//3. Put Column titles on the chart – two are Legend titles
ws->Cells[row, col] = "Day";
ws->Cells[row, col+1] = "Projected";
ws->Cells[row, col+2] = "Actual";
//4. Extract the data from two SortedLists and put it on the chart
IDictionaryEnumerator^ ide = slByDayNYProjected->GetEnumerator();
while (ide->MoveNext()) {
//Day and projected tons form one SortedList
day = ide->Key->ToString();
tonsP = Convert::ToDouble(ide->Value);
ws->Cells[row+1, col] = day;
ws->Cells[row+1, col+1] = tonsP;
//Use key to get actual tons form the other SortedList
tonsA = Convert::ToDouble(slByDayNYActual[ide->Key]);
ws->Cells[row+1, col+2] = tonsA;
row++;
}
//5. Create a ChartObject Collection for the Worksheet
ChartObjects^ chObjs = safe_cast<ChartObjects^>(ws->ChartObjects(Type::Missing));
//6. Add the ChartObject to the collection at(x, y, width, height) in points
// Width = 350 to prevent title from wrapping
ChartObject^ chObj = chObjs->Add(xPos, yPos, 350, 300);
//7. Create a chart from the ChartObject
Chart^ ch = chObj->Chart;
//8. Create a Range object & set the data range.
Range^ rn = ws->Range["I2:J9", Type::Missing];
//9. Do the chart
ch->ChartWizard(rn->CurrentRegion, //Source
XlChartType::xlLine, //Gallery
Type::Missing, //Format
XlRowCol::xlColumns, //Plot by
1, //Category Labels
1, //Series Labels
true, //Has Legend
title, //Title
"Day", //Category Title
"Tons", //Value Title
Type::Missing); //Extra Title
//10. Tell it the chart type again - initially comes up as a "lineMarked" type
ch->ChartType = safe_cast<XlChartType>(XlChartType::xlLine);
//11. Position the Chart Legend from the side to the bottom
ch->Legend->Position = XlLegendPosition::xlLegendPositionBottom;
//12. Format the Y-axis numbers to integers
safe_cast<Axis^>(ch->Axes(XlAxisType::xlValue, \
XlAxisGroup::xlPrimary))->TickLabels->NumberFormat = "#,##0";
//13. Make the lines thick
safe_cast<Series^>(ch->SeriesCollection(1))->Border->Weight = \
XlBorderWeight::xlThick;
safe_cast<Series^>(ch->SeriesCollection(2))->Border->Weight = \
XlBorderWeight::xlThick;
//14. Change the line colors
safe_cast<Series^>(ch->SeriesCollection(1))->Border->ColorIndex = 3;
safe_cast<Series^>(ch->SeriesCollection(2))->Border->ColorIndex = 32;
}
如果你编译和运行,你将会得到下面的图表:
结束语
小警告:当我使用Office2003时,我发现在我关闭Excel时,有时候EXCEL.EXE仍然活跃在内在中。当我下次打开Excel时,就会有两个EXCEL.EXE实例运行而且Excel的标题会显示Book2(或者Book3或者进程运行数量的数字),而不是Book1.除非我从任务管理器中强制强束所有的EXCEL.EXE进程,不然这种情况会一直继续下去。到目前为止,我还没在Office2007中发现这种情况。