概述
本 Demo 展示了 SpreadJS 条件格式的基本用法，通过创建 7 种不同类型的条件格式规则，演示如何根据单元格值、文本内容、日期等条件自动应用格式样式。Demo 还提供了规则管理功能，可以查看规则数量、移除选定区域的规则或清除所有规则。
实现思路
初始化工作表并获取条件格式集合对象 (conditionalFormats)
创建 7 个示例区域，分别展示不同类型的条件格式规则
为每个区域设置表格数据（数值、日期或文本）
创建对应的样式对象和条件格式规则
为移除规则和清除所有规则按钮添加点击事件处理
实时显示当前规则数量
代码解析
单元格值规则示例
这段代码使用 addCellValueRule 方法添加单元格值规则。参数说明：
comparisonOperator：比较运算符，这里使用 greaterThan（大于）
value1 和 value2：比较值，对于大于运算只需 value1，value2 传 0
style：满足条件时的样式对象
ranges：应用规则的单元格范围数组
双色渐变规则示例
add2ScaleRule 方法创建双色渐变规则。参数说明：
minType 和 maxType：最小值和最大值的类型，这里使用 lowestValue 和 highestValue
minValue 和 maxValue：具体的最小值和最大值，对于自动最值传 null
minColor 和 maxColor：最小值和最大值对应的颜色
ranges：应用规则的单元格范围数组
三色渐变规则示例
add3ScaleRule 方法创建三色渐变规则，增加了中间值的参数：
midType、midValue、midColor：中间值的类型、数值和颜色
数据条规则示例
addDataBarRule 方法创建数据条规则，返回规则对象后可以进一步设置属性：
gradient(true)：启用渐变效果
showBorder(false)：不显示边框
showBarOnly(false)：显示数据条的同时也显示单元格值
图标集规则示例
addIconSetRule 方法创建图标集规则：
iconSetType：图标集类型，这里使用五色箭头（fiveArrowsColored）
reverseIconOrder(false)：不反转图标顺序
showIconOnly(false)：显示图标的同时也显示单元格值
日期出现规则示例
addDateOccurringRule 方法根据日期类型应用格式：
type：日期条件类型，这里使用 nextWeek（下周）
style：满足条件时的样式
ranges：应用规则的单元格范围
特定文本规则示例
addSpecificTextRule 方法根据文本比较运算符应用格式：
comparisonOperator：文本比较运算符，这里使用 contains（包含）
text：要匹配的文本内容
style：满足条件时的样式
ranges：应用规则的单元格范围
规则管理示例
conditionalFormats 对象提供了规则管理方法：
count()：获取当前规则总数
removeRuleByRange()：移除指定范围的规则
clearRule()：清除所有规则
运行效果
表格分为 7 个区域，每个区域展示一种条件格式规则
左上区域：数值大于 50 的单元格显示绿色背景
右上区域：数值在 60-70 之间的单元格显示红色背景
左中区域：使用双色渐变（红色到蓝色）表示数值大小
右中区域：使用三色渐变（红色到绿色到蓝色）表示数值大小
左下区域：下周日期显示蓝色背景
右下区域：包含字母 "o" 的文本显示红色粗体
左底区域：使用绿色数据条表示数值大小
右底区域：使用五色箭头图标表示数值范围
选择单元格后点击"移除所选内容的规则"按钮可移除该区域的规则
点击"移除所有规则"按钮可清除所有条件格式
页面实时显示当前规则数量
var spreadNS = GC.Spread.Sheets;
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
initSpread(spread);
};
function initSpread(spread) {
var sheet = spread.getSheet(0);
sheet.suspendPaint();
var cfs = sheet.conditionalFormats ;
// sample title
sheet.addSpan(1, 1, 1, 7);
sheet.setValue(1, 1, "条件格式示例");
sheet.getCell(1, 1).font("24px sans-serif");
sheet.getCell(1, 1).hAlign(spreadNS.HorizontalAlign.center);
sheet.setRowHeight(1, 35);
// cell value rule
var r = 2;
var c = 1;
var w = 3;
var h = 4;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "显示绿色背景：值 > 50：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
var increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + 3, col + 1, increase);
increase += 10;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
var style = new spreadNS.Style();
style.backColor = "#CCFFCC";
cfs.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.greaterThan, 50, 0, style, [new spreadNS.Range(r + 1, c, h - 1, w)]);
r = 2;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "显示红色背景：值 >= 60 且值 <= 70：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + r + 1, col + c, increase);
increase += 10;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
style = new spreadNS.Style();
style.backColor = "#FFCCCC";
cfs.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.between, 60, 70, style, [ new spreadNS.Range(r + 1, c, h - 1, w) ]);
// 2 color scale rule
r = 7;
c = 1;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "显示双色渐变表示单元格值：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + r + 1, col + c, increase);
increase += 10;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
cfs.add2ScaleRule(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null, "#FF9999", spreadNS.ConditionalFormatting.ScaleValueType.highestValue, null, "#9999FF", [ new spreadNS.Range(r + 1, c, h - 1, w) ]);
// 3 color scale rule
r = 7;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "显示三色渐变表示单元格值：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + r + 1, col + c, increase);
increase += 10;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
cfs.add3ScaleRule(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null, "#FF9999",
spreadNS.ConditionalFormatting.ScaleValueType.number, 100, "#99FF99",
spreadNS.ConditionalFormatting.ScaleValueType.highestValue, null, "#9999FF", [new spreadNS.Range(r + 1, c, h - 1, w) ]);
// date occurring rule
addDays = function (date, days) {
var dt = new Date(date.getFullYear(), date.getMonth(), date.getDate() + days);
if (days) {
if (dt.getDate() === date.getDate()) {
dt = new Date(date.getFullYear(), date.getMonth(), date.getDate());
dt.setTime(dt.getTime() + (days * 24 * 3600 * 1000));
}
}
return dt;
};
r = 12;
c = 1;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "如果单元格值在下周，则显示蓝色背景：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + r + 1, col + c, addDays(new Date(), increase));
sheet.setFormatter(row + r + 1, col + c, "yyyy/mm/dd", spreadNS.SheetArea.viewport);
increase += 1;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
style = new spreadNS.Style();
style.backColor = "#CCCCFF";
cfs.addDateOccurringRule(spreadNS.ConditionalFormatting.DateOccurringType.nextWeek, style, [ new spreadNS.Range(r + 1, c, h - 1, w) ]);
// specific text rule
r = 12;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "如果单元格值包含 \"o\"，则显示红色前景色：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
var data = ["The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog", "The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog"];
increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + r + 1, col + c, data[increase]);
increase += 1;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
var style = new spreadNS.Style();
style.foreColor = "Red";
style.font = "bold 12px sans-serif";
cfs.addSpecificTextRule(spreadNS.ConditionalFormatting.TextComparisonOperators.contains, "o", style, [ new spreadNS.Range(r + 1, c, h - 1, w) ]);
// data bar rule
r = 17;
c = 1;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "显示一个彩色数据条表示单元格值：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + r + 1, col + c, increase);
increase += 10;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), { all: true });
var dataBarRule = cfs.addDataBarRule(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null,
spreadNS.ConditionalFormatting.ScaleValueType.highestValue, null,
"green", [new spreadNS.Range(r + 1, c, h - 1, w)]);
dataBarRule.gradient(true);
dataBarRule.showBorder(false);
dataBarRule.showBarOnly(false);
// icon set rule
r = 17;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "显示一个图标表示单元格值：");
sheet.getCell(r, c).wordWrap(true);
sheet.setRowHeight(r, 40);
increase = 0;
for (var row = 0; row < h - 1; row++) {
for (var col = 0; col < w; col++) {
sheet.setValue(row + r + 1, col + c, increase);
increase += 10;
}
}
sheet.getRange(r, c, h, w).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), { all: true });
var iconSetRule = cfs.addIconSetRule(spreadNS.ConditionalFormatting.IconSetType.fiveArrowsColored, [new spreadNS.Range(r + 1, c, h - 1, w)]);
iconSetRule.iconSetType();
iconSetRule.reverseIconOrder(false);
iconSetRule.showIconOnly(false);
sheet.resumePaint();
updateRuleCount();
document.getElementById("removeRule").addEventListener('click',function() {
var sels = sheet.getSelections();
if (sels && sels.length > 0) {
var sel = sels[0];
cfs.removeRuleByRange(sel.row, sel.col, sel.rowCount, sel.colCount);
updateRuleCount();
}
});
document.getElementById("clearRule").addEventListener('click',function() {
cfs.clearRule();
updateRuleCount();
});
function updateRuleCount() {
document.getElementById("ruleCount").innerHTML="规则数量: " + cfs.count();
}
};
function getActualRange(range, maxRowCount, maxColCount) {
var row = range.row < 0 ? 0 : range.row;
var col = range.col < 0 ? 0 : range.col;
var rowCount = range.rowCount < 0 ? maxRowCount : range.rowCount;
var colCount = range.colCount < 0 ? maxColCount : range.colCount;
return new spreadNS.Range(row, col, rowCount, colCount);
}
<!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">
<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="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 class="option-row">
<label id="ruleCount"></label>
</div>
<div class="option-row">
<input type="button" id="removeRule" value="移除所选内容的规则" />
<input type="button" id="clearRule" value="移除所有规则">
</div>
</div>
</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;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.option-row {
padding-top: 6px;
}
input {
padding: 4px 8px;
margin-bottom: 6px;
box-sizing: border-box;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
