Click here to Skip to main content
13,456,454 members
Click here to Skip to main content
Add your own
alternative version


18 bookmarked
Posted 12 Jan 2012

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.


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
    '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

        '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
          iRow = iRow + 1

        'Attach worksheet to workbook
        iTotalRows = iTotalRows + iRow
    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
    End If

    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.


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


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.

You may also be interested in...


Comments and Discussions

QuestionExcel Library Pin
Mjpanopio031011-Nov-14 15:19
memberMjpanopio031011-Nov-14 15:19 
QuestionWhich Refrence to be add?? Pin
Basmeh Awad24-Jun-13 21:09
professionalBasmeh Awad24-Jun-13 21:09 
Questionthanks Pin
Hakimi14-Aug-12 23:48
memberHakimi14-Aug-12 23:48 
GeneralReason for my vote of 5 Received the help needed. Pin
mmansf15-Jan-12 8:55
membermmansf15-Jan-12 8:55 
QuestionAnother Question Read Excel File into dataset? Pin
mmansf23-Jan-12 9:54
membermmansf23-Jan-12 9:54 
AnswerRe: Another Question Read Excel File into dataset? Pin
Robby Tendean25-Jan-12 4:15
memberRobby Tendean25-Jan-12 4:15 
Suggestion[My vote of 1] More Info please Pin
mmansf15-Jan-12 2:42
membermmansf15-Jan-12 2:42 
GeneralRe: [My vote of 1] More Info please Pin
Robby Tendean15-Jan-12 5:26
memberRobby Tendean15-Jan-12 5:26 
GeneralRe: [My vote of 1] More Info please Pin
mmansf15-Jan-12 8:53
membermmansf15-Jan-12 8:53 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04-2016 | 2.8.180322.1 | Last Updated 16 Jan 2012
Article Copyright 2012 by Robby Tendean
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid