Style Rules

The GC.Data.View supports applying style rules, which can be formula or state rules.

Formula Rule When you want a certain value of a row to be highlighted because it meets certain conditions, you can use style rules to achieve this requirement. For example, if you need to mark the lower price of the product, you could set a formula like "[@UnitPrice]<5" with a different fore color. You could use view.addStyleRule(name, formula, style, options) like: Or you could add the row formula rule in viewOptions(GC.Data.ViewOptions) at the initialization of adding a view to table, like: State Rule When you want to use row or column state with a style, you could also use style rule. For example, if you want to highlight the selected column with a gray back color:
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ var tablesheet; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; //init a data manager var baseApiUrl = getBaseApiUrl(); var dataManager = spread.dataManager(); //add product table var productTable = dataManager.addTable("productTable", { remote: { read: { url: baseApiUrl + "/Product" } } }); //init a table sheet var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet); sheet.options.alternatingRowOptions = null; tablesheet = sheet; sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); //add validator var dv1 = { type: 'list', source: '0,5,10', inputTitle: 'Please choose a number:', inputMessage: '0, 5, 10' }; //bind a view to the table sheet var myView = productTable.addView("myView", [ { value: "Id", caption: "ID" }, { value: "ProductName", caption: "Name", width: 200 }, { value: "ReorderLevel", caption: "Reorder Level", width: 120, validator: dv1 }, { value: "UnitPrice", caption: "Unit Price", width: 120, style:{formatter: "$ #,##0.00"} }, { value: "UnitsInStock", caption: "Units In Stock", width: 120 }, { value: "UnitsOnOrder", caption: "Units On Order", width: 140 }, { value: "=[@UnitsInStock] + [@UnitsOnOrder]", caption: "Total Units", width: 120 }, { value: "=[@UnitPrice] * ([@UnitsInStock] + [@UnitsOnOrder])", caption: "Stock Value", width: 120 , style:{formatter: "$ #,##0.00"}} ]); // Styles var hoverStyle = new GC.Spread.Sheets.Style(); hoverStyle.backColor = '#DDEDF5'; var readonlyStyle = new GC.Spread.Sheets.Style(); readonlyStyle.foreColor = '#777777'; var selectedStyle = new GC.Spread.Sheets.Style(); selectedStyle.backColor = '#DDDDDD'; var activeStyle = new GC.Spread.Sheets.Style(); activeStyle.backColor = '#98c0e5'; var dirtyStyle = new GC.Spread.Sheets.Style(); dirtyStyle.backColor = '#F5D9D9'; var insertedStyle = new GC.Spread.Sheets.Style(); insertedStyle.backColor = '#9FF1CD'; var updatedStyle = new GC.Spread.Sheets.Style(); updatedStyle.backColor = '#B7E0B7'; var formulaStyle = new GC.Spread.Sheets.Style(); formulaStyle.foreColor = 'red'; var styleDict = { "1": hoverStyle, "4": readonlyStyle, "16": selectedStyle, "32": activeStyle, "64": dirtyStyle, "128": insertedStyle, "256": updatedStyle } myView.addStyleRule("hover-row", hoverStyle, { state: GC.Data.RowColumnStates.hover, direction: GC.Data.StateRuleDirection.row }); myView.addStyleRule("hover-column", hoverStyle, { state: GC.Data.RowColumnStates.hover, direction: GC.Data.StateRuleDirection.column }); myView.addStyleRule("readonly-column", readonlyStyle, { state: GC.Data.RowColumnStates.readonly, direction: GC.Data.StateRuleDirection.column }); myView.addStyleRule("formula", formulaStyle, { formula: "AND([@ReorderLevel] > 20, [@ReorderLevel] < 30)" }); myView.fetch().then(function () { sheet.setDataView(myView); }); spread.resumePaint(); var statesTable = document.getElementById("states-table"); statesTable.addEventListener("click", function (e) { var target = e.target; if (target && target.tagName.toLowerCase() === "input") { var stateType = target.getAttribute("myState"); var state = GC.Data.RowColumnStates[stateType]; var directionType = target.getAttribute("myDirection"); var direction = GC.Data.StateRuleDirection[directionType]; var styleRuleName = stateType + "-" + directionType; if (target.checked) { myView.addStyleRule(styleRuleName, styleDict[state], { state: state, direction: direction }); } else { myView.removeStyleRule(styleRuleName); } sheet.setDataView(myView); } }); var formulaInput = document.getElementById("formula-input"); var formulaSetButton = document.getElementById("formula-set"); var formulaRemoveButton = document.getElementById("formula-remove"); formulaSetButton.addEventListener("click", function (e) { var formula = formulaInput.value; myView.removeStyleRule("formula"); myView.addStyleRule("formula", formulaStyle, { formula: formula }); sheet.setDataView(myView); }); formulaRemoveButton.addEventListener("click", function (e) { myView.removeStyleRule("formula"); sheet.setDataView(myView); }); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api'; }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <h3>State Rule</h3> <hr> <div class="option-row"><label>Select different options in the table below and see how they affect the TableSheet.</label> </div> <div class="option-row"> <table id="states-table"> <tr> <td></td> <td>Rows</td> <td>Columns</td> </tr> <tr> <td>Hover</td> <td><input type="checkbox" checked="checked" myState="hover" myDirection="row" /></td> <td><input type="checkbox" checked="checked" myState="hover" myDirection="column" /></td> </tr> <tr> <td>Readonly</td> <td><input type="checkbox" disabled="disabled" myState="readonly" myDirection="row" /></td> <td><input type="checkbox" checked="checked" myState="readonly" myDirection="column" /></td> </tr> <tr> <td>Active</td> <td><input type="checkbox" myState="active" myDirection="row" /></td> <td><input type="checkbox" myState="active" myDirection="column" /></td> </tr> <tr> <td>Selected</td> <td><input type="checkbox" myState="selected" myDirection="row" /></td> <td><input type="checkbox" myState="selected" myDirection="column" /></td> </tr> <tr> <td>Dirty</td> <td><input type="checkbox" myState="dirty" myDirection="row" /></td> <td><input type="checkbox" disabled="disabled" myState="dirty" myDirection="column" /></td> </tr> <tr> <td>Inserted</td> <td><input type="checkbox" myState="inserted" myDirection="row" /></td> <td><input type="checkbox" disabled="disabled" myState="inserted" myDirection="column" /></td> </tr> <tr> <td>Updated</td> <td><input type="checkbox" myState="updated" myDirection="row" /></td> <td><input type="checkbox" disabled="disabled" myState="updated" myDirection="column" /></td> </tr> </table> </div> <h3>Formula Rule</h3> <hr> <div class="option-row"><label>Input some formula to apply the formula rule. The matched rows values will be rendered as red foreColor.</label> </div> <br> <input type="text" id="formula-input" value="AND([@ReorderLevel] > 20, [@ReorderLevel] < 30)" /> <br> <div class="button-container clear"> <input type="button" class="float-left" id="formula-set" value="SET" /> <input type="button" class="float-right" id="formula-remove" value="REMOVE" /> </div> </div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } #formula-input { width: calc(100% - 10px); margin-bottom: 6px; } .clear:after { display: block; width: 0; height: 0; visibility: hidden; content: ""; clear: both; } .button-container > input { width: calc(48%); } .float-left { float: left; } .float-right { float: right; } .option-row { font-size: 14px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } #states-table { width: 100%; border-collapse: collapse; text-align: center; } #states-table td { border: 1px solid grey; }