TableSheet Rules

TableSheet supports different data rules to help emphasize and visualize variations in the data as well as control the type of data entered in a TableSheet.

Conditional Formatting and Data Validation

TableSheet supports data-bound conditional formatting and data validation in the TableSheet view.

You can specify the conditionalFormat and validator properties when defining the view column info. These properties accept different types of rule option arrays which are listed in the table below.

The following conditional format and data validation types are supported in the TableSheet.

Property	Types
Conditional Format	`AverageRule` `CellValueRule` `DataBarRule` `DateOccuringRule` `DuplicateRule` `FormulaRule` `IconSetRule` `SpecificTextRule` `ThreeScaleRule` `Top10Rule` `TwoScaleRule` `UniqueRule`
Data Validator	`DateValidator` `FormulaListValidator` `FormulaValidator` `ListValidator` `NumberValidator` `TextLengthValidator` `TimeValidator`

Property

Types

Conditional Format

AverageRule

CellValueRule

DataBarRule

DateOccuringRule

DuplicateRule

FormulaRule

IconSetRule

SpecificTextRule

ThreeScaleRule

Top10Rule

TwoScaleRule

UniqueRule

Data Validator

DateValidator

FormulaListValidator

FormulaValidator

ListValidator

NumberValidator

TextLengthValidator

TimeValidator

The following code sample shows how to apply conditional formatting and data validation in a TableSheet.

// formula to apply Conditional Formatting
var formulaRule = {
    ruleType: "formulaRule",
    formula: "@<10",
    style: {
        backColor: "green",
        foreColor: "lime"
    }
};
// create a data validator
var positiveNumberValidator = {
    type: "formula",
    formula: '@>0',
    inputTitle: 'Data validation:',
    inputMessage: 'UnitPrice cannot be less than 0.',
    highlightStyle: {
        type: 'icon',
        color: "red",
        position: 'outsideRight',
    }
};

//bind a view to the tablesheet
myTable.fetch().then(function () {
    var view = myTable.addView("myView", [

        { value: "stockItem", width: 300, caption: "Stock Item" },
        { value: "unitPrice", width: 120, caption: "Unit Price", conditionalFormats: [formulaRule], validator: positiveNumberValidator }, // apply conditional formating and data validation here
        { value: "taxRate", width: 120, caption: "Tax Rate" },
        { value: "=([@unitPrice] * [@taxRate])/100 + [@unitPrice]", caption: "Total Price", width: 150 }
    ]);
    sheet.setDataView(view);
});

Style Rules

Style Rules are styles that are applied when a condition is met. You can add a state rule and formula rule using the addStyleRule method from GC.Data.View class or you can define viewOptions parameter of the addView method that also enables you to add a view to the Data Manager table using the GC.Data.RowColumnStates enumerations. You can also choose viewport or colHeader as view area to implement the style rule.

State Rule – Apply style when a row or column state is triggered. For example, if you want to highlight a row with a gray back color whenever the mouse hovers over it, or highlight the selected column as illustrated in the image below:
Formula Rule – Apply style to specific values that meet certain conditions. For example, if you want to highlight the lower price of a product, set formulas like "[@UnitPrice]<10" with a different fore color as shown in the image below:

The following code sample shows how to apply both the state rule and formula rule in a TableSheet.

            // Setting formula rule
            var formulaRule = {
                ruleType: "formulaRule",
                formula: "@<=10",
                style: {
                    borderLeft: { color: "orange", style: "medium" },
                    borderRight: { color: "orange", style: "medium" },
                    borderTop: { color: "orange", style: "medium" },
                    borderBottom: { color: "orange", style: "medium" }
                }
            };
            //bind a view to the tablesheet 
            var myView = productTable.addView("myView", [
                { value: "Id", caption: "ID" },
                { value: "ProductName", caption: "Name", width: 400 },
                { value: "ReorderLevel", caption: "ReorderLevel", width: 120 },
                { value: "UnitPrice", caption: "Unit Price", width: 100, readonly: true, conditionalFormats: [formulaRule] },
                { value: "UnitsInStock", caption: "Units In Stock", width: 100 },
                { value: "UnitsOnOrder", caption: "Units On Order", width: 100 }
            ]);
            // Adding rules through addStyleRule method 
            myView.addStyleRule("lowPrice", { backColor: "green", foreColor: "white" }, {
                formula: "[@UnitPrice] <= 10"
            });
            myView.addStyleRule("HoverStyle", { backColor: "gray", foreColor: "white" }, {
                area: GC.Data.ViewArea.colHeader,
                direction: GC.Data.StateRuleDirection.row,
                state: GC.Data.RowColumnStates.hover
            });
            myView.addStyleRule("SelectedStyle", { backColor: "yellow", foreColor: "red" }, {
                area: GC.Data.ViewArea.colHeader,
                direction: GC.Data.StateRuleDirection.column,
                state: GC.Data.RowColumnStates.selected
            })
            myView.fetch().then(function () {
                sheet.setDataView(myView);
            });

Types of States

The following table lists all the available row and column state enumeration options in GC.Data.RowColumnStates.

Enumeration Values	Description
active	When the row or the column is in focus, its state includes the "active" state.
dirty	When cell value is changed, cell row and column state include "dirty" state.
hover	When mouse hovers on the row and the column, its state includes "hover" state.
inserted	When inserted a row, its state includes "inserted" state. This state is only supported for row.
pin	When pin a row/column, its state includes "pin" state.
primaryKey	When the column has primary key, its state includes "primaryKey" state. This state is only supported for column.
readonly	When the column is locked, its state includes "readonly" state. This state only support for column.
required	When the value of the column is required, its state includes "required" state. This state is only supported for column.
selected	When the cell is in the selection range, the cell row and column state include the "selected" state.
updated	When updated a row, its state includes "updated" state. This state is only supported for row.

Using SpreadJS, you can also update or remove the default rules.

The following code sample shows how to update the readonly state icon.

            // update the icon of the readonly state
            view.addStyleRule("readonly", {
                decoration: {
                    icons: [
                        {
                            src: 'readonly.png'
                        }
                    ]
                }
            }, {
                area: GC.Data.ViewArea.colHeader,
                direction: GC.Data.StateRuleDirection.column,
                state: GC.Data.RowColumnStates.readonly
            });

The following code sample shows how to remove the style rule readonly icon.

 // Remove the readonly state icon 
 view.removeStyleRule("readonly");

The following image shows how "Is Primary Key", "Read Only" and "IsRequired" icons appear on the column header of the TableSheet.

tablesheetrule-code

The following image shows how the tablesheet looks after implementing the removeStyleRule method on readonly icon.

after-removerule

Using Designer

You can assign the pre-defined rules to the column headers using the Column Setting tab of the SpreadJS Designer.

set-rule

The following image shows how rules appear on the column header of the TableSheet.

tablesheet-rule (1)

After applying rules, you can style the rules in a TableSheet using the Table Sheet Design Tab of the SpreadJS Designer.

style-rules

Steps to style the rules are as follows:

Click on the State Rules option from the Table Sheet Design tab to open the Manage State Rules dialog box.
Choose a suitable option to apply rules on the column header.
To style the selected column header, you can either create a new rule using the New Rule button or you can edit the existing rules and assign it to the column header.
To edit the mentioned rules, use the icon from the Style column that opens the Format Cells dialog box.
To remove the icon from the selected column header, use the Remove Rule button.