Live Blazor Server Sample
This sample utilizes SpreadsheetGear in a Blazor Server component to demonstrate a variety of features:
- Opening up a template Excel workbook file on the server, which contains a pre-formatted range and chart.
- Querying data from a database using Entity Framework Core and copying that queried data into a range of cells in the template workbook.
- Setting a chart's series to refer to the newly-added range data.
- Creating a Razor Component (RangeToTable.razor) that accepts an IRange object and converts it into an HTML table.
- Render an image of a chart object and dynamically stream it to the web page as an embedded/inline image in an <img ... /> element.
- Saving a workbook file to Open XML (*.xlsx) and prompting the end-user to save it from their browser.
<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
};
await HandleValidSubmit();
}
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);
}
Quick-Fill