将具有多个表的 dataset 导出到 Excel 文件中的不同工作表

引言

本文介绍了如何将包含多个表的 dataset 的内容导出到 Excel 工作簿中的单独工作表。本文还有助于理解 Excel 应用程序对象的基本原理及其用于创建工作簿和在其中插入工作表的使用方法。但是，以下代码可以通过合并格式化写入数据（如单元格背景、字体颜色、插入公式等）的方法和过程来扩展其功能。

背景

编写此代码的需求出现在我的一个应用程序需要相同的功能时，我的存储过程返回了两个结果集，我将它们存储在一个 dataset 中。用户希望这两个结果集显示在 Excel 文件中的两个单独的工作表中。但是，当前方法 Response.AddHeader ("content-disposition") 允许我将输出写入一个单独的工作表中，两个结果集一个在另一个下方。在探索网络和整合了一些技术之后，我想出了一个解决方案，可以解决我创建 Excel 文件，然后将数据从 dataset 表写入 Excel 文件的目的。

使用代码 - 实现

代码非常简单明了。作为一个先决条件，您应该在系统上安装 Excel。我使用了 VB.NET 作为我的基本语言。虽然稍作修改，您始终可以将现有代码转换为 C#。

为了开始使用该代码，请添加对 COM 对象 Microsoft Excel Object Library 的引用。由于我的系统上安装了 Microsoft Office 2003，因此在我的例子中是 Microsoft Excel 11.0 Object Library。现在将 Excel 库和 InteropServices 的命名空间导入到您的代码中。

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices.Marshal

我们需要导入 InteropServices，因为 Microsoft Office 代码仍然基于旧的、非托管的世界，您需要使用 COM Interop 来促进与其的通信。现在将以下代码复制到您的代码隐藏文件中，以将 dataset 表导出到 Excel。该函数将包含 DataTable 的 DataSet 作为参数。

Public Sub ExportToExcel(ByVal DS_MyDataset As DataSet)

    'The full path where the excel file will be stored
    Dim strFileName As String = _
        AppDomain.CurrentDomain.BaseDirectory.Replace("/", "\") 
    strFileName = strFileName & "\MyExcelFile" & _
                  System.DateTime.Now.Ticks.ToString() ".xls"

    Dim objExcel As Excel.Application
    Dim objBooks As Excel.Workbooks, objBook As Excel.Workbook
    Dim objSheets As Excel.Sheets, objSheet As Excel.Worksheet
    Dim objRange As Excel.Range

    Try
        'Creating a new object of the Excel application object
        objExcel = New Excel.Application
        'Hiding the Excel application
        objExcel.Visible = False
        'Hiding all the alert messages occurring during the process
        objExcel.DisplayAlerts = False

        'Adding a collection of Workbooks to the Excel object
        objBook = CType(objExcel.Workbooks.Add(), Excel. Workbook)

        'Saving the Workbook as a normal workbook format.
        objBook.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal) 

        'Getting the collection of workbooks in an object
        objBooks = objExcel.Workbooks

        'Get the reference to the first sheet
        'in the workbook collection in a variable
        objSheet = CType(objBooks(1).objSheets.Item(1), Excel.Worksheet)
        'Optionally name the worksheet
        objSheet.Name = "First Sheet"
        'You can even set the font attributes of a range of cells
        'in the sheet. Here we have set the fonts to bold.
        objSheet.Range("A1","Z1").Font.Bold = True 

        'Get the cells collection of the sheet in a variable, to write the data.
        objRange = objSheet.Cells

        'Calling the function to write the dataset data in the cells of the first sheet.
        WriteData(DS_MyDataset.Tables(0), objCells)

        'Setting the width of the specified range of cells
        'so as to absolutely fit the written data.
        objSheet.Range("A1","Z1").EntireColumn.AutoFit()
        'Saving the worksheet.
        objSheet.SaveAs(strFileName)
        
        objBook = objBooks.Item(1)
        objSheets = objBook.Worksheets
        objSheet = CType(objSheets.Item(2), Excel.Worksheet)
        objSheet.Name = "Second Sheet"
        'Setting the color of the specified range of cells
        'to Red (ColorIndex 3 denoted Red color)
        objSheet.Range("A1","Z1").Font.ColorIndex = 3

        objRange = objSheet.Cells
        WriteData(DS_MyDataset.Tables(1), objCells)
        objSheet.Range("A1","Z1").EntireColumn.AutoFit()

        objSheet.SaveAs(strFileName)

    Catch ex As Exception
        Response.Write(ex.Message)
    Finally
        'Close the Excel application
        objExcel.Quit()

        'Release all the COM objects so as to free the memory
        ReleaseComObject(objRange)
        ReleaseComObject(objSheet)
        ReleaseComObject(objSheets)
        ReleaseComObject(objBook)
        ReleaseComObject(objBooks)
        ReleaseComObject(objExcel)

        'Set the all the objects for the Garbage collector to collect them.
        objExcel = Nothing 
        objBooks = Nothing 
        objBook = Nothing 
        objSheets = Nothing 
        objSheet = Nothing 
        objRange = Nothing 

        'Specifically call the garbage collector.
        System.GC.Collect()
    End Try
End Sub

Private Function WriteData(ByVal DT_DataTable As DataTable, _
        ByVal objCells As Excel.Range) As String
    Dim iRow As Integer, iCol As Integer

    'Traverse through the DataTable columns to write the
    'headers on the first row of the excel sheet.
    For iCol = 0 To DT_DataTable.Columns.Count - 1
        objCells(1, iCol + 1) = DT_DataTable.Columns(iCol).ToString
    Next

    'Traverse through the rows and columns
    'of the datatable to write the data in the sheet.
    For iRow = 0 To DT_DataTable.Rows.Count - 1
        For iCol = 0 To DT_DataTable.Columns.Count - 1
            objCells(iRow + 2, iCol + 1) = DT_DataTable.Rows(iRow)(iCol)
        Next
    Next
End Function

优点和缺点

优点

• 该代码可以用作将格式化报告导出到 Excel 的组件。
• 该代码非常小，可以根据用户需求进行修改，以便在 Excel 工作表中以可呈现的格式获得格式化的输出。

缺点

• 该代码使用 Excel 对象库，该库是开发服务器上必需的。
• 由于代码通过 Interop 服务创建 COM 组件的对象，如果组件没有被有效释放，可能会导致内存泄漏。

结论

您可以看到上面的代码是不言自明且非常容易理解的。可以通过格式化写入的数据（如单元格的背景、向 Excel 表添加网格线等）来进一步增强代码。如果 dataset 包含三个以上的表，我们甚至可以向文件添加更多工作表，因为默认工作簿有三个工作表。上面的代码只是一个可以使用 Excel 对象完成的所有操作的示例。可以通过捕获更具体的异常来完成更强大的异常处理。尽情享受，编码愉快!!!!