SpreadJS PivotTables provide the ShowDataAs API to quickly present values in different ways.
There are 14 kinds of ShowDataAs options are available.
Show Data As name
Enum Value
Show Data As Info Required Properties
No Calculation
normal
showDataAs
% of Grand Total
percentOfTotal
showDataAs
% of Column Total
percentOfRow
showDataAs
% of Row Total
percentOfCol
showDataAs
% Of
percent
showDataAs, baseFieldName, baseFieldItemType, baseFieldItem?
% of Parent Row Total
percentOfParentRow
showDataAs
% of Parent Column Total
percentOfParentCol
showDataAs
% of Parent Total
percentOfParent
showDataAs, baseFieldName
Difference From
difference
showDataAs, baseFieldName, baseFieldItemType, baseFieldItem?
% Difference From
percentDiff
showDataAs, baseFieldName, baseFieldItemType, baseFieldItem?
Running Total in
runTotal
showDataAs, baseFieldName
% Running Total in
percentOfRunningTotal
showDataAs, baseFieldName
Rank Smallest to Largest
rankAscending
showDataAs, baseFieldName
Rank Largest to Smallest
rankDescending
showDataAs, baseFieldName
Index
index
showDataAs
Sample:
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { FormsModule } from '@angular/forms';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import './styles.css';
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
hostStyle = {
width: 'calc(100% - 300px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
spread: any;
pt: any;
showValueAs = "0";
showFieldPanel: boolean;
showItemPanel: boolean;
showValueAsDialogLabel: string;
baseField: any;
baseItems: any;
baseFields: any[];
baseItem: any;
constructor() {
}
initSpread($Event: any) {
let spread = $Event.spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = this.getDataSource(sheet2, pivotSales);
let pivotTable = this.initPivotTable(sheet1, tableName);
this.pt = pivotTable;
this.spread = spread;
this.showFieldPanel = false;
this.showItemPanel = false;
spread.resumePaint();
}
getDataSource(sheet: GC.Spread.Sheets.Worksheet, tableSource: any) {
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();
}
initPivotTable(sheet: GC.Spread.Sheets.Worksheet, source: any) {
sheet.name("ShowDataAs");
sheet.setRowCount(1000);
let option = {
showRowHeader:true,
showColumnHeader:true,
bandRows:true,
bandColumns:true
};
let pivotTable = sheet.pivotTables.add("pivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8,option);
pivotTable.suspendLayout();
pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
pivotTable.group(groupInfo);
pivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
return pivotTable;
}
onShowValueAsChange(event: any) {
let selectIndex = this.showValueAs;
this.showFieldPanel = false;
this.showItemPanel = false;
this.showValueAsDialogLabel = "";
if (["4", "7", "8", "9", "10", "11", "12", "13"].indexOf(selectIndex) > -1) {
this.showFieldPanel = true;
this.baseFields = [...this.pt.getFieldsByArea(1), ...this.pt.getFieldsByArea(2)];
this.baseField = "0";
this.showValueAsDialogLabel = "Calculation: " + event.target.selectedOptions[0].text;
if (["4", "8", "9"].indexOf(selectIndex) > -1) {
this.showItemPanel = true;
let text = this.baseFields[0].fieldName;
this.baseItems = [...this.pt.getItemsByField(text)];
this.baseItems.unshift("next");
this.baseItems.unshift("previous");
this.baseItem = "0";
}
}
}
onBaseFieldChange(event: any) {
this.baseItems = [...this.pt.getItemsByField(this.baseFields[parseInt(this.baseField)].fieldName)];
this.baseItems.unshift("next");
this.baseItems.unshift("previous");
this.baseItem = "0";
}
applyShowDataAsStyle(pivotTable: GC.Spread.Pivot.PivotTable, fieldName: string, showValueAsType: GC.Pivot.PivotShowDataAs) {
let style, valueFieldArea = {
dataOnly: true,
references: [{
fieldName: "Values",
items: [fieldName]
}]
};
style = pivotTable.getStyle(valueFieldArea);
if (!style) {
style = new GC.Spread.Sheets.Style();
}
let needApplyStyle = [GC.Pivot.PivotShowDataAs.percentOfTotal, GC.Pivot.PivotShowDataAs.percentOfRow, GC.Pivot.PivotShowDataAs.percentOfCol, GC.Pivot.PivotShowDataAs.percent, GC.Pivot.PivotShowDataAs.percentOfParentRow,
GC.Pivot.PivotShowDataAs.percentOfParentCol, GC.Pivot.PivotShowDataAs.percentOfParent, GC.Pivot.PivotShowDataAs.percentDiff, GC.Pivot.PivotShowDataAs.percentOfRunningTotal].indexOf(showValueAsType) > -1;
if (needApplyStyle) {
style.formatter = "0.00%";
} else {
style = null;
}
pivotTable.setStyle(valueFieldArea, style);
}
applySetting(event: any) {
let showValueAsIndex = this.showValueAs;
let fieldName = this.pt.getFieldsByArea(3)[0].fieldName, showDataAsType = parseInt(showValueAsIndex, 10), baseFieldName;
if (this.baseFields && this.baseFields[this.baseField]) {
baseFieldName = this.baseFields[this.baseField].fieldName;
}
if (["4", "8", "9"].indexOf(showValueAsIndex) > -1) {
let baseIndex = this.baseItem;
let baseFieldItem = this.baseItems[this.baseItem];
if (baseIndex === "0") {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName, baseFieldItemType: 2 });
} else if (baseIndex === "1") {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName, baseFieldItemType: 1 });
} else {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName, baseFieldItemType: 0, baseFieldItem: baseFieldItem });
}
} else if (["7", "10", "11", "12", "13"].indexOf(this.showValueAs) > -1) {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName });
} else {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType });
}
this.applyShowDataAsStyle(this.pt, fieldName, showDataAsType);
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule, FormsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
<gc-worksheet>
</gc-worksheet>
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container" id="container">
<div class="option-row">
<label>ShowValueAs:</label>
<select id="showValueAs" ngDefaultControl [(ngModel)]="showValueAs" (change)="onShowValueAsChange($event)">
<option value="0" >No Calculation</option>
<option value="1">% of Grand Total</option>
<option value="2">% of Column Total</option>
<option value="3">% of Row Total</option>
<option value="4">% Of ...</option>
<option value="5">% of Parent Row Total</option>
<option value="6">% of Parent Column Total</option>
<option value="7">% of Parent Total ...</option>
<option value="8">Difference From ...</option>
<option value="9">% Of Difference From ...</option>
<option value="10">Running Total In ...</option>
<option value="11">% Running Total In ...</option>
<option value="12">Rank Smallest to Largest ...</option>
<option value="13">Rank Largest to Smallest ...</option>
<option value="14">Index</option>
</select>
</div>
<label style="padding: 5px; margin-top: 10px;" id="showValueAsDialogLabel" ngDefaultControl>{{showValueAsDialogLabel}}</label>
<div class="option-row" id="showValueAsBaseFieldPanel" ngDefaultControl *ngIf="showFieldPanel">
<label id="showValueAsBaseFieldLabel">Base Field:</label>
<select id="showValueAsBaseField" ngDefaultControl [(ngModel)]="baseField" (change)="onBaseFieldChange($event)">
<option ngDefaultControl *ngFor="let field of baseFields let i = index" value={{i}}>{{field.fieldName}}</option>
</select>
</div>
<div class="option-row" id="showValueAsBaseItemPanel" ngDefaultControl *ngIf="showItemPanel">
<label id="showValueAsBaseItemLabel">Base Item:</label>
<select id="showValueAsBaseItem" ngDefaultControl [(ngModel)]="baseItem">
<option ngDefaultControl *ngFor="let item of baseItems let i = index" value={{i}}>{{item}}</option>
</select>
</div>
<div class="option-row">
<input type="button" value="Apply Setting" id="applySetting" (click)="applySetting($event)" />
</div>
</div>
</div>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
p{
padding:2px 10px;
background-color:#F4F8EB;
}
input, select {
width: 100%;
padding: 4px 6px;
box-sizing: border-box;
}
label {
display:block;
margin-bottom: 6px;
}
input[type="checkbox"], input[type="radio"] {
display: inline-block;
width: auto;
}
input[type="checkbox"]+label, input[type="radio"]+label {
display: inline-block;
}
input[type="button"] {
display: block;
margin: 0 0 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js',
'@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-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
