Exporting a Dataset with multiple tables in separate sheets in an Excel file

Introduction

This article explains how to export the contents of a Dataset with more than one table, to an excel workbook in separate sheets. The article also helps understand the basics behind the Excel application object and its usage for creating a Workbook and inserting Worksheets in it. The following code can however extend its functionalities by incorporating methods and procedures for formatting the written data like the cell background, font color, inserting formulas etc.

Background

The need to write this code came up when one of my applications needed the same functionalities where my stored procedure returned two result sets which I stored in a Dataset. The user wanted the two result sets to be displayed in two separate sheets in an excel file. But the current method of Response.AddHeader ("content-disposition") allowed me to write the output in one single sheet with the two result sets one below the other. After exploring the net and integration of a couple of techniques, I came up with a solution which could solve my purpose of creating an excel file and then writing the data from the dataset tables in the excel file.

Using the Code - Implementation

The code is pretty simple and straight forward. As a pre-requisite you should have Excel installed on your system. I have used VB.Net as my base language. Although with a little modification you can always convert the existing code in C# too.

In order to start using the code, add a reference to the COM object Microsoft Excel Object Library. Since I had Microsoft Office 2003 installed on my system, it was Microsoft Excel 11.0 Object Library in my case. Now import the namespaces for the Excel library and InterpServices into your code

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

We will need to import the InteropServices because the Microsoft Office code is still based in the old, unmanaged world and you need to use COM Interop to facilitate communication with it. Now copy the following code to your Code behind file to export the dataset tables to excel. The function takes as parameter a DataSet containing the DataTables

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

Pros and Cons

Pros:

• Code can be used as a component to export formatted reports to excel.
• The code is very small and can be modified as per the user requirements for getting formatted output on excel sheets in a presentable format.

Cons:

• The code uses Excel Object library which is required at the development server.
• Since the code creates objects of the COM components through Interop services, if the components are not efficiently released, may result in memory leakage.

Conclusion

You can see that the above code is self explanatory and quite understandable. The code can further be enhanced by formatting the written data like the background of the cells, adding gridlines to the excel sheet etc. We can even add more sheets to the file if the dataset contains more than 3 tables since the default workbook has 3 sheets. The above code is just a sample of what all can be done with the excel object. A more enhanced exception handling can be done by catching more specific exceptions. Enjoy and happy coding!!!!