基本应用

当你的表单含有数千行数据时,仅仅通过从原始数据中检查模式和趋势是极其困难的。与迷你图类似,条件格式化提供了另外一种途径来可视化数据和使表单更加容易理解。

要为 SpreadJS 添加条件格式,首先调用 conditionalFormats 属性来获得表单的条件格式。然后你可以创建一个条件规则并调用 addRule 方法来添加它。你也可以使用特殊规则的添加方法,比如 addSpecificTextRule, addCellValueRule 等。例如: 在你为表单添加了一些规则之后,便可以调用 getRule 方法通过规则的索引来获取指定规则或者获取所有的条件规则。例如: 如果你想删除一些条件规则,请调用 removeRule, removeRuleByRange, 或者 clearRule 方法。例如:
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); 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, "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.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, "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.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, "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.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, "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.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, "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.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, "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.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, "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.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, "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.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="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); }
<!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/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/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>Select cell(s) that contains rule(s):</p> <div class="option-row"> <label id="ruleCount"></label> </div> <div class="option-row"> <input type="button" id="removeRule" value="Remove the selection's rule" /> <input type="button" id="clearRule" value="Remove all the rules"> </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; }