Blog

Export data to multiple sheets of Excel doc

By Mahesh Chand Blogs | Current Affairs Jun 25, 2007
This code show how you can get data from multiple data sources and create add multiple sheets to an Excel doc programmatically using Office Interop,

First of all, you need to add reference to Excel.dll using Add Reference on the project and selecting Microsoft Excel 9.0 Object Library from COM tab on the Add Reference dialog.

 

After that, import the following namespace:

Imports System.Runtime.InteropServices.Marshal

Now use this code:

Dim rescDS As DataSet = New DataSet

        Dim studiesDS As DataSet = New DataSet

        ' Get data

        rescDS = helper.GetFirstDataSet()

        studiesDS = helper.GetSecondDataSet()

        ' Create Excel Application, Workbook, and WorkSheets

        Dim xlExcel As New Excel.Application

        Dim xlBooks As Excel.Workbooks

        Dim xlBook As Excel.Workbook

        Dim xlSheets As Excel.Sheets

        Dim stdSheet As Excel.Worksheet

        Dim xlCells As Excel.Range

        Dim sFile As String

        Dim sTemplate As String

        Dim rescSheet As Excel.Worksheet

 

        sFile = Server.MapPath(Request.ApplicationPath) & "\Excel.xls"

        ' Formatted template the way you want.

        ' If you want to change the format, change this template

        sTemplate = Server.MapPath(Request.ApplicationPath) & "\XLTemplate.xls"

        xlExcel.Visible = False : xlExcel.DisplayAlerts = False

        ' Get all workbooks and open first workbook

        xlBooks = xlExcel.Workbooks

        xlBooks.Open(Server.MapPath(Request.ApplicationPath) & "\XLTemplate.xls")

        xlBook = xlBooks.Item(1)

        ' Get all sheets available in first book

        xlSheets = xlBook.Worksheets

        ' Get first sheet, change its name and get all cells

        stdSheet = CType(xlSheets.Item(1), Excel.Worksheet)

        stdSheet.Name = "First Sheet"

        xlCells = stdSheet.Cells

        ' Fill all cells with data

        GenerateExcelFile(studiesDS.Tables(0), xlCells) 'Fill in the data

 

        ' Get second sheet, change its name and get all cells

        rescSheet = CType(xlSheets.Item(2), Excel.Worksheet)

        rescSheet.Name = "Second Sheet "

        xlCells = rescSheet.Cells

        ' Fill all cells with data

        GenerateExcelFile(rescDS.Tables(0), xlCells)

 

        ' Save created sheets as a file

        xlBook.SaveAs(sFile)

 

        ' Make sure all objects are disposed

        xlBook.Close()

        xlExcel.Quit()

        ReleaseComObject(xlCells)

        ReleaseComObject(stdSheet)

        ReleaseComObject(xlSheets)

        ReleaseComObject(xlBook)

        ReleaseComObject(xlBooks)

        ReleaseComObject(xlExcel)

        xlExcel = Nothing

        xlBooks = Nothing

        xlBook = Nothing

        xlSheets = Nothing

        stdSheet = Nothing

        xlCells = Nothing

        rescSheet = Nothing

        ' Let GC know about it

        GC.Collect()

 

        ' Export Excel for download

        Response.Redirect(sFile)

 

 

 

    ' Generates Excel sheet for the given DataTable's data

    Private Function GenerateExcelFile(ByRef table As DataTable, ByVal xlCells As Excel.Range) As String

        Dim dr As DataRow, ary() As Object

        Dim iRow As Integer, iCol As Integer

        'Output Column Headers

        For iCol = 0 To table.Columns.Count - 1

            xlCells(1, iCol + 1) = table.Columns(iCol).ToString

        Next

        'Output Data

        For iRow = 0 To table.Rows.Count - 1

            dr = table.Rows.Item(iRow)

            ary = dr.ItemArray

            For iCol = 0 To UBound(ary)

                xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString

                Response.Write(ary(iCol).ToString & vbTab)

            Next

        Next

    End Function

 

COMMENT USING