【翻译】用EXcel扩展.NET应用程序

Extend your .NET application with Excel

用EXcel扩展.NET应用程序

by Tony Patton
作者：Tony Patton
翻译：Purple Endurer

Keywords:.Net | Microsoft Office | Office suites | Programming languages | Software engineering/development
关键字：.Net | Microsoft Office | Office suites | 程序设计语言 | 软件工程/开发

http://techrepublic.com.com/5100-3513_11-5815515.html?tag=nl.e101

Takeaway:
A .NET application may be greatly enhanced by providing additional functional via Excel integration. This includes the number-crunching capabilities inherent in Excel, as well as charting and much more. Learn more about Excel and .NET integration.

概述：
通过Excel集成提供附加功能，一个.NET应用程序能得到巨大的增强。这包括Excel固有的数字处理能力，以及图表等等。让我们学习更多关于Excel和.NET集成的内容。

---

In a recent column, we explored the process of integrating Microsoft Word with the .NET Framework. There are numerous integration possibilities as the full power of the Microsoft Office Suite is available. In this article, we examine another scenario involving Microsoft Excel.

在近期专栏里，我们浏览了微软Word与.NET Framework集成的过程。当有微软OFFICE套件的全部功能可用时有许多的集成可能性，在这篇文章里，我们检验其他涉及微软Excel的情况。

VBA persists

We must point out that the Microsoft Office product suite utilizes the Visual Basic for Applications (VBA), so a little knowledge of the Component Object Model (COM) object is helpful. However, the .NET COM interop feature makes it easy to utilize COM objects within a .NET application. Let's begin with an overview of the Excel object model.

VBA持续
我们必须指出微软OFFICE产品套件使用了VBA，所以略知组件对象模型（COM）是有帮助的。然而.NET COM互操作特性使它易于利用.NET应用程序内的COM对象。

Excel object model

Microsoft Excel provides literally hundreds of objects for programmatically working within its environment. The whole set is beyond the scope of this article, so let's examine a few objects to get up and running. Here are four common objects:

Excel对象模型
微软Excel精确地提供了许许多多利于在其环境下工作的对象。这个全集超出了这篇文章的范畴，所以我们检验一些对象来获取和运行。这有四个常用对象。

• Application: Represents the entire Excel application. It exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance.
  Application：代表整个Excel应用程序。它展示了正在运行的应用程序大量信息，应用到实体的选项，和实体内当前用户对象。
• Workbook: A single Excel workbook that may contain one or more worksheets. Workbook（工作簿）：一个单一Excel 工作簿（workbook）可能包含一个或更多工作表（worksheets）。
• Worksheet: An individual Excel worksheet. Most of the properties, methods, and events of the Worksheet object are identical or similar to members provided by the Application and/or Workbook classes.
  worksheets（工作表）：一个单独的工作表。工作表的绝大多数属性、方法和事件是等同或类似于Application和/或Workbook对象提供的成员的。
• Range: A range of cells within a worksheet. A Range object represents a cell, a row, a column, a selection of cells containing one or more blocks of cells, or even a group of cells on multiple sheets.
  Range：是工作表内的单元格区域。一个Range对象代表一个单元格，一行，一列，一个包含一个或多个单元格块的选定区域，甚至是一群多个工作表中的单元格。

The object model begins with the Application class at the top, since it is the starting point for accessing Excel. Before you can begin working with the Excel object model via .NET COM interop, you must make it available to your project.

对象模式从顶端的Application类开始，因为这是访问Excel的起点。在你可能通过.NET COM互操作开始与Excel对像模型工作前，你必须使它对你的项目可用。

Using Microsoft Excel

The Microsoft Excel Object Library must be made available to your .NET project. If using Visual Studio .NET, a reference may be added to a project via the Project | Add Reference menu selection. The COM tab within the Add Reference window provides access to COM libraries installed on the system. Excel is listed as Microsoft.Excel, and the specific name will depend on the Excel version installed. I have Excel 2003 on my system, so the COM library is listed as Microsoft.Excel 11.0 Object Library. In addition, two namespaces are necessary:

使用微软Excel
必须使微软Excel对象库对你的.NET工程可用。如果使用Visual Studio .NET，必须通过选择菜单：工程（Project） | 添加引用（Add Reference）来 添加一个引用。添加引用窗口中的COM选项卡提供了对系统中安装的COM库的访问。Excel被列为Microsoft.Excel，specific name将依赖于你安装的Excel版本。我的系统有Excel 2003，所以COM库列为Microsoft.Excel 11.0对象库。另外，两个名空间是必需的：

• Microsoft.Office.Interop.Excel: Allows you to work with Excel objects via .NET interop.
  Microsoft.Office.Interop.Excel：允许你与通过.NET互操作与Excel对象工作。
  
• System.Runtime.InteropServices: Includes the COMException class, allowing you to properly handle COM-related exceptions.
  System.Runtime.InteropServices：包括了COM异常类，允许你正确地处理COM相关异常。

The code snippet in Listing A loads and opens an Excel file from the local file system.

清单A里的代码段从本地文件系统装载并打开了一个Excel文件。

---

Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;

object  missing  = Type.Missing;

try  {

excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Open("c://test.xls", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
excel.Visible = true;
wb.Activate();

}  catch (COMException ex) {
MessageBox.Show("Error accessing Excel: " + ex.ToString());

} catch (Exception ex) {
MessageBox.Show("Error: " + ex.ToString());

}

---

( Listing B contains the equivalent VB.NET code.)
（清单B包含了等价的VB.NET的代码）

---

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Try
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open("c://test.xls")
excel.Visible = True
wb.Activate()
Catch ex As COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString())
End Try

Here are a few notes on the code:

代码中的一些注解：

• The Open method of the Workbooks object (accessed via the Application object) allows you to access an existing Excel file. Note: As a C# developer, the Type.Missing value is necessary since the Excel VBA object model accepts numerous optional parameters. The Type.Missing value allows you to pass nothing to the parameter, but still recognize it. VB.NET supports optional parameters to this approach.
  Workbooks对象（通过Application对象访问）的Open方法允放你访问一个存在的Excel文件。注意：作为C#开发者，Type.Missing值是必要的，因为 Excel VBA对像模型接收大量可选参数。Type.Missing值允许你不传送参数，但仍能识别。VB.NET支持可选参数。
  
• The Application object is set to visible and the Workbook object is activated to make it show on the screen.
  Application对象是被设置为可见，并且Workbook对象被激活以显示在屏幕上。
  
• The catch blocks handle specific COM-related exceptions as well as general exceptions.
  catch块处理特殊的COM相关异常和一般异常。
  

The previous code does not encompass a complete application, but demonstrates how Excel may be used in both C# and VB.NET. Let's take it a step further by manipulating the data within a worksheet.

前面的代码不包含完整的应用程序，但演示了怎样在C#和VB.NET使用Excel。让我们通过操作工作表中的数据来更进一步。

The VB.NET code in Listing C creates a new Excel sheet, inserts numbers, and performs a calculation.

清单C中的VB.NET代码创建了一个新的Excel工作表，插入数字，并进行计算。

---

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim excel As Microsoft.Office.Interop.Excel.Application

Dim wb As Microsoft.Office.Interop.Excel.Workbook

Dim ws As Microsoft.Office.Interop.Excel.Worksheet

Dim rng As Microsoft.Office.Interop.Excel.Range

Try

excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Add()
ws = wb.ActiveSheet()
rng = ws.Range("A1")
rng.Value = "Techrepublic.com"
rng = ws.Range("A3")
rng.Value = "Quarter"
rng = ws.Range("B3")
rng.Value = "Sales"
rng = ws.Range("A5")
rng.Value = "First"
rng = ws.Range("B5")
rng.Value = 1000.0
rng = ws.Range("A6")
rng.Value = "Second"
rng = ws.Range("B6")
rng.Value = 2000.0
rng = ws.Range("A7")
rng.Value = "Third"
rng = ws.Range("B7")
rng.Value = 4500.0
rng = ws.Range("A8")
rng.Value = "Fourth"
rng = ws.Range("B8")
rng.Value = 4000.0
rng = ws.Range("A10")
rng.Value = "Total"
rng = ws.Range("B10")
rng.Formula = "=@Sum(B5..B8)"
rng.Calculate()
excel.Visible = True
wb.Activate()

Catch ex As COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())

Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString())

End Try

End Sub

---

Only the code for a button is included. ( Listing D contains the equivalent C# code.)
这只是一个按钮的代码。（清单D包含了等价的C#代码。）

private void button1_Click(object sender, System.EventArgs e) {
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range rng = null;
object  missing  = Type.Missing;
try {
excel = new Microsoft.Office.Interop.Excel.Application();
wb = excel.Workbooks.Add(missing);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
rng = ws.get_Range("A1", missing);
rng.Value2 = "Techrepublic.com";
rng = ws.get_Range("A3", missing);
rng.Value2 = "Quarter";
rng = ws.get_Range("B3", missing);
rng.Value2 = "Sales";
rng = ws.get_Range("A5", missing);
rng.Value2 = "First";
rng = ws.get_Range("B5", missing);
rng.Value2 = 1000.0;
rng = ws.get_Range("A6", missing);
rng.Value2 = "Second";
rng = ws.get_Range("B6", missing);
rng.Value2 = 2000.0;
rng = ws.get_Range("A7", missing);
rng.Value2 = "Third";
rng = ws.get_Range("B7", missing);
rng.Value2 = 4500.0;
rng = ws.get_Range("A8", missing);
rng.Value2 = "Fourth";
rng = ws.get_Range("B8", missing);
rng.Value2 = 4000.0;
rng = ws.get_Range("A10", missing);
rng.Value2 = "Total";
rng = ws.get_Range("B10", missing);
rng.Formula = "=@Sum(B5..B8)";
rng.Calculate();
excel.Visible = true;
wb.Activate();
} catch (COMException ex) {
MessageBox.Show("Error accessing Excel: " + ex.ToString());
} catch (Exception ex) {
MessageBox.Show("Error: " + ex.ToString());
} }

Here are a few notes on the code:
代码中的一些注解：

• A new Excel Workbook is created with the Add method of the Workbook's property of the Application object. This creates a workbook with one blank worksheet.
  用Application对象的Workbook属性的Add方法创建一个新的工作簿。这创建一个有一个空工作表的工作簿。
  
• The current sheet is accessed via the ActiveSheet property of the Worksheet object.
  当前工作表可以通过Worksheet对象的ActiveSheet属性访问。
  
• A Range object is used to work with individual cells. The cell is accessed via its location on the sheet. For example, a title for the sheet (Techrepublic.com) is inserted at the first cell (A1 = column A and row 1). The Value property of the Range object is used to populate the cell.
  一个Range对象用于与单独的单元格工作。单元格可以通过它在工作表的位置来访问。例如，工作表标题(Techrepublic.com) 被插入到第一个单元格（A1 = 列 A 行 1）。
  Range对象的Value属性用于单元格置值。
  
• The Formula property of the Range object allows you to assign a formula to a cell or group of cells. In this example, the total of the second column of values is displayed.
  Range对象的Formula属于允许你指派一个公式到一个单元格或一群单元格。在这个例子中，第二列合计值被显示。
  
• The Calculate method of the Range object processes the formula.
  Range对象的Calculate方法处理公式。
  

You may notice some differences between the C# and VB.NET versions. The get_Range method of the Worksheet class is used to instantiate the Range object in C#. In addition, the get_Range method features a second optional parameter so the Type.Missing value is used. Finally, the ActiveSheet object must be cast to the Worksheet class to use it. You should be prepared for such differences when using C# to utilize VBA COM objects.

你可能注意到C#和VB.NET版本的一些不同。在C#中，Worksheet类的get_Range方法用来实例化Range对像，get_Range方法使用了第二个可选参数，所以用到了Type.Missing。最后，ActiveSheet对象必须被放到Worksheet类使用。当你使用C#来利用VBA COM对象时，你需要为这些不同作准备。

The environment

Some readers have questioned the vulnerability of a user's system when working with Excel, but the examples in this article are built as Windows Form-based applications. The environment envisioned is an internal application, so security should not be as big an issue as if it is opened to the world. Using Excel via ASP.NET provides its own set of issues, and it is beyond the scope of this article.

环境

一些读者已经询问与Excel工作时用户系统的弱点，但这篇文章中的例子是创建基于Windows平台的应用程序。预想环境是一个内部应用程序，所以不管它是否对世界畅开，安全不会是大问题。通过ASP.NET使用Excel提供了它自己的结果集，并且这超出了这篇文章的范畴。

Extending functionality

Integrating Excel with a .NET application allows you to easily and quickly provide powerful functionality within an application. The calculation and presentation features of Excel offer a wealth of options.

扩充功能性

集成Excel的.NET应用程序让你更轻松快捷地提供强大的功能。Excel的计算和表示特性提供了多样性。