Live Razor Page Samples

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