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:
And there are some default StateRule (readonly, pin, primaryKey, required) that are applied on the column header in the View options:
It could update or remove the default StateRule:
If you want to judge the state of the row, you could call hasRowState:
/*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", isPrimaryKey: true },
{ value: "ProductName", caption: "Name", width: 200, required: true, },
{ 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 defaultReadonlyStyle = new GC.Spread.Sheets.Style();
defaultReadonlyStyle.decoration = {
icons: [{
src: 'data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIGhlaWdodD0iMjRweCIgdmlld0JveD0iMCAwIDI0IDI0IiB3aWR0aD0iMjRweCIgZmlsbD0iIzAwMDAwMCI+PGcgZmlsbD0ibm9uZSI+PHBhdGggZD0iTTAgMGgyNHYyNEgwVjB6Ii8+PHBhdGggZD0iTTAgMGgyNHYyNEgwVjB6IiBvcGFjaXR5PSIuODciLz48L2c+PHBhdGggZD0iTTIwIDhoLTNWNi4yMWMwLTIuNjEtMS45MS00Ljk0LTQuNTEtNS4xOUM5LjUxLjc0IDcgMy4wOCA3IDZ2Mkg0djE0aDE2Vjh6bS04IDljLTEuMSAwLTItLjktMi0ycy45LTIgMi0yIDIgLjkgMiAyLS45IDItMiAyek05IDhWNmMwLTEuNjYgMS4zNC0zIDMtM3MzIDEuMzQgMyAzdjJIOXoiLz48L3N2Zz4='
}]
};
var defaultPinStyle = new GC.Spread.Sheets.Style();
defaultPinStyle.decoration = {
icons: [{
src: 'data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTIiIGhlaWdodD0iMTIiIHZpZXdCb3g9IjAgMCAxMiAxMiIgZmlsbD0ibm9uZSIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj4KPHJlY3Qgd2lkdGg9IjEyIiBoZWlnaHQ9IjEyIiBmaWxsPSJ0cmFuc3BhcmVudCIvPgo8cGF0aCBkPSJNNC4xNjIyNSAwLjAwMTIyMzY5QzMuOTg3ODYgMC4wMDA5NDU5MjkgMy44MTU4NSAwLjA0ODI3MDUgMy42NjAwMiAwLjEzOTM5NkMzLjUwNDIgMC4yMzA1MjEgMy4zNjg5MiAwLjM2MjkwNiAzLjI2NTA0IDAuNTI1OTE3QzMuMTYxMTUgMC42ODg5MjggMy4wOTE1OCAwLjg3ODAyIDMuMDYxODkgMS4wNzhDMy4wMzIyIDEuMjc3OTkgMy4wNDMyMyAxLjQ4MzI4IDMuMDk0MDkgMS42Nzc0MUw0LjAyNDUzIDUuMjMxNjVMMy4wMzk0MiA3LjUyNDQ3QzMuMDExMDEgNy41OTA0MSAyLjk5NzU3IDcuNjYzNzIgMy4wMDAzNiA3LjczNzQxQzMuMDAzMTYgNy44MTExMSAzLjAyMjEgNy44ODI3NCAzLjA1NTM5IDcuOTQ1NTJDMy4wODg2OSA4LjAwODI5IDMuMTM1MjIgOC4wNjAxMSAzLjE5MDU3IDguMDk2MDZDMy4yNDU5MiA4LjEzMjAxIDMuMzA4MjUgOC4xNTA4OSAzLjM3MTY0IDguMTUwOUw1LjYyODM1IDguMTUwMjhWMTEuMzg4OUw2IDEyTDYuMzcxNjUgMTEuMzg4OVY4LjE1MDI4TDguNjI4MzYgOC4xNTA5QzguNjkxNzUgOC4xNTA4OCA4Ljc1NDA4IDguMTMyMDEgOC44MDk0MyA4LjA5NjA2QzguODY0NzggOC4wNjAxMSA4LjkxMTMxIDguMDA4MjkgOC45NDQ2MSA3Ljk0NTUyQzguOTc3OSA3Ljg4Mjc0IDguOTk2ODQgNy44MTExMSA4Ljk5OTY0IDcuNzM3NDFDOS4wMDI0MyA3LjY2MzcyIDguOTg4OTkgNy41OTA0MSA4Ljk2MDU4IDcuNTI0NDdMNy45NzYgNS4yMzIyNkw4Ljg5ODU1IDEuNjgyM0M4Ljk0ODc0IDEuNDg4NyA4Ljk1OTM2IDEuMjg0MTMgOC45Mjk1NSAxLjA4NDkxQzguODk5NzUgMC44ODU2ODkgOC44MzAzNSAwLjY5NzMzMSA4LjcyNjg5IDAuNTM0ODYxQzguNjIzNDQgMC4zNzIzOTEgOC40ODg3OCAwLjI0MDMwNSA4LjMzMzY4IDAuMTQ5MTQxQzguMTc4NTcgMC4wNTc5NzY2IDguMDA3MzEgMC4wMTAyNTY4IDcuODMzNTQgMC4wMDk3ODgyMkw0LjE2MjI1IDBMNC4xNjIyNSAwLjAwMTIyMzY5WiIgZmlsbD0iIzY2NjY2NiIvPgo8L3N2Zz4K'
}]
};
var defaultPrimaryKeyStyle = new GC.Spread.Sheets.Style();
defaultPrimaryKeyStyle.decoration = {
icons: [{
src: 'data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHhtbG5zOnhsaW5rPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5L3hsaW5rIiB2ZXJzaW9uPSIxLjEiIHdpZHRoPSIxMDAwIiBoZWlnaHQ9IjEwMDAiIHZpZXdCb3g9IjAgMCAxMDAwIDEwMDAiIHhtbDpzcGFjZT0icHJlc2VydmUiPgo8ZGVzYz5DcmVhdGVkIHdpdGggRmFicmljLmpzIDMuNS4wPC9kZXNjPgo8ZGVmcz4KPC9kZWZzPgo8cmVjdCB4PSIwIiB5PSIwIiB3aWR0aD0iMTAwJSIgaGVpZ2h0PSIxMDAlIiBmaWxsPSJyZ2JhKDI1NSwyNTUsMjU1LDApIi8+CjxnIHRyYW5zZm9ybT0ibWF0cml4KDAgMjAuNjYxMiAyMC42NjEyIDAgNDk5Ljk5OTUgNTAwLjAwMDkpIiBpZD0iNDk5NTE3Ij4KPHBhdGggc3R5bGU9InN0cm9rZTogbm9uZTsgc3Ryb2tlLXdpZHRoOiAxOyBzdHJva2UtZGFzaGFycmF5OiBub25lOyBzdHJva2UtbGluZWNhcDogYnV0dDsgc3Ryb2tlLWRhc2hvZmZzZXQ6IDA7IHN0cm9rZS1saW5lam9pbjogbWl0ZXI7IHN0cm9rZS1taXRlcmxpbWl0OiA0OyBpcy1jdXN0b20tZm9udDogbm9uZTsgZm9udC1maWxlLXVybDogbm9uZTsgZmlsbDogcmdiKDAsMCwwKTsgZmlsbC1ydWxlOiBub256ZXJvOyBvcGFjaXR5OiAxOyIgdmVjdG9yLWVmZmVjdD0ibm9uLXNjYWxpbmctc3Ryb2tlIiB0cmFuc2Zvcm09IiB0cmFuc2xhdGUoLTI0LCAtMjQpIiBkPSJNIDE0IDM2IHEgLTUgMCAtOC41IC0zLjUgVCAyIDI0IHEgMCAtNSAzLjUgLTguNSBUIDE0IDEyIHEgNC4zIDAgNy4zMjUgMi40NSBxIDMuMDI1IDIuNDUgNC4xNzUgNS45IEggNDYgdiA3LjMgaCAtNS4zIFYgMzYgaCAtNi4zIHYgLTguMzUgaCAtOC45IHEgLTEuMTUgMy40NSAtNC4xNzUgNS45IFQgMTQgMzYgWiBtIDAgLTguNiBxIDEuNDUgMCAyLjQyNSAtMC45NzUgcSAwLjk3NSAtMC45NzUgMC45NzUgLTIuNDI1IHEgMCAtMS40NSAtMC45NzUgLTIuNDI1IFEgMTUuNDUgMjAuNiAxNCAyMC42IHEgLTEuNDUgMCAtMi40MjUgMC45NzUgUSAxMC42IDIyLjU1IDEwLjYgMjQgcSAwIDEuNDUgMC45NzUgMi40MjUgcSAwLjk3NSAwLjk3NSAyLjQyNSAwLjk3NSBaIiBzdHJva2UtbGluZWNhcD0icm91bmQiLz4KPC9nPgo8L3N2Zz4='
}]
};
var defaultRequiredStyle = new GC.Spread.Sheets.Style();
defaultRequiredStyle.decoration = {
icons: [{
src: 'data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIGhlaWdodD0iNDgiIHdpZHRoPSI0OCI+PHBhdGggZD0ibTExLjY1IDQ0IDMuMjUtMTQuMDVMNCAyMC41bDE0LjQtMS4yNUwyNCA2bDUuNiAxMy4yNUw0NCAyMC41bC0xMC45IDkuNDVMMzYuMzUgNDQgMjQgMzYuNTVaIi8+PC9zdmc+'
}]
};
var styleDict = {
"1": hoverStyle,
"4": readonlyStyle,
"16": selectedStyle,
"32": activeStyle,
"64": dirtyStyle,
"128": insertedStyle,
"256": updatedStyle,
"1024": defaultPinStyle,
"2048": defaultPrimaryKeyStyle,
"4096": defaultRequiredStyle
}
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);
sheet.togglePinnedColumns([1]);
});
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;
var area = GC.Data.ViewArea.viewport;
var defaultKeys = ['pin', 'primaryKey', 'required'];
if (defaultKeys.indexOf(stateType) > -1) {
area = GC.Data.ViewArea.colHeader;
styleRuleName = stateType;
}
if (target.checked) {
myView.addStyleRule(styleRuleName, styleDict[state], {
state: state,
direction: direction,
area: area
});
if (stateType === 'readonly') {
myView.addStyleRule(stateType, defaultReadonlyStyle, {
state: GC.Data.RowColumnStates.readonly,
direction: GC.Data.StateRuleDirection.column,
area: GC.Data.ViewArea.colHeader
});
}
} else {
myView.removeStyleRule(styleRuleName);
if (stateType === 'readonly') {
myView.removeStyleRule(stateType);
}
}
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/@mescius/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/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/en/purejs/node_modules/@mescius/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>
<tr>
<td>Pin</td>
<td><input type="checkbox" disabled="disabled" myState="pin" myDirection="row" /></td>
<td><input type="checkbox" checked="checked" myState="pin" myDirection="column" /></td>
</tr>
<tr>
<td>PrimaryKey</td>
<td><input type="checkbox" disabled="disabled" myState="primaryKey" myDirection="row" /></td>
<td><input type="checkbox" checked="checked" myState="primaryKey" myDirection="column" /></td>
</tr>
<tr>
<td>Required</td>
<td><input type="checkbox" disabled="disabled" myState="required" myDirection="row" /></td>
<td><input type="checkbox" checked="checked" myState="required" 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;
}
