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*/
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<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" @click="onStatesChanged">
<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>
<hr>
<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" v-model="formula" @change="onFormulaChanged" />
<br>
<div class="button-container clear">
<input type="button" class="float-left" id="formula-set" value="SET" @click="onSetFormula"/>
<input type="button" class="float-right" id="formula-remove" value="REMOVE" @click="onRemoveFormula" />
</div>
</div>
</div>
</template>
<script>
import Vue from "vue";
import "@mescius/spread-sheets-vue";
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-tablesheet";
import "./styles.css";
let App = Vue.extend({
name: "app",
data: function () {
return {
state: 1,
direction: 1,
spread: null,
tablesheet: null,
formula: "AND([@ReorderLevel] > 20, [@ReorderLevel] < 30)",
styleDict: {}
}
},
methods: {
initSpread: function (spread) {
this.spread = spread;
spread.suspendPaint();
spread.clearSheets();
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;
this.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+'
}]
};
this.styleDict = {
"1": hoverStyle,
"4": readonlyStyle,
"16": selectedStyle,
"32": activeStyle,
"64": dirtyStyle,
"128": insertedStyle,
"256": updatedStyle,
"1024": defaultPinStyle,
"2048": defaultPrimaryKeyStyle,
"4096": defaultRequiredStyle
}
this.styleDict.defaultReadonlyStyle = defaultReadonlyStyle;
this.styleDict.formulaStyle= formulaStyle;
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: this.formula
});
myView.fetch().then(function () {
sheet.setDataView(myView);
sheet.togglePinnedColumns([1]);
});
spread.resumePaint();
},
onStatesChanged(e) {
let sheet = this.tablesheet;
let myView = sheet.getDataView();
let styleDict = this.styleDict;
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, styleDict.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);
}
},
onFormulaChanged($event) {
this.formula = $event.target.value;
},
onSetFormula() {
let sheet = this.tablesheet;
let myView = sheet.getDataView();
let formulaStyle = this.styleDict.formulaStyle;
let formula = this.formula;
myView.removeStyleRule("formula");
myView.addStyleRule("formula", formulaStyle, {
formula: formula
});
sheet.setDataView(myView);
},
onRemoveFormula() {
let sheet = this.tablesheet;
let myView = sheet.getDataView();
myView.removeStyleRule("formula");
sheet.setDataView(myView);
}
}
});
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
new Vue({
render: (h) => h(App),
}).$mount("#app");
</script>
<!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/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/vue/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('./src/app.vue');
System.import('$DEMOROOT$/en/lib/vue/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</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;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
meta: {
'*.css': { loader: 'css' },
'*.vue': { loader: 'vue-loader' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'jszip': 'npm:jszip/dist/jszip.js',
'css': 'npm:systemjs-plugin-css/css.js',
'vue': 'npm:vue/dist/vue.min.js',
'vue-loader': 'npm:systemjs-vue-browser/index.js',
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'js'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
}
}
});
})(this);
