使用 ExcelLibrary 将 DataSet 转换为 Excel 文件

• 下载源代码 - 106 KB

引言

在 VB.NET 中写入 Excel 工作簿文件 (*.xls) 有几种方法。 一种是使用 .NET 框架中的 COM Interop 库，这需要我们在 Web 服务器上安装 Microsoft Excel，您可以在 此处 了解更多信息。 还有许多第三方库也不需要安装 Microsoft Excel 在 Web 服务器上。 在本技巧中，我将使用 ExcelLibrary，可以从 此处 下载 (选择最新版本)。 它采用 GNU Lesser GPL 许可，这意味着它可以用于商业应用程序。

ExcelLibrary 支持 Microsoft Excel 97-2003 工作簿文件格式。 换句话说，它使用 BIFF8 文件格式。 ExcelLibrary 还有一个众所周知的错误。 当生成的 Excel 工作簿文件大小小于 7 KB 时，使用 Microsoft Excel 打开时会显示警告。 本技巧将包含此错误的解决方法。

实现 ExcelLibrary

ExcelLibrary 非常易于使用。 基本上，它是一个 *.dll 格式的库文件。 因此，我们需要在项目中 添加引用 (请参阅 此处 了解如何添加引用)。 要使用 ExcelLibrary，我们需要将 ExcelLibrary 类导入到页面级别 (*.aspx.vb)。

Imports ExcelLibrary.SpreadSheet

要创建一个 Excel 工作簿文件，首先我们需要创建一个工作簿实例。 然后，我们需要创建一个包含单元格的工作表。 之后，需要将工作表附加到工作簿以创建一个完整的 Excel 文件。 请参阅下面的代码，将 DataSet 写入 Excel 工作簿文件

Public Function WriteXLSFile(ByVal pFileName As String, ByVal pDataSet As DataSet) As Boolean
  Try
    'This function CreateWorkbook will cause xls file cannot be opened
    'normally when file size below 7 KB, see my work around below
    'ExcelLibrary.DataSetHelper.CreateWorkbook(pFileName, pDataSet)

    'Create a workbook instance
    Dim workbook As Workbook = New Workbook()
    Dim worksheet As Worksheet
    Dim iRow As Integer = 0
    Dim iCol As Integer = 0
    Dim sTemp As String = String.Empty
    Dim dTemp As Double = 0
    Dim iTemp As Integer = 0
    Dim dtTemp As DateTime
    Dim count As Integer = 0
    Dim iTotalRows As Integer = 0
    Dim iSheetCount As Integer = 0

    'Read DataSet
    If Not pDataSet Is Nothing And pDataSet.Tables.Count > 0 Then

      'Traverse DataTable inside the DataSet
      For Each dt As DataTable In pDataSet.Tables       
    
        'Create a worksheet instance
        iSheetCount = iSheetCount + 1
        worksheet = New Worksheet("Sheet " & iSheetCount.ToString())

        'Write Table Header
        For Each dc As DataColumn In dt.Columns
          worksheet.Cells(iRow, iCol) = New Cell(dc.ColumnName)
          iCol = iCol + 1
        Next

        'Write Table Body
        iRow = 1
        For Each dr As DataRow In dt.Rows
          iCol = 0
          For Each dc As DataColumn In dt.Columns
            sTemp = dr(dc.ColumnName).ToString()
            Select Case dc.DataType
              Case GetType(DateTime)
                DateTime.TryParse(sTemp, dtTemp)
                worksheet.Cells(iRow, iCol) = New Cell(dtTemp, "MM/DD/YYYY")
              Case GetType(Double)
                Double.TryParse(sTemp, dTemp)
                worksheet.Cells(iRow, iCol) = New Cell(dTemp, "#,##0.00")
              Case Else
                worksheet.Cells(iRow, iCol) = New Cell(sTemp)
            End Select
            iCol = iCol + 1
          Next
          iRow = iRow + 1
        Next

        'Attach worksheet to workbook
        workbook.Worksheets.Add(worksheet)
        iTotalRows = iTotalRows + iRow
      Next
    End If

    'Bug on Excel Library, min file size must be 7 Kb
    'thus we need to add empty row for safety
    If iTotalRows < 100 Then
      worksheet = New Worksheet("Sheet X")
      count = 1
      Do While count < 100
        worksheet.Cells(count, 0) = New Cell(" ")
        count = count + 1
      Loop
      workbook.Worksheets.Add(worksheet)
    End If

    workbook.Save(pFileName)
    Return True
  Catch ex As Exception
    Return False
  End Try
End Function

基本上，上面的代码遍历 DataSet 中的 DataTables 和 DataRows，然后将其转换为 Excel 工作簿文件。 每个 DataTable 将转换为一个工作表。 代码中使用的额外解决方法是在总行数小于 100 行时，插入一个名为 Sheet X 的新工作表，包含 100 行。 这将确保文件大小大于 7 KB，从而避免 ExcelLibrary 的错误。