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.

Description
app.vue
index.html
styles.css
Copy to CodeMine

PivotTable offers ability to set style to the specified dimensions. No matter how the PivotTable layout changes, the style only follows the specified dimensions.

let labelPivotArea = {
        labelOnly: true,
        references: [{
            fieldName: "Cars",
            items: ["BMW"]
        }]
    };
let style = new GC.Spread.Sheets.Style();
style.backColor = "#82bc00";
pivotTable.setStyle(labelPivotArea, style);

Add/Remove style through setStyle API

///* function setStyle (pivotArea: GC.Spread.Pivot.IPivotArea, style: GC.Spread.Sheets.Style): void
/**
 * @description Set style to the specific pivotArea.
 * @param {GC.Spread.Pivot.IPivotArea} pivotArea the specific pivotArea
 * @param {GC.Spread.Sheets.Style} style the style set to the specific pivotArea, null or undefined to remove style of the specific pivotArea.
 */
pivotTable.setStyle(pivotArea, style);

Get style through getStyle API

///* function getStyle (pivotArea: GC.Spread.Pivot.IPivotArea, style: GC.Spread.Sheets.Style): void
/**
 * @description Get style to the specific pivotArea.
 * @param {GC.Spread.Pivot.IPivotArea} pivotArea the specific pivotArea
 * @returns GC.Spread.Sheets.Style
 */
pivotTable.getStyle(pivotArea);
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
<template> <div class="sample-tutorial"> <gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread"> <gc-worksheet> </gc-worksheet> <gc-worksheet> </gc-worksheet> <gc-worksheet> </gc-worksheet> <gc-worksheet> </gc-worksheet> </gc-spread-sheets> <div class="sample-panel"> <div id="panel"></div> </div> </div> </template> <script> import Vue from 'vue'; import '@mescius/spread-sheets-vue' import GC from '@mescius/spread-sheets'; import '@mescius/spread-sheets-shapes'; import '@mescius/spread-sheets-pivot-addon'; import './styles.css'; let App = Vue.extend({ name: "app", methods: { initSpread: function (spread) { this.initSheets(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); this.setMarkQtrStyle(markQtrPivotTable); this.setMarkItemStyle(markItemPivotTable); this.setMarkOffsetStyle(markOffsetPivotTable); this.bindEvent(spread); }, initSheets: function (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(); }, initMarkItem: function (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("Quarters (date)", "Quarters (date)", 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; }, initMarkQtr: function (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); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] }; myPivotTable.group(groupInfo); myPivotTable.add("Quarters (date)", "Quarters (date)", 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")); myPivotTable.resumeLayout(); myPivotTable.autoFitColumn(); return myPivotTable; }, 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("Quarters (date)", "Quarters (date)", 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; }, setMarkQtrStyle (pivotTable) { let style = new GC.Spread.Sheets.Style(); style.backColor = "#82bc00"; style.foreColor = "white"; let labelPivotArea = { labelOnly: true, references: [{ fieldName: "Quarters (date)", subtotals: true, items: ["Qtr1", "Qtr3"] }] }; pivotTable.setStyle(labelPivotArea, style); let dataPivotArea = { dataOnly: true, references: [{ fieldName: "Quarters (date)", subtotals: true, items: ["Qtr1", "Qtr3"] }] }; pivotTable.setStyle(dataPivotArea, style); }, 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); }, 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); }, bindEvent(spread) { spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanged, function(e, args) { let panel = GC.Spread.Pivot.PivotPanel.findControl("panel"); let pivotTable = args.newSheet.pivotTables.all()[0]; let panelContainer = document.getElementById("panel"); if (!pivotTable) { panelContainer.style.display = "none"; } else { panelContainer.style.display = "unset"; panel.attach(pivotTable); } }); } }, computed: { dataSource() { return getData(); } } }); new Vue({ render: h => h(App) }).$mount('#app'); </script>
<!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/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/vue/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app.vue'); System.import('$DEMOROOT$/en/lib/vue/license.js'); </script> </head> <body> <div id="app"></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%; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true }, meta: { '*.css': { loader: 'css' }, '*.vue': { loader: 'vue-loader' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', '@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js', '@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js', '@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'jszip': 'npm:jszip/dist/jszip.js', 'css': 'npm:systemjs-plugin-css/css.js', 'vue': 'npm:vue/dist/vue.min.js', 'vue-loader': 'npm:systemjs-vue-browser/index.js', 'tiny-emitter': 'npm:tiny-emitter/index.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'js' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' } } }); })(this);