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 three 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: 4 }); initSpread(spread); let markQtrLayout = spread.getSheet(0); let markQtrPivotTable = this.initMarkQtr(markQtrLayout); let markitemLayout = spread.getSheet(1); let markItemPivotTable = this.initMarkItem(markitemLayout); let markOffsetLayout = spread.getSheet(2); let markOffsetPivotTable = this.initMarkOffset(markOffsetLayout); setMarkQtrStyle(markQtrPivotTable); setMarkItemStyle(markItemPivotTable); setMarkOffsetStyle(markOffsetPivotTable); bindEvent(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet = spread.getSheet(3); 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. "); let sheet2 = spread.getSheet(2); sheet2.name("MarkOffsetLayout"); sheet2.setValue(0,0, "This style is only applied to the offset area of the 'Alan' item"); 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); 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 initMarkOffset(sheet) { let myPivotTable = sheet.pivotTables.add("markOffsetPivotTable", "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); myPivotTable.add("quantity", "Sum of Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); 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 setMarkOffsetStyle (pivotTable) { let style = new GC.Spread.Sheets.Style(); style.backColor = "rgb(247, 167, 17)"; let labelPivotAreaWithOffset = { labelOnly: true, references: [{ fieldName: "Salesperson", items: ["Alan"] }], offset: { row: 1, col: 0, rowCount: 3, colCount: 1 } }; pivotTable.setStyle(labelPivotAreaWithOffset, style); let cornerPivotAreaWithOffset = { type: GC.Spread.Pivot.PivotAreaType.corner, offset: { row: 0, col: 1, rowCount: 1, colCount: 1 } }; pivotTable.setStyle(cornerPivotAreaWithOffset, style); let topRightPivotAreaWithOffset = { type: GC.Spread.Pivot.PivotAreaType.topRight, offset: { row: 0, col: 0, rowCount: 1, colCount: 4 } }; pivotTable.setStyle(topRightPivotAreaWithOffset, 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/@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/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%; }