Live Blazor Server Sample

This sample utilizes SpreadsheetGear in a Blazor Server component to demonstrate a variety of features:

<div class="card">
  <h3 class="card-header">Report Details</h3>
  <div class="card-body">
    <div class="card-text">
      <EditForm Model="model" OnValidSubmit="HandleValidSubmit">
        <div class="row">
          <div class="col">
            <h5>Start Date</h5>
            <div class="mb-2 my-1">
              <label for="year-start" class="form-label mx-2">Year:</label>
              <InputSelect @bind-Value="model.YearStart" id="year-start" class="form-control">
                @for (int year = inputYearMin; year <= inputYearMax; year++)
                {
                  <option value="@year">@year</option>
                }
              </InputSelect>
            </div>
            <div class="mb-2 my-1">
              <label for="month-start" class="form-label mx-2">Month:</label>
              <InputSelect @bind-Value="model.MonthStart" id="month-start" class="form-control">
                @for (int month = 1; month <= 12; month++)
                {
	                <option value="@month">@(new DateTime(2000, month, 1).ToString("MMMM"))</option>
                }
              </InputSelect>
            </div>
          </div>

          <div class="col">
            <h5>End Date</h5>
            <div class="mb-2 my-1">
              <label for="year-end" class="form-label mx-2">Year:</label>
              <InputSelect @bind-Value="model.YearEnd" id="year-end" class="form-control">
                @for (int year = inputYearMin; year <= inputYearMax; year++)
                {
                  <option value="@year">@year</option>
                }
              </InputSelect>
            </div>
            <div class="mb-2 my-1">
              <label for="month-end" class="form-label mx-2">Month:</label>
              <InputSelect @bind-Value="model.MonthEnd" id="month-end" class="form-control">
                @for (int month = 1; month <= 12; month++)
                {
	                <option value="@month">@(new DateTime(2000, month, 1).ToString("MMMM"))</option>
                }
              </InputSelect>
            </div>
          </div>
        </div>
    
        <div class="mt-4 text-center">
          <button @onclick="@(() => model.DownloadExcelFile = false)" class="btn btn-primary my-1">
            <i class="fas fa-redo-alt"></i> Refresh Report
          </button>
          <button @onclick="@(() => model.DownloadExcelFile = true)" class="btn btn-primary my-1">
            <i class="fas fa-arrow-to-bottom"></i> Download Report
          </button>
        </div>
      </EditForm>
    </div>
  </div>
</div>

@{
  if (componentState == ComponentState.Loading)
  {
    <div class="alert alert-secondary text-center">
      <i class="fas fa-spinner fa-spin"></i> Loading Report...
    </div>
  }
  if (componentState == ComponentState.Complete)
  {
    <div class="row mt-3">
      @if (!string.IsNullOrEmpty(imgData))
      {
        <div class="col">
          <img src="@imgData" style="width: @(imgWidth)pt; height: @(imgHeight)pt;" />
        </div>
      }
      <div class="col">
        <RangeToTable Range="reportRange" FirstRowIsHeader="true"></RangeToTable>
      </div>
    </div>
  }
  
  if (!string.IsNullOrWhiteSpace(errorMessage))
  {
    <div class="alert alert-danger mt-3">@errorMessage</div>
  }   
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Components;
using Microsoft.EntityFrameworkCore;
using Microsoft.JSInterop;

namespace Website.Pages.Support.Samples.Blazor.Server
{
    public partial class BlazorServerSample : ComponentBase
    {
        [Inject]
        protected IJSRuntime JSRuntime { get; set; } = null!;

        [Inject]
        protected SamplesDbContext DbSalesContext { get; set; } = null!;

        // User submitted the report form
        private async Task HandleValidSubmit()
        {
            // Initialize variables and component's state to indicate the report is loading, which will show a "Loading..." alert
            // box if it takes a non-trivial amount of time to complete.
            errorMessage = imgData = "";
            componentState = ComponentState.Loading;

            // Queries a database for sales data within the date range provided by the end-user, populates a workbook using that 
            // data, then updates the source range used by a chart in the workbook.
            SpreadsheetGear.IWorkbook workbook = await GenerateReportWorkbook();

            // Get reference to "Sheet1" worksheet.
            SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];

            // The "reportRange" field is passed into the RangeToTable Razor Component to render the range on the web page.
            reportRange = worksheet.UsedRange;

            // If there's only one row in the reportRange (the header row), then there's no data to display.  Indicate this in
            // an error message on the page.
            if (reportRange.RowCount == 1)
            {
                errorMessage = "Report has no data.  Try altering the Start and End Dates for the report.";
                reportRange = null;
            }
            // Render an image of the chart if there is data in the reportRange
            else
            {
                // Get a reference to the chart shape.
                SpreadsheetGear.Shapes.IShape chartShape = worksheet.Shapes["Chart 1"];

                // Wrap rendering the chart in a Task.  While this operation should complete quickly, performing it in a task is
                // good practice so as to keep the UI responsive in case of unexpected delays.
                imgData = await Task.Factory.StartNew(() => {
                    // Create an Image class, which will be responsible for rendering an image of the chart.
                    SpreadsheetGear.Drawing.Image image = new SpreadsheetGear.Drawing.Image(chartShape);

                    // By default the DPI is set to 96.  Increase to 192 to provide better rendering on high-DPI screens.
                    image.Dpi = 96 * 2;

                    // Render a bitmap of the chart.
                    using System.Drawing.Bitmap bitmap = image.GetBitmap();

                    // Create a MemoryStream and save a PNG image to the stream.
                    using var stream = new System.IO.MemoryStream();
                    bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Png);

                    // Convert the stream to bytes and then to a Base-64 string, which can be used to embed the image file directly
                    // in an <img /> tag and its "src" attribute.
                    return $"data:image/png;base64,{Convert.ToBase64String(stream.ToArray())}";
                });

                // Determine the width and height of the chart shape, in points, and use these values for the size of the chart on
                // the web page.
                imgWidth = (int)Math.Round(chartShape.Width);
                imgHeight = (int)Math.Round(chartShape.Height);
            }

            // Indicate this component's state as completed so that next refresh will include the above rendered chart image (if
            // available) and reportRange table data.
            componentState = ComponentState.Complete;

            // Handle "Download Report" button if it was clicked.
            if (model.DownloadExcelFile)
            {
                // Prompt user to download the Excel workbook file if no error occurred.
                if (string.IsNullOrEmpty(errorMessage))
                {
                    // Save the workbook to a byte array.
                    var workbookBytes = workbook.SaveToMemory(SpreadsheetGear.FileFormat.OpenXMLWorkbook);

                    // JSInterop call which sends the file bytes to the client and prompts the download.
                    await PromptDownload("SpreadsheetGear-Samples-Blazor-Report.xlsx", workbookBytes);
                }
                // If there was an error, notify the user something went wrong with a popup (in addition to the error box shown
                // on the page).
                else
                {
                    await ShowAlert($"Cannot download Excel workbook file: '{errorMessage}'");
                }
            }
        }


        private async Task<SpreadsheetGear.IWorkbook> GenerateReportWorkbook()
        {
            // Queries the database using Entity Framework Core for monthly sales totals broken down per region.
            var monthlySales = await GetMonthlySalesFromDB();

            // Wrap loading the workbook and chart population in a Task.  While this operation should complete quickly, performing
            // it in a task is good practice so as to keep the UI responsive in case of unexpected delays.
            SpreadsheetGear.IWorkbook workbook = await Task.Factory.StartNew(() => {
                // Open a template workbook stored on the server that is ready to be populated with monthly regional sales data.  It
                // also includes an empty pre-formatted chart, also ready to be populated with sales data.
                SpreadsheetGear.IWorkbook wb = SpreadsheetGear.Factory.GetWorkbook(@"wwwroot\support\BlazorReportTemplate.xlsx");

                // Get reference to "Sheet1" worksheet and its cells.
                SpreadsheetGear.IWorksheet worksheet = wb.Worksheets["Sheet1"];
                SpreadsheetGear.IRange cells = worksheet.Cells;

                // Setup a mapping between each region and its corresponding Column Index in the worksheet.
                var regionColMapper = new Dictionary<string, int>() {
                    { "North", 1 },
                    { "South", 2 },
                    { "East", 3 },
                    { "West", 4 },
                };

                // Loop through each monthly sales item to copy into the worksheet
                int currentRow = 1;
                foreach (var sales in monthlySales)
                {
                    // IRange.Value can be used to set the value of a cell, in this case the sales month.
                    cells[currentRow, 0].Value = sales.SalesMonth;

                    // Loop through each region's monthly sales details.
                    foreach (var region in sales.RegionalSales)
                    {
                        // Figure out which column we should populate.
                        int regionCol = regionColMapper[region.RegionName];

                        // Get a reference to the desired cell via the IRange[...] indexer and set its value.
                        cells[currentRow, regionCol].Value = region.MonthlySalesTotal;
                    }
                    currentRow++;
                }

                // Get a reference to the chart.
                SpreadsheetGear.Charts.IChart chart = worksheet.Shapes["Chart 1"].Chart;

                // Set the source range of the chart to the UsedRange of this worksheet.
                chart.SetSourceData(worksheet.UsedRange, SpreadsheetGear.Charts.RowCol.Columns);

                return wb;
            });

            return workbook;
        }


        // Uses Entity Framework Core to retrieve monthly sales data broken down per region.
        private async Task<IEnumerable<MonthlyRegionalSales>> GetMonthlySalesFromDB()
        {
            // Convert model inputs into DateTime objects.  Note "endDate" has 1 month added to it, but 
            // is used exclusively (<) in the query to capture all sales within the specified last month.
            DateTime startDate = new DateTime(model.YearStart, model.MonthStart, 1);
            DateTime endDate = new DateTime(model.YearEnd, model.MonthEnd, 1).AddMonths(1);

            // Get all sales between the requested dates and return a projection of the needed data.
            var allSales = await DbSalesContext.Sales
                .Where(s => s.PurchaseDate >= startDate && s.PurchaseDate < endDate)
                .OrderBy(s => s.PurchaseDate)
                .Select(s => new { 
                    s.PurchaseDate, 
                    s.Region, 
                    SaleTotal = s.Product.UnitCost * s.Quantity })
                .ToListAsync();

            // Group sales item into "monthly chunks" (grouped by by purchase date year/month), then within each
            // monthly group, further group sales into each region and its monthly sales total.
            var monthlySales = allSales
                .GroupBy(s => new { s.PurchaseDate.Year, s.PurchaseDate.Month })
                .Select(g => new MonthlyRegionalSales() {
                    SalesMonth = new DateTime(g.Key.Year, g.Key.Month, 1),
                    RegionalSales = g.GroupBy(gg => gg.Region).Select(gg => new RegionalSalesItem() {
                        RegionName = gg.Key,
                        MonthlySalesTotal = gg.Sum(s => s.SaleTotal)
                    })
                });

            return monthlySales;
        }


        protected async override Task OnInitializedAsync()
        {
            // Query the database to find the earliest and latest purchase dates available.
            DateTime earliestDate = (await DbSalesContext.Sales.MinAsync(s => s.PurchaseDate));
            DateTime latestDate = (await DbSalesContext.Sales.MaxAsync(s => s.PurchaseDate));

            // Set the range of dates that will be presented in the <select> elements on the form for the start and
            // end report dates.
            inputYearMin = earliestDate.Year;
            inputYearMax = latestDate.Year;

            // Setup default values for the model.
            model = new ReportModel() {
                YearStart = earliestDate.Year,
                MonthStart = earliestDate.Month,
                YearEnd = latestDate.Year,
                MonthEnd = latestDate.Month
            };
        }


        public ValueTask<object> PromptDownload(string filename, byte[] fileBytes)
        {
            // See also BlazorServerHelpers.js for the promptDownload(...) method.
            return JSRuntime.InvokeAsync<object>("promptDownload", filename, Convert.ToBase64String(fileBytes));
        }


        public ValueTask<object> ShowAlert(string message)
        {
            // Call the native Javascript alert method to popup the provided message to the end user.
            return JSRuntime.InvokeAsync<object>("window.alert", message);
        }


        // Model for the sales report form presented to the end-user.
        private ReportModel model;

        // The RangeToTable Razor Component is provided this range to render.
        private SpreadsheetGear.IRange reportRange;

        // Range of years presented to the end-user on the report form.
        private int inputYearMin, inputYearMax;

        // Stores the contents of the "src" attribute of an <img /> tag that will display a rendered image of a chart.
        private string imgData = "";

        // Holds the width and height of the chart image.
        private int imgWidth = 0;
        private int imgHeight = 0;

        private ComponentState componentState = ComponentState.Init;
        private string errorMessage = "";


        public class ReportModel
        {
            public int YearStart { get; set; }
            public int MonthStart { get; set; }

            public int YearEnd { get; set; }
            public int MonthEnd { get; set; }

            public bool DownloadExcelFile { get; set; }
        }

        public class MonthlyRegionalSales
        {
            public DateTime SalesMonth { get; set; }
            public IEnumerable<RegionalSalesItem> RegionalSales { get; set; }
        }

        public class RegionalSalesItem
        {
            public string RegionName { get; set; }
            public decimal MonthlySalesTotal { get; set; }
        }

        /// <summary>
        /// Setup an enum of basic flags indicating the various states this component can have.
        /// </summary>
        public enum ComponentState
        {
            Init,
            Loading,
            Complete
        }
    }
}
@if (Range != null)
{
  <table class="table table-striped table-sm small">
    @if (FirstRowIsHeader)
    {
      <thead class="table-dark">
        <tr>
          @foreach (SpreadsheetGear.IRange cell in headerRow.Columns)
          {
            // Get some basic formatting from this cell.
            string classes = GetClassAttribute(cell);

            // Using IRange.Text here, which will return the formatted value of a cell.
            <th class="@(classes.Length > 0 ? classes : "")">@cell.Text</th>
          }
        </tr>
      </thead>
    }
    <tbody>
      @if (dataRange != null)
      {
        @foreach (SpreadsheetGear.IRange row in dataRange.Rows)
        {
          // Don't render hidden rows.
          @if (!row.Hidden)
          {
            <tr>
              @foreach (SpreadsheetGear.IRange cell in row.Columns)
              {
                // Get some basic formatting from this cell.
                string classes = GetClassAttribute(cell);
        
                // Again, using IRange.Text here, which will return the formatted value of a cell.
                <td class="@(classes.Length > 0 ? classes : "")">@cell.Text</td>
              }
            </tr>
          }
        }
      }
      else
      {
        <tr>
          <td colspan="@headerRow.ColumnCount" class="text-center text-muted">
            No Data To Display
          </td>
        </tr>
      }
    </tbody>
  </table>
}

@code {
    [Parameter]
    public SpreadsheetGear.IRange Range { get; set; }

    [Parameter]
    public bool FirstRowIsHeader { get; set; }

    private SpreadsheetGear.IRange headerRow => FirstRowIsHeader ? Range[0, 0, 0, Range.ColumnCount - 1] : null;
    private SpreadsheetGear.IRange dataRange => FirstRowIsHeader ? Range.Subtract(headerRow) : Range;

    /// <summary>
    /// A very basic routine to add formatting to HTML output based on the corresponding IRange cell's formatting.
    /// </summary>
    /// <param name="cell">A single cell, for which formatting classes will be based off.</param>
    /// <returns>A string such as ' class="..."' if formatting options are found, otherwise an empty string.</returns>
    private string GetClassAttribute(SpreadsheetGear.IRange cell)
    {
      List<string> classes = new List<string>();

      // Horizontal Alignment
      if (cell.HorizontalAlignment == SpreadsheetGear.HAlign.Center)
        classes.Add("text-center");
      else if (cell.HorizontalAlignment == SpreadsheetGear.HAlign.Right)
        classes.Add("text-end");

      // Basic font formatting
      if (cell.Font.Bold)
        classes.Add("fw-bold");
      if (cell.Font.Italic)
        classes.Add("fst-italic");
      if (cell.Font.Underline != SpreadsheetGear.UnderlineStyle.None)
        classes.Add("text-underline");

      // If desired, this could be expanded to include other formatting, such as other font settings, cell 
      // interior color, etc.

      if (classes.Count > 0)
        return string.Join(' ', classes);
      return "";
    }
}
// Intended to be called via IJSInterop to prompt the download of a file generated by the Blazor app.
function promptDownload(fileName, fileBytesBase64) {
  // Create a hidden <a href=""></a> where the "href" attribute contains the contents of the file in Base-64 form.
  var hyperlink = document.createElement("a");
  hyperlink.download = fileName;
  hyperlink.href = "data:application/octet-stream;base64," + fileBytesBase64;
  hyperlink.style = "display:none;";

  // Add the link tag to the end of the DOM..
  document.body.appendChild(hyperlink);

  // Simulate a click event to prompt the download.
  hyperlink.click();

  // Remove the link from the DOM.
  document.body.removeChild(hyperlink);
}