SpreadJS 15
Features / TableSheet / TableSheet Rules
In This Topic
    TableSheet Rules
    In This Topic

    TableSheet supports different data rules to help emphasize and visualize variations in 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 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

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

    JavaScript
    Copy Code
    // 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 table sheet
    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 GC.Data.View class method addStyleRule or using the addView method parameter viewOptions when adding a view to the Data Manager table.

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

    JavaScript
    Copy Code
    // 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 table sheet
    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" }, {
        direction: GC.Data.StateRuleDirection.row,
        state: GC.Data.RowColumnStates.hover
    });
    
    myView.addStyleRule("SelectedStyle", { backColor: "yellow", foreColor: "red"}, {
        direction: GC.Data.StateRuleDirection.column,
        state: GC.Data.RowColumnStates.selected
    })
    
    myView.fetch().then(function () {
        sheet.setDataView(myView);
    });