PivotTable offers the ability to add a calculated field item with a formula
It also supports removing calculated items.
You can modify a calculated item by modifying the formula or priority of a calculated item.
You can get information about all the Calculated items on this field by a Source Field Name.
If you don't want to provide the name, then you will get all the information of the calculated item of the field
You can decide whether the calculated values should be merged into the subtotal and grandTotal of the PivotTable.
This is an option for PivotTable with the following data types:
The GC.Spread.Pivot.CalcItemAggregation type is defined as follows:
window.onload = function () {
let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2});
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getDataSource(sheet2, pivotCalcItemData);
let pivotTable = initPivotTable(sheet1, tableName);
initUI(pivotTable);
bindEvent(spread, pivotTable);
spread.resumePaint();
}
function getDataSource(sheet, tableSource){
sheet.name("DataSource");
sheet.setRowCount(100);
sheet.setColumnWidth(0, 120);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1,5,0,3).formatter("$ #,##0");
let table = sheet.tables.add('table', 0, 0, 81, 8);
sheet.setArray(0, 0, tableSource);
return table.name();
}
function initPivotTable(sheet, source){
sheet.suspendPaint();
sheet.name("PivotTable");
sheet.setRowCount(100);
sheet.setColumnCount(20);
let option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true,
calcItemAggregation: GC.Spread.Pivot.CalcItemAggregation.exclude
};
let pivotTable = sheet.pivotTables.add("PivotTable", source, 4, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light16, option);
pivotTable.suspendLayout();
pivotTable.add("OrderStatus", "Order Status", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Product", "Product", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Total", "Total", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.addCalcItem("OrderStatus", "Outstanding orders","=OrderStatus[Canceled] + OrderStatus[Backorder] + OrderStatus[Pending]");
pivotTable.addCalcItem("OrderStatus", "Total order", "=OrderStatus['Outstanding orders'] + OrderStatus[Shipped]");
pivotTable.addCalcItem("OrderStatus", "Order Proportion", "=OrderStatus['Outstanding orders']/OrderStatus['Total order']");
let groupInfo = { originFieldName: "OrderStatus", textGroup: {
fieldName: "Order Analysis",
groupItems:{
"Raw Data": ["Shipped", "Pending", "Backorder", "Canceled"],
"Analysis Results": ["Outstanding orders", "Total order", "Order Proportion"]
}
} };
pivotTable.group(groupInfo);
pivotTable.add("Order Analysis", "Order Analysis", GC.Spread.Pivot.PivotTableFieldType.rowField, null, 0);
let dataArea = {
dataOnly: true,
references:[{
fieldName: "Values",
items: ["Total"]
}]
}
let style = new GC.Spread.Sheets.Style();
style.formatter = "0.00";
pivotTable.setStyle(dataArea, style);
sheet.setColumnWidth(1, 150);
sheet.addSpan(0,2,1,7);
sheet.addSpan(1,2,1,7);
sheet.addSpan(2,2,1,7);
sheet.setValue(0, 1, "Outstanding orders");
sheet.setValue(1, 1, "Total order");
sheet.setValue(2, 1, "Order Proportion");
sheet.setValue(0, 2, "The total number of orders in each product that have not yet been delivered");
sheet.setValue(1, 2, "Total amount of all orders for each product");
sheet.setValue(2, 2, "The ratio of the total of all undelivered orders to the total of all orders for each product");
pivotTable.resumeLayout();
sheet.resumePaint();
pivotTable.autoFitColumn();
return pivotTable;
}
function bindEvent(spread, pivotTable){
document.getElementById("add-calc-item").addEventListener("click", function(event){
pivotTable.suspendLayout();
let fieldName = document.getElementById("fieldName").value;
let calcItemName = document.getElementById("calcItemName").value;
let formula = document.getElementById("formula").value;
if (isNullOrUndefined(fieldName) || isNullOrUndefined(calcItemName) || isNullOrUndefined(formula)) {
return;
}
pivotTable.addCalcItem(fieldName, calcItemName, formula);
pivotTable.resumeLayout();
});
document.getElementById("remove-calc-item").addEventListener("click", function(event){
pivotTable.suspendLayout();
let fieldName = document.getElementById("fieldName").value;
let calcItemName = document.getElementById("calcItemName").value;
if (isNullOrUndefined(fieldName)) {
return;
}
pivotTable.removeCalcItem(fieldName, calcItemName);
pivotTable.resumeLayout();
});
document.getElementById("update-calc-item").addEventListener("click", function(event){
pivotTable.suspendLayout();
let fieldName = document.getElementById("fieldName").value;
let calcItemName = document.getElementById("calcItemName").value;
let formula = document.getElementById("formula").value || null;
let priority = document.getElementById("priority").value;
if (isNullOrUndefined(fieldName) || isNullOrUndefined(calcItemName)) {
return;
}
pivotTable.updateCalcItem(fieldName, calcItemName, formula, +priority);
pivotTable.resumeLayout();
});
document.getElementById("CalculatedItemAgg").addEventListener("click", function(event){
pivotTable.suspendLayout();
let status = event.target.checked;
pivotTable.options.calcItemAggregation = status ? 0 : 1;
pivotTable.resumeLayout();
});
document.getElementById("list-calc-items").addEventListener("click", function(event){
spread.suspendPaint();
var sheet = new GC.Spread.Sheets.Worksheet();
var sheetCount = spread.getSheetCount();
spread.addSheet(sheetCount, sheet);
sheet.setColumnWidth(0,150);
sheet.setColumnWidth(1,200);
sheet.setColumnWidth(2,200);
sheet.setColumnWidth(3,100);
sheet.setValue(0, 0, "Source Field Name");
sheet.setValue(0, 1, "Calculated Item Name");
sheet.setValue(0, 2, "Formula");
sheet.setValue(0, 3, "Priority");
var calcItemsInfos = pivotTable.getCalcItems();
for (var i = 0; i < calcItemsInfos.length; i++) {
sheet.setValue(i + 1, 0, calcItemsInfos[i].sourceName);
sheet.setValue(i + 1, 1, calcItemsInfos[i].calcItemName);
sheet.setValue(i + 1, 2, calcItemsInfos[i].formula);
sheet.setValue(i + 1, 3, calcItemsInfos[i].priority);
}
spread.setActiveSheet(sheet.name());
let name = "CalcItemList";
let count = 1;
while (spread.getSheetFromName(name + count)) {
count++;
}
sheet.name(name + count);
spread.resumePaint();
});
}
function isNullOrUndefined (value) {
if (value === undefined || value === null) {
return true;
}
return false;
}
function initUI (pivotTable) {
let aggDom = document.getElementById("CalculatedItemAgg");
aggDom.checked = pivotTable.options.calcItemAggregation === GC.Spread.Pivot.CalcItemAggregation.exclude ? false : true;
}
<!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/data.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 id="ss" class="sample-spreadsheets"></div>
<div id="container" class="options-container">
<div>
<input type="checkbox" id="CalculatedItemAgg"/>
<label for="CalculatedItemAgg">Whether to aggregate the values of the Calculated item value the total</label>
</div>
<div>
<div class = "select-option-class">Source Field Name:</div>
<input type="text" name="" id="fieldName" class="missingCaption select-option-select"/>
</div>
<div>
<div class = "select-option-class">Calculated Item Name:</div>
<input type="text" name="" id="calcItemName" class="rowLabelIndent select-option-select"/>
</div>
<div>
<div class = "select-option-class">Formula:</div>
<input type="text" name="" id="formula" class="rowLabelIndent select-option-select"/>
</div>
<div>
<div class = "select-option-class">Priority:</div>
<input type="number" min="0" name="" id="priority" class="rowLabelIndent select-option-select"/>
</div>
<hr />
<div>
<label class = "select-option-class">- To add a Calculated Item enter Source Field Name， CalcItemName and Formula and click the below button.</label>
<label class = "select-option-class"><u>For Example</u>: Add <b>Product</b> to source field name, and <b>all pens</b> to calculated item name, and <b>=Product[Pen]+Product[Pencil]</b> to formula, to have a calculated item for 'all pens'. You can find the inserted item in the current field.</label>
</div>
<input type="button" value="Add Calculated Item" class="set-option" id="add-calc-item" />
<div>
<label class = "select-option-class">- To remove a Calculated Item, enter the name in the Source Field Name box and enter the name in the Calculated Item Name box click the below button.</label>
<label class = "select-option-class"><u>For Example</u>: Add <b>OrderStatus</b> to source field name, and <b>Order Proportion</b> to calculated item name</label>
</div>
<input type="button" value="Remove Calculated Item" class="set-option" id="remove-calc-item" />
<div>
<label class = "select-option-class">- To update a Calculated Item, You can modify a Calculated Item by modifying the formula and priority</label>
<label class = "select-option-class"><u>For Example</u>: Add <b>OrderStatus</b> to source field name, and <b>Order Proportion</b> to calculated item name, and <b>=OrderStatus['Total order']/OrderStatus['Outstanding orders']</b> to formula, and <b>2</b> to priority</label>
</div>
<input type="button" value="Update Calculated Item" class="set-option" id="update-calc-item" />
<div>
<label class = "select-option-class">- Click the List Calculated Items to see the list of all Calculated Items that have been created.</label>
</div>
<input type="button" value="List Calculated Items" class="set-option" id="list-calc-items" />
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 300px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 300px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.set-option {
display: block;
margin-top: 20px;
width: 250px;
}
#reportFilterFieldsPerColumn {
width: 28px;
}
.select-option-class{
display: block;
margin-top: 10px;
margin-bottom: 10px
}
.select-option-select{
width: 250px;
display: block;
margin-bottom: 20px;
}
.option-item{
display: block;
height: 20px;
margin-bottom: 20px;
}
