Dot NET操作Excel COM对象

转载 2007年09月19日 16:15:00
多年来 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 托管成员。

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

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
jossef@championzone.net

2
Ashley Larsen
Senior VP Sales & Mktg
Sales
555-0109
ashley@championzone.net

3
Eric Lang
Corporate Counsel
Operations
555-0110
eric@championzone.net

4
Linda Leste
Treasurer
Finance
555-0111
linda@championzone.net

5
Ketan Dalal
Secretary
Finance
555-0112
ketan@championzone.net

  

在本示例中,以“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文件:

原文地址:http://hi.baidu.com/amyasp/blog/i ... aeb9afbae5133ef.html 



 

ASP.NET操作EXCEL的总结篇

http://www.jb51.net/article/26273.htm 今年有个系统的部分EXCEL的操作也让我做,顺便结合之前操作EXCEL的经验作一下总结,可能也算不上什么,对于绝大多数来...
  • a364416036
  • a364416036
  • 2016年07月29日 09:19
  • 1362

.NET操作Excel COM对象

多年来 COM 对象一直是 Windows 编程的基础,然而随着技术的进步和发展,微软推出了更佳出色的.NET。.NET Framework 提供了一个称为公共语言运行库的运行时环境(CLR),它的托...
  • fangxinggood
  • fangxinggood
  • 2006年04月08日 14:38
  • 35508

使用COM组件实现对Excel文件的操控

1 使用COM组件操控Excel的优势和缺点优势:提供了完整的Excel操控能力。可以方便的进行复杂操作。提供了对Excel文件的底层操作,工作效率高。和VBA方式相比可以脱离特定的Excel文件进行...
  • DKman803
  • DKman803
  • 2007年06月26日 20:05
  • 20840

asp.net中使用OLEDB操作Excel

最近项目中需要导出Excel数据表单,试了好几种方法,都感觉不怎么顺手,然后老大喊我去看看OLEDB,我接着花世间去学习了一下,感觉还挺不错的。 开始在网上找了一些代码, 然后需要配置连接字符串 ...
  • yangmingxing980
  • yangmingxing980
  • 2014年09月04日 11:02
  • 1224

.NET操作Excel免费开源类库简介及比较

自从上次找到NPOI之后,根据园友提供的线索以及Google,又找到了一些开源免费的类库,所以都简单体验了一遍。 主要找到以下类库: MyXls(http://sourceforge.net/pr...
  • sven_xu
  • sven_xu
  • 2015年06月02日 09:18
  • 1335

Excel操控方法之Com组件

使用微软官方Com组件Microsoft.Office.Interop.Excel对Excel进行操控,实现数据的读写,单元格背景颜色的修改等操作...
  • AdairXY
  • AdairXY
  • 2016年07月07日 10:44
  • 1435

MFC中使用OLE/COM操作EXCEL的方法

使用OLE的方法操作EXCEL,首先计算机必须安装excel,这样才会有接口暴露出来。本次使用大神封装好的类。 excel作为OLE/COM库插件,定义好了各类交互接口,而且这些接口是跨语言的,可以导...
  • lht501692913
  • lht501692913
  • 2015年12月17日 19:25
  • 2430

.Net程序中如何彻底关闭Excel - 关闭进程外COM方法

在.Net中, 经常我们会利用Excel来展现数据. 我们知道此时, Excel做为一个进程外COM被激活. 可是却无法彻底关闭该进程. 这是什么原因呢?COM的生命周期依靠计数机制,只有当引用计数为...
  • redoc_li
  • redoc_li
  • 2007年06月29日 21:47
  • 1129

Asp.net操作Excel

using System; using System.IO; using System.Web; using System.Text; using System.Web.UI; using Syste...
  • mh942408056
  • mh942408056
  • 2014年07月25日 11:39
  • 1049

COM组件对象与.NET类对象的相互转换

 运行环境:Visual Studio.NET Beta2, VC7, C#参考资料:MSDN级别:入门级 一、前言COM组件对象与.NET类对象是完全不同的,但为了使COM客户程序象调用COM组件一...
  • mydriverc
  • mydriverc
  • 2007年08月14日 10:37
  • 853
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Dot NET操作Excel COM对象
举报原因:
原因补充:

(最多只允许输入30个字)