Powered by SpreadsheetGear
<%@ Page Language="VB" EnableViewState="false" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<title>DataSet to Excel Workbook with Formats and Formulas using ASP.NET and VB.NET - SpreadsheetGear</title>
<meta content="DataSet to Excel Workbook with Formats and Formulas using ASP.NET, Visual Basic .NET and SpreadsheetGear, a royalty free Microsoft Excel compatible spreadsheet component for the Microsoft .NET Framework featuring the fastest and most complete calculation engine available. Create, read, modify, calculate and write Microsoft Excel workbooks from your Microsoft .NET, ASP.NET, C#, VB.NET and Microsoft Office solutions. Integrates with Microsoft Visual Studio .NET, including IntelliSense and Dynamic Help." name="description" />
<script language="VB" runat="server">

    Sub Page_Load(sender As Object, e As EventArgs)

        ' Create a DataSet from an XML file.
        Dim xmlFileName As String = Server.MapPath("files/nfl.xml")
        Dim dataset As New System.Data.DataSet()

        ' Create a workbook set to hold the template workbook and the report workbook.
        Dim workbookSet As SpreadsheetGear.IWorkbookSet = SpreadsheetGear.Factory.GetWorkbookSet()

        ' Open the template workbook which contains formats, borders and formulas.
        Dim filename As String = Server.MapPath("files/nfltemplate.xls")
        Dim templateWorkbook As SpreadsheetGear.IWorkbook = workbookSet.Workbooks.Open(filename)

        ' Get the template range from a defined name in the template workbook.
        Dim templateRange As SpreadsheetGear.IRange = _

        ' Get the number of rows and columns in the template range.
        Dim templateRangeRowCount As Integer = templateRange.RowCount
        Dim templateRangeColCount As Integer = templateRange.ColumnCount

        ' Create a new workbook with one blank worksheet to hold the new Excel Report.
        Dim reportWorkbook As SpreadsheetGear.IWorkbook = workbookSet.Workbooks.Add()
        Dim reportWorksheet As SpreadsheetGear.IWorksheet = reportWorkbook.Worksheets(0)
        reportWorksheet.WindowInfo.DisplayGridlines = False
        reportWorksheet.Name = dataset.DataSetName

        ' Start at cell B2
        Dim row As Integer = 1
        Dim col As Integer = 1

        ' Insert each DataTable from the DataSet...
        Dim datatable As System.Data.DataTable
        For Each datatable In dataset.Tables
            ' Get the destination range in the report worksheet.
            Dim dstRange As SpreadsheetGear.IRange = reportWorksheet.Cells(row, col, _
              row + templateRangeRowCount - 1, col + templateRangeColCount - 1)

            ' Copy the template range formats and formulas to the report worksheet.
            templateRange.Copy(dstRange, SpreadsheetGear.PasteType.All, _
              SpreadsheetGear.PasteOperation.None, False, False)

            If row = 1 Then
                ' Copy the template range column widths to the report worksheet once.
                templateRange.Copy(dstRange, SpreadsheetGear.PasteType.ColumnWidths, _
                  SpreadsheetGear.PasteOperation.None, False, False)
            End If

            ' Use the TableName for the title of the range - this is a merged
            ' cell centered across the top of the destination range.
            reportWorksheet.Cells(row, col).Formula = datatable.TableName

            ' Add a defined name for the new destination range. This defined
            ' name will be adjusted by IRange.CopyFromDataTable, allowing us
            ' to skip over the newly inserted range and any summary rows
            ' added by the template.
            Dim dstRangeName As SpreadsheetGear.IName = reportWorkbook.Names.Add( _
              datatable.TableName.Replace(" ", ""), "=" + dstRange.Address)

            ' Insert the DataTable into the worksheet. This will adjust the defined name,
            ' as well as the formats, cell borders and formulas which were copied from
            ' the template workbook.
            reportWorksheet.Cells(row + 1, col, row + 3, col).CopyFromDataTable(datatable, _

            ' Update the row counter to the end of the inserted table
            Dim range As SpreadsheetGear.IRange = dstRangeName.RefersToRange
            row = range.Row + range.RowCount + 1
        Next datatable

        ' Stream the Excel workbook to the client in the Excel 97-2003 (xls) 
        ' file format compatible with Excel 97-2019 and Excel for Office 365.
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
        reportWorkbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8)

    End Sub 'Page_Load