DataSet to Excel File Conversion using ExcelLibrary

• Download source code - 106 KB
  

Introduction

There are several ways to write an Excel workbook file (*.xls) in VB.NET. One way is using COM Interop library in .NET framework which requires us to install Microsoft Excel in web servers, which you can read about it here. There are a lot of third party libraries as well which do not require to install Microsoft Excel in web servers. In this tips, I will use ExcelLibrary which can be downloaded here (choose the latest version). It is under GNU Lesser GPL license, which means it can be used by commercial applications.

ExcelLibrary supports Microsoft Excel 97-2003 workbook file format. In other words, it is using BIFF8 file format. ExcelLibrary also has one well known bug. When generated Excel workbook file size is below 7 KB, it will show warning when you try to open using Microsoft Excel. This tip will include the work around for this bug.

Implementing ExcelLibrary

ExcelLibrary is very easy to use. Basically it is a library file in *.dll format. So we will need to Add Reference in our project to it (see how to add reference here). To use ExcelLibrary, we will need to import ExcelLibrary class to the page level (*.aspx.vb):

Imports ExcelLibrary.SpreadSheet

To create an Excel workbook file, first we will need to create a workbook instance. Then, we need to create a worksheet complete with the cells. After that, worksheet needs to be attached to the workbook to make a complete Excel file. See the code below to write DataSet to Excel workbook file:

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

Basically, the code above traverses DataTables and DataRows inside DataSet, then converts it into Excel workbook file. Every DataTable will be converted to one worksheet. Additional workaround used inside the code is to insert new worksheet called Sheet X with 100 rows when the total rows are less than 100. This will ensure that file size will be bigger than 7 KB, thus the bug of ExcelLibrary will never appear.