Sheets in Blazor Sites

Blazor is a framework for building user interfaces using C#, HTML, JS and CSS.

SheetJS is a JavaScript library for reading and writing data from spreadsheets.

This demo uses Blazor and SheetJS to process and generate spreadsheets. We'll explore how to load SheetJS in Razor components and compare common state models and data flow strategies.

Blazor support is considered experimental.

Great open source software grows with user tests and reports. Any issues should be reported to the Blazor project for further diagnosis.

Telemetry

The dotnet command embeds telemetry.

The DOTNET_CLI_TELEMETRY_OPTOUT environment variable should be set to 1.

"Platform Configuration" includes instructions for setting the environment variable on supported platforms.

Integration Details

Installation

The SheetJS library can be loaded when the page is loaded or imported whenever the library functionality is used.

Standalone Script

The SheetJS Standalone scripts can be loaded in the root HTML page (typically wwwroot/index.html):

<!-- use version 0.20.3 -->
<script lang="javascript" src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>

ECMAScript Module

The SheetJS ECMAScript module script can be dynamically imported from functions. This ensures the library is only loaded when necessary. The following JS example loads the library and returns a Promise that resolves to the version string:

async function sheetjs_version(id) {
  /* dynamically import the script in the event listener */
  const XLSX = await import("https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs");

  /* use the library */
  return XLSX.version;
}

Calling JS from C#

Callbacks for events in Razor elements invoke C# methods. The C# methods can use Blazor APIs to invoke JS methods that are visible in the browser global scope.

Setup

The primary mechanism for invoking JS functions from Blazor is IJSRuntime¹. It should be injected at the top of relevant Razor component scripts:

Injecting IJSRuntime

@inject IJSRuntime JS

Fire and Forget

When exporting a file with the SheetJS writeFile method², browser APIs do not provide success or error feedback. As a result, this demo invokes functions using the InvokeVoidAsync static method³.

The following C# method will invoke the export_method method in the browser:

Invoking JS functions from C#
private async Task ExportDataset() {
    await JS.InvokeVoidAsync("export_method", data);
}

The JS methods must be defined in the global scope!

In this demo, the script is added to the HEAD block of the root HTML file:

wwwroot/index.html
<head>
  <!-- ... meta / title / base / link tags -->
  <link href="SheetJSBlazorWasm.styles.css" rel="stylesheet" />

  <!-- script with `export_method` is in the HEAD block -->
  <script>
    /* In a normal script tag, Blazor JS can call this method */
    async function export_method(...rows) {
      /* display the array of objects */
      console.log(rows);
    }
  </script>
</head>

When using <script type="module">, top-level function definitions are not visible to Blazor by default. They must be attached to globalThis:

Attaching methods to globalThis
<script type="module">
  /* Using `type="module"`, Blazor JS cannot see this function definition */
  async function export_method(...rows) {
    /* display the array of objects */
    console.log(rows);
  }

  /* Once attached to `globalThis`, Blazor JS can call this method */
  globalThis.export_method = export_method;
</script>

Blazor Callbacks

Methods are commonly bound to buttons in the Razor template using @onclick. When the following button is clicked, Blazor will invoke ExportDataset:

Binding callback to a HTML button
<button @onclick="ExportDataset">Export Dataset</button>

State in Blazor

The example presidents sheet has one header row with "Name" and "Index" columns.

pres.xlsx data

C# Representation

The natural C# representation of a single row is a class object:

President class
public class President {
  public string Name { get; set; }
  public int Index { get; set; }
}

var PrezClinton = new President() { Name = "Bill Clinton", Index = 42 };

The entire dataset is typically stored in an array of class objects:

President dataset
private President[] data;

Data Interchange

InvokeVoidAsync can pass data from the C# state to a JS function:

    await JS.InvokeVoidAsync("export_method", data);

Each row in the dataset will be passed as a separate argument to the JavaScript method, so the JavaScript code should collect the arguments:

Collecting rows in a JS callback
/* NOTE: blazor spreads the C# array, so the ... spread syntax is required */
async function export_method(...rows) {
  /* display the array of objects */
  console.log(rows);
}

Each row is a simple JavaScript object.

Blazor automatically spreads arrays. Each row is passed as a separate argument to the JavaScript method.

The example method uses the JavaScript spread syntax to collect the arguments.

Exporting Data

With the collected array of objects, the SheetJS json_to_sheet method⁴ will generate a SheetJS worksheet⁵ from the dataset. After creating a workbook⁶ object with the book_new method⁷, the file is written with writeFile²:

JS Callback for exporting datasets
/* NOTE: blazor spreads the C# array, so the spread is required */
async function export_method(...rows) {
  const XLSX = await import("https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs");
  const ws = XLSX.utils.json_to_sheet(rows);
  const wb = XLSX.utils.book_new(ws, "Data");
  XLSX.writeFile(wb, "SheetJSBlazor.xlsx");
}

HTML Tables

When displaying datasets, Razor components typically generate HTML tables:

Razor template from official starter
<table class="table" id="weather-table">
  <thead>
    <tr><th>Date</th><th>Temp. (C)</th><th>Temp. (F)</th><th>Summary</th></tr>
  </thead>
  <tbody>
    @foreach (var forecast in forecasts)
    {
      <tr>
        <td>@forecast.Date.ToShortDateString()</td>
        <td>@forecast.TemperatureC</td>
        <td>@forecast.TemperatureF</td>
        <td>@forecast.Summary</td>
      </tr>
    }
  </tbody>
</table>

If it has an id, JS code on the frontend can find the table element using the document.getElementById DOM method. A SheetJS workbook object can be generated using the table_to_book method⁸ and exported with writeFile²:

JS Callback for exporting HTML TABLE elements
/* NOTE: blazor spreads the C# array, so the spread is required */
async function export_method() {
  const XLSX = await import("https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs");
  const wb = XLSX.utils.table_to_book(document.getElementById("weather-table"));
  XLSX.writeFile(wb, "SheetJSBlazor.xlsx");
}

This approach uses data that already exists in the document, so no additional data is passed from C# to JavaScript.

Complete Demo

The Blazor + WASM starter app includes a "Weather" component that displays data from a C#-managed dataset. This demo uses SheetJS to export data in two ways:

"Export Dataset" will send row objects from the underlying C# data store to the frontend. The SheetJS json_to_sheet method⁴ builds the worksheet.
"Export HTML Table" will scrape the table using the SheetJS table_to_book method⁸. No extra data will be sent to the frontend.

Tested Deployments

This demo was tested in the following deployments:

Architecture	Date
`darwin-arm`	2024-10-15

Platform Configuration

Set the DOTNET_CLI_TELEMETRY_OPTOUT environment variable to 1.

How to disable telemetry (click to hide)

Linux/MacOS
Windows

Add the following line to .profile, .bashrc and .zshrc:

(add to .profile , .bashrc , and .zshrc)
export DOTNET_CLI_TELEMETRY_OPTOUT=1

Close and restart the Terminal to load the changes.

Type env in the search bar and select "Edit the system environment variables".

In the new window, click the "Environment Variables..." button.

In the new window, look for the "System variables" section and click "New..."

Set the "Variable name" to DOTNET_CLI_TELEMETRY_OPTOUT and the value to 1.

Click "OK" in each window (3 windows) and restart your computer.

Install .NET

Installation Notes (click to show)

For macOS x64 and ARM64, install the dotnet-sdk Cask with Homebrew:

brew install --cask dotnet-sdk

For Steam Deck Holo and other Arch Linux x64 distributions, the dotnet-sdk and dotnet-runtime packages should be installed using pacman:

sudo pacman -Syu dotnet-sdk dotnet-runtime

https://dotnet.microsoft.com/en-us/download/dotnet/6.0 is the official source for Windows and ARM64 Linux versions.

Open a new Terminal window in macOS or PowerShell window in Windows.

App Creation

Create a new blazorwasm app:

dotnet new blazorwasm -o SheetJSBlazorWasm
cd SheetJSBlazorWasm
dotnet run

When the Blazor service runs, the terminal will display a URL:

info: Microsoft.Hosting.Lifetime[14]
      Now listening on: http://localhost:6969

In a new browser window, open the displayed URL from Step 3.
Click the "Weather" link and confirm the page includes a data table.
Stop the server (press CTRL+C in the terminal window).

SheetJS Integration

Add the following script tag to wwwroot/index.html in the HEAD block:

wwwroot/index.html (add within the HEAD block)
<script>
/* NOTE: blazor spreads the C# array, so the spread is required */
async function export_dataset(...rows) {
  const XLSX = await import("https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs");
  const ws = XLSX.utils.json_to_sheet(rows);
  const wb = XLSX.utils.book_new(ws, "Data");
  XLSX.writeFile(wb, "SheetJSBlazorDataset.xlsx");
}

async function export_html(id) {
  const XLSX = await import("https://cdn.sheetjs.com/xlsx-0.20.3/package/xlsx.mjs");
  const wb = XLSX.utils.table_to_book(document.getElementById(id));
  XLSX.writeFile(wb, "SheetJSBlazorHTML.xlsx");
}
</script>

Inject the IJSRuntime dependency near the top of Pages/Weather.razor:

Pages/Weather.razor (add highlighted lines)
@page "/weather"
@inject HttpClient Http
@inject IJSRuntime JS

Add an ID to the TABLE element in Pages/Weather.razor:

Pages/Weather.razor (add id to TABLE element)
{
    <table class="table" id="weather-table">
        <thead>
            <tr>

Add callbacks to the @code section in Pages/Weather.razor:

Pages/Weather.razor (add within the @code section)
    private async Task ExportDataset()
    {
        await JS.InvokeVoidAsync("export_dataset", forecasts);
    }

    private async Task ExportHTML()
    {
        await JS.InvokeVoidAsync("export_html", "weather-table");
    }

Add Export buttons to the template in Pages/Weather.razor:

Pages/Weather.razor (add highlighted lines)
<p>This component demonstrates fetching data from the server.</p>

<button @onclick="ExportDataset">Export Dataset</button>
<button @onclick="ExportHTML">Export HTML TABLE</button>

Testing

Launch the dotnet process again:

dotnet run

When the Blazor service runs, the terminal will display a URL:

info: Microsoft.Hosting.Lifetime[14]
      Now listening on: http://localhost:6969

In a new browser window, open the displayed URL from Step 12.
Click the "Weather" link. The page should match the following screenshot:

SheetJSBlazorWasm with Exports

Click the "Export Dataset" button and save the generated file to SheetJSBlazorDataset.xlsx. Open the file in a spreadsheet editor and confirm the data matches the table. The column labels will differ since the underlying dataset uses different labels.

SheetJSBlazorDataset.xlsx

Click the "Export HTML TABLE" button and save the generated file to SheetJSBlazorHTML.xlsx. Open the file in a spreadsheet editor and confirm the data matches the table. The column labels will match the HTML table.

SheetJSBlazorHTML.xlsx

It is somewhat curious that the official dotnet Blazor sample dataset marks 1 C and -13 C as "freezing" but marks -2 C as "chilly". It stands to reason that -2 C should also be freezing.

See "Microsoft.JSInterop.IJSRuntime" in the dotnet documentation. ↩
See writeFile in "Writing Files" ↩ ↩² ↩³
See "Microsoft.JSInterop.JSRuntimeExtensions.InvokeVoidAsync" in the dotnet documentation. ↩
See json_to_sheet in "Utilities" ↩ ↩²
See "Sheet Objects" ↩
See "Workbook Object" ↩
See book_new in "Utilities" ↩
See table_to_book in "HTML" Utilities ↩ ↩²

Integration Details​

Installation​

Standalone Script​

ECMAScript Module​

Calling JS from C#​

Setup​

Fire and Forget​

Blazor Callbacks​

State in Blazor​

C# Representation​

Data Interchange​

Exporting Data​

HTML Tables​

Complete Demo​

Platform Configuration​

App Creation​

SheetJS Integration​

Testing​

Footnotes​