Pivot Node Info
To edit the value of a PivotTable, the first step is to describe the cell.
We defined an interface called PivotNodeInfo which can describe the field and value info of a pivot table node.
Overwrite Value
Overwriting values in a pivot table will cause those values to be updated in the calculation.
This only supports [ sum, count, countNums, max, min ] types to include in calculation. Other types of subtotals will only change that subtotal value
If the overwritten value is a subtotal, its children nodes value will not change and its parent nodes will use its overwritten value to calculate directly, rather than calculating the result of its children nodes.
Updating the source will remove the values that would be set
API List
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { FormsModule } from '@angular/forms';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
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 {
spread: GC.Spread.Sheets.Workbook;
detailsSpread: GC.Spread.Sheets.Workbook;
hostStyle = {
height: '100%',
width: '100%',
overflow: 'hidden',
};
pivotTable: GC.Spread.Pivot.PivotTable;
table: GC.Spread.Sheets.Tables.Table;
sourceSheet: GC.Spread.Sheets.Worksheet;
enableDataValueEditing = true;
nodeInfo: string;
nodeValue: number;
constructor() {
}
initSpreadDetails($event: any) {
this.detailsSpread = $event.spread;
let detailsSpread = this.detailsSpread;
let spread = this.spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = this.getDataSource(sheet1, pivotSales);
this.pivotTable = this.initPivotTable(sheet2, tableName);
spread.setActiveSheet("PivotTable");
let detailsSheet = detailsSpread.getSheet(0);
detailsSheet.name("PivotTable Details");
this.addEvent(sheet2, detailsSheet, this.pivotTable);
spread.resumePaint();
}
initSpread($event: any) {
this.spread = $event.spread;
}
addEvent(sheet2: GC.Spread.Sheets.Worksheet, detailsSheet: GC.Spread.Sheets.Worksheet, pivotTable: GC.Spread.Pivot.PivotTable) {
detailsSheet.setRowCount(700);
detailsSheet.setColumnCount(10);
detailsSheet.addSpan(0, 0, 2, 6);
let style = new GC.Spread.Sheets.Style();
style.wordWrap = true;
detailsSheet.setStyle(0, 0, style);
detailsSheet.setValue(0, 0, "In PivotTable of SpreadJS, pivot table show detail will find all merged data in data source");
sheet2.addSpan(0, 0, 2, 5);
sheet2.setStyle(0, 0, style);
sheet2.setValue(0, 0, "You Can Edit Cell Values in Data Area.\nuncheck the 'Enable Edit Value in Data Area' to close edit ablilty");
sheet2.bind(GC.Spread.Sheets.Events.CellClick, (sender, args) => {
this.nodeInfo = "";
this.updateDetailSheet(detailsSheet, args.row, args.col, pivotTable);
});
}
getPivotDetails(row: number, col: number, pivotTable: GC.Spread.Pivot.PivotTable) {
let pivotInfo = pivotTable.getPivotInfo(row, col), detailsObj: any[] = [];
if (!pivotInfo || pivotInfo.area !== 4) {
return void 0;
}
let colInfo = pivotInfo.colInfos;
let rowInfo = pivotInfo.rowInfos;
this.setDetails(colInfo, detailsObj);
this.setDetails(rowInfo, detailsObj);
let dataSource = pivotTable.getPivotDetails(detailsObj);
this.updateNodeInfo(row, col);
return dataSource;
}
updateNodeInfo(row: number, col: number) {
let nodeInfo = this.pivotTable.getNodeInfo(row, col);
if (!nodeInfo) {
return;
}
this.nodeInfo = JSON.stringify(nodeInfo, null, 4);
this.nodeValue = this.pivotTable.getNodeValue(nodeInfo);
this.enableDataValueEditing = this.pivotTable.options.enableDataValueEditing;
}
setDetails(rowOrColInfo: any, detailsObj: any) {
if (rowOrColInfo && rowOrColInfo.length > 0) {
for (let item of rowOrColInfo) {
if (item.isGrandTotal) {
break;
}
detailsObj.push({ fieldName: item.fieldName, fieldItem: item.itemName });
}
}
}
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);
this.table = table;
this.sourceSheet = sheet;
return table.name();
}
initPivotTable(sheet: GC.Spread.Sheets.Worksheet, tableName: string) {
sheet.name("PivotTable");
sheet.setRowCount(1000);
let option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true,
enableDataValueEditing: true
};
let pivotTable = sheet.pivotTables.add("pivotTable", tableName, 2, 0, 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)", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let style = new GC.Spread.Sheets.Style();
style.formatter = "$ #,##0";
pivotTable.setStyle({ dataOnly: true }, style);
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
return pivotTable;
}
setNodeValue() {
if (!this.nodeInfo) {
return;
}
let nodeInfo = JSON.parse(this.nodeInfo);
let nodeValue = this.nodeValue;
if (nodeInfo && nodeValue !== undefined && nodeValue !== null && !isNaN(nodeValue)) {
this.pivotTable.setNodeValue(nodeInfo, nodeValue);
}
}
changeOption() {
this.pivotTable.options.enableDataValueEditing = !this.pivotTable.options.enableDataValueEditing;
this.enableDataValueEditing = this.pivotTable.options.enableDataValueEditing;
}
updateDetailSheet (detailsSheet: any, row: number, col: number, pivotTable: any) {
detailsSheet.suspendPaint();
let dataSource = this.getPivotDetails(row, col, pivotTable);
detailsSheet.tables.remove("details");
if (dataSource) {
var table = detailsSheet.tables.add('details', 2, 0, dataSource.length, dataSource[0].length);
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
}
detailsSheet.getCell(-1, 0).formatter("YYYY-mm-DD");
detailsSheet.getRange(-1, 4, 0, 2).formatter("$ #,##0");
detailsSheet.setColumnWidth(0, 120);
detailsSheet.setArray(2, 0, dataSource);
detailsSheet.resumePaint();
}
syncValue () {
let pivotTable = this.pivotTable, table = this.table, sheet = this.sourceSheet;
let list = pivotTable.getOverwriteList();
let tableRange = table.dataRange();
sheet.clearFormula(tableRange.row, 5, tableRange.rowCount, 1, function () {return true});
if (list && list.length > 0) {
let columnNames: string[] = [];
for (let i = tableRange.col; i < tableRange.col + tableRange.colCount; i ++) {
columnNames[i] = table.getColumnName(i);
}
let filter = table.rowFilter();
let compareType = GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo;
list.forEach((info: any) => {
let fieldInfos = info.fieldInfos;
let details = pivotTable.getPivotDetails(fieldInfos);
fieldInfos.forEach((fieldInfo: any) => {
let expected = fieldInfo.fieldItem;
let ptField = pivotTable.getField(fieldInfo.fieldName);
let fieldType = ptField.dataType;
let sourceName = ptField.sourceName;
let columnIndex = columnNames.indexOf(sourceName);
if (fieldType === GC.Pivot.PivotDataType.date) {
for (let i = 1; i < details.length; i ++) {
let dateValue = GC.Spread.Sheets.CellTypes.Base.prototype.format(new Date(details[i][columnIndex]), "YYYY-mm-DD");
let condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {compareType: compareType, expected: dateValue});
filter.addFilterItem(columnIndex, condition);
}
} else {
filter.addFilterItem(columnIndex, new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {compareType: compareType, expected: expected}));
}
});
filter.filter();
let valueColIndex = columnNames.indexOf((info.valueInfo.sourceName));
let leftRowIndexes = [];
for (let i = tableRange.row; i < tableRange.row + tableRange.rowCount; i ++) {
if (!filter.isRowFilteredOut(i)) {
leftRowIndexes.push(i);
}
}
let avgValue = info.value / leftRowIndexes.length;
for (let i = 0; i < leftRowIndexes.length; i ++) {
sheet.setValue(leftRowIndexes[i], valueColIndex, avgValue);
}
filter.reset();
})
}
pivotTable.updateSource();
let detailsSheet = this.detailsSpread.getSheet(0), ptSheet = this.spread.getSheet(1);
this.updateDetailSheet(detailsSheet, ptSheet.getActiveRowIndex(), ptSheet.getActiveColumnIndex(), pivotTable);
}
}
@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="sjss">
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
</gc-spread-sheets>
</div>
<div class="sample-tutorial-details">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpreadDetails($event)">
</gc-spread-sheets>
</div>
</div>
<div class="sample-panel">
<div id="config">
<h2>Overwrite Panel</h2>
<div class="option node">
<label class="control-label">
<input id="enableDataValueEditing" type="checkbox" style="display: none;" [checked]="enableDataValueEditing === true" (change)="changeOption()" />
<div class="check"></div>
<div class="circle"></div>
</label>
<label for="enableDataValueEditing" class="label-text">Enable Edit Value in Data Area</label>
</div>
<br>
<div class="node">
<div class="textarea-block">
<span>Active Cell Node Info:</span>
<textarea name="nodeInfo" id="nodeInfo" cols="20" rows="10" [(ngModel)]="nodeInfo"></textarea>
</div>
<div class="label-block">
<span>Node Value:</span>
<input type="number" name="nodeValue" id="nodeValue" placeholder="value" [(ngModel)]="nodeValue">
</div>
<button id="setNodeValue" (click)="setNodeValue()">SET</button>
</div>
<div class="sync-value">
<h3>Notice:</h3>
<div>
"Sync Value" will sync changing data back to the orginal data table by average.
</div>
<div>
The PivotTable will update source and the overwrite info will be cleared.
</div>
<button id="syncValue" (click)="syncValue()">Sync Value</button>
</div>
</div>
</div>
.sjss {
width: 70%;
height: 100%;
display: inline-block;
}
.sample-tutorial {
position: relative;
height: 58%;
width: 100%;
}
.sample-tutorial-details {
position: relative;
width: 100%;
height: 40%;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
overflow: hidden;
}
#config {
width: 75%;
margin-left: 10%;
}
.sample-panel {
position: relative;
float: right;
width: 30%;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow-y: auto;
overflow-x: hidden;
}
#app {
height: 100%;
}
label {
position: relative;
}
.check {
width: 2rem;
height: 1rem;
border-radius: 100rem;
border: 1px solid #a19b9b;
transition: .3s;
}
.circle {
width: 1rem;
height: 1rem;
border-radius: 50%;
background: #a19b9b;
position: absolute;
left: 1px;
top: 1px;
transform: translateX(0rem);
transition: .3s;
}
input:checked~.check {
background: #0099CC;
transition: .3s;
border-color: #0099CC;
}
input:checked~.circle {
transform: translateX(1rem);
transition: .3s;
background: #EEEEEE;
}
.control-label {
display: inline-block;
top: 3px;
}
#config h2 {
width: 100%;
text-align: center;
}
.label-text {
user-select: none;
line-height: 20px;
white-space: nowrap;
}
.option {
font-size: 15px;
vertical-align: middle;
white-space: nowrap;
}
.node {
width: 100%;
position: relative;
}
.textarea-block textarea {
outline: none;
resize: none;
border: none;
width: 100%;
border-top: 1px solid #0099CC;
font-family: 'Calibri', 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
background-color: transparent;
}
#config button {
line-height: 18px;
padding: 8px 4px;
font-size: 13px;
border: 1px solid #0099CC;
cursor: pointer;
background: transparent;
color: #0099CC;
text-align: center;
-webkit-transition-duration: 0.3s;
/* Safari */
transition-duration: 0.3s;
text-decoration: none;
text-transform: uppercase;
}
#config button:hover {
background-color: #008CBA;
color: white;
}
#setNodeValue {
position: absolute;
width: 30%;
right: 0;
}
.label-block {
position: absolute;
left: 0;
border: 1px solid #0099CC;
padding: 8px 4px;
color: #0099CC;
width: 50%;
white-space: nowrap;
font-size: 13px;
overflow: hidden;
line-height: 18px;
}
.label-block input {
display: inline-block;
border: none;
outline: none;
width: 48%;
}
.label-block span {
display: inline-block;
width: 48%;
}
.textarea-block {
display: block;
margin-bottom: 10px;
margin-top: 10px;
padding: 8px 8px;
color: #0e84ab;
border: 1px solid #0099CC;
}
input[type=number]::-webkit-inner-spin-button,
input[type=number]::-webkit-outer-spin-button {
-webkit-appearance: none;
margin: 0;
}
input[type=number] {
-moz-appearance: textfield;
}
#syncValue {
display: inline-block;
margin-left: 25%;
margin-top: 10px;
width: 50%;
}
.sync-value {
margin-top: 60px;
}
(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);
