Live Razor Page Samples

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

namespace Website.Pages.Support.Samples.RazorPages.Reporting
{
    public partial class ExcelReportOutlinesModel : PageModel
    {
        public FileResult OnGet()
        {
            // Create a new workbook.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

            // Get a reference to the first worksheet and name it.
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
            worksheet.Name = $"{DateTime.Now.Year} Sales";

            // Get the worksheet cells reference.
            SpreadsheetGear.IRange cells = worksheet.Cells;

            // Clear any existing outlines.
            cells.ClearOutline();

            // Set column titles and formatting.
            cells["A1"].Formula = "Region";
            cells["B1"].Formula = "Quarter";
            cells["C1"].Formula = "Sales";
            cells["A1:C1"].Font.Bold = true;

            // Call method to group and summarize each region.
            CreateRegion("East", cells["A2:C5"], cells["A6:C6"]);
            CreateRegion("West", cells["A7:C10"], cells["A11:C11"]);
            CreateRegion("North", cells["A12:C15"], cells["A16:C16"]);
            CreateRegion("South", cells["A17:C20"], cells["A21:C21"]);

            // Set summary total for all regions.
            SpreadsheetGear.IRange totalCell = cells["C22"];
            totalCell.Formula = "=SUM(C6,C11,C16,C21)";
            totalCell.NumberFormat = "$#,##0_);($#,##0)";
            totalCell.Font.Bold = true;

            // Group all regions.
            cells["A2:A21"].EntireRow.Group();

            // Collapse all region detail levels.
            worksheet.Outline.ShowLevels(2, 0);

            // Show detail for one region.
            cells["A6"].EntireRow.ShowDetail = true;

            // Save workbook to stream using the Open XML (*.xlsx) file format compatible with Excel 2007 and later.
            System.IO.Stream workbookStream = workbook.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-ExcelReportOutlines.xlsx";
            return File(workbookStream, contentType, fileName);
        }


        private static void CreateRegion(string region, SpreadsheetGear.IRange detailCells, SpreadsheetGear.IRange summaryCells)
        {
            // Set region data, formulas and formatting.
            int quarter = 1;
            for (int iRow = 0; iRow < detailCells.RowCount; iRow++)
            {
                detailCells[iRow, 0].Formula = region;
                detailCells[iRow, 1].Formula = "Q" + quarter++;
                detailCells[iRow, 2].Formula = "=RAND() * 10000";
                detailCells[iRow, 2].NumberFormat = "$#,##0_);($#,##0)";
            }

            // Group the region.
            detailCells.EntireRow.Group();

            // Get the data column of the detail cells.
            int lastRow = detailCells.RowCount - 1;
            int lastColumn = detailCells.ColumnCount - 1;
            SpreadsheetGear.IRange dataCells = detailCells[0, lastColumn, lastRow, lastColumn];

            // Set summary titles for the region.
            summaryCells[0, 0].Formula = region;
            summaryCells[0, 1].Formula = "Total";

            // Set summary total for the region.
            SpreadsheetGear.IRange totalCell = summaryCells[0, 2];
            totalCell.Formula = "=SUM(" + dataCells.Address + ")";
            totalCell.NumberFormat = "$#,##0_);($#,##0)";
            totalCell.Font.Bold = true;
        }
    }
}