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 SimpleExcelReportModel : PageModel
    {
        [BindProperty]
        public SpreadsheetGear.FileFormat FileFormat { get; set; } = SpreadsheetGear.FileFormat.OpenXMLWorkbook;


        public void OnGet() { }


        public FileResult OnPost()
        {
            // Generate a stream of the workbook using the specified file format.
            System.IO.Stream workbookStream = GenerateWorkbookStream(FileFormat);

            // Get ContentType string corresponding to the specified file format and file name.
            var contentType = GetContentType(FileFormat);
            var extension = GetFileNameExtension(FileFormat);
            var fileName = $"SpreadsheetGear-Sample-SimpleExcelReport.{extension}";

            // Stream the Excel workbook to the client.
            return File(workbookStream, contentType, fileName);
        }


        private static System.IO.Stream GenerateWorkbookStream(SpreadsheetGear.FileFormat fileFormat)
        {
            // Create a new workbook and references to the first sheet and its cells.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
            SpreadsheetGear.IRange cells = worksheet.Cells;

            // Set the worksheet name.
            worksheet.Name = $"{DateTime.Now.Year} Sales";

            // Create column titles and center.
            cells["B1"].Formula = "North";
            cells["C1"].Formula = "South";
            cells["D1"].Formula = "East";
            cells["E1"].Formula = "West";
            cells["B1:E1"].HorizontalAlignment = SpreadsheetGear.HAlign.Center;

            // Load row titles using multiple cell text reference and iteration.
            int quarter = 1;
            foreach (SpreadsheetGear.IRange cell in cells["A2:A5"])
                cell.Formula = "Q" + quarter++;

            // Load random data and format as $ using a multiple cell range.
            SpreadsheetGear.IRange body = cells[1, 1, 4, 4];
            body.Formula = "=RAND()*10000";
            body.NumberFormat = "$#,##0_);($#,##0)";

            // Save workbook to stream.
            System.IO.Stream workbookStream = workbook.SaveToStream(fileFormat);

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

            return workbookStream;
        }


        private static string GetContentType(SpreadsheetGear.FileFormat fileFormat)
        {
            switch (fileFormat)
            {
                case SpreadsheetGear.FileFormat.OpenXMLWorkbook:
                default:
                    return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                case SpreadsheetGear.FileFormat.OpenXMLWorkbookMacroEnabled:
                    return "application/vnd.ms-excel.sheet.macroEnabled.12";
                case SpreadsheetGear.FileFormat.Excel8:
                    return "application/vnd.ms-excel";
                case SpreadsheetGear.FileFormat.CSV:
                    return "text/csv";
                case SpreadsheetGear.FileFormat.UnicodeText:
                    return "text/tab-separated-values";
            }
        }

        private static string GetFileNameExtension(SpreadsheetGear.FileFormat fileFormat)
        {
            switch (fileFormat)
            {
                case SpreadsheetGear.FileFormat.OpenXMLWorkbook:
                default:
                    return "xlsx";
                case SpreadsheetGear.FileFormat.OpenXMLWorkbookMacroEnabled:
                    return "xlsm";
                case SpreadsheetGear.FileFormat.Excel8:
                    return "xls";
                case SpreadsheetGear.FileFormat.CSV:
                    return "csv";
                case SpreadsheetGear.FileFormat.UnicodeText:
                    return "txt";
            }
        }
    }
}