Live Razor Page Samples

Excel Report with Formulas and Formatting

This sample shows how to create a new workbook, add values, formulas, defined names and formatting, and stream it to Microsoft Excel.
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

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

            // Get the worksheet and change the name. 
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
            worksheet.Name = "2005 Sales";

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

            // Add column headers. 
            cells["B1"].Formula = "Jan";
            cells["C1"].Formula = "Feb";
            cells["D1"].Formula = "Mar";

            // Add row headers. 
            cells["A2"].Formula = "West";
            cells["A3"].Formula = "Central";
            cells["A4"].Formula = "East";

            // Add random data. 
            cells["B2:D4"].Formula = "=RAND()*10000";

            // Center the column headers. 
            cells["B1:D1"].HorizontalAlignment = SpreadsheetGear.HAlign.Center;

            // Bold the row and column headers. 
            cells["B1:D1,A2:A4"].Font.Bold = true;

            // Add defined names. 
            SpreadsheetGear.INames names = workbook.Names;
            names.Add("Jan", "='2005 Sales'!$B$2:$B$4");
            names.Add("Feb", "='2005 Sales'!$C$2:$C$4");
            names.Add("Mar", "='2005 Sales'!$D$2:$D$4");
            names.Add("Sales", "='2005 Sales'!$B$2:$D$4");

            // Format sales data as currency. 
            cells["Sales"].NumberFormat = "$#,##0";

            // Sum each month. 
            cells["B5"].Formula = "=SUM(Jan)";
            cells["C5"].Formula = "=SUM(Feb)";
            cells["D5"].Formula = "=SUM(Mar)";

            // Format sums. 
            cells["B5:D5"].NumberFormat = "$#,##0";

            // 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 beginning of 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-FormulasAndFormatting.xlsx";
            return File(workbookStream, contentType, fileName);
        }
    }
}