Live Razor Page Samples

Entity Framework Core to Workbook with Multiple Sheets

This sample queries a particular "region's" data out of a database with Entity Framework Core and generates a multi-worksheet workbook from the data, including a sheet for each quarter of sales for a list of products as well as a summary sheet for all sales across all quarters. Many other useful APIs are demonstrated during this process.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Database
{
    public partial class EFCoreToWorkbookMultipleSheetsModel : PageModel
    {
        // Context is injected from the PageModel's constructor which is not shown here.
        private readonly SamplesDbContext _dbContext;

        [BindProperty]
        public string Region { get; set; }

        [BindProperty]
        public int SalesYear { get; set; }


        public void OnGet()
        {
            Region = "North";
            SalesYear = DateTime.Today.Year - 1;
        }

        public async Task<FileResult> OnPost()
        {
            // Create a new workbook.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();

            // Creates a new worksheet for each quarter and populates with data from a database.
            await GenerateQuarterlySalesSheets(workbook);

            // Pulls all the totals from the Quarterly sheets onto a single summary sheet.
            SetupSummarySheet(workbook);

            // Stream the Excel workbook to the client.
            return SaveWorkbookToFileResultStream(workbook);
        }

        private async Task GenerateQuarterlySalesSheets(SpreadsheetGear.IWorkbook workbook)
        {
            // Define quarter dates and a label for the specified year and loop through each one.
            var quarters = new List<(string Label, DateTime Start, DateTime End)>() {
                ( "Q1 Sales", new DateTime(SalesYear, 1, 1), new DateTime(SalesYear, 3, 31)),
                ( "Q2 Sales", new DateTime(SalesYear, 4, 1), new DateTime(SalesYear, 6, 30)),
                ( "Q3 Sales", new DateTime(SalesYear, 7, 1), new DateTime(SalesYear, 9, 30)),
                ( "Q4 Sales", new DateTime(SalesYear, 10, 1), new DateTime(SalesYear, 12, 31))};
            foreach (var (Label, Start, End) in quarters)
            {
                // Use Entity Framework Core to query database with specified information.
                var products = await _dbContext.Products
                    .Select(p => new {
                        p.Id,
                        p.Name,
                        p.UnitCost,
                        UnitsSold = p.Sales
                            .Where(s => s.Region == Region && (s.PurchaseDate >= Start && s.PurchaseDate <= End))
                            .Sum(s => s.Quantity)
                    })
                    .OrderBy(p => p.Name)
                    .ToListAsync();

                // Create a new worksheet for this quarter, rename the sheet and setup a header row.
                SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets.Add();
                worksheet.Name = Label;
                SpreadsheetGear.IRange cells = worksheet.Cells;
                cells["A1"].Value = "Id";
                cells["B1"].Value = "Name";
                cells["C1"].Value = "Units";
                cells["D1"].Value = "Unit Cost";
                cells["E1"].Value = "Total Sale";

                // Add data to the worksheet, starting in Row 2 (A1-notation, not 0-based integer indexes).
                int currentRow = 2;
                foreach (var product in products)
                {
                    cells[$"A{currentRow}"].Value = product.Id;
                    cells[$"B{currentRow}"].Value = product.Name;
                    cells[$"C{currentRow}"].Value = product.UnitsSold;
                    cells[$"D{currentRow}"].Value = product.UnitCost;
                    currentRow++;
                }
                int lastRow = currentRow - 1;

                // Despite using the same formula string for all cells, due to the use of relative row references the actual 
                // addresses for each cell will be shifted according the row it is in, just like in Excel.
                cells[$"E2:E{lastRow}"].Value = "=C2*D2";

                // Apply some formatting.
                cells["A1:E1"].Font.Bold = true;
                cells[$"C1:E{lastRow}"].HorizontalAlignment = SpreadsheetGear.HAlign.Right;
                cells[$"D1:E{lastRow}"].NumberFormat = "$#,##0.00";
            }
        }

        private void SetupSummarySheet(SpreadsheetGear.IWorkbook workbook)
        {
            // Use the workbook's default "Sheet1" for the summary sheet, starting by renaming it.
            SpreadsheetGear.IWorksheet summarySheet = workbook.Worksheets["Sheet1"];
            summarySheet.Name = $"{Region} - Year Sales - {SalesYear}";

            // We need to get the product names, so we'll copy them from the Q1 sheet.
            SpreadsheetGear.IWorksheet q1Sheet = workbook.Worksheets[1];

            // Use the Intersect(...) method to get a range of cells which is the interscction of Column B and the
            // worksheet's used range.
            SpreadsheetGear.IRange productsCol = q1Sheet.Cells["B:B"].Intersect(q1Sheet.UsedRange);

            // Copy the "productsCol" range to the summary sheet, including its header row.
            productsCol.Copy(summarySheet.Cells["A1"]);

            // This code creates a series of cross-sheet formulas to each of the other Quarter worksheets, referencing their
            // total columns so as to provide a summary of totals for all quarters.
            int currentColIndex = 1;
            int numProducts = productsCol.RowCount - 1;
            for (int i = 1; i < workbook.Worksheets.Count; i++)
            {
                // Setup the cross-sheet formula that references each Quarter's totals.
                string sheetName = workbook.Worksheets[i].Name;
                summarySheet.Cells[1, currentColIndex, numProducts, currentColIndex].Formula = $"='{sheetName}'!E2";

                // Setup and apply a bit of formatting to the header row.
                summarySheet.Cells[0, currentColIndex].Value = sheetName;
                summarySheet.Cells[0, currentColIndex].Font.Bold = true;

                currentColIndex++;
            }

            // Create a final total row for each quarter.  
            int summaryRowIndex = summarySheet.UsedRange.RowCount;
            int lastColIndex = summarySheet.UsedRange.ColumnCount - 1;

            // For demonstration purposes (and the fact that it's easier this way), note that we can set a formula using R1C1 notation
            // with the IRange.FormulaR1C1 property even though the workbook is in A1 mode.  R1C1 allows to reference adjacent rows using
            // offsets relative to the current cell.  In this case since we have the number of products and know the data is directly above
            // this cell, using R1C1 makes this process a little easier.
            SpreadsheetGear.IRange totalCells = summarySheet.Cells[summaryRowIndex, 1, summaryRowIndex, lastColIndex];
            totalCells.FormulaR1C1 = $"=SUM(R[-{numProducts}]C:R[-1]C)";
            totalCells.Font.Bold = true;
            totalCells.Borders[SpreadsheetGear.BordersIndex.EdgeTop].LineStyle = SpreadsheetGear.LineStyle.Double;

            // To apply formatting, create a range to the "total" cells using some range manipulation with IRange.Subtract(...).
            SpreadsheetGear.IRange totalsCells = summarySheet.UsedRange.Subtract(summarySheet.Cells["A:A"]).Subtract(summarySheet.Cells["1:1"]);
            totalsCells.NumberFormat = "$#,##0.00";

            // AutoFit all columns in the used range.
            summarySheet.UsedRange.EntireColumn.AutoFit();

            // Make this the active sheet when the workbook is opened in Excel.
            summarySheet.Select();
        }

        private FileStreamResult SaveWorkbookToFileResultStream(SpreadsheetGear.IWorkbook workbook)
        {
            // Save workbook to stream.
            System.IO.Stream workbookStream = workbook.SaveToStream(SpreadsheetGear.FileFormat.OpenXMLWorkbook);

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

            // Use ContentType string corresponding to the XLSX file format and set a file name.
            var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            var fileName = "SpreadsheetGear-Sample-DatabaseToWorkbook.xlsx";

            return File(workbookStream, contentType, fileName);
        }
    }
}
@page
@model EFCoreToWorkbookMultipleSheetsModel

<div class="row">
  <div class="col-sm-12 offset-sm-0 col-md-10 offset-md-1 col-lg-8 offset-lg-2">
    <div class="card">
      <h2 class="card-header">Report Options</h2>
      <div class="card-body">
        <form asp-page="EFCoreToWorkbookMultipleSheets" method="post">
          <div class="mb-2">
            <label asp-for="Region" class="form-label">Region</label>
            <select asp-for="Region" class="form-select">
              <option value="North">North</option>
              <option value="South">South</option>
              <option value="East">East</option>
              <option value="West">West</option>
            </select>
          </div>
          <div class="mb-2">
            <label asp-for="SalesYear" class="form-label">Sales Year</label>
            <select asp-for="SalesYear" class="form-select">
              @for (int year = DateTime.Today.Year - 3; year < DateTime.Today.Year; year++)
              {
                <option value="@year">@year</option>
              }
            </select>
          </div>
          <div class="text-center">
            <button class="btn btn-primary"><i class="fas fa-arrow-to-bottom"></i> Generate Workbook</button>
          </div>
        </form>
      </div>
    </div>
  </div>
</div>

  
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace Website.Pages.Support.Samples
{
    public class SamplesDbContext(DbContextOptions<SamplesDbContext> options) : DbContext(options)
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(b => {
                b.HasMany(p => p.Sales)
                    .WithOne(oi => oi.Product);
                b.Property(p => p.UnitCost)
                    .HasConversion<double>();    // As of this writing, SQLite doesn't support decimal.
                b.HasData(SampleDataSeeder.Products);
            });

            // Seed database with some sample data.
            var sales = SampleDataSeeder.GenerateSales(5);

            modelBuilder.Entity<Sale>(b => {
                b.HasData(sales.Select(s => new { 
                    s.Id, 
                    s.PurchaseDate, 
                    s.Region, 
                    ProductId = s.Product.Id,
                    s.Quantity
                }));
            });
        }

        public DbSet<Product> Products { get; set; }
        public DbSet<Sale> Sales { get; set; }
    }


    public class Product(int id, string name, decimal unitCost)
    {
        public int Id { get; set; } = id;
        public string Name { get; set; } = name;
        public decimal UnitCost { get; set; } = unitCost;
        public IList<Sale> Sales { get; set; } = new List<Sale>();
    }


    public class Sale
    {
        public int Id { get; set; }
        public DateTime PurchaseDate { get; set; }
        public string Region { get; set; }
        public Product Product { get; set; }
        public int Quantity { get; set; }
    }


    // Helper class to seed the database with some sample data.
    public static class SampleDataSeeder
    {
        public static List<Product> Products = [
            new Product(10001, "Basil", 1.99m),
            new Product(10002, "Black Pepper - Fine", 2.99m),
            new Product(10003, "Cayenne Pepper", 3.99m),
            new Product(10201, "Chili Powder", 2.99m),
            new Product(10202, "Cumin - Ground", 2.99m),
            new Product(10203, "Garlic Powder", 1.99m),
            new Product(10401, "Marjoram", 3.99m),
            new Product(10402, "Onion Powder", 1.99m),
            new Product(10403, "Oregano", 2.99m),
            new Product(10801, "Red Pepper - Crushed", 2.99m),
            new Product(10802, "Rosemary", 3.99m),
            new Product(10803, "Thyme", 3.99m)
        ];

        public static List<string> Regions = ["North", "South", "East", "West"];

        public static List<Sale> GenerateSales(int numYears)
        {
            var sales = new List<Sale>();
            var rand = new Random();

            DateTime startDate = new DateTime(DateTime.Today.Year - numYears, 1, 1);
            DateTime endDate = new DateTime(DateTime.Today.Year, 1, 1);

            // Used to control the rate at which each region's sales increase over time.
            var regionSalesGrowthRates = new Dictionary<string, (double Min, double Max)>() {
                { "North", (60.0, 80.0) },
                { "South", (40.0, 60.0) },
                { "East", (20.0, 40.0) },
                { "West", (5.0, 20.0) }
            };

            // Populate sales in a way that increases sales over time and at varying velocities depending on
            // the region.  This is done to provide some visual separation of each series when charting based
            // on regions...or just to make the charts prettier :)
            int idCounter = 1;
            foreach (var region in Regions)
            {
                int monthCounter = 1;
                DateTime currentMonth = startDate;

                // Increments 1 month at a time.
                while (currentMonth < endDate)
                {
                    // Let each monthly sales vary by some random but bounded amount, thereby producing a non-linear but 
                    // still-growing sales trend.  Usage of "regionSalesGrowthRates" allows each region to grow at a 
                    // different rate from other regions.
                    double monthlySalesTotalMin = regionSalesGrowthRates[region].Min * monthCounter;
                    double monthlySalesTotalMax = regionSalesGrowthRates[region].Max * monthCounter;
                    double randomBoundedMonthlySales = rand.Next((int)monthlySalesTotalMin, (int)monthlySalesTotalMax);

                    // Fill this month with sales until the sales total exceeds randomBoundedMonthlySales
                    double totalMonthlySales = 0.0;
                    do
                    {
                        var sale = new Sale() {
                            Id = idCounter++,
                            // Randomize specific day of month
                            PurchaseDate = new DateTime(currentMonth.Year, currentMonth.Month, 
                                rand.Next(1, DateTime.DaysInMonth(currentMonth.Year, currentMonth.Month) + 1)),
                            Region = region,
                            Product = Products[rand.Next(Products.Count)],
                            Quantity = rand.Next(1, 25)
                        };
                        sales.Add(sale);
                        totalMonthlySales += (double)(sale.Product.UnitCost * sale.Quantity);
                    } while (totalMonthlySales < randomBoundedMonthlySales);

                    // Setup for next iteration
                    currentMonth = currentMonth.AddMonths(1);
                    monthCounter++;
                }
            }

            return sales;
        }
    }
}