Pivot Style

You can apply a pivot table style to highlight any data in the specified dimension(s). The style method can be used to apply styles to row and column fields. Switch between the two sheets below to see the different styles being applied.

PivotTable offers ability to set style to the specified dimensions. No matter how the pivotTable layout changes, the style only follows the specified dimensions. Add/Remove style through setStyle API Get style through getStyle API
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 3 }); initSpread(spread); let markQtrLayout = spread.getSheet(0); let markQtrPivotTable = this.initMarkQtr(markQtrLayout); let markitemLayout = spread.getSheet(1); let markItemPivotTable = this.initMarkItem(markitemLayout); setMarkQtrStyle(markQtrPivotTable); setMarkItemStyle(markItemPivotTable); bindEvent(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet = spread.getSheet(2); 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('tableSales', 0, 0, 117, 6); for(let i=2;i<=117;i++) { sheet.setFormula(i,5,'=D'+i+'*E'+i) } table.style(GC.Spread.Sheets.Tables.TableThemes["none"]); sheet.setArray(0, 0, pivotSales); let sheet0 = spread.getSheet(0); sheet0.name("MarkQtrLayout"); sheet0.setValue(0,0, "A style is applied for the 1st and 3rd Quarter Subtotal."); let sheet1 = spread.getSheet(1); sheet1.name("MarkItemLayout"); sheet1.setValue(0,0, "This style is only applied to 'BMW' and will be applied whether this field is being displayed in the PivotTable or not. "); spread.resumePaint(); } function initMarkQtr(sheet) { let myPivotTable = sheet.pivotTables.add("markQtrPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8); myPivotTable.suspendLayout(); myPivotTable.options.showRowHeader = true; myPivotTable.options.showColumnHeader = true; myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("date", "Qt", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; myPivotTable.group(groupInfo); myPivotTable.add("quantity", "Sum of Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); myPivotTable.resumeLayout(); myPivotTable.autoFitColumn(); return myPivotTable; } function initMarkItem(sheet) { let myPivotTable = sheet.pivotTables.add("markItemPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8); myPivotTable.suspendLayout(); myPivotTable.options.showRowHeader = true; myPivotTable.options.showColumnHeader = true; myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("date", "Qt", GC.Spread.Pivot.PivotTableFieldType.columnField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; myPivotTable.group(groupInfo); myPivotTable.add("quantity", "Sum of Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); new GC.Spread.Pivot.PivotPanel("myPivotPanel", myPivotTable, document.getElementById("panel")); document.getElementById("panel").style.display = "none"; myPivotTable.resumeLayout(); myPivotTable.autoFitColumn(); return myPivotTable; } function _getElementById(id) { return document.getElementById(id); } function setMarkQtrStyle (pivotTable) { let style = new GC.Spread.Sheets.Style(); style.backColor = "#82bc00"; style.foreColor = "white"; let labelPivotArea = { labelOnly: true, references: [{ fieldName: "Qt", subtotals: true, items: ["Qtr1", "Qtr3"] }] }; pivotTable.setStyle(labelPivotArea, style); let dataPivotArea = { dataOnly: true, references: [{ fieldName: "Qt", subtotals: true, items: ["Qtr1", "Qtr3"] }] }; pivotTable.setStyle(dataPivotArea, style); } function setMarkItemStyle (pivotTable) { let style = new GC.Spread.Sheets.Style(); style.backColor = "rgb(247, 167, 17)"; let labelPivotArea = { labelOnly: true, references: [{ fieldName: "Cars", items: ["BMW"] }] }; pivotTable.setStyle(labelPivotArea, style); let dataPivotArea = { dataOnly: true, references: [{ fieldName: "Cars", items: ["BMW"] }] }; pivotTable.setStyle(dataPivotArea, style); } function bindEvent(spread){ spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanged, function(e, args) { args.newSheet.name() === "MarkItemLayout" ? _getElementById("panel").style.display = "unset": _getElementById("panel").style.display = "none" }); }
<!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/pivot-data.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="sample-panel"> <div id="panel"></div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; } .sample-spreadsheets { width: calc(100% - 300px); height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; overflow: hidden; } .sample-panel { float: right; width: 300px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .gc-panel { padding: 10px; background-color: rgb(230, 230, 230); } #panel { position: absolute; right: 0; width: 300px; height: 100%; top: 0; } #app { height: 100%; }