BlazorDatasheet

BlazorDatasheet

A simple datasheet component for editing tabular data.

Features

• Data editing
  • Built in editors including text, date, select, boolean, text area, enum
  • Add custom editors for any data type
• Conditional formatting
• Data validation
• Formula
• Keyboard navigation
• Excel-like copy, cut and paste: formulas, formatting and merges within a workbook, rich HTML/text interop with Excel and Google Sheets
• Virtualization - handles many cells at once in both rows & cols.

Demo: https://anmcgrath.github.io/BlazorDatasheet/

Getting Started

Install the NuGet package

dotnet add package BlazorDatasheet

Configure Program.cs

In Program.cs, add the required services:

builder.Services.AddBlazorDatasheet();

Import JS/CSS

In _Layout.cshtml or index.html add

<link href="_content/BlazorDatasheet/sheet-styles.css" rel="stylesheet"/>

Blazor Datasheet provides a Datasheet Blazor component that accepts a Sheet.

A Sheet holds the data and configuration for a Datasheet. The data is set per Cell, or can be built using the ObjectEditorBuilder, which creates a datasheet based on a list of objects.

The following code displays an empty 3 x 3 data grid.

<Datasheet
    Sheet="sheet"/>

@code{

    private Sheet sheet;

    protected override void OnInitialized()
    {
        sheet = new Sheet(3, 3);
    }

}

The default editor is the text editor, but can be changed by defining the Type property of each cell.

Setting & getting cell values

Cell values can be set in a few ways:

sheet.Cells[0, 0].Value = "Test"
sheet.Range("A1").Value = "Test";
sheet.Cells.SetValue(0, 0, "Test");
sheet.Commands.ExecuteCommand(new SetCellValueCommand(0, 0, "Test"));

Cell values are stored internally inside a CellValue wrapper. Values are converted implicitly when set above and a CellValueType is assigned to the cell.

The CellValueType is used for formula evaluation and can be one of the following:

    Empty = 0,
    Error = 1,
    Array = 2,
    Unknown = 3,
    Sequence = 4,
    Reference = 5,
    Number = 6,
    Date = 7,
    Text = 8,
    Logical = 9,

This conversion can be controlled, for example when setting the cell type to “text”, values will always be stored as strings and no conversion will be made.

The conversion can additionally be modified by using the Sheet.Cells.BeforeCellValueConversion event. By changing the NewValue property of the argument, the value that is stored is modified.

Controlling selection input

Subscribe to Sheet.BeforeSelectionInput to cancel or adjust mouse and keyboard selection before it changes the selection or drag preview. For example, prevent selection from including a restricted area:

using System.Linq;
using BlazorDatasheet.DataStructures.Geometry;

var restricted = new Region(2, 4, 3, 5); // Zero-based rows 2–4 and columns 3–5.
sheet.BeforeSelectionInput += (_, e) =>
{
    e.Cancel = e.ProposedRegions.Any(region => region.Intersects(restricted));
};

Alternatively, clamp every proposed region to an allowed rectangle:

var allowed = new Region(0, 9, 0, 4); 
sheet.BeforeSelectionInput += (_, e) =>
{
    if (e.ProposedRegions.Count == 0)
        return;

    var regions = e.ProposedRegions.Select(region => region.GetIntersection(allowed)).ToArray();
    if (regions.Any(region => region == null))
    {
        e.Cancel = true;
        return;
    }

    e.ProposedRegions = regions.Select(region => region!).ToArray();
    e.ProposedActiveCellPosition = e.ProposedRegions[e.ProposedActiveRegionIndex]
        .GetConstrained(e.ProposedActiveCellPosition);
};

Formula

Formula can be applied to cells. When the cells or ranges that the formula cells reference change, the cell value is re-calculated.

sheet.Cells[0, 0].Formula = "=10+A2"

Custom functions

Functions are added with FormulaOptions.ConfigureFunctions. The descriptions are optional, and are shown in the function suggestions and the hint box of the formula editor.

var sheet = new Sheet(10, 10, formulaOptions: new FormulaOptions
{
    ConfigureFunctions = builder => builder.Add(new FunctionDescriptor(
        name: "DOUBLE",
        parameterDefinitions:
        [
            new ParameterDefinition("number", ParameterType.Number, description: "The number to double.")
        ],
        invoker: (args, _) => CellValue.Number(args[0].GetValue<double>() * 2),
        description: "Returns a number multiplied by two."))
});

Formatting

Cell formats can be set in the following ways:

sheet.Range("A1:A2").Format = new CellFormat() { BackgroundColor = "red" };
sheet.Commands.ExecuteCommand(
    new SetFormatCommand(new RowRegion(10, 12), new CellFormat() { ForegroundColor = "blue" }));
sheet.SetFormat(sheet.Range(new ColumnRegion(5)), new CellFormat() { FontWeight = "bold" });
sheet.Cells[0, 0].Format = new CellFormat() { TextAlign = "center" };

When a cell format is set, it will be merged into any existing cell formats in the region that it is applied to. Any non-null format paremeters will be merged:

sheet.Range("A1").Format = new CellFormat() { BackgroundColor = "red" };
sheet.Range("A1:A2").Format = new CellFormat() { ForegroundColor = "blue" };
var format = sheet.Cells[0, 0].Format; // backroundColor = "red", foreground = "blue"
var format2 = sheet.Cells[1, 0].Format; // foreground = "blue"

Numbers that do not fit their column

A number that is too wide for its column is shown according to the datasheet’s NumberOverflow parameter:

<Datasheet Sheet="sheet" NumberOverflow="NumberOverflowMode.RoundToFit" />

Mode	Shown
RoundToFit (default)	a General number is rounded to fewer decimal places until it fits, and becomes ####### when no rounding does.
Hashes	the number is shown whole, or as ####### when it does not fit.
Clip	the number is clipped by the cell, as text is.

RoundToFit renders as nested elements to let the browser handle the hashing. This may cause a performance hit for large numeric grids.0

In this case, setting a number format, reducing the default rounding, or NumberOverflow="NumberOverflowMode.Clip" can help improve performance.

Cell types

The cell type specifies which renderer and editor are used for the cell. Cell types also help with explicit conversions when cell values are set.

sheet.Range("A1:B5").Type = "boolean"; // renders a checkbox

Custom editors and renderers can be defined. See the examples for more information.

Validation

Data validation can be set on cells/ranges. There are two modes of validation: strict and non-strict. When a validator is strict, the cell value will not be set by the editor if it fails validation.

If validation is not strict, the value can be set during editing but will show a validation error when rendered.

Although a strict validation may be set on a cell, the value can be changed programmatically, but it will display as a validation error.

sheet.Validators.Add(new ColumnRegion(0), new NumberValidator(isStrict: true));

Regions and ranges

A region is a geometric construct, for example:

var region = new Region(0, 5, 0, 5); // r0 to r5, c0 to c5
var cellRegion = new Region(0, 0); // cell A1
var colRegion = new ColumnRegion(0, 4); // col region spanning A to D
var rowRegion = new RowRegion(0, 3); // row region spanning 1 to 4

A range is a of region that also knows about the sheet. Ranges can be used to set certain parts of the sheet.

var range = sheet.Range("A1:C5");
var range = sheet.Range(new ColumnRegion(0));
var range = sheet.Range(0, 0, 4, 5);

Support

• Buy me a coffee

Community Links

• Using a Blazor Excel Datasheet To Update A Database
• Using OpenAI to Update A Blazor Excel Worksheet
• Microsoft Fabric and the Blazor Data Sheet