Live Razor Page Samples
Entity Framework Core to Workbook
This sample uses a simple Entity Framework Core query to extract some data out of a database and copy it into a worksheet, which can be downloaded to the end-user's computer.
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 EFCoreToWorkbookModel : PageModel
{
// Context is injected from the PageModel's constructor which is not shown here.
private readonly SamplesDbContext _dbContext;
public async Task<FileResult> OnGet()
{
// Use Entity Framework Core to query database for all products.
var products = await _dbContext.Products
.OrderBy(p => p.Id)
.ToListAsync();
// 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 = $"Products";
// Add header row
SpreadsheetGear.IRange headerRow = cells["A1:C1"];
cells["A1"].Value = "Id";
cells["B1"].Value = "Name";
cells["C1"].Value = "Unit Cost";
int currentRow = 1;
foreach (Product product in products)
{
cells[currentRow, 0].Value = product.Id;
cells[currentRow, 1].Value = product.Name;
cells[currentRow, 2].Value = product.UnitCost;
currentRow++;
}
// Apply some formatting
headerRow.Font.Bold = true;
SpreadsheetGear.IRange unitCostCells = worksheet.UsedRange.Subtract(headerRow).Intersect(cells["C:C"]);
unitCostCells.NumberFormat = "$#,##0.00";
unitCostCells.HorizontalAlignment = SpreadsheetGear.HAlign.Right;
// AutoFit
worksheet.UsedRange.EntireColumn.AutoFit();
// 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";
// Stream the Excel workbook to the client.
return File(workbookStream, contentType, fileName);
}
}
}
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; } = [];
}
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;
}
}
}
Quick-Fill