Live Razor Page Samples

Simple Excel Report

This sample shows how to create a new workbook, add some values, and stream it to a Microsoft Excel file format (*.xlsx / *.xlsm / *.xls).
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";
            }
        }
    }
}
@page
@model SimpleExcelReportModel

<div class="row">
  <div class="col-8 offset-2">
    <div class="card">
      <h4 class="card-header">Choose a file format for the report:</h4>
      <div class="card-body">
        <form asp-page="SimpleExcelReport" method="post">
          <div class="mb-3">
            <label asp-for="FileFormat" class="form-label">File Format:</label>
            <select asp-for="FileFormat" class="form-select">
              <option value="@SpreadsheetGear.FileFormat.OpenXMLWorkbook">
                Open XML (*.xlsx)
              </option>
              <option value="@SpreadsheetGear.FileFormat.OpenXMLWorkbookMacroEnabled">
                Macro-Enabled Open XML (*.xlsm)
              </option>
              <option value="@SpreadsheetGear.FileFormat.Excel8">
                Excel 97-2003 (*.xls)
              </option>
              <option value="@SpreadsheetGear.FileFormat.CSV">
                Comma-Separated Values (*.csv)
              </option>
              <option value="@SpreadsheetGear.FileFormat.UnicodeText">
                Unicode Tab-Delimited Text (*.txt)
              </option>
            </select>
          </div>
          <button class="form-control btn btn-primary"><i class="fas fa-arrow-alt-to-bottom"></i> Download Workbook</button>
        </form>
      </div>
    </div>
  </div>
</div>