使用 XSL 进行 Excel 导出组件

• 下载 DLL (.NET 2.0) - 7.25 Kb
• 下载源代码 (.NET 2.0) - 13.6 Kb
• 下载演示源代码 (.NET 2.0) - 36.7 Kb
• 下载 DLL (.NET 1.1) - 5.74 Kb
• 下载源代码 (.NET 1.1) - 12 Kb

引言

我创建了一个使用 XSL 和 XML 的 Excel 导出组件，可以生成格式非常好的 Excel 文件。此组件无需任何 Excel 库即可创建不包含图表和宏的 Excel 文件，但如果您需要在 Excel 中添加图表或宏，则必须在计算机上安装 Excel。我将通过三个示例来解释这一点。

• 将 `DataTable` 导出到 Excel 工作表中，该工作表将不包含任何格式。
• 将 `XMLDataDocument`（包含来自 `DataTable` 的数据并添加了一些标记）导出到 Excel 工作表中，该工作表将具有许多炫酷的格式。
• 将 `XMLDataDocument`（包含来自 `DataTable` 的数据并添加了一些标记）导出到 Excel 工作表中，该工作表将包含图表和许多炫酷的格式。

使用代码

在我们开始示例之前，让我先介绍一下这个库中公开的函数和属性。

函数

• `TransformDataTableToExcel` - 具有三个重载，用于将 `DataTable` 转换为 Excel 文件。
• `TransformXMLDocumentToExcel` - 将 `XMLDataDocument` 转换为 Excel 文件。
• `AddExcelSheetToExcelTemplate` - 具有四个重载，用于将 Excel 工作表（来自 Excel 工作簿）添加到 Excel 模板。
• `SendExcelToClient` - 将文件作为附件发送给客户端。
• `CleanUpTemporaryFiles` - 清理前几次请求创建的临时文件。

属性

• `TempFolder` - 将创建临时 Excel 文件的文件夹路径。
• `TemplateFolder` - 保存模板 Excel 文件的文件夹路径。
• `XSLStyleSheetFolder` - 保存 XSL 样式表的文件夹路径。

让我们来看一下示例。

我们将使用 VB.NET 编写我们的代码。

示例 - 1

在这个示例中，我们将一个 `DataTable` 导出到一个不包含任何格式的 Excel 工作表中。

'The Excel object is declared at the page level
Dim objExport As ExportToExcel.ExcelExport

Protected Sub Page_Load(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles Me.Load

    'The Excel object is initialized
    objExport = New ExportToExcel.ExcelExport

    'The Folders the excel obejct would be using is set
    objExport.TempFolder = "\Excel\Temp\"
    objExport.TemplateFolder = "\Excel\Template\"
    objExport.XSLStyleSheetFolder = "\Excel\XSLStyleSheet\"

    'The CleanUpTemporaryFiles is called so that
    'files created previously are destroyed.
    objExport.CleanUpTemporaryFiles()
End Sub

Protected Sub cmdExport_Click(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles cmdExport.Click
    Dim strSql As String
    Dim strCon As String
    Dim strExcelFile As String
    Dim dsOrders As DataSet

    Try
        'Get the data from the database
        strSql = "select ord.orderid,ord.EmployeeID, " & _
                 "ordDet.ProductID, ordDet.UnitPrice, " & _
                 "ordDet.Quantity, "
        strSql = strSql & " ordDet.Discount from " & _
                 "orders ord inner join ""order details"" ordDet "
        strSql = strSql & "on ord.orderid = " & _
                 "ordDet.orderid where customerID" & _
                 " = 'ALFKI' order by ord.orderid"

        strCon = _
          System.Configuration.ConfigurationManager.AppSettings("ConStr")

        dsOrders = SqlHelper.ExecuteDataset(strCon, _
                   CommandType.Text, strSql)

        'Transform the data from the datatable into excel.
        'This function would return the name of
        'the Excel file created.
        strExcelFile = _
          objExport.TransformDataTableToExcel(_
          dsOrders.Tables(0), True)

        'send the excel file to the client
        objExport.SendExcelToClient(strExcelFile)

    Catch ex As Threading.ThreadAbortException
        'Do nothing
    Catch ex As Exception
        Response.Write(ex.ToString)
   End Try

End Sub

从该示例生成的 Excel 文件如下图所示：

示例 - 2

在这个示例中，我们将一个 `XMLDataDocument`（包含来自 `DataSet` 的数据并添加了一些标记）导出到一个 Excel 工作表中，该工作表将具有许多炫酷的格式。

'The Excel object is declared at the page level
Dim objExport As ExportToExcel.ExcelExport

Protected Sub Page_Load(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles Me.Load

    'The Excel object is initialized
    objExport = New ExportToExcel.ExcelExport

    'The Folders the excel obejct would be using is set
    objExport.TempFolder = "\Excel\Temp\"
    objExport.TemplateFolder = "\Excel\Template\"
    objExport.XSLStyleSheetFolder = "\Excel\XSLStyleSheet\"

    'The CleanUpTemporaryFiles is called so
    'that files created previously are destroyed.
    objExport.CleanUpTemporaryFiles()
End Sub

Protected Sub cmdExport_Click(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles cmdExport.Click
    Dim strSql As String
    Dim strCon As String
    Dim strExcelFile As String
    Dim dsOrders As DataSet
    Dim XMLDoc As XmlDataDocument
    Dim objNewNode As XmlNode
    Dim objFrstNode As XmlNode

    Try

        'Get the data from the database
        strSql = "select ord.orderid,ord.EmployeeID, " & _
                 "ordDet.ProductID, ordDet.UnitPrice, " & _
                 "ordDet.Quantity, "
        strSql = strSql & " ordDet.Discount from " & _
                 "orders ord inner join ""order details"" ordDet "
        strSql = strSql & "on ord.orderid = " & _
                 "ordDet.orderid where customerID" & _
                 " = 'ALFKI' order by ord.orderid"

        strCon = _
          System.Configuration.ConfigurationManager.AppSettings("ConStr")

        dsOrders = SqlHelper.ExecuteDataset(strCon, _
                   CommandType.Text,strSql)

        'Create the XML Data Document from the dataset.
        XMLDoc = New XmlDataDocument(dsOrders)

        'Add Additional information that has to be
        'displayed in the Excel into the XML Document.
        objNewNode = XMLDoc.CreateElement("CustomerDetails")
        objNewNode.InnerXml = "<CustomerId>ALFKI</" & _
                   "CustomerId><CustomerNm>Alfreds " & _
                   "Futterkiste</CustomerNm> " & _
                   "<ContactNm>Maria Anders" & _
                   "</ContactNm><City>Berlin</City>"
        XMLDoc.DataSet.EnforceConstraints = False
        objFrstNode = XMLDoc.DocumentElement.FirstChild
        XMLDoc.DocumentElement.InsertBefore(objNewNode, _
                                            objFrstNode)

        'Transform the data from the datatable into excel.
        'This function would return the name of
        'the Excel file created. Here we are using a XSL
        'file to define the structure of the Excel file.
        strExcelFile = _
          objExport.TransformXMLDocumentToExcel(XMLDoc, _
          "Example2.xsl")

        'send the excel file to the client
        objExport.SendExcelToClient(strExcelFile)

    Catch ex As Threading.ThreadAbortException
        'Do nothing
    Catch ex As Exception
        Response.Write(ex.ToString)
    End Try
End Sub

在此示例中，我们使用 XSL 文件定义 Excel 文件的结构。单击此处下载 ZIP 压缩的 XSL 文件。解释 XSL 超出了本文档的范围。如果您想了解更多关于 XSL 的信息，请单击此处。

如果您知道如何在 HTML 中创建表格，您就可以轻松理解它。就像那样简单。

从该示例生成的 Excel 文件如下所示：

示例 - 3

在这个示例中，我们将一个 `XMLDataDocument`（包含来自 `DataTable` 的数据并添加了一些标记）导出到一个 Excel 工作表中，该工作表将包含图表和许多炫酷的格式。

网页中的代码

'The Excel object is declared at the page level
Dim objExport As ExportToExcel.ExcelExport

Protected Sub Page_Load(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles Me.Load

    'The Excel object is initialized
    objExport = New ExportToExcel.ExcelExport

    'The Folders the excel obejct would be using is set
    objExport.TempFolder = "\Excel\Temp\"
    objExport.TemplateFolder = "\Excel\Template\"
    objExport.XSLStyleSheetFolder = "\Excel\XSLStyleSheet\"

    'The CleanUpTemporaryFiles is called so that
    'files created previously are destroyed.
    objExport.CleanUpTemporaryFiles()
End Sub

Protected Sub cmdExport_Click(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles cmdExport.Click
    Dim strSql As String
    Dim strCon As String
    Dim strExcelFile As String
    Dim dsOrders As DataSet
    Dim XMLDoc As XmlDataDocument

    Try
        'Get the data from the database
        strSql = "select ord.customerID, sum((ordDet.UnitPrice" & _
                 " * (ordDet.Discount / 100)) * ordDet.Quantity)"
        strSql = strSql & " as Order_Amount from orders " & _
                 "ord inner join ""order details"""
        strSql = strSql & "ordDet on ord.orderid = _
                 ordDet.orderid group by ord.customerID "

        strCon = _
          System.Configuration.ConfigurationManager.AppSettings("ConStr")

        dsOrders = SqlHelper.ExecuteDataset(strCon, _
                   CommandType.Text, strSql)

        'Create the XML Data Document from the dataset.
        XMLDoc = New XmlDataDocument(dsOrders)

        'Transform the data from the datatable into excel.
        'This function would return the name of the
        'Excel file created. Here we are using a XSL file
        'to define the structure of the Excel file.
        strExcelFile = _
         objExport.TransformXMLDocumentToExcel(XMLDoc, _
         "Example3.xsl")

        'add the excel sheet in the work book returned
        'from the TransformXMLDocumentToExcel function
        'to the Excel Template.
        strExcelFile = _
         objExport.AddExcelSheetToExcelTemplate(strExcelFile, _
         "Example3.xls")

        'send the excel file to the client
        objExport.SendExcelToClient(strExcelFile)

    Catch ex As Threading.ThreadAbortException
        'Do nothing
    Catch ex As Exception
        Response.Write(ex.ToString)
    End Try

End Sub

在此示例中，我们使用 XSL 文件定义 Excel 文件的结构。单击此处下载 ZIP 压缩的 XSL 文件。一旦我们从 `TransformXMLDocumentToExcel` 函数获取了 Excel 文件，我们就将此工作簿中的 Excel 工作表（例如 Temp）添加到 Excel 模板中，然后将其发送给客户端。模板中的 Excel 宏将填充图表，并将数据从 Temp 工作表复制到包含图表的工作表中。

我们还使用了其中保存了图表对象的 Excel 模板。

从该示例生成的 Excel 文件如下所示：

关注点

1. 当您想在不使用模拟的应用程序中使用此功能时，您的默认 ASP.NET 帐户必须对“Temp”文件夹（创建 Excel 文件的位置）具有“写入”访问权限。
2. 以下链接包含 Excel 颜色的十六进制代码：Excel 颜色。

历史

• 2006-08-02：文章创建。
• 2006-10-23：为 .NET 1.1 添加了源代码和 DLL。