Powered by SpreadsheetGear
<%@ Page Language="C#" 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 C# - Returns XLS Workbook Using SpreadsheetGear</title>
<meta content="Worksheet with Chart to Multiple Worksheets with Charts with ASP.NET, C# 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="C#" runat="server">
    
    void Page_Load(Object sender, EventArgs e)
    {
        // Create a workbook set to hold the template workbook and the report workbook.
        SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
        
        // Open a workbook template containing a chart.
        String ssFile = Server.MapPath("files/chartsalesregional.xls");
        SpreadsheetGear.IWorkbook templateWorkbook = workbookSet.Workbooks.Open(ssFile);
        SpreadsheetGear.IWorksheet templateWorksheet = templateWorkbook.Worksheets["Format Sheet"];
        
        // Create a new workbook to hold the new Excel Report.
        SpreadsheetGear.IWorkbook reportWorkbook = 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.
        String xmlfile = Server.MapPath("files/spicesalesregional.xml");
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataSet.ReadXml(xmlfile);
        
        // Create a new report worksheet from each table in the DataSet.
        foreach (System.Data.DataTable dataTable in dataSet.Tables)
            AddReportWorksheet(reportWorkbook, templateWorksheet, 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();
    }
    
    private void AddReportWorksheet(
        SpreadsheetGear.IWorkbook reportWorkbook, 
        SpreadsheetGear.IWorksheet templateWorksheet,
        System.Data.DataTable dataTable)
    {
        // Create a new worksheet by copying the template worksheet 
        // after the last worksheet in the report workbook.
        SpreadsheetGear.IWorksheet lastWorksheet = 
            reportWorkbook.Worksheets[reportWorkbook.Worksheets.Count - 1];
        SpreadsheetGear.IWorksheet reportWorksheet = 
            (SpreadsheetGear.IWorksheet)templateWorksheet.CopyAfter(lastWorksheet);
        reportWorksheet.Name = dataTable.TableName;
        
        // Copy the DataTable to the worksheet starting at the top left cell.
        SpreadsheetGear.IRange range = 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();
    }
    
    private void AddSummaryWorksheet(
        SpreadsheetGear.IWorkbook reportWorkbook,
        SpreadsheetGear.IWorksheet templateWorksheet)
    {        
        // Create a new worksheet by copying the template worksheet
        // before the first worksheet in the report workbook.
        SpreadsheetGear.IWorksheet firstWorksheet = reportWorkbook.Worksheets[0];
        SpreadsheetGear.IWorksheet summaryWorksheet = 
            (SpreadsheetGear.IWorksheet)templateWorksheet.CopyBefore(firstWorksheet);
        summaryWorksheet.Name = "Total";
        
        // Size the summary worksheet data range to match one of the region
        // worksheets which will enable us to consolidate all regions.
        int rowCount = 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();
    }
    
</script>
</head>
</html>