在 ASP.NET 中進行 EXCEL 開發(fā)

趨明 2012-03-28

展開全文

在 ASP.NET 中進行 EXCEL 開發(fā)

在顯示大量數據的時候，使用 EXCEL 無疑是一個很好的選擇，以網格為基本的數據單元，配合上眾多的計算公式和顏色、線條可以組成功能強大而美觀的數據報表。在現代企業(yè)中，這種應用也是最為廣泛的。因此在 Web 項目中，生成并輸出 EXCEL 報表也成了非常常見的功能。但是，在 ASP.NET 中使用 EXCEL 卻一直存在著以 EXCEL 進程無法釋放為代表的幾個比較討厭而且難于解決的問題，在這篇文章中將給出完整的解決方案。其中包含以下幾個方面：

	開發(fā)環(huán)境
	配置DCOM
	在 ASP.NET 中進行 EXCEL 編程
	優(yōu)化 EXCEL 的執(zhí)行效率
	在 Web 項目中使用 EXCEL 的其他方案

本文使用了下列技術：

.NET、C# 或 Visual Basic .NET、Visual Studio .NET、EXCEL

本頁內容

開發(fā)環(huán)境

本文討論的是在 ASP.NET 中使用 Microsoft Excel Object Library 進行開發(fā)的過程，開發(fā)環(huán)境的配置如下：

Windows 2000 + SP4
Office XP + SP1
Visual Studio.NET 2003

注意：在這里，請確認一下你的操作系統(tǒng)和 Office 的版本，包括補丁的版本。
雖然在絕大多數情況下 Windows2000/WindowsXP、Office2000/OfficeXP 的任意組合都能正常工作，但也確實存在同樣的代碼在僅僅是SP版本不同的兩臺機器上一個執(zhí)行正常，而另一個卻出現異常的情況。
所以，強烈建議使用與服務器環(huán)境完全一致的開發(fā)環(huán)境。

配置DCOM

對 Excel 進行編程，實際上就是通過 .Net Framework 去調用 Excel 的 COM 組件，所有要在 Web 環(huán)境下調用 COM 組件的時候，都需要對其進行相應的配置。
很多朋友都反映在 Windows 環(huán)境下調試正常的程序，一拿到 Web 環(huán)境中就出錯，實際上就是因為缺少了這一步。
下面就詳細介紹 DCOM 的配置過程。

1、運行“dcomcnfg”，打開 DCOM 配置程序。

2、在應用程序列表中找到“Microsoft Excel 應用程序”，點擊“屬性”。

3、將 “常規(guī)” 選項卡中的 “身份驗證級別” 設為 “無”。

4、選中 “安全性” 選項卡中的 “使用自定義配置權限”，點擊 “編輯”。

5、在打開的對話框中添加 “Internet來賓用戶”（通常是IUSR_機器名），訪問類型設為“完全控制”。

6、將 “身份標識” 選項卡中的用戶設為 “交互式用戶” 。

7、點擊“應用”之后，關閉屬性頁和 DCOM 配置程序。配置完成。

這里只給出了 DCOM 的配置方法，關于為什么要這么配置，以及更多的關于 DCOM 的知識就不屬于這篇文章的討論范圍了，有興趣的朋友可以自己去查閱相關資料。

在 ASP.NET 中進行 EXCEL 編程

相信 99% 的閱讀本文的朋友，對關于如何引用 Excel 對象已經是非常熟悉了的。但是為了保證這篇文章的完整性，就讓我再對剩下的 1％的朋友再多羅嗦幾句。

打開VS.NET，創(chuàng)建一個Web應用程序，點擊 “項目” 菜單中的 “添加引用” ，在打開的對話框的 “COM” 選項卡中找到 “Microsoft Excel 10.0 Object Library”，將其添加到項目中。

10.0 的版本號表示這是 OfficeXP ，同樣的 9.0 是 Office2000，11.0 是 Office2003，這取決于你所安裝的Office。

在我們正式開始編程之前，還有2件事要做。第一件，在 web.config 文件的節(jié)中，加入以下這條：

<identity  impersonate="true" />

第二件，確認一下“ASPNET”用戶是否對你將要操作的 Excel 文件的所在目錄擁有讀寫權限。比如，我的 Web 項目是在默認的 “C:/Inetpub/wwwroot” 目錄中，但我要讀寫的 Excel 文件卻放在 “D:/TEMP” 中，那么你就要在 “D:/TEMP” 的安全屬性中加入 “ASPNET” 用戶，并賦予相應的權限。

至此，執(zhí)行 Excel 所需要的所有配置都已完成。

在即將開始的 ASP.NET 編程中，我將使用 VB.NET 語言，這可能會讓 C# 的擁護者們感到失望，但我相信在省去了大量的 Missing 和類型轉換之后，你可以更清晰的看到在程序中是如何控制 Excel 的。其實，我也只有在對 Excel 進行編程的時候才用 VB.NET 的：）。

本文的示例在最后完成的時候，將輸出下面這樣的結果：

為了更清楚的理解 Excel 編程的結構，我們先簡單的輸出一下標題部分，代碼如下：

Dim filePath As String

filePath = Server.MapPath("testFile.xls")

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range

Try

  xlApp = New Excel.Application

  ''不顯示Excel窗口，自動釋放，不顯示提示信息
  xlApp.Visible = False
  xlApp.UserControl = False
  xlApp.DisplayAlerts = False

  ''打開一個作為輸出模板的文件
  'xlBook = xlApp.Workbooks.Open(filePath)
  ''或是新建一個文件
  xlBook = xlApp.Workbooks.Add()

  ''選擇當前的 Sheet 
  ''【注意】索引的下標是從1開始的，而不是0
  xlSheet = xlBook.Sheets(1)

  Dim strTitle As String
  Dim strPos As String

  strTitle = "大航海時代4 補給港口列表"

  ''選擇單元格
  strPos = "A1:F1"
  xlRange = xlSheet.Range(strPos)

  ''設置單元格內容
  xlRange.Formula = strTitle

  ''字體：18號，粗體
  xlRange.Font.Size = 18
  xlRange.Font.Bold = True

  ''合并單元格
  xlRange.MergeCells = True

  ''設置背景色
  ''使用內置的顏色
  ''xlRange.Interior.ColorIndex = 40
  ''或者直接設置RGB顏色
  xlRange.Interior.Color = RGB(255, 204, 153)

  ''設置居中
  xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

  ''設置行高
  xlRange.RowHeight = 25

  ''保存打開的文件
  'xlBook.Save()
  ''保存新建的文件
  xlBook.SaveAs(filePath)

Catch ex As Exception
  Throw ex
Finally

  ''釋放對象資源
  ReleaseComObject(xlRange)
  ReleaseComObject(xlSheet)

  If Not xlBook Is Nothing Then
        xlBook.Close()
        ReleaseComObject(xlBook)
  End If

  If Not xlApp Is Nothing Then
        xlApp.Quit()
        ReleaseComObject(xlApp)
  End If

  ''強制回收內存
  GC.Collect()
End Try

其中的ReleaseComObject方法：

  Sub ReleaseComObject()Sub ReleaseComObject(ByVal obj As Object)
    If Not obj Is Nothing Then
      System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
      obj = Nothing
    End If
  End Sub

將這段代碼放到你的項目中，執(zhí)行之后，你就會在 Web 項目的目錄中找到輸出的 “testFile.xls”文件。

現在，我們將整個過程的關鍵部分提出來，就簡化為下面這樣的結構：

[定義Excel對象]

Try

  [使用Excel對象]

Catch ex As Exception
  Throw ex
Finally

  [釋放Excel對象]

  [強制回收內存]

End Try

這種結構是保證 Excel 進程可以被正確釋放的關鍵，也就是說，你必須保證所有使用過的 Excel 對象都被釋放了。
為了讓你對這種結構有更清楚的認識，我們將上面的代碼按照不同的級別重新整理一下。

這樣我們就可以更清楚的看到，釋放對象的原則就是：在本函數中定義并使用的 Excel 對象，一定要在本函數中釋放。

整理過的代碼如下所示：

Sub ExportExcel()Sub ExportExcel(ByVal filePath As String)

  Dim xlApp As Excel.Application
  Dim xlBook As Excel.Workbook


  Try
    xlApp = New Excel.Application

    ''不顯示Excel窗口，自動釋放，不顯示提示信息
    xlApp.Visible = False
    xlApp.UserControl = False
    xlApp.DisplayAlerts = False

    ''打開一個作為輸出模板的文件
    'xlBook = xlApp.Workbooks.Open(filePath)
    ''或是新建一個文件
    xlBook = xlApp.Workbooks.Add()


    FillSheet(xlBook, 1)

    ''保存打開的文件
    'xlBook.Save()
    ''保存新建的文件
    xlBook.SaveAs(filePath)
  Catch ex As Exception
    Throw ex
  Finally

    ''釋放對象資源
    If Not xlBook Is Nothing Then
      xlBook.Close()
      ReleaseComObject(xlBook)
    End If

    If Not xlApp Is Nothing Then
      xlApp.Quit()
      ReleaseComObject(xlApp)
    End If

    ''強制回收內存
    GC.Collect()
  End Try
End Sub


Sub FillSheet()Sub FillSheet(ByVal xlBook As Excel.Workbook, ByVal index As Integer)

  Dim xlSheet As Excel.Worksheet

  Try

    xlSheet = xlBook.Sheets(index)

    SetTitle(xlSheet)

  Catch ex As Exception
    Throw ex
  Finally
    ReleaseComObject(xlSheet)
  End Try
End Sub


Sub SetTitle()Sub SetTitle(ByVal xlSheet As Excel.Worksheet)

  Dim xlRange As Excel.Range
  Dim xlFont As Excel.Font

  Try

    Dim strTitle As String
    Dim strPos As String

    strTitle = "大航海時代4 補給港口列表"

    ''選擇單元格
    strPos = "A1:F1"
    xlRange = xlSheet.Range(strPos)

    ''設置單元格內容
    xlRange.Formula = strTitle

    ''字體：18號，粗體
    xlFont = xlRange.Font
    xlFont.Size = 18
    xlFont.Bold = True

    ''合并單元格
    xlRange.MergeCells = True

    ''設置背景色
    ''使用內置的顏色
    ''xlRange.Interior.ColorIndex = 40
    ''或者直接設置RGB顏色
    xlRange.Interior.Color = RGB(255, 204, 153)

    ''設置居中
    xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    ''設置行高
    xlRange.RowHeight = 25

  Catch ex As Exception
    Throw ex
  Finally

    ReleaseComObject(xlFont)
    ReleaseComObject(xlRange)
  End Try

End Sub

值得注意的一個地方是，在 SetTitle 函數中，我們又定義了一個 Excel.Font 對象，對字體的設置通過這個對象來完成，并且在執(zhí)行后釋放掉。
在通常情況下，我們不需要使用這么麻煩的方法，但在某些時候，如果不釋放這些間接引用的對象（比如xlRange.Font.Size中的Font對象）的話，就無法正常中止 Excel 進程。如果我們非常不幸的遇到了這種情況的時候，就需要仔細檢查代碼，保證不存在間接引用（簡單的說就是沒有使用兩個“.”同時出現的代碼）。

關于如何設置單元格，其實并沒有什么值得說的，基本上所有的問題都可以通過在 Excel 中錄制宏來解決。此外，當你還希望了解更多的關于 Excel 對象的信息的時候，可以去看 Excel 的 VBA 參考手冊，在我的機器上，它位于 “F:/Program Files/Microsoft Office/Office10/2052/VBAXL10.chm” 。
如果你在你的機器上沒有找到這個文件，那就需要添加安裝 Office 安裝選項中 “Office共享功能” 下面的 “Visual Basic for Applictions” 組件中的 “Visual Basic 幫助” 。

優(yōu)化 EXCEL 的執(zhí)行效率

通過前面的介紹，我們已經可以在 ASP.NET 中進行 Excel 開發(fā)了，但如果是要進行企業(yè)級的開發(fā)的話，我們就必須考慮一下效率的問題了。這是因為：
1、每當一個用戶提交請求時， Web 服務器上都需要創(chuàng)建一個 Excel 進程，每個進程都會占用大量的內存，少則幾M多則20~30M。
2、每次對Excel單元格的寫操作都是非?；〞r間的，高達幾十ms（測試機為P4 3.0,1G內存）。

在我曾經參加過的一個項目中，輸出的一個Excel文件里，最多要有8個Sheet。最大的一個Sheet中，每條記錄要占20行197列，整個模版文件就2M多。在優(yōu)化之前，輸出這樣一個文件大概要40分鐘（測試人員掐表算的，我沒敢試-_-!! ）。

對 Excel 的操作程序進行優(yōu)化的原則非常簡單，就是
1、在一次操作中盡可能做更多的事。
2、不需要做的事情不做。