Search results

Conditional Formatting in ASP.NET Core Pivot Grid control

14 Oct 2021 / 1 minute to read

Conditional formatting allows users to change the appearance of pivot grid value cells with its background color, font color, font family, and font size based on specific conditions. To achieve this, set the allowConditionalFormatting to true and use the conditionalFormatSettings object in the pivot grid widget along with the following properties.

  • measure: Specifies the value field name for which style will be applied.
  • condition: Specifies the operator type such as equals, greater than, less than, etc.
  • value1: Specifies the start value.
  • value2: Specifies the end value.
  • style: Specifies the style for the cell.
tagHelper
Formatting.cs
Copied to clipboard
<ejs-pivotview id="PivotGrid"  width="100%" height="300" allowConditionalFormatting="true" showFieldList="true">
                <e-datasource data="@ViewBag.data" expandAll="false" enableSorting="true">
                    <e-formatsettings>
                        <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
                    </e-formatsettings>
                    <e-drilledmembers>
                        <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
                    </e-drilledmembers>
                    <e-rows>
                        <e-field name="Country"></e-field>
                        <e-field name="Products"></e-field>
                    </e-rows>
                    <e-columns>
                        <e-field name="Year"></e-field>
                        <e-field name="Order_Source" caption="Order Source"></e-field>
                    </e-columns>
                    <e-values>
                        <e-field name="In_Stock" caption="In Stock"></e-field>
                        <e-field name="Sold" caption="Units Sold"></e-field>
                    </e-values>
                    <e-filters>
                        <e-field name="Product_Categories" caption="Product Categories"></e-field>
                    </e-filters>
                    <e-conditionalformatsettings>
                        <e-format measure="In Stock" conditions="LessThan" value1="5000">
                            <e-fieldlist-style backgroundColor="#80cbc4" color="black" fontFamily="Tahoma" fontSize="12px"/>
                        </e-format>
                        <e-format measure="Units Sold" conditions="Between" value1="3400" value2="40000">
                            <e-fieldlist-style backgroundColor="#f48fb1" color="black" fontFamily="Tahoma" fontSize="12px" />
                        </e-format>
                    </e-conditionalformatsettings>
                </e-datasource>
                <e-gridSettings columnWidth="100"></e-gridSettings>
</ejs-pivotview>
Copied to clipboard
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

Meanwhile, you can also achieve this in UI by invoking showConditionalFormattingDialog method on an external button click which is shown in the below code sample.

tagHelper
Formatting.cs
Copied to clipboard
<ejs-button id="conditional-formatting-btn" content="Apply Formatting" isPrimary="true"></ejs-button>
<ejs-pivotview id="PivotGrid"  width="100%" height="300" allowConditionalFormatting="true" showFieldList="true">
                <e-datasource data="@ViewBag.data" expandAll="false" enableSorting="true">
                    <e-formatsettings>
                        <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
                    </e-formatsettings>
                    <e-drilledmembers>
                        <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
                    </e-drilledmembers>
                    <e-rows>
                        <e-field name="Country"></e-field>
                        <e-field name="Products"></e-field>
                    </e-rows>
                    <e-columns>
                        <e-field name="Year"></e-field>
                        <e-field name="Order_Source" caption="Order Source"></e-field>
                    </e-columns>
                    <e-values>
                        <e-field name="In_Stock" caption="In Stock"></e-field>
                        <e-field name="Sold" caption="Units Sold"></e-field>
                    </e-values>
                    <e-filters>
                        <e-field name="Product_Categories" caption="Product Categories"></e-field>
                    </e-filters>
                    <e-conditionalformatsettings>
                        <e-format measure="In Stock" conditions="LessThan" value1="5000">
                            <e-fieldlist-style backgroundColor="#80cbc4" color="black" fontFamily="Tahoma" fontSize="12px"/>
                        </e-format>
                        <e-format measure="Units Sold" conditions="Between" value1="3400" value2="40000">
                            <e-fieldlist-style backgroundColor="#f48fb1" color="black" fontFamily="Tahoma" fontSize="12px" />
                        </e-format>
                    </e-conditionalformatsettings>
                </e-datasource>
                <e-gridSettings columnWidth="100"></e-gridSettings>
</ejs-pivotview>

<script>
    document.getElementById("conditional-formatting-btn").addEventListener('click', function () {
        var pivotGridObj = document.getElementById("PivotGrid").ej2_instances[0];
        pivotGridObj.conditionalFormattingModule.showConditionalFormattingDialog();
    });
</script>
Copied to clipboard
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

See Also