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:
import { Component, NgModule, enableProdMode } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@grapecity/spread-sheets-angular'; import GC from '@grapecity/spread-sheets'; import './styles.css'; @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { spread: GC.Spread.Sheets.Workbook; hostStyle = { width: 'calc(100% - 280px)', height: '100%', overflow: 'hidden', float: 'left' }; ruleCountDescription = ""; constructor() { } initSpread($event: any) { this.spread = $event.spread; let spread = this.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: Date, days: number) { 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(); } } @NgModule({ imports: [BrowserModule, SpreadSheetsModule], declarations: [AppComponent], exports: [AppComponent], bootstrap: [AppComponent] }) export class AppModule { } enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Polyfills --> <script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script> <script src="$DEMOROOT$/en/angular/node_modules/zone.js/dist/zone.min.js"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('$DEMOROOT$/en/lib/angular/license.ts'); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html>
<div class="sample-tutorial"> <gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)"> <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($event)" /> <input type="button" id="clearRule" value="Remove all the rules" (click)="clearRule($event)"> </div> </div> </div>
.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: 'ts', typescriptOptions: { tsconfig: true }, meta: { 'typescript': { "exports": "ts" }, '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'core-js': 'npm:core-js/client/shim.min.js', 'zone': 'npm:zone.js/dist/zone.min.js', 'rxjs': 'npm:rxjs/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/bundles/core.umd.min.js', '@angular/common': 'npm:@angular/common/bundles/common.umd.min.js', '@angular/compiler': 'npm:@angular/compiler/bundles/compiler.umd.min.js', '@angular/platform-browser': 'npm:@angular/platform-browser/bundles/platform-browser.umd.min.js', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/bundles/platform-browser-dynamic.umd.min.js', '@angular/http': 'npm:@angular/http/bundles/http.umd.min.js', '@angular/common/http': 'npm:@angular/common/bundles/common-http.umd.min.js', '@angular/router': 'npm:@angular/router/bundles/router.umd.min.js', '@angular/forms': 'npm:@angular/forms/bundles/forms.umd.min.js', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': 'npm:plugin-typescript/lib/plugin.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js', '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-angular': 'npm:@grapecity/spread-sheets-angular/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'ts' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);