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">
<head>
<title>Worksheet with Chart to Multiple Worksheets with Charts using ASP.NET and VB.NET - Returns XLS Workbook Using SpreadsheetGear</title>
<meta content="Worksheet with Chart to Multiple Worksheets with Charts with ASP.NET, VB.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 workbook set to hold the template workbook and the report workbook.
        Dim workbookSet As SpreadsheetGear.IWorkbookSet = SpreadsheetGear.Factory.GetWorkbookSet()

        ' Open a workbook template containing a chart.
        Dim ssFile As String = Server.MapPath("files/chartsalesregional.xls")
        Dim templateWorkbook As SpreadsheetGear.IWorkbook = workbookSet.Workbooks.Open(ssFile)
        Dim templateWorksheet As SpreadsheetGear.IWorksheet = templateWorkbook.Worksheets("Format Sheet")

        ' Create a new workbook to hold the new Excel Report.
        Dim reportWorkbook As SpreadsheetGear.IWorkbook = workbookSet.Workbooks.Add()

        ' Create a DataSet from an XML file.  Modify this code to use
        ' any DataSet such as one returned from a database query.
        Dim xmlfile As String = Server.MapPath("files/spicesalesregional.xml")
        Dim dataSet As New System.Data.DataSet()
        dataSet.ReadXml(xmlfile)

        ' Create a new report worksheet from each table in the DataSet.
        Dim dataTable As System.Data.DataTable
        For Each dataTable In  dataSet.Tables
            AddReportWorksheet(reportWorkbook, templateWorksheet, dataTable)
        Next dataTable

        ' Delete original blank empty sheet from report workbook.
        reportWorkbook.Worksheets("Sheet1").Delete()

        ' Consolidate all existing worksheets into a summary worksheet.
        AddSummaryWorksheet(reportWorkbook, templateWorksheet)

        ' 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.Clear()
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
        reportWorkbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8)
        Response.End()

    End Sub 'Page_Load

    Private Sub AddReportWorksheet(reportWorkbook As SpreadsheetGear.IWorkbook, templateWorksheet As SpreadsheetGear.IWorksheet, dataTable As System.Data.DataTable)

        ' Create a new worksheet by copying the template worksheet 
        ' after the last worksheet in the report workbook.
        Dim lastWorksheet As SpreadsheetGear.IWorksheet = reportWorkbook.Worksheets(reportWorkbook.Worksheets.Count - 1)
        Dim reportWorksheet As SpreadsheetGear.IWorksheet = CType(templateWorksheet.CopyAfter(lastWorksheet), SpreadsheetGear.IWorksheet)
        reportWorksheet.Name = dataTable.TableName

        ' Copy the DataTable to the worksheet starting at the top left cell.
        Dim range As SpreadsheetGear.IRange = reportWorksheet.Cells(0, 0, 2, 0)
        range.CopyFromDataTable(dataTable, SpreadsheetGear.Data.SetDataFlags.InsertCells)

        ' Auto size all worksheet columns which contain data.
        reportWorksheet.UsedRange.Columns.AutoFit()

    End Sub 'AddReportWorksheet

    Private Sub AddSummaryWorksheet(reportWorkbook As SpreadsheetGear.IWorkbook, templateWorksheet As SpreadsheetGear.IWorksheet)

        ' Create a new worksheet by copying the template worksheet
        ' before the first worksheet in the report workbook.
        Dim firstWorksheet As SpreadsheetGear.IWorksheet = reportWorkbook.Worksheets(0)
        Dim summaryWorksheet As SpreadsheetGear.IWorksheet = CType(templateWorksheet.CopyBefore(firstWorksheet), SpreadsheetGear.IWorksheet)
        summaryWorksheet.Name = "Total"

        ' Size the summary worksheet data range to match one of the region
        ' worksheets which will enable us to consolidate all regions.
        Dim rowCount As Integer = reportWorkbook.Worksheets("EastRegion").Cells("Sales").RowCount
        ' row2 for the IRange.Insert range is:
        '    2 (Row 3 of worksheet which is the 2nd data row)
        '    + rowCount (add the rowCount of the sales table)
        '    - 2 (subtract two because the template starts with two rows)
        '    - 1 (subtract one to get the 2nd row of the range to insert)
        ' NOTE: This code assumes rowCount >= 2.
        '    If rowCount == 2, no Insert or Delete is needed.
        '    If rowCount < 2, use IRange.Delete to delete the 2nd row or both rows.
        summaryWorksheet.Cells(2, 0, 2 + rowCount - 2 - 1, 4).Insert(SpreadsheetGear.InsertShiftDirection.Down)

        ' Use an array formula to retrieve the product names from a region worksheet.
        summaryWorksheet.Cells("Products").FormulaArray = "=EastRegion!Products"

        ' Use an array formula to consolidate all regional sales.
        summaryWorksheet.Cells("Sales").FormulaArray = "=EastRegion!Sales + NorthRegion!Sales + SouthRegion!Sales + WestRegion!Sales"

        ' Auto size all worksheet columns which contain data.
        summaryWorksheet.UsedRange.Columns.AutoFit()

    End Sub 'AddSummaryWorksheet

</script>
</head>
</html>