Dot NET操作Excel COM对象_com撖寡情excel

在本示例中，以“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