Pivot Conditional Rules

You can apply, get, and remove conditional formatting rules to a pivot area. The conditional rules work as expected irrespective of the changes to pivot table layout.

The following demo applies different color formatting depending on the cell values, with green being the lowest and red the highest.

PivotTable offers ability to set conditional rules to the specified dimensions. No matter how the pivotTable layout changes, the conditional rules only follows the specified dimensions. Add conditionalRule through setConditionalRule API get conditionalRule through getConditionalRule API Remove conditionalRule through removeConditionalRule API
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); window.scaleRule = new GC.Spread.Sheets.ConditionalFormatting.ScaleRule(11, 1, 0, "#82bc00", 0, 40000, "#f7ea00", 2, 100000, "#e45d5d"); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let tableName = getDataSource(sheet2, pivotSales); let pivotTable = initPivotTable(sheet1, tableName); bindEvent(spread, pivotTable); spread.resumePaint(); } function getDataSource(sheet, tableSource) { sheet.name("DataSource"); sheet.setRowCount(117); sheet.setColumnWidth(0, 120); sheet.getCell(-1, 0).formatter("YYYY-mm-DD"); sheet.getRange(-1, 4, 0, 2).formatter("$ #,##0"); let table = sheet.tables.add('table', 0, 0, 117, 6); for (let i = 2; i <= 117; i++) { sheet.setFormula(i - 1, 5, '=D' + i + '*E' + i) } table.style(GC.Spread.Sheets.Tables.TableThemes["none"]); sheet.setArray(0, 0, tableSource); return table.name(); } function initPivotTable(sheet, tableName) { sheet.name("PivotTable"); sheet.setRowCount(1000); let pivotTableOptions = { bandRows: true, bandColumns: true }; let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8, pivotTableOptions); pivotTable.suspendLayout(); pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; pivotTable.group(groupInfo); pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); let pivotArea = { dataOnly: true, references: [{ fieldName: "Salesperson", items: ["Alan"] }, { fieldName: "Cars" }] }; pivotTable.addConditionalRule([pivotArea], scaleRule); let style = new GC.Spread.Sheets.Style(); style.formatter = "$ #,##0"; pivotTable.setStyle({ dataOnly: true }, style); pivotTable.resumeLayout(); pivotTable.autoFitColumn(); return pivotTable; } function bindEvent(spread, pivotTable) { let refs = ['Alan', "Bob", "John", "Serena", "Tess"], refsCount = refs.length; let currentIndex = 0; function getCurrentPivotAreas() { return [ { dataOnly: true, references: [{ fieldName: "Salesperson", items: [refs[currentIndex % refsCount]] }, { fieldName: "Cars" }] } ]; } function getNextPivotAreas() { currentIndex++; return getCurrentPivotAreas(); } function getPreviousPivotAreas() { if (currentIndex === 0) { currentIndex = refsCount * 10; } currentIndex--; return getCurrentPivotAreas(); } document.getElementById("switch-region-up").addEventListener("click", function (event) { spread.suspendPaint(); pivotTable.removeConditionalRule(scaleRule); pivotTable.addConditionalRule(getPreviousPivotAreas(), scaleRule); spread.resumePaint(); }); document.getElementById("switch-region-down").addEventListener("click", function (event) { spread.suspendPaint(); pivotTable.removeConditionalRule(scaleRule); pivotTable.addConditionalRule(getNextPivotAreas(), scaleRule); spread.resumePaint(); }); document.getElementById("layoutType").addEventListener("change", () => { if (spread) { var type = parseInt(document.getElementById("layoutType").value, 10); pivotTable.layoutType(type); pivotTable.autoFitColumn(); } }); }
<!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$/en/purejs/node_modules/@grapecity/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/pivot-data.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 id="container" class="options-container"> <div class="option-row"> <label><b>Settings:</b> </label> </div> <hr> <div class="option-row"> <label>Click the <b>MoveNext</b> or <b>MovePrev</b> buttons to apply the conditional rule to the Salesperson above or below the currently highlighted dimension. </label> </div> <input type="button" value="MovePrev" class="set-option" id="switch-region-up" /> <br /> <input type="button" value="MoveNext" class="set-option" id="switch-region-down" /> <br /> <hr> <div class="option-row"> <label>Change the dropdown menu below to see how changing pivot table layout affects the conditional rules. </label> </div> <br /> <div class="option-row"> <select id="layoutType"> <option value="0">Compact Form</option> <option value="1" selected>Outline Form</option> <option value="2">Tabular Form</option> </select> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 300px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 300px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .set-option { display: block; margin-top: 20px; width: 250px; } #reportFilterFieldsPerColumn { width: 28px; } .select-option-class{ display: block; margin-top: 20px; margin-bottom: 10px } .select-option-select{ width: 250px; display: block; margin-bottom: 20px; } .option-item{ height: 20px; margin-bottom: 10px; }