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
window.onload = function () {
let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2});
let detailsSpread = new GC.Spread.Sheets.Workbook(document.getElementById("pivot-details"), {sheetCount: 1});
initSpread(spread, detailsSpread);
};
function initSpread(spread, detailsSpread) {
spread.suspendPaint();
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let table = getDataSource(sheet1, pivotSales);
let pivotTable = initPivotTable(sheet2, table.name());
spread.setActiveSheet("PivotTable");
let detailsSheet = detailsSpread.getSheet(0);
detailsSheet.name("PivotTable Details");
addEvent(sheet2, detailsSheet, pivotTable, table);
spread.resumePaint();
window.sheet = sheet1;
window.table = table;
window.pt = pivotTable;
}
function addEvent(sheet2, detailsSheet, pivotTable, table) {
detailsSheet.setRowCount(700);
detailsSheet.setColumnCount(10);
detailsSheet.addSpan(0, 0, 2, 5);
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) => {
_getElementById("nodeInfo").innerHTML = "";
updateDetailSheet(detailsSheet, args.row, args.col, pivotTable);
});
_getElementById("setNodeValue").addEventListener("click", () => {
let value = parseInt(_getElementById("nodeValue").value, 10);
let nodeInfo = _getElementById("nodeInfo").innerHTML;
if (value !== undefined && value !== null && !isNaN(value) && nodeInfo) {
nodeInfo = JSON.parse(nodeInfo);
if (nodeInfo) {
pivotTable.setNodeValue(nodeInfo, value);
}
}
});
_getElementById("enableDataValueEditing").addEventListener("change", (e) => {
pivotTable.options.enableDataValueEditing = !!e.target.checked;
});
_getElementById("syncValue").addEventListener("click", (e) => {
syncValue(pivotTable, table, sheet2.parent.getSheet(0))
_getElementById("nodeValue").value = "";
updateDetailSheet(detailsSheet, sheet2.getActiveRowIndex(), sheet2.getActiveColumnIndex(), pivotTable);
});
}
function updateDetailSheet (detailsSheet, row, col, pivotTable) {
detailsSheet.suspendPaint();
let dataSource = 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();
}
function syncValue (pivotTable, table, sheet) {
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 = [];
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) => {
let fieldInfos = info.fieldInfos;
let details = pivotTable.getPivotDetails(fieldInfos);
fieldInfos.forEach((fieldInfo) => {
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();
}
function getPivotDetails(row, col, pivotTable) {
let pivotInfo = pivotTable.getPivotInfo(row, col),
detailsObj = [];
if (! pivotInfo || pivotInfo.area !== 4) {
return void 0;
}
let colInfo = pivotInfo.colInfos;
let rowInfo = pivotInfo.rowInfos;
setDetails(colInfo, detailsObj);
setDetails(rowInfo, detailsObj);
let dataSource = pivotTable.getPivotDetails(detailsObj);
updateNodeInfo(pivotTable, row, col);
return dataSource;
}
function updateNodeInfo (pivotTable, row, col) {
let nodeInfo = pivotTable.getNodeInfo(row, col);
if (!nodeInfo) {
return;
}
_getElementById("nodeInfo").innerHTML = JSON.stringify(nodeInfo, null, 4);
_getElementById("nodeValue").value = pivotTable.getNodeValue(nodeInfo) || "";
_getElementById("enableDataValueEditing").checked = pivotTable.options.enableDataValueEditing;
}
function setDetails(rowOrColInfo, detailsObj) {
if (rowOrColInfo && rowOrColInfo.length > 0) {
for (let item of rowOrColInfo) {
if (item.isGrandTotal) {
break;
}
detailsObj.push({fieldName: item.fieldName, fieldItem: item.itemName});
}
}
}
function getDataSource(sheet, tableSource) {
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;
}
function initPivotTable(sheet, tableName) {
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;
}
function _getElementById (id) {
return document.getElementById(id);
}
<!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/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivot-data.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 class="sjss">
<div id="ss" class="sample-spreadsheets"></div>
<div id="pivot-details" class="sample-spreadsheets-details"></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>
<div class="check"></div>
<div class="circle"></div>
</label>
<label for="enableDataValueEditing" class="label-text">Enable Edit Value in Data Area</label>
</div>
<div class="node">
<div class="textarea-block">
<span>Active Cell Node Info:</span>
<textarea name="nodeInfo" id="nodeInfo" cols="20" rows="10"></textarea>
</div>
<div class="label-block">
<span>Node Value:</span>
<input type="number" name="nodeValue" id="nodeValue" placeholder="value">
</div>
<button id="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">Sync Value</button>
</div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
}
.sjss {
width: 70%;
height: 100%;
display: inline-block;
}
.sample-spreadsheets {
width: 100%;
height: 58%;
overflow: hidden;
}
.sample-spreadsheets-details{
width: 100%;
height: 40%;
overflow: hidden;
}
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%;
margin-top: 0;
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-size: 12px;
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;
}
