Overview

If you have a sheet with thousands of rows of data, it can be extremely difficult to see patterns and trends just from examining the raw information. Similar to sparklines, conditional formatting provides another way to visualize data and make sheets easier to understand.

<p>To add conditional formatting in SpreadJS, first use the <strong>conditionalFormats</strong> to get the conditional format for the sheet. Then you can create a conditional rule and use the <strong>addRule</strong> method to add it. Also you can use the specified rule's add method, like <strong>addSpecificTextRule</strong>, <strong>addCellValueRule</strong>, and so on. For example:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> cfs = sheet.conditionalFormats; <span class="hljs-keyword">var</span> style = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Style(); style.backColor = <span class="hljs-string">'#CCFFCC'</span>; <span class="hljs-keyword">var</span> cvRule = cfs.addCellValueRule(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThan, <span class="hljs-number">100</span>, <span class="hljs-number">0</span>, style, [<span class="hljs-keyword">new</span> GC.Spread.Sheets.Range(<span class="hljs-number">3</span>, <span class="hljs-number">3</span>, <span class="hljs-number">3</span>, <span class="hljs-number">3</span>)]); </code></pre> <p>After you add rules to the sheet, you can use the <strong>getRule</strong> method to return the rule using the rule index or get all the conditional rules. For example:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> cfs = sheet.conditionalFormats; <span class="hljs-comment">// The new added is at the end.</span> <span class="hljs-keyword">var</span> cvRule = cfs.getRule(<span class="hljs-number">1</span>); <span class="hljs-comment">// Or get all the conditional rules.</span> <span class="hljs-keyword">var</span> rules = cfs.getRules(); </code></pre> <p>If you want to remove the conditional rule, use the <strong>removeRule</strong>, <strong>removeRuleByRange</strong>, or <strong>clearRule</strong> method. For example:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> cfs = sheet.conditionalFormats; cfs.removeRule(cvRule) cfs.removeRuleByRange(<span class="hljs-number">3</span>, <span class="hljs-number">3</span>, <span class="hljs-number">3</span>, <span class="hljs-number">3</span>); cfs.clearRule(); <span class="hljs-comment">//removes all rules</span> </code></pre>
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 charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.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; }