概述
本 Demo 展示了如何为单元格的不同状态应用自定义样式。通过界面可以选择单元格范围、命名样式和单元格状态类型，动态添加单元格状态规则。Demo 还演示了单元格状态与工作表保护、数据验证、无效公式检测等功能的结合使用。
实现思路
加载数据源并初始化工作簿，设置默认行高和列宽
从预设数据中添加多个命名样式到工作簿
绑定界面事件：应用单元格状态、保护工作表、锁定范围、数据验证、允许无效公式
根据用户选择，调用 cellStates.add() 方法为指定范围添加状态样式
实现无效公式状态的特殊处理，为无效公式单元格添加红色虚线边框
代码解析
添加命名样式
这段代码从预设的 nameStyles 数组中读取样式配置，创建命名样式对象并添加到工作簿。命名样式可以重复使用，适合应用在多个单元格或单元格状态中。
应用单元格状态
这段代码演示了如何应用单元格状态。cellStates.add() 方法接受三个参数：range（单元格范围）、cellStateType（状态类型枚举值）和 style（样式对象）。当单元格进入指定状态时，会自动应用对应的样式。
处理无效公式状态
这段代码为整个工作表添加无效公式状态样式。当工作簿禁止无效公式时（spread.options.allowInvalidFormula = false），包含无效公式的单元格会自动显示红色虚线边框。
设置数据验证
通过数据验证创建 invalid 状态。当单元格数据不符合验证规则时，会触发无效状态并应用相应样式。
运行效果
在工作表中选择任意单元格范围
从"样式"下拉列表中选择一个预定义的命名样式
从"单元格状态类型"下拉列表中选择状态（悬停、无效、编辑、只读、活动、选择、已修改、无效公式）
点击"应用单元格状态"按钮，选中的范围会添加状态规则
测试悬停状态：鼠标悬停在已添加规则的单元格上，查看样式变化
测试只读状态：勾选"保护工作表"，点击"锁定范围单元格"，被锁定的单元格会显示只读样式
测试无效状态：设置数据验证范围和条件，输入不符合条件的数据，查看无效样式
测试无效公式状态：勾选/取消"允许无效公式"，查看无效公式单元格的边框变化
API 参考
cellStates.add()
range：GC.Spread.Sheets.Range 对象，指定应用状态的单元格范围
cellStateType：GC.Spread.Sheets.CellStatesType 枚举值，状态类型
style：GC.Spread.Sheets.Style 对象，状态激活时应用的样式
sheetArea：可选，GC.Spread.Sheets.SheetArea 枚举，默认为 viewport
CellStatesType 枚举
hover (1)：鼠标悬停
invalid (2)：数据验证失败
readonly (4)：只读（工作表保护时被锁定的单元格）
edit (8)：正在编辑
active (16)：获得焦点
selected (32)：被选中
dirty (64)：值被修改
invalidFormula (128)：无效公式
状态优先级
当单元格同时满足多个状态时，样式会合并应用，优先级为：edit > hover > active > selected > invalidFormula > dirty > invalid > readonly
命名样式相关方法
cellStates.remove()
移除指定范围的单元格状态规则。
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
spread.suspendPaint();
var sheet = spread.getActiveSheet();
sheet.setDataSource(dataSource);
sheet.defaults.rowHeight = 30;
sheet.defaults.colWidth = 120;
initSpread(spread);
spread.resumePaint();
};
function initSpread(spread) {
addNameStyles(spread);
initPage(spread);
bindEvent(spread);
}
function getStyleNames(spread) {
var styles = spread.getNamedStyles();
return styles.map(function (style) {
return style.name;
});
}
function initPage(spread) {
//init style list
var $styleList = document.getElementById("style-list");
var styleListHtml = '';
var names = getStyleNames(spread);
names.forEach(function (name) {
styleListHtml += '<option>' + name + '</option>'
});
$styleList.innerHTML = styleListHtml;
}
function addNameStyles(spread) {
var temp;
for (var i = 0, len = nameStyles.length; i < len; i++) {
temp = new GC.Spread.Sheets.Style();
temp.fromJSON(nameStyles[i]);
spread.addNamedStyle(temp);
}
}
function bindEvent(spread) {
var btn = document.getElementById("apply-cell-state");
btn.addEventListener("click", function () {
var sheet = spread.getActiveSheet();
var range = sheet.getSelections()[0];
var styleName = document.getElementById("style-list").value;
var style;
if (styleName) {
style = spread.getNamedStyle(styleName);
}
var cellStateType = parseInt(document.getElementById("cell-states-type").value, 10);
if (range && cellStateType !== undefined && style) {
sheet.cellStates.add(range, cellStateType, style);
}
});
var protectSheetBtn = document.getElementById("protectSheet");
protectSheetBtn.addEventListener("change", function () {
var checked = !!protectSheetBtn.checked;
var sheet = spread.getActiveSheet();
sheet.options.isProtected = checked;
});
var lockRangeBtn = document.getElementById("lockRange");
lockRangeBtn.addEventListener("click", function () {
var sheet = spread.getActiveSheet();
var range = sheet.getSelections()[0];
sheet.getRange(range.row, range.col, range.rowCount, range.colCount).locked(true);
});
var validationBtn = document.getElementById("data-validation-apply");
validationBtn.addEventListener("click", function () {
var value1 = parseInt(document.getElementById("data-validation-from").value, 10);
var value2 = parseInt(document.getElementById("data-validation-to").value, 10);
var op = parseInt(document.getElementById("data-validation-op").value);
if (value1 !== undefined && value2 !== undefined) {
var sheet = spread.getActiveSheet();
var range = sheet.getSelections()[0];
var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(op, value1, value2);
sheet.setDataValidator(range.row, range.col, range.rowCount, range.colCount, dv);
}
});
var invalidFormulaCheckbox = document.getElementById("allowInvalidFormula");
invalidFormulaCheckbox.addEventListener("change", function () {
var checked = !!invalidFormulaCheckbox.checked;
spread.options.allowInvalidFormula = checked;
var sheet = spread.getActiveSheet();
changeInvalidFormulaCellState(sheet, !checked);
sheet.recalcAll();
})
}
function changeInvalidFormulaCellState (sheet, isRemove) {
var wholeRange = new GC.Spread.Sheets.Range(0, 0, sheet.getRowCount(), sheet.getColumnCount());
if (isRemove) {
sheet.cellStates.remove(wholeRange, GC.Spread.Sheets.CellStatesType.invalidFormula, GC.Spread.Sheets.SheetArea.viewport);
return;
}
var style = new GC.Spread.Sheets.Style();
var borderTop = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
var borderBottom = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
var borderLeft = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
var borderRight = new GC.Spread.Sheets.LineBorder('red', GC.Spread.Sheets.LineStyle.mediumDashed);
style.borderTop = borderTop;
style.borderBottom = borderBottom;
style.borderLeft = borderLeft;
style.borderRight = borderRight;
sheet.cellStates.add(wholeRange, GC.Spread.Sheets.CellStatesType.invalidFormula, style, GC.Spread.Sheets.SheetArea.viewport);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<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">
<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-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="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/namestyle.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">
<p>修改以下属性，然后点击“应用单元格状态”按钮来应用这些更改。</p>
<div>
<label>
<h4>范围:</h4>
</label>
<div class="row-content">
<span>工作表中选中的范围将用作单元格状态范围。</span>
</div>
</div>
<div class="row">
<label>
<h4>样式:</h4>
</label>
<div class="row-content">
<select class="row-select" id="style-list"></select>
</div>
</div>
<div class="row">
<label>
<h4>单元格状态类型:</h4>
</label>
<div class="row-content">
<select id="cell-states-type" class="row-select">
<option value="1">悬停</option>
<option value="2">无效</option>
<option value="8">编辑</option>
<option value="4">只读</option>
<option value="16">活动</option>
<option value="32">选择</option>
<option value="64">已修改</option>
<option value="128">无效公式</option>
</select>
<div id="style-list-dialog">
</div>
</div>
</div>
<div class="row">
<label>
<h4>保护信息:</h4>
</label>
<div class="row-content">
<label class="protect-info"><input type="checkbox" id="protectSheet"> 保护工作表</label>
<label class="protect-info"><input type="button" id="lockRange" value=" 锁定范围单元格"></label>
</div>
</div>
<div class="row">
<label>
<h4>工作簿选项:</h4>
</label>
<div class="row-content">
<label class="protect-info"><input type="checkbox" id="allowInvalidFormula"> 允许无效公式</label>
</div>
</div>
<div class="row">
<label>
<h4> 数据验证:</h4>
</label>
<div class="row-content">
<label class="protect-info"><input type="number" id="data-validation-from"
placeholder="数据验证值1">
</label>
<label class="protect-info">
<select id="data-validation-op" class="row-select">
<option value="6" selected="">介于</option>
<option value="7">不介于</option>
<option value="0">等于</option>
<option value="1">不等于</option>
<option value="2">大于</option>
<option value="4">小于</option>
<option value="3">大于或等于</option>
<option value="5">小于或等于</option>
</select>
</label>
<label class="protect-info"><input type="number" id="data-validation-to"
placeholder="数据验证值2">
</label>
<label class="protect-info"><input type="button" id="data-validation-apply"
value="添加验证"></label>
</div>
</div>
<div class="row">
<label> <span></span></label>
<div class="row-content">
<input type="button" value="应用单元格状态" id="apply-cell-state">
</div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 300px);
height: 100%;
overflow: hidden;
float: left;
box-sizing: border-box;
}
.options-container {
float: right;
width: 300px;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.option-row {
padding-bottom: 8px;
}
label {
padding-bottom: 4px;
display: block;
}
input {
width: 100%;
padding: 4px 8px;
box-sizing: border-box;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
h4{
margin: 0;
}
.row{
line-height: 30px;
}
.row > label{
width: 150px;
text-align: left;
padding: 0 5px;
}
.row-select{
width: 240px;
margin-left: 20px;
}
.protect-info{
width: 100%;
display: inline-block;
margin: 5px 0;
}
.protect-info input[type="checkbox"]{
width: auto;
}
.protect-info input[type="number"] , .protect-info input[type="text"], .protect-info input[type="button"]{
width: 240px;
margin-left: 20px;
}
.protect-info input[type="button"]{
height: 30px;
line-height: 30px;
}
#apply-cell-state {
font-weight: bold;
margin-top: 15px;
}
