SpreadJS使用教程：如何实现Formula 和Icon set方法

SpreadJS最新试用版

使用 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);// ORvar 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);

如果你对我们的产品感兴趣或者有任何疑问，欢迎咨询在线客服>>

标签：