Pivot Conditional Rules

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

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

PivotTable offers the ability to set conditional rules to specific dimensions. No matter how the PivotTable layout changes, the conditional rules are only applied the specified dimensions. Add a conditionalRule with the setConditionalRule API Get a conditionalRule with the getConditionalRulee API Remove a conditionalRule with the 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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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; }