Live Razor Page Samples

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Reporting
{
    public partial class ExcelReportWithChartMultipleWorksheetsModel : PageModel
    {
        public FileResult OnGet()
        {
            // 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.
            SpreadsheetGear.IWorkbook templateWorkbook = workbookSet.Workbooks.Open("files/chartsalesregional.xlsx");
            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.
            System.Data.DataSet dataSet = new System.Data.DataSet();
            dataSet.ReadXml(INTERNAL_GetSupportingFileStream("spicesalesregional.xml"));
            dataSet.ReadXml("files/spicesalesregional.xml");

            // 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);

            // Save workbook to stream using the Open XML (*.xlsx) file format compatible with Excel 2007 and later.
            System.IO.Stream workbookStream = reportWorkbook.SaveToStream(SpreadsheetGear.FileFormat.OpenXMLWorkbook);

            // Reset position to the beginning of the stream.
            workbookStream.Seek(0, System.IO.SeekOrigin.Begin);

            // Stream the Excel workbook to the client.
            var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            var fileName = "SpreadsheetGear-Sample-ExcelReportWithChartMultipleWorksheets.xlsx";
            return File(workbookStream, contentType, fileName);
        }

        private static 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 static 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();
        }
    }
}