Managing Cell Ranges in Blazor Spreadsheet component

17 Dec 20258 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

To know more about cell formatting, refer here.

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.

Autofill options

Autofill supports multiple behaviors that control how adjacent cells are populated when using the fill handle. The available options are:

  • Copy Cells
  • Fill Series
  • Fill Formatting Only
  • Fill Without Formatting

Copy Cells

Copies the source cell content and formatting to the selected destination range. After dragging the fill handle from the selection to the target area, choose Copy Cells from the AutoFillOption menu to replicate both values and presentation. When the source contains formulas, relative references are adjusted to match the destination.

Fill Series

Extends a recognizable pattern—such as numbers (1, 2, 3), days or months (Mon, Tue; Jan, Feb), or dates—into the destination range while preserving the source formatting. Drag the fill handle to the target cells and choose Fill Series in the AutoFillOptions menu to continue the detected sequence.

Fill Formatting Only

Applies only the source styling—number format, font, fill color, borders, and alignment—to the destination range, leaving existing values unchanged. Drag the fill handle over the target cells and select Fill Formatting Only from the AutoFillOptions menu to unify appearance without altering data.

Fill Without Formatting

Continues the detected series into the destination range but retains the destination’s existing formatting. After dragging the fill handle, choose Fill Without Formatting from the AutoFillOptions menu to apply only the new values while keeping the target style intact.

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

Autofill Illustration

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

Parameter Type Description
fillRange string Specifies the fill range in A1 notation (e.g., “A1:A5”).
dataRange string Specifies the source data range in A1 notation (e.g., “B1:B5”).
direction string Specifies the direction to be filled (“Up”, “Right”, “Down”, or “Left”).

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");
    }
}

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:

Event Arguments Description
FillRange (read-only) The address of the target range for the autofill operation (e.g., “Sheet1!A2:A5”).
DataRange (read-only) The source data range for the autofill operation (e.g., “Sheet1!A1:A1”).
Direction (read-only) The direction of the autofill operation (“Down”, “Right”, “Up”, or “Left”).
Cancel A boolean value to cancel the autofill operation. 
@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:

Event Arguments Description
FillRange (read-only) The address of the target range where the autofill was applied (e.g., “Sheet1!A2:A5”).
DataRange (read-only) The source data range used for the autofill operation (e.g., “Sheet1!A1:A1”).
Direction (read-only) The direction of the autofill operation (“Down”, “Right”, “Up”, or “Left”). 
@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.

Option Use
Clear All Clears all contents, formats, and hyperlinks.
Clear Formats Clears only the formatting from the selected cells.
Clear Contents Clears only the contents (formulas and data) from the selected cells.
Clear Hyperlinks Clears only the hyperlinks from the selected cells.

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

Clear options in the Blazor Spreadsheet