Managing Cell Ranges in Blazor Spreadsheet component

17 Nov 20259 minutes to read

A cell range is a set of selected cells in a Spreadsheet, typically specified using A1 notation (for example, A1:B10). A range may be a single cell or a contiguous block of cells that can be manipulated or processed collectively.

Cell formatting

Cell formatting enhances the visual presentation of data by applying styles such as font changes, colors, borders, and alignment to individual cells or cell ranges. This helps organize content and emphasize important information for faster interpretation.

Cell formatting options include:

• Bold - Applies a heavier font weight to make the text stand out in the Spreadsheet.

• Italic - Slants the text to give it a distinct look, often used for emphasis or to highlight differences.

• Underline - Adds a line below the text, commonly used for emphasis or to indicate hyperlinks.

• Strikethrough - Draws a line through the text, often used to show completed tasks or outdated information.

• Font Family - Changes the typeface of the text (e.g., Arial, Calibri, Times New Roman, and more) to enhance readability or visual appeal.

• Font Size - Adjusts the size of the text to create visual hierarchy or improve readability in the Spreadsheet.

• Font Color - Changes the color of the text to improve visual hierarchy or to organize information using color codes.

• Fill Color - Adds color to the cell background to visually organize data or highlight important information.

• Horizontal Alignment - Controls the position of text from left to right within a cell. Options include:
  • Left - Default for text
  • Center - Useful for headings
  • Right - Default for numbers
• Vertical Alignment - Controls the position of text from top to bottom within a cell. Options include:
  • Top – Aligns content to the top of the cell
  • Middle – Centers content vertically
  • Bottom – Default alignment
• Wrap Text - Displays long content on multiple lines within a single cell, preventing it from overflowing into adjacent cells. To enable text wrapping:
  1. Select the target cell or range (e.g., C5).
  2. Go to the Home tab.
  3. Click Wrap Text in the ribbon to toggle text wrapping for the selected cells.

Cell formatting can be applied or removed from a cell or range by using the options available in the component’s built-in Ribbon under the Home tab.

Autofill

Autofill is used to fill cells with data that follows a pattern or is based on data in other cells. It helps avoid entering repetitive data manually. The AllowAutofill property can be used to enable or disable this feature.

• The default value of the AllowAutofill property is true.

Autofill can be performed in one of the following ways:

• Drag and drop the cell using the fill handle element.
• Use the AutofillAsync() method programmatically.

The AutofillAsync() method accepts string parameters in A1 notation for fillRange and dataRange. The available parameters are:

Implementing autofill programmatically

@using Syncfusion.Blazor.Spreadsheet <button @onclick="AutofillRangeHandler">Autofill</button> <SfSpreadsheet @ref="spreadsheetObj" DataSource="DataSourceBytes"> <SpreadsheetRibbon></SpreadsheetRibbon> </SfSpreadsheet> @code { public byte[] DataSourceBytes { get; set; } public SfSpreadsheet spreadsheetObj; protected override void OnInitialized() { string filePath = "wwwroot/Sample.xlsx"; DataSourceBytes = File.ReadAllBytes(filePath); } public async Task AutofillRangeHandler() { // Basic usage with only the fill range parameter. await spreadsheetObj.AutofillAsync("B7:B8"); } }

The following illustration demonstrates the use of autofill in the Spreadsheet component.

Events

The Blazor Spreadsheet provides events that are triggered during autofill operations, such as AutofillActionBegin and AutofillActionEnd. These events enable the execution of custom actions before and after an autofill operation, allowing for validation, customization, and response handling.

AutofillActionBegin

The AutofillActionBegin event is triggered before an autofill operation is performed. This event provides an opportunity to validate the autofill operation and apply restrictions based on custom logic, such as preventing the operation under specific conditions.

Purpose

This event is useful for scenarios where autofill behavior needs to be controlled dynamically—such as restricting autofill in specific ranges or preventing autofill based on certain conditions.

Event Arguments

The event uses the AutofillActionBeginEventArgs class, which includes the following properties:

@using Syncfusion.Blazor.Spreadsheet <SfSpreadsheet DataSource="DataSourceBytes" AutofillActionBegin="OnAutofillActionBegin"> <SpreadsheetRibbon></SpreadsheetRibbon> </SfSpreadsheet> @code { public byte[] DataSourceBytes { get; set; } protected override void OnInitialized() { string filePath = "wwwroot/Sample.xlsx"; DataSourceBytes = File.ReadAllBytes(filePath); } public void OnAutofillActionBegin(AutofillActionBeginEventArgs args) { // Prevent autofill for a specific range. if (args.FillRange == "A1:A10") { args.Cancel = true; } // Prevent autofill when dragging upward. if (args.Direction == "Up") { args.Cancel = true; } } }

AutofillActionEnd

The AutofillActionEnd event is triggered after an autofill operation has been successfully completed. This event provides detailed information about the completed autofill action, enabling further processing or logging if required.

Purpose

This event is useful for scenarios where post-autofill actions are needed, such as logging the autofill operation, updating related data, or triggering additional UI updates.

Event Arguments

The event uses the AutofillActionEndEventArgs class, which includes the following properties:

@using Syncfusion.Blazor.Spreadsheet <SfSpreadsheet DataSource="DataSourceBytes" AutofillActionEnd="OnAutofillActionEnd"> <SpreadsheetRibbon></SpreadsheetRibbon> </SfSpreadsheet> @code { public byte[] DataSourceBytes { get; set; } protected override void OnInitialized() { string filePath = "wwwroot/Sample.xlsx"; DataSourceBytes = File.ReadAllBytes(filePath); } public void OnAutofillActionEnd(AutofillActionEndEventArgs args) { // Log or process the completed autofill operation. Console.WriteLine($"Autofill completed for range: {args.FillRange}"); } }

Clear

The clear functionality helps remove cell contents (formulas and data), formats (including number formats), and hyperlinks from a selected range.

Applying the clear functionality

The clear support can be applied using the following way:

You can apply the clear functionality by selecting the Clear icon in the Ribbon under the Home tab.

The following image displays the clear options available in the Ribbon toolbar under the Home tab of the Blazor Spreadsheet.