.NET操作Excel COM对象

多年来 COM 对象一直是 Windows 编程的基础，然而随着技术的进步和发展，微软推出了更佳出色的.NET。.NET Framework 提供了一个称为公共语言运行库的运行时环境(CLR)，它的托管执行过程，自动的内存管理，以及在版本的控制上都较COM技术有很大的提高。可以预见的是，.NET 平台应用程序将最终取代那些用 COM 开发的应用程序。但不可避免的是，在向.NET过渡时，我们还是需要继续使用现有的COM对象的。CLR不管所用的编程语言是什么，所有.NET 应用程序都共享一组公共类型，这些公共类型允许对象互操作。COM 对象的参数和返回值使用的数据类型有时会与托管代码中的有所不同。“互操作性封送处理”是一个打包过程，在将参数和返回值移动到 COM 对象或从 COM 对象移出时，此过程将这些参数和返回值打包为等价的数据类型。

公共语言运行库通过名为运行库可调用包装 (Runtime Callable Wrapper，RCW) 的代理来公开 COM 对象，如图所示。虽然 RCW 在 .NET 客户端看来是普通的对象，但它的主要功能是封送在 .NET 客户端和 COM 对象之间传递的调用。同时.NET提供Interop 程序集，它用作托管和非托管代码之间的桥梁，将 COM 对象成员映射为等价的 .NET 托管成员。

l .NET中如何引用COM组件？

方法一，通过IDE来生成PIA：

首先，工程添加引用，选择COM选项卡，选择Excel Object Library xx.0(xx为版本号，不同版本的Office，生成的PIA的版本也不同)。如下图所示：

这个引用过程就是RCW的打包过程，.NET自动创建 PIA。当然，你也可以通过.NET提供的工具Tlbimp.exe手动创建PIA。

方法二，手动生成PIA：

首先，启动.NET Framework 2003工具中的控制台：

然后找到当前操作系统 中安装的EXCEL.EXE的位置，输 入：

结果就会在指定目录里生成Excel.dll。当然，你还可以指定生成PIA的命名空间名称和程序集名。

生成之后的dll是经过包装后的.NET的程序集，可以直接引用。（很明显，是使用IDE来得方便，但有当要使用一个未在Windows上注册的COM组件时，就要使用到这个手动工具）。

引用之后就可以通过IDE的Object Browser来查看COM组件里提供的对象和方法了：

当然，由于语法的不同，.NET上不同语言封装之后的COM对象也稍稍有点不同，比如C#和VB.NET。上图是C#工程里的Object Browser。

另外，在ASP.NET应用开发中使用Excel COM组件还需要对该组件进行访问授权，因为ASP.NET程序的用户为ASPNET，而该用户在默认情况下是无权访问COM对象的。可以使用命令行命令dcomcnfg来对COM对象授权。

l Excel对象结构（Microsoft Excel object hierarchy）

当启动Excel应用程序的时候，将会启动一个Excel Application进程（进程名为：EXCEL.EXE），一个Excel文件相当于Excel Application中的一个Workbook对象。文件中的一个Sheet相当于Excel Workbook对象中的Worksheet对象，而Excel单元格，行，列，区域都是一个Range对象。Excel里的主要对象就是Workbook, Worksheet, Range。具体的类结构如下图：

你可以通过录制Excel Macro来了解操作Excel的方法，比如：赋值，格式化等操作。如下图：

然后按Alt+F11进入VBA编辑环境，查看代码。在.NET中利用Excel COM组件操作Excel 其方法和属性都跟VBA中的代码类似，如果是VB.NET有些VBA的代码甚至可以直接拷贝过来使用。

l 创建Excel对象

以下代码演示了怎么在.NET下新建一个Workbook，添加一个Worksheet，并对其中的单元格赋值，最后保存在当前程序运行目录下：

[C#]

using Excel;

_Application xlApp = null;

_Workbook xlWorkbook = null;

_Worksheet xlWorksheet = null;

System.Reflection.Missing oMissing = System.Reflection.Missing.Value;

string saveAsPath = "";

try

{

xlApp = new ApplicationClass();

xlApp.Visible = true;

xlWorkbook = xlApp.Workbooks.Add(oMissing);

xlWorksheet = xlWorkbook.Worksheets.Add(oMissing, oMissing, 1, oMissing) as _Worksheet;

xlWorksheet.Name = "NewWorksheet";

xlWorksheet.Cells[1, 1] = "Topic: ";

xlWorksheet.Cells[1, 2] = ".Net Interop Excel Demo";

saveAsPath = System.Windows.Forms.Application.StartupPath + "//" + xlWorkbook.Name;

xlWorkbook.SaveAs(saveAsPath, oMissing, oMissing, oMissing, oMissing,

oMissing, Excel.XlSaveAsAccessMode.xlShared, oMissing, oMissing, oMissing,

oMissing, oMissing);

xlApp.Quit();

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

xlApp = null;

GC.Collect();

}

在上面的代码中，你可能会注意到出现了许多oMissing对象：

System.Reflection.Missing oMissing = System.Reflection.Missing.Value;

这是因为有些方法（比如：SaveAs方法）的参数是可选的，因为使用的是 C#（C#没有VB/VB.NET中的可选参数），必须发送一个值表明缺少值。大家可能会认为可以简单地传递null，但是方法要求使用引用传递参数(VB/VB.NET中的ByRef，这些方法最初是由VB实现的)，因此无法使用null表示缺省值，而使用了System.Reflection.Missing.Value。

[VB.NET]

Imports Excel

Dim xlApp As Excel.Application

Dim xlWorkbook As Workbook

Dim xlWorksheet As Worksheet

Dim saveAsPath As String = ""

Try

xlApp = New Excel.Application

xlApp.Visible = True

xlWorkbook = xlApp.Workbooks.Add()

xlWorksheet = xlWorkbook.Worksheets.Add()

xlWorksheet.Name = "NewWorksheet"

xlWorksheet.Cells(1, 1) = "Topic: "

xlWorksheet.Cells(1, 2) = ".NET Interop Excel Demo"

saveAsPath = System.Windows.Forms.Application.StartupPath + "/" + xlWorkbook.Name

xlWorkbook.SaveAs(saveAsPath)

xlApp.Quit()

Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

If Not xlApp Is Nothing Then

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

xlApp = Nothing

GC.Collect()

End If

End Try

通过比较，可以看到对于Excel PIA的调用上，还是VB.NET要占便宜, 毕竟是VB/VB.NET一家亲。另外一点，上面的代码在最后都调用了

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

因为COM对象是非托管对象，虽然当RCW已经不在程序范围之内，并且不能再被程序访问，但是RCW没被垃圾回收器回收并销毁，那么它就没有真正释放被其包装的COM对象，所以内存的释放也必须另做处理。另外需要注意的是使用Excel Object Library COM对象不同的Office版本包装出来的PIA中的方法会有不同，尤其在使用C#进行编程的时候需要注意参数个数在不同版本下的变化。所以最好使用低版本的PIA以保证程序在安装了不同版本的机器上都能运行。

通过比较也可以发现，因为VB.NET的可选参数的语法，在操作Excel上，VB.NET的代码要比C#的代码更加的简洁。

l 几种Excel赋值方法的比较

通常在实际项目的开发中，对Excel的操作往往是很复杂的，除了复杂的格式化要求，还有大量的赋值操作。通常是从数据库里取出大量的数据在程序中处理之后再赋值给Excel的单元格里，大量的、连续的单元格赋值操作在数据量大的时候会明显的降低程序的效率。这里提出几种大量Excel单元格赋值的方法，供大家参考。

假设要将数据库里的以下数据导出到Excel中：

No	Name	Title	Department	Telephone	E-Mail
1	Jossef Goldberg	President & CEO	Office of the President	555-0100	[e-mail]
2	Ashley Larsen	Senior VP Sales & Mktg	Sales	555-0109	[e-mail]
3	Eric Lang	Corporate Counsel	Operations	555-0110	[e-mail]
4	Linda Leste	Treasurer	Finance	555-0111	[e-mail]
5	Ketan Dalal	Secretary	Finance	555-0112	[e-mail]

在本示例中，以“A1”作为开始单元格。

Delegate Sub SetValueToExcel(ByVal xlWorksheet As Worksheet, ByVal strBeginCell As String, ByVal objDataTable As System.Data.DataTable)

' SetValueToExcelCellByCell button click

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Me.MakeExcel("SetValueToExcelCellByCell.xls", AddressOf SetValueToExcelCellByCell)

End Sub

' SetValueToExcelByClipboard button click

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

Me.MakeExcel("SetValueToExcelByClipboard.xls", AddressOf SetValueToExcelByClipboard)

End Sub

' SetValueToExcelByResize button click

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Me.MakeExcel("SetValueToExcelByResize.xls", AddressOf SetValueToExcelByResize)

End Sub

Private Sub MakeExcel(ByVal strExcelName As String, ByVal subSetValueToExcel As SetValueToExcel)

Dim xlApp As Excel.Application

Dim xlWorkbook As Workbook

Dim xlWorksheet As Worksheet

Dim saveAsPath As String = ""

Try

xlApp = New Excel.Application

xlApp.Visible = True

xlWorkbook = xlApp.Workbooks.Add()

xlWorksheet = xlWorkbook.Worksheets.Add()

xlWorksheet.Name = strExcelName.Replace(".xls", "")

' Call Delegate

subSetValueToExcel(xlWorksheet, "A1", Me.objDataTable)

Me.FormatTable(xlWorksheet, "A1", Me.objDataTable)

saveAsPath = System.Windows.Forms.Application.StartupPath + "/" + strExcelName

xlApp.DisplayAlerts = False

xlWorkbook.SaveAs(saveAsPath)

xlApp.DisplayAlerts = True

xlApp.Quit()

Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

If Not xlApp Is Nothing Then

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

xlApp = Nothing

GC.Collect()

End If

End Try

End Sub

1． 利用Offset属性对Excel单元格赋值。

在Excel Object Library 的Range对象中提供了一个属性叫Offset，顾名思义就是根据该Range进行偏移，并返回偏移之后的Range对象：

Public Overridable ReadOnly Property Offset(Optional ByVal RowOffset As Object = Nothing, Optional ByVal ColumnOffset As Object = Nothing) As Excel.Range

利用这一属性，我们就可以在已知开始单元格的基础上进行偏移并赋值，而不用同时定位行和列的绝对位置：

Private Sub SetValueToExcelCellByCell(ByVal xlWorksheet As Worksheet, ByVal strBeginCell As String, ByVal objDataTable As System.Data.DataTable)

' Output the title

For i As Integer = 0 To objDataTable.Columns.Count - 1

xlWorksheet.Range(strBeginCell).Offset(0, i).Value = objDataTable.Columns(i).ColumnName

Next

' Output the value

For i As Integer = 0 To objDataTable.Rows.Count - 1

For j As Integer = 0 To objDataTable.Columns.Count - 1

xlWorksheet.Range(strBeginCell).Offset(1 + i, j).Value = objDataTable.Rows(i)(j).ToString().Trim()

Next

Next

End Sub

代码中，第一个循环输出表头(列名)，因为表头只占一行，所以行偏移量是0；第二个循环输出DataTable里的数据，因为表头占去第一行，所以行偏移量从1开始。

此方法相当于遍历了所有要赋值的单元格，一一进行赋值操作。也就是说当数据量为m*n的情况下，xlWorksheet.Range(strBeginCell).Offset(1 + i, j).Value被执行了m*n次，跨越托管堆到非托管堆的数据转移发生了m*n次。同时考虑到RCW将COM对象中方法的参数都包装成Object，因此这里还要发生大量的装箱操作，所以当数据量非常大的时候，该方法的速度是比较慢的。

2． 利用系统剪切板进行的赋值操作

这种方法是基于Excel格式的原理而考虑的，比如：将notepad中的以Tab分隔的数据拷贝粘贴到Excel中，你会发现原来Excel中的列与列是之间Tab符隔开，行与行之间是回车换行隔开的。

利用这种格式，我们可以想到，先将DataTable里的数据转化成Tab分隔的数据，再放到系统剪切板中，最后粘贴到Excel上就完成上面的赋值操作了。

Private Sub SetValueToExcelByClipboard(ByVal xlWorksheet As Worksheet, ByVal strBeginCell As String, ByVal objDataTable As System.Data.DataTable)

Dim objSB As System.Text.StringBuilder = New System.Text.StringBuilder

' Build the title

For i As Integer = 0 To objDataTable.Columns.Count - 1

objSB.Append(objDataTable.Columns(i).ColumnName)

If i < objDataTable.Columns.Count - 1 Then

objSB.Append(vbTab)

End If

Next

objSB.Append(vbCrLf)

' Build the value

For i As Integer = 0 To objDataTable.Rows.Count - 1

For j As Integer = 0 To objDataTable.Columns.Count - 1

objSB.Append(objDataTable.Rows(i)(j).ToString().Trim())

If j < objDataTable.Columns.Count - 1 Then

objSB.Append(vbTab)

End If

Next

If i < objDataTable.Rows.Count - 1 Then

objSB.Append(vbCrLf)

End If

Next

System.Windows.Forms.Clipboard.SetDataObject(objSB.ToString())

xlWorksheet.Range(strBeginCell).Activate()

xlWorksheet.Paste()

System.Windows.Forms.Clipboard.SetDataObject("")

End Sub

这里的主要操作主要是在组装StringBuilder上，而系统剪切板利用DDE(Dynamic Data Exchange)的方式转移数据，速度还是很快的。但是，因为系统剪切板是系统共享资源，所以在多线程的应用程序里需要考虑对该共享资源的同步问题。另外，在Web应用中，因为用户是ASPNET而不是Administrator所以对Clipboard的访问是没有权限的。因此该方法也是受限的。

3． 利用数组进行赋值操作

因为Range.Value可以接受数组，并将数组里的值赋给Range内相应单元格。利用这个特点，我们可以将Range设定为整个要赋值的范围，再将DataTable里的数据放到一个Object二维数组中，让COM对象自己完成对范围赋值的过程。

Private Sub SetValueToExcelByResize(ByVal xlWorksheet As Worksheet, ByVal strBeginCell As String, ByVal objDataTable As System.Data.DataTable)

' The first row is title.

Dim objData(objDataTable.Rows.Count, objDataTable.Columns.Count - 1) As Object

' Set the title

For i As Integer = 0 To objDataTable.Columns.Count - 1

objData(0, i) = objDataTable.Columns(i).ColumnName

Next

' Set the value

For i As Integer = 0 To objDataTable.Rows.Count - 1

For j As Integer = 0 To objDataTable.Columns.Count - 1

objData(1 + i, j) = objDataTable.Rows(i)(j).ToString().Trim()

Next

Next

xlWorksheet.Range(strBeginCell).Resize(objData.GetUpperBound(0) + 1, objData.GetUpperBound(1) + 1).Value = objData

End Sub

这里用到Range.Resize属性，这个属性将已知开始的单元格扩大为要赋值的区域。

Public Overridable ReadOnly Property Resize(Optional ByVal RowSize As Object = Nothing, Optional ByVal ColumnSize As Object = Nothing) As Excel.Range

注意：RowSize, ColumnSize必须大于1

这个赋值过程，跨越托管堆到非托管堆的数据转移只有一次，而且没有大量的装箱操作，也不用考虑到系统共享资源的问题，所以在大数据量赋值的时候，应该考虑使用该方法。

最后生成Excel：

l 调用Excel宏

说到Excel就不能不提到宏，正是因为能够使用VBA为Excel进行二次开发使得Excel成为最好的电子表格工具，这也使得通过.NET操作Excel又多出一种渠道，我们可以利用Excel中的VBA进行我们的快速开发。比如：利用宏将Excel转化为PDF格式的文件。

先来看看Excel.Application.Run方法，Run方法共有30个参数，第一个是要调用宏方法的限定名，剩下的是方法的参数。使用该方法可以调用Application中的宏，宏可以写在.xls或者.xla文件中，通过宏方法限定名来调用，宏方法的限定名为：

“文件名!模块名.方法名”（如：PdfConverter.xla!MdlMain.ConvertToPDF）

Private Sub ConvertToPDF(ByVal xlApp As Application, ByVal strExcelName As String, ByVal strSheetName As String)

Dim strMacroFileName As String = System.Windows.Forms.Application.StartupPath + "/PdfConverter.xla"

Dim strMacroMethodName As String = "PdfConverter.xla!MdlMain.ConvertToPDF"

Dim strPDFFileName As String = xlApp.Workbooks(strExcelName).Path + "/" + strExcelName.Replace(".xls", "") + ".pdf"

xlApp.DisplayAlerts = False

xlApp.Workbooks.Open(strMacroFileName)

xlApp.Run(strMacroMethodName, strExcelName, strSheetName, strPDFFileName)

xlApp.DisplayAlerts = True

End Sub

写在PdfConverter.xla中的VBA代码：

Public Sub ConvertToPDF(ByVal strExcelName As String, ByVal strSheetName As String, ByVal strPDFFileName As String)

' Define the postscript and .pdf file names.

Dim strPSFileName As String

Dim xlWorksheet As Worksheet

Dim objPdfDistiller As PdfDistiller

strPSFileName = Left(strPDFFileName, InStrRev(strPDFFileName, "/")) & "tmpPostScript.ps"

Application.ActivePrinter = "Adobe PDF on Ne02:"

' Print the Excel ActiveSheet to the postscript file

xlWorksheet = Application.Workbooks(strExcelName).Worksheets(strSheetName)

xlWorksheet.PrintOut(Copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=True, Collate:=True, prtofilename:=strPSFileName)

' Convert the postscript file to .pdf

objPdfDistiller = New PdfDistiller

objPdfDistiller.FileToPDF(strPSFileName, strPDFFileName, "")

' Finally, delete the postscript file

Call Kill(strPSFileName)

End Sub

调用之后生成PDF文件：

比如操作Excel，我们最直接的方法就是利用Excel提供的Excel Object Library COM组件，并将包装后的程序集叫做“互操作程序集” （Primary Interop Assembly, PIA）。