Live Razor Page Samples
SQL DataTable to Template Workbook
This sample queries data into a DataTable from SQLite and copies it into a preformatted range from an existing Excel workbook template using IRange.CopyFromDataTable(...).
Supporting Files
The following file is utilized by this sample:
using System.Data;
using System.Data.SQLite;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace Website.Pages.Support.Samples.RazorPages.Database
{
public partial class SqlDataTableToWorkbookTemplateModel : PageModel
{
// Use configuration to get ConnectionString for the database. This is injected from
// the PageModel's constructor which is not shown here.
private readonly IConfiguration _config;
public FileResult OnGet()
{
// Execute query on Products table and place results into a DataTable.
DataTable dataTable = new DataTable();
using (SQLiteConnection connection = new SQLiteConnection(_config.GetConnectionString("SamplesDatabase")))
{
SQLiteCommand cmd = new SQLiteCommand(@"
SELECT Id AS ""Product ID"", Name, UnitCost AS ""Unit Cost""
FROM Products
ORDER BY Name", connection);
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
{
da.Fill(dataTable);
}
}
// Create a workbook and reference to the active sheet.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(@"files\template.xlsx");
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
// Use IRange.CopyFromDataTable(...) to copy the contents of the DataTable into the specified range.
// SetDataFlags.None will add a header row, using the value of DataColumn.ColumnName for the text of
// each column.
worksheet.Cells["B3:D5"].CopyFromDataTable(dataTable, SpreadsheetGear.Data.SetDataFlags.InsertCells);
// 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-SqlDataTableToWorkbookTemplate.xlsx";
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; } = 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;
}
}
}
Quick-Fill