SpreadJS是一款基于 HTML5 的纯前端电子表格控件,兼容 450 种以上的 Excel 公式,凭借其 “高性能、跨平台、与 Excel 高度兼容”的产品特性,备受以华为、苏宁易购、天弘基金等为代表的企业用户青睐。SpreadJS 为用户带来亲切的 Excel 使用体验的同时,满足 Web Excel 组件开发、表格文档协同编辑、数据填报、Excel 类报表设计等业务场景,极大降低了企业研发成本和项目交付风险。
使用 Formula 规则
DateFormula 规则可以让你使用公式来检查单元格的条件。
以下代码创建了 Formula 规则。
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var ranges = [new GC.Spread.Sheets.Range(0, 0, 2, 1)];
activeSheet.conditionalFormats.addFormulaRule("=A1=B1+C1", style, ranges);
activeSheet.setValue(0, 0, 2,3);
activeSheet.setValue(0, 1, 1,3);
activeSheet.setValue(0, 2,1,3);
activeSheet.setValue(1, 0, 1,3);
// OR
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.FormulaRule);
rule.formula("=A1=B1+C1");
rule.ranges([new GC.Spread.Sheets.Range(0, 0, 2, 1)]);
rule.style(style);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0, 0, 2,3);
activeSheet.setValue(0, 1, 1,3);
activeSheet.setValue(0, 2,1,3);
activeSheet.setValue(1, 0, 1,3);
Icon Set 规则可以基于单元格中的值来显示图标,如下图所示:
你可以使用iconSetType 方法和IconSetType枚举来指定图标的样式。你也可以使用reverseIconOrder方法来反转图标的样式。或者使用 showIconOnly方法设置只显示图标还是图标和数据一起显示。或自行创建一系列的图标。
以下代码创建了 Icon Set 规则:
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,15,3);
activeSheet.setValue(2,0,25,3);
activeSheet.setValue(3,0,-1,3);
var iconSetRule = new GC.Spread.Sheets.ConditionalFormatting.IconSetRule();
iconSetRule.ranges([new GC.Spread.Sheets.Range(0,0,4,1)]);
iconSetRule.iconSetType(GC.Spread.Sheets.ConditionalFormatting.IconSetType.FourTrafficLights);
var iconCriteria = iconSetRule.iconCriteria();
iconCriteria[0] = new GC.Spread.Sheets.ConditionalFormatting.IconCriterion(true, GC.Spread.Sheets.ConditionalFormatting.IconValueType.Number, 1);
iconCriteria[1] = new GC.Spread.Sheets.ConditionalFormatting.IconCriterion(true, GC.Spread.Sheets.ConditionalFormatting.IconValueType.Number, 10);
iconCriteria[2] = new GC.Spread.Sheets.ConditionalFormatting.IconCriterion(true, GC.Spread.Sheets.ConditionalFormatting.IconValueType.Number, 20);
iconSetRule.reverseIconOrder(false);
iconSetRule.showIconOnly(false);
activeSheet.conditionalFormats.addRule(iconSetRule);
以下代码在 Icon Set 规则中创建了自定义图标。
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,15,3);
activeSheet.setValue(2,0,25,3);
activeSheet.setValue(3,0,-1,3);
var base = GC.Spread.Sheets.ConditionalFormatting.IconSetRule.getIcon;
GC.Spread.Sheets.ConditionalFormatting.IconSetRule.getIcon = function (iconSetType, iconIndex) {
var icon = base.apply(this, arguments);
if (iconSetType === GC.Spread.Sheets.ConditionalFormatting.IconSetType.ThreeArrowsColored) {
if (iconIndex === 0) {
return "images/Star2.png";
} else if (iconIndex === 1){
return "images/Rating4.png";
} else if (iconIndex === 2) {
return "images/Box4.png";
}
}
return icon;
};
var iconSetRule = new GC.Spread.Sheets.ConditionalFormatting.IconSetRule();
iconSetRule.ranges([new GC.Spread.Sheets.Range(0,0,4,1)]);
iconSetRule.iconSetType(GC.Spread.Sheets.ConditionalFormatting.IconSetType.ThreeArrowsColored);
var iconCriteria = iconSetRule.iconCriteria();
iconCriteria[0] = new GC.Spread.Sheets.ConditionalFormatting.IconCriterion(true, GC.Spread.Sheets.ConditionalFormatting.IconValueType.Number, 1);
iconCriteria[1] = new GC.Spread.Sheets.ConditionalFormatting.IconCriterion(true, GC.Spread.Sheets.ConditionalFormatting.IconValueType.Number, 10);
iconCriteria[2] = new GC.Spread.Sheets.ConditionalFormatting.IconCriterion(true, GC.Spread.Sheets.ConditionalFormatting.IconValueType.Number, 20);
iconSetRule.reverseIconOrder(false);
iconSetRule.showIconOnly(false);
activeSheet.conditionalFormats.addRule(iconSetRule);