概述
本 Demo 展示了 TableSheet 的样式规则功能，包括状态规则和公式规则两种类型。Demo 提供了交互界面，可以动态启用或禁用不同的状态规则（如悬停、只读、选中、已修改等），也可以自定义公式规则来突出显示满足条件的数据行。
实现思路
创建各种样式对象，定义不同状态下的视觉外观（悬停、只读、选中、已修改等）
使用 addStyleRule 方法为视图添加状态规则，指定状态类型和作用方向（行或列）
通过 HTML 复选框实现动态控制，根据用户选择添加或移除对应的样式规则
使用 removeStyleRule 方法移除不需要的样式规则
提供输入框让用户自定义公式规则，动态更新条件表达式
代码解析
创建样式对象
Demo 定义了多种样式对象，每种样式对应一种状态。通过设置 backColor、foreColor 等属性定义样式外观。
添加状态规则
使用 addStyleRule 方法添加状态规则。第一个参数是规则名称，第二个参数是样式对象，第三个参数是规则配置。state 指定状态类型，direction 指定规则应用于行还是列。
添加公式规则
公式规则通过 formula 属性指定条件表达式。满足条件的行会自动应用指定的样式。公式中使用 [@字段名] 语法引用当前行的字段值。
动态移除样式规则
使用 removeStyleRule 方法按名称移除样式规则。Demo 中通过复选框的状态来决定添加或移除对应的样式规则。
处理列头默认规则
对于固定、主键、必填等列头默认规则，需要将 area 设置为 GC.Data.ViewArea.colHeader，并通过 SVG 图标显示状态标识。
运行效果
状态规则交互：在右侧面板勾选或取消勾选状态选项（如悬停、只读、选中），TableSheet 会立即应用或移除对应的样式效果
悬停效果：当勾选"悬停"选项时，鼠标悬停在行或列上会显示浅蓝色背景
公式规则：在公式输入框中输入条件表达式（如 [@ReorderLevel] > 20），点击"设置"按钮后，满足条件的行会以红色字体显示
列头图标：固定列、主键列、必填列会在列头显示对应的图标标识
API 参考
addStyleRule 方法
向视图添加样式规则。
name：样式规则名称
style：样式对象，可包含 backColor、foreColor 等属性
rule：规则配置对象
状态规则：包含 state、direction、area 属性
公式规则：包含 formula 属性
removeStyleRule 方法
按名称从视图中移除样式规则。
name：样式规则名称
RowColumnStates 枚举
可用的行和列状态：
hover：鼠标悬停状态
selected：选中状态
active：活动状态
readonly：只读状态
dirty：已修改状态
inserted：已插入状态
updated：已更新状态
pin：固定状态
primaryKey：主键状态
required：必填状态
/*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\/SpreadJSTutorial\//)[0] + 'server/api';
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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>状态规则</h3>
<hr>
<div class="option-row"><label>在下方表格中选择不同选项，观察它们对 TableSheet 的影响。</label>
</div>
<div class="option-row">
<table id="states-table">
<tr>
<td></td>
<td>行</td>
<td>列</td>
</tr>
<tr>
<td>悬停</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>只读</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>活动</td>
<td><input type="checkbox" myState="active" myDirection="row" /></td>
<td><input type="checkbox" myState="active" myDirection="column" /></td>
</tr>
<tr>
<td>选中</td>
<td><input type="checkbox" myState="selected" myDirection="row" /></td>
<td><input type="checkbox" myState="selected" myDirection="column" /></td>
</tr>
<tr>
<td>已修改</td>
<td><input type="checkbox" myState="dirty" myDirection="row" /></td>
<td><input type="checkbox" disabled="disabled" myState="dirty" myDirection="column" /></td>
</tr>
<tr>
<td>已插入</td>
<td><input type="checkbox" myState="inserted" myDirection="row" /></td>
<td><input type="checkbox" disabled="disabled" myState="inserted" myDirection="column" /></td>
</tr>
<tr>
<td>已更新</td>
<td><input type="checkbox" myState="updated" myDirection="row" /></td>
<td><input type="checkbox" disabled="disabled" myState="updated" myDirection="column" /></td>
</tr>
<tr>
<td>固定</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>主键</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>必填</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>公式规则</h3>
<hr>
<div class="option-row"><label>输入公式以应用公式规则。匹配行的值将显示为红色字体。</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="设置" />
<input type="button" class="float-right" id="formula-remove" value="移除" />
</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;
}
