How to customize values in a Blazor Pivot Table based on specified conditions?

Introduction

When working with a Blazor pivot table, you might want to customize the displayed cell values based on certain conditions. This customization may be required for both the UI representation and the exported document, like an Excel file. This article will demonstrate how to achieve this using coding examples.

Customizing cell values in Blazor Pivot Table

To customize the values displayed in a Blazor pivot table, you need to use the CellTemplate option. This event allows you to define the content of cell elements according to your specific needs.

Here is a code snippet demonstrating how to use the CellTemplate option to replace certain numeric values (i.e., “0”, “1”, “-1”, “-2”) with the text “Custom text”:

[Index.razor]

<SfPivotView>
    <PivotViewTemplates>
        <CellTemplate>
            @{
                var data = (context as AxisSet);
                if (data != null)
                {
                    if (data.Axis == "value")
                    {
                        // Here we checked the formatted text of each cell.
                        if (data.FormattedText == "0" || data.FormattedText == "1" || data.FormattedText == "-1" || data.FormattedText == "-2" || data.FormattedText == "-3")
                        // Here, we changed the formatted text to "Custom text" if it met the condition.
                        Pivot.PivotValues[data.RowIndex][data.ColIndex].FormattedText = "Custom text";                  
                        @Pivot.PivotValues[data.RowIndex][data.ColIndex].FormattedText;
                    }
                    else
                    {
                        // Here, we displayed the original text if no condition is met.
                        @data.FormattedText
                    }
                }
            }
        </CellTemplate>
    </PivotViewTemplates>
</SfPivotView>

In the above code, we used the CellTemplate option to check if the cell belongs to the value axis and if its FormattedText matches one of the specified values (i.e., “0”, “1”, “-1”, “-2”). If so, we replace the text with “Custom text”. This customization appears directly in the displayed pivot table.

The following screenshot, which portrays difference between before and after customizing cell values in pivot table,

Screenshots

Before customizing cell values in pivot table,

After customizing cell values in pivot table,

Customizing cell values during excel exporting

Customizing cell values via the CellTemplate option only appears in the pivot table UI and does not take into account other user scenarios such as exporting. If you want to retain these customized values when exporting the pivot table to an Excel document, you will need to use the ExcelQueryCellInfo event. This event enables you to manipulate cell values during the Excel export process.

Here is a code snippet demonstrating how to use the ExcelQueryCellInfo event:

[Index.razor]

<SfPivotView>
	<PivotViewEvents TValue="ProductDetails" ExcelQueryCellInfo="ExcelQueryCellInfo"></PivotViewEvents>
</SfPivotView>

@code {
    public void ExcelQueryCellInfo(ExcelQueryCellInfoEventArgs<ProductDetails> args)
    {
        if (args.Value.ToString() == "0" || args.Value.ToString() == "1" || args.Value.ToString() == "-1" || args.Value.ToString() == "-2" || args.Value.ToString() == "-3")
        {
	    // Here, we customize the cell value to "Custom text" during export.
            args.Value = args.Cell.Value = "Custom text";
        }
    }
}

In the above example, we used the ExcelQueryCellInfo event to check a condition similar to the one implemented in the CellTemplate option. If it matches, we replace the text with “Custom text” using the args.Value property. These changes will be reflected when exporting the pivot table as an excel document.

The following screenshot, which portrays difference between before and after customizing cell values during excel exporting,

Screenshots

Before customizing cell values during excel exporting,

After customizing cell values during excel exporting.

View Sample in GitHub

Conclusion

I hope you enjoyed learning how to customize values in a Blazor Pivot Table based on specified conditions.

You can also refer to our Blazor Pivot Table feature tour feature tour page to learn about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our Pivot Table example to understand how to create and manipulate data.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!