当使用表格展示成千上万的数据时，由于数据太多，又采用统一的颜色或模板显示的话，就造成阅读起来相当费劲，抓不到重点数据，这时候就需要对表格进行条件格式化，把一些重要的数据通过改变其字体颜色等进行突出显示，SpreadJS可以帮助开发人员快速实现该功能，具体可以参考下面的代码：
var SpreadNS = GcSpread.Sheets;
$(document).ready(function () {
var Spread = new GcSpread.Sheets.Spread(document.getElementById("ss"), { sheetCount: 2 });
});
var sheet = Spread.getSheet(0);
sheet.isPaintSuspended(true);
var cfs = sheet.getConditionalFormats();
// sample title
sheet.addSpan(1, 1, 1, 7);
sheet.setValue(1, 1, "Conditional Format Samples");
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, "Displays green background: value > 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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), {all: true});
var cvRule = new SpreadNS.CellValueRule();
cvRule.ranges = [new SpreadNS.Range(r + 1, c, h - 1, w)];
cvRule.operator = SpreadNS.ComparisonOperator.GreaterThan;
cvRule.value1 = 50;
var style = new SpreadNS.Style();
style.backColor = "#CCFFCC";
cvRule.style = style;
cfs.addRule(cvRule);
r = 2;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "Displays red background: value >= 60 and value <= 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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), {all: true});
cvRule = new SpreadNS.CellValueRule();
cvRule.ranges = [ new SpreadNS.Range(r + 1, c, h - 1, w) ];
cvRule.operator = SpreadNS.ComparisonOperator.Between;
cvRule.value1 = 60;
cvRule.value2 = 70;
style = new SpreadNS.Style();
style.backColor = "#FFCCCC";
cvRule.style = style;
cfs.addRule(cvRule);
// 2 color scale rule
r = 7;
c = 1;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "Displays two color gradient represents cell value:");
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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), {all: true});
var tcsRule = new SpreadNS.TwoScaleRule(SpreadNS.ScaleValueType.LowestValue, null, "#FF9999", SpreadNS.ScaleValueType.HighestValue, null, "#9999FF");
tcsRule.ranges = [ new SpreadNS.Range(r + 1, c, h - 1, w) ];
cfs.addRule(tcsRule);
// 3 color scale rule
r = 7;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "Displays three color gradient represents cell value:");
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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), {all: true});
var threecsRule = new SpreadNS.ThreeScaleRule(SpreadNS.ScaleValueType.LowestValue, null, "#FF9999",
SpreadNS.ScaleValueType.Number, 100, "#99FF99",
SpreadNS.ScaleValueType.HighestValue, null, "#9999FF");
threecsRule.ranges = [new SpreadNS.Range(r + 1, c, h - 1, w) ];
cfs.addRule(threecsRule);
// 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, "Display blue background if cell value is in next week:");
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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), {all: true});
var doRule = new SpreadNS.DateOccurringRule();
doRule.ranges = [ new SpreadNS.Range(r + 1, c, h - 1, w) ];
doRule.type = SpreadNS.DateOccurringType.NextWeek;
style = new SpreadNS.Style();
style.backColor = "#CCCCFF";
doRule.style = style;
cfs.addRule(doRule);
// specific text rule
r = 12;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "Display red foreground if cell value contains \"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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), {all: true});
var stRule = new SpreadNS.SpecificTextRule();
stRule.ranges = [ new SpreadNS.Range(r + 1, c, h - 1, w) ];
stRule.operator = SpreadNS.TextComparisonOperator.Contains;
stRule.text = "o";
var style = new SpreadNS.Style();
style.foreColor = "Red";
style.font = "bold 12px sans-serif";
stRule.style = style;
cfs.addRule(stRule);
// data bar rule
r = 17;
c = 1;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "Display a colored data bar represents cell value:");
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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), { all: true });
var dataBarRule = new SpreadNS.DataBarRule();
dataBarRule.ranges = [new SpreadNS.Range(r + 1, c, h - 1, w)];
dataBarRule.minimumType(SpreadNS.ScaleValueType.LowestValue);
dataBarRule.maximumType(SpreadNS.ScaleValueType.HighestValue);
dataBarRule.gradient(true);
dataBarRule.color("green");
dataBarRule.showBorder(false);
dataBarRule.showBarOnly(false);
cfs.addRule(dataBarRule);
// icon set rule
r = 17;
c = 5;
sheet.addSpan(r, c, 1, w);
sheet.setValue(r, c, "Displays an icon represents cell value:");
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.setBorder(new SpreadNS.Range(r, c, h, w), new SpreadNS.LineBorder("Black", SpreadNS.LineStyle.dashed), { all: true });
var iconSetRule = new SpreadNS.IconSetRule();
iconSetRule.ranges = [new SpreadNS.Range(r + 1, c, h - 1, w)];
iconSetRule.iconSetType(SpreadNS.IconSetType.FiveArrowsColored);
iconSetRule.reverseIconOrder(false);
iconSetRule.showIconOnly(false);
cfs.addRule(iconSetRule);
sheet.isPaintSuspended(false);
updateRuleCount();
$("#removeRule").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();
}
});
$("#clearRule").click(function() {
cfs.clearRule();
updateRuleCount();
});
function updateRuleCount() {
$("#ruleCount").text("Rule Count: " + 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);
}