Click here to Skip to main content
Click here to Skip to main content
Go to top

DataSet to Excel File Conversion using ExcelLibrary

, 15 Jan 2012
Rate this:
Please Sign up or sign in to vote.
ExcelLibrary implementation sample to convert DataSet to Excel file in VB.NET

Editorial Note

This article appears in the Third Party Products and Tools section. Articles in this section are for the members only and must not be used to promote or advertise products in any way, shape or form. Please report any spam or advertising.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Robby Tendean
Software Developer
Singapore Singapore
Robby Tendean had his Master Degree in Computer Science from National Taiwan University of Science and Technology. His master degree thesis, Energy-Efficient Routing Protocol for Wireless Sensor Networks with Static Clustering and Dynamic Structure, has been published in Springerlink International Journal.
 
Currently he is working as Software Engineer based in Singapore with several years experience in HTML, Javascript, JQuery, C#.NET, VB.NET, Microsoft SQL Server for web development.

Comments and Discussions

 
Suggestion[My vote of 1] More Info please Pinmembermmansf15-Jan-12 2:42 
GeneralRe: [My vote of 1] More Info please [modified] PinmemberRobby Tendean15-Jan-12 5:26 
GeneralRe: [My vote of 1] More Info please Pinmembermmansf15-Jan-12 8:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 16 Jan 2012
Article Copyright 2012 by Robby Tendean
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid