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;
        }
    }
}