Click here to Skip to main content
13,296,374 members (55,858 online)
Click here to Skip to main content
Add your own
alternative version


48 bookmarked
Posted 23 May 2007

Exporting a dataset with multiple tables to separate sheets in an Excel file

, 23 May 2007
Rate this:
Please Sign up or sign in to vote.
This article describes how to export data from multiple tables in a dataset to an Excel file in separate sheets.


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 to 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.


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 straightforward. 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 to 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 InteropServices into your code.

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

We will need to import InteropServices because the Microsoft Office code is still based on 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

        '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.
        'Saving the worksheet.
        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)


    Catch ex As Exception
        'Close the Excel application

        'Release all the COM objects so as to free the memory

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

    '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)
End Function

Pros and Cons


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


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


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 three tables since the default workbook has three sheets. The above code is just a sample of what all can be done with the Excel object. More enhanced exception handling can be done by catching more specific exceptions. Enjoy and happy coding!!!!


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


About the Author

Web Developer
India India
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 1 Pin
niki_reid30-May-13 3:04
memberniki_reid30-May-13 3:04 
GeneralMy vote of 5 Pin
leo.sanni9-Nov-11 17:22
memberleo.sanni9-Nov-11 17:22 
GeneralAlternative Pin
gg423716-Nov-09 0:58
membergg423716-Nov-09 0:58 
QuestionCan I ge c# code? Pin
Selvakumar - B4-Sep-09 22:55
memberSelvakumar - B4-Sep-09 22:55 
GeneralMy vote of 1 Pin
vannavada3-Feb-09 8:48
membervannavada3-Feb-09 8:48 
GeneralGetting error while adding Worksheet Pin
Mark_20006-Jun-08 4:36
memberMark_20006-Jun-08 4:36 
QuestionGot object reference Error Pin
prakashkpd3-Oct-07 22:22
memberprakashkpd3-Oct-07 22:22 
QuestionExporting Excel pivot charts in ASP.NET 1.1 Aplication? Pin
k_bhawna1-Jun-07 20:01
memberk_bhawna1-Jun-07 20:01 
GeneralAvoiding the need for Excel on the server Pin
purplepangolin24-May-07 0:55
memberpurplepangolin24-May-07 0:55 
GeneralRe: Avoiding the need for Excel on the server Pin
jain.ashish2124-May-07 1:20
memberjain.ashish2124-May-07 1:20 
Can u be more explanatory on what do you mean by saving the file using one of the XML formats so that I can construct a Excel document without using Excel on the server. Do you have any sample code or URL for the same.

If my mind can conceive it, and my heart can believe it, I know I can achieve it.

GeneralRe: Avoiding the need for Excel on the server Pin
purplepangolin24-May-07 1:39
memberpurplepangolin24-May-07 1:39 
QuestionRe: Avoiding the need for Excel on the server Pin
jain.ashish2124-May-07 23:16
memberjain.ashish2124-May-07 23:16 
AnswerRe: Avoiding the need for Excel on the server Pin
purplepangolin24-May-07 23:39
memberpurplepangolin24-May-07 23:39 
QuestionRe: Avoiding the need for Excel on the server Pin
jain.ashish2124-May-07 23:44
memberjain.ashish2124-May-07 23:44 
AnswerRe: Avoiding the need for Excel on the server Pin
purplepangolin24-May-07 23:50
memberpurplepangolin24-May-07 23:50 
QuestionRe: Avoiding the need for Excel on the server Pin
jain.ashish2125-May-07 0:06
memberjain.ashish2125-May-07 0:06 
AnswerRe: Avoiding the need for Excel on the server Pin
purplepangolin25-May-07 0:11
memberpurplepangolin25-May-07 0:11 
GeneralRe: Avoiding the need for Excel on the server Pin
ramki_mars22-Jul-08 22:18
memberramki_mars22-Jul-08 22:18 
AnswerRe: Avoiding the need for Excel on the server Pin
minga4823-Sep-08 4:18
memberminga4823-Sep-08 4:18 
AnswerRe: Avoiding the need for Excel on the server Pin
Jrgen Andersson30-May-07 1:38
memberJrgen Andersson30-May-07 1:38 
GeneralRe: Avoiding the need for Excel on the server Pin
ramki_mars22-Jul-08 22:21
memberramki_mars22-Jul-08 22:21 

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
Web01 | 2.8.171207.1 | Last Updated 24 May 2007
Article Copyright 2007 by jain.ashish21
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid