在 ASP.NET 中进行 EXCEL 开发

在 ASP.NET 中进行 EXCEL 开发

在显示大量数据的时候，使用 EXCEL 无疑是一个很好的选择，以网格为基本的数据单元，配合上众多的计算公式和颜色、线条可以组成功能强大而美观的数据报表。 在现代企业中，这种应用也是最为广泛的。因此在 Web 项目中，生成并输出 EXCEL 报表也成了非常常见的功能。 但是，在 ASP.NET 中使用 EXCEL 却一直存在着以 EXCEL 进程无法释放为代表的几个比较讨厌而且难于解决的问题，在这篇文章中将给出完整的解决方案。 其中包含以下几个方面：

	开发环境
	配置DCOM
	在 ASP.NET 中进行 EXCEL 编程
	优化 EXCEL 的执行效率
	在 Web 项目中使用 EXCEL 的其他方案

本文使用了下列技术：

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

本页内容

开发环境

本文讨论的是在 ASP.NET 中使用 Microsoft Excel Object Library 进行开发的过程，开发环境的配置如下：

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

注意：在这里，请确认一下你的操作系统和 Office 的版本，包括补丁的版本。
虽然在绝大多数情况下 Windows2000/WindowsXP、Office2000/OfficeXP 的任意组合都能正常工作，但也确实存在同样的代码在仅仅是SP版本不同的两台机器上一个执行正常，而另一个却出现异常的情况。
所以，强烈建议使用与服务器环境完全一致的开发环境。

配置DCOM

对 Excel 进行编程，实际上就是通过 .Net Framework 去调用 Excel 的 COM 组件，所有要在 Web 环境下调用 COM 组件的时候，都需要对其进行相应的配置。
很多朋友都反映在 Windows 环境下调试正常的程序，一拿到 Web 环境中就出错，实际上就是因为缺少了这一步。
下面就详细介绍 DCOM 的配置过程。

1、运行“dcomcnfg”，打开 DCOM 配置程序。

2、在应用程序列表中找到“Microsoft Excel 应用程序”，点击“属性”。

3、将 “常规” 选项卡中的 “身份验证级别” 设为 “无”。

4、选中 “安全性” 选项卡中的 “使用自定义配置权限”，点击 “编辑”。

5、在打开的对话框中添加 “Internet来宾用户”（通常是IUSR_机器名） ，访问类型设为“完全控制”。

6、将 “身份标识” 选项卡中的用户设为 “交互式用户” 。

7、点击“应用”之后，关闭属性页和 DCOM 配置程序。配置完成 。

这里只给出了 DCOM 的配置方法，关于为什么要这么配置，以及更多的关于 DCOM 的知识就不属于这篇文章的讨论范围了，有兴趣的朋友可以自己去查阅相关资料。

在 ASP.NET 中进行 EXCEL 编程

相信 99% 的阅读本文的朋友，对关于如何引用 Excel 对象已经是非常熟悉了的。但是为了保证这篇文章的完整性，就让我再对剩下的 1％的朋友再多罗嗦几句。

打开VS.NET，创建一个Web应用程序，点击 “项目” 菜单中的 “添加引用” ，在打开的对话框的 “COM” 选项卡中找到 “Microsoft Excel 10.0 Object Library”，将其添加到项目中。
 
10.0 的版本号表示这是 OfficeXP ，同样的 9.0 是 Office2000，11.0 是 Office2003，这取决于你所安装的Office。

在我们正式开始编程之前，还有2件事要做。第一件，在 web.config 文件的节中，加入以下这条：

<identity  impersonate="true" />

第二件，确认一下“ASPNET”用户是否对你将要操作的 Excel 文件的所在目录拥有读写权限。比如，我的 Web 项目是在默认的 “C:/Inetpub/wwwroot” 目录中，但我要读写的 Excel 文件却放在 “D:/TEMP” 中，那么你就要在 “D:/TEMP” 的安全属性中加入 “ASPNET” 用户，并赋予相应的权限。

至此，执行 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
        

将这段代码放到你的项目中，执行之后，你就会在 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 对象，对字体的设置通过这个对象来完成，并且在执行后释放掉。
在通常情况下，我们不需要使用这么麻烦的方法，但在某些时候，如果不释放这些间接引用的对象（比如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 帮助” 。

优化 EXCEL 的执行效率

通过前面的介绍，我们已经可以在 ASP.NET 中进行 Excel 开发了，但如果是要进行企业级的开发的话，我们就必须考虑一下效率的问题了。这是因为：
1、每当一个用户提交请求时， Web 服务器上都需要创建一个 Excel 进程，每个进程都会占用大量的内存，少则几M多则20~30M。
2、每次对Excel单元格的写操作都是非常花时间的，高达几十ms（测试机为P4 3.0,1G内存）。

在我曾经参加过的一个项目中，输出的一个Excel文件里，最多要有8个Sheet。最大的一个Sheet中，每条记录要占20行197列，整个模版文件就2M多。在优化之前，输出这样一个文件大概要40分钟（测试人员掐表算的，我没敢试-_-!! ）。

 对 Excel 的操作程序进行优化的原则非常简单，就是
1、在一次操作中尽可能做更多的事。
2、不需要做的事情不做。