Conditional formatting in React Pivotview component
8 Aug 202324 minutes to read
Allows end user to change the appearance of the pivot table value cells with its background color, font color, font family, and font size based on specific conditions.
The conditional formatting can be applied at runtime through the built-in dialog, invoked from the toolbar. To do so, set allowConditionalFormatting
and showToolbar
properties to true. Also, include the item ConditionalFormatting within the toolbar
property. End user can now see the “Conditional Formatting” icon in toolbar UI automatically, which on clicking will invoke the formatting dialog to perform necessary operations.
To use the conditional formatting feature, You need to inject the
ConditionalFormatting
module in pivot table.
To learn about how to work with conditional formatting options, you can check on this video for React Grid.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { PivotViewComponent, Inject, Toolbar, ConditionalFormatting } from '@syncfusion/ej2-react-pivotview';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
let toolbarOptions = ['ConditionalFormatting'];
return (<PivotViewComponent id='PivotView' ref={scope => pivotObj = scope} dataSourceSettings={dataSourceSettings} width={'100%'} height={350} gridSettings={{ columnWidth: 140 }} allowConditionalFormatting={true} showToolbar={true} toolbar={toolbarOptions}><Inject services={[Toolbar, ConditionalFormatting]}/></PivotViewComponent>);
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import {
PivotViewComponent, IDataOptions, Inject, Toolbar, ConditionalFormatting
} from '@syncfusion/ej2-react-pivotview';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj: PivotViewComponent;
let toolbarOptions: any = ['ConditionalFormatting'];
return (<PivotViewComponent id='PivotView' ref={scope => pivotObj = scope} dataSourceSettings={dataSourceSettings} width={'100%'} height={350} gridSettings={{ columnWidth: 140 }} allowConditionalFormatting={true} showToolbar={true} toolbar={toolbarOptions}><Inject services={[Toolbar, ConditionalFormatting]} /></PivotViewComponent>);
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Conditional formatting can also be included in the pivot table through code-behind using the conditionalFormatSettings
. The required properties to apply a new conditional formatting are,
-
measure
: Specifies the value field name for which style will be applied. -
conditions
: 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.
The available style properties in style
, to set in value cells are:
-
backgroundColor
: Specifies the background color. -
color
: Specifies the font color. -
fontFamily
: Specifies the font family. -
fontSize
: Specifies the font size.
Meanwhile, user can also view conditional formatting dialog in UI by invoking showConditionalFormattingDialog
method on an external button click which is shown in the below code sample.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { ConditionalFormatting, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
measure: 'In_Stock',
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
},
{
value1: 3400,
value2: 40000,
measure: 'Sold',
conditions: 'Between',
style: {
backgroundColor: '#f48fb1',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
};
let pivotObj;
return (<div><div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Apply Formatting</ButtonComponent></div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
function btnClick() {
pivotObj.conditionalFormattingModule.showConditionalFormattingDialog();
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { ConditionalFormatting, IDataOptions, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
measure: 'In_Stock',
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
},
{
value1: 3400,
value2: 40000,
measure: 'Sold',
conditions: 'Between',
style: {
backgroundColor: '#f48fb1',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
}
let pivotObj: PivotViewComponent;
return (<div><div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Apply Formatting</ButtonComponent></div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
function btnClick(): void {
pivotObj.conditionalFormattingModule.showConditionalFormattingDialog();
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Conditional formatting for all fields
Allows end user to apply conditional formatting commonly for all value fields just by ignoring the measure
property and setting rest of the properties in conditionalFormatSettings
.
import { ConditionalFormatting, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
},
{
value1: 3400,
value2: 40000,
conditions: 'Between',
style: {
backgroundColor: '#f48fb1',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ConditionalFormatting, IDataOptions, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
},
{
value1: 3400,
value2: 40000,
conditions: 'Between',
style: {
backgroundColor: '#f48fb1',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Conditional formatting for specific value field
Allows end user to apply conditional formatting to a specific value field by setting the measure
property with specific value field name in conditionalFormatSettings
.
import { ConditionalFormatting, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
measure: 'In_Stock',
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
},
{
value1: 3400,
value2: 40000,
measure: 'Sold',
conditions: 'Between',
style: {
backgroundColor: '#f48fb1',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { ConditionalFormatting, IDataOptions, IDataSet, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
measure: 'In_Stock',
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
},
{
value1: 3400,
value2: 40000,
measure: 'Sold',
conditions: 'Between',
style: {
backgroundColor: '#f48fb1',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Editing and removing existing conditional format
Editing and removing existing conditional format can be done through the UI at runtime. To do so, open the conditional formatting dialog and edit the “Value”, “Condition” and “Format” options based on user requirement and click “OK”. To remove a conditional format, click the “Delete” icon besides the respective condition.
Event
ConditionalFormatting
The event conditionalFormatting
is triggered initially while clicking the “ADD CONDITION” button inside the conditional formatting dialog in-order to fill user specific condition instead of default condition at runtime. To use this event, allowConditionalFormatting
property in PivotView must be set to true. It has following parameters -
-
applyGrandTotals
- boolean property, by setting this to true user can enable formatting to grand totals. -
conditions
- condition to be filled in conditional formatting dialog. -
label
- Label value for conditional formatting dialog. -
measure
- measure value for the conditional formatting dialog. -
style
- style property of the conditional formatting dialog. -
value1
- value 1 for conditional formatting dialog. -
value2
- value 2 for conditional formatting dialog, this is applicable only for selected conditions like Between and NotBetween.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { ConditionalFormatting, Inject, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
measure: 'In_Stock',
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
};
let pivotObj;
return (<div><div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Apply Formatting</ButtonComponent></div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true} conditionalFormatting={conditionalFormatting.bind(this)}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
function btnClick() {
pivotObj.conditionalFormattingModule.showConditionalFormattingDialog();
}
function conditionalFormatting(args) {
args.skipFormatting = true;
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { ConditionalFormatting, IDataOptions, IDataSet, Inject, PivotViewComponent, IConditionalFormatSettings } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
dataSource: pivotData,
expandAll: false,
enableSorting: true,
drilledMembers: [{ name: 'Country', items: ['France', 'Germany'] }],
columns: [{ name: 'Year' }, { name: 'Order_Source', caption: 'Order Source' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'In_Stock', caption: 'In Stock' },
{ name: 'Sold', caption: 'Units Sold' }],
filters: [{ name: 'Product_Categories', caption: 'Product Categories' }],
conditionalFormatSettings: [
{
measure: 'In_Stock',
value1: 5000,
conditions: 'LessThan',
style: {
backgroundColor: '#80cbc4',
color: 'black',
fontFamily: 'Tahoma',
fontSize: '12px'
}
}
]
}
let pivotObj: PivotViewComponent;
return (<div><div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Apply Formatting</ButtonComponent></div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true} conditionalFormatting={conditionalFormatting.bind(this)}><Inject services={[ConditionalFormatting]}/> </PivotViewComponent></div>
</div>);
function btnClick(): void {
pivotObj.conditionalFormattingModule.showConditionalFormattingDialog();
}
function conditionalFormatting(args: IConditionalFormatSettings): void {
args.skipFormatting = true;
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));