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.

To add conditional formatting in SpreadJS, first use the conditionalFormats to get the conditional format for the sheet. Then you can create a conditional rule and use the addRule method to add it. Also you can use the specified rule's add method, like addSpecificTextRule, addCellValueRule, and so on. For example: After you add rules to the sheet, you can use the getRule method to return the rule using the rule index or get all the conditional rules. For example: If you want to remove the conditional rule, use the removeRule, removeRuleByRange, or clearRule method. For example:
<template> <div class="sample-tutorial"> <gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread"> <gc-worksheet></gc-worksheet> </gc-spread-sheets> <div class="options-container"> <p>Select cell(s) that contains rule(s):</p> <div class="option-row"> <input id="ruleCount" style="border: none;" :value="ruleCountDescription" /> </div> <div class="option-row"> <input type="button" id="removeRule" value="Remove the selection's rule" @click="removeRule" /> <input type="button" id="clearRule" value="Remove all the rules" @click="clearRule" /> </div> </div> </div> </template> <script> import Vue from "vue"; import "@grapecity/spread-sheets-vue"; import GC from "@grapecity/spread-sheets"; import "./styles.css"; let App = Vue.extend({ name: "app", data: function () { return { spread: null, ruleCountDescription: "" }; }, methods: { initSpread: function (spread) { this.spread = spread; let spreadNS = GC.Spread.Sheets; let sheet = spread.getSheet(0); sheet.suspendPaint(); let 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 let r = 2; let c = 1; let w = 3; let 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); let increase = 0; for (let row = 0; row < h - 1; row++) { for (let 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 } ); let 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 (let row = 0; row < h - 1; row++) { for (let 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 (let row = 0; row < h - 1; row++) { for (let 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 (let row = 0; row < h - 1; row++) { for (let 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)] ); 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 (let row = 0; row < h - 1; row++) { for (let col = 0; col < w; col++) { sheet.setValue( row + r + 1, col + c, this.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); let data = [ "The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog", "The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog" ]; increase = 0; for (let row = 0; row < h - 1; row++) { for (let 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 } ); 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 (let row = 0; row < h - 1; row++) { for (let 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 } ); let 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 (let row = 0; row < h - 1; row++) { for (let 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 } ); let 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(); this.updateRuleCount(); }, // date occurring rule addDays(date, days) { let 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; }, removeRule() { let sheet = this.spread.getSheet(0); let cfs = sheet.conditionalFormats; let sels = sheet.getSelections(); if (sels && sels.length > 0) { let sel = sels[0]; cfs.removeRuleByRange(sel.row, sel.col, sel.rowCount, sel.colCount); this.updateRuleCount(); } }, clearRule() { let sheet = this.spread.getSheet(0); let cfs = sheet.conditionalFormats; cfs.clearRule(); this.updateRuleCount(); }, updateRuleCount() { let sheet = this.spread.getSheet(0); let cfs = sheet.conditionalFormats; this.ruleCountDescription = "Rule Count: " + cfs.count(); } } }); new Vue({ render: h => h(App) }).$mount("#app"); </script>
<!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/vue/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/en/vue/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app.vue'); System.import('$DEMOROOT$/en/lib/vue/license.js'); </script> </head> <body> <div id="app"></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; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true }, meta: { '*.css': { loader: 'css' }, '*.vue': { loader: 'vue-loader' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-vue': 'npm:@grapecity/spread-sheets-vue/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'jszip': 'npm:jszip/dist/jszip.js', 'css': 'npm:systemjs-plugin-css/css.js', 'vue': 'npm:vue/dist/vue.min.js', 'vue-loader': 'npm:systemjs-vue-browser/index.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'js' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' } } }); })(this);