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:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread"> </gc-spread-sheets>
<div id="container" class="options-container">
<div>
<input type="checkbox" id="CalculatedItemAgg" v-model="calcItemAgg" @click="updateAgg()"/>
<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" v-model="sourceFieldName"/>
</div>
<div>
<div class = "select-option-class">Calculated Item Name:</div>
<input type="text" name="" id="calcItemName" class="rowLabelIndent select-option-select" v-model="calcItemName"/>
</div>
<div>
<div class = "select-option-class">Formula:</div>
<input type="text" name="" id="formula" class="rowLabelIndent select-option-select" v-model="formula"/>
</div>
<div>
<div class = "select-option-class">Priority:</div>
<input type="number" min="0" name="" id="priority" class="rowLabelIndent select-option-select" v-model="priority"/>
</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" @click="addCalcItem()"/>
<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" @click="removeCalcItem()"/>
<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" @click="updateCalcItem()"/>
<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" @click="listCalculatedItems()"/>
</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",
data: function () {
return {
spread: null,
pivotTable: null,
sourceFieldName: '',
calcItemName: '',
formula: '',
priority: '',
calcItemAgg: true
};
},
watch: {
sourceFieldName(value) {
this.sourceFieldName = value;
},
formula(value) {
this.formula = value;
},
calcItemName(value) {
this.calcItemName = value;
},
priority(value) {
this.priority = value;
},
calcItemAgg(value) {
this.calcItemAgg = value;
}
},
methods: {
initSpread: function (spread) {
this.spread = spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = this.getDataSource(sheet2, pivotCalcItemData);
let pivotTable = this.initPivotTable(sheet1, tableName);
this.pivotTable = pivotTable;
this.calcItemAgg = pivotTable.options.calcItemAggregation === GC.Spread.Pivot.CalcItemAggregation.exclude ? false : true;
spread.resumePaint();
},
getDataSource: function (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();
},
initPivotTable: function (sheet, source) {
sheet.suspendPaint();
sheet.name("PivotTable");
sheet.setRowCount(100);
sheet.setColumnCount(20);
let option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true,
};
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"]
}]
}
pivotTable.options.calcItemAggregation = GC.Spread.Pivot.CalcItemAggregation.exclude;
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;
},
addCalcItem: function () {
let pivotTable = this.pivotTable;
pivotTable.suspendLayout();
let fieldName = this.sourceFieldName;
let calcItemName = this.calcItemName;
let formula = this.formula;
if (isNullOrUndefined(fieldName) || isNullOrUndefined(calcItemName) || isNullOrUndefined(formula)) {
return;
}
pivotTable.addCalcItem(fieldName, calcItemName, formula);
pivotTable.resumeLayout();
},
removeCalcItem: function () {
let pivotTable = this.pivotTable;
pivotTable.suspendLayout();
let fieldName = this.sourceFieldName;
let calcItemName = this.calcItemName;
if (isNullOrUndefined(fieldName)) {
return;
}
pivotTable.removeCalcItem(fieldName, calcItemName);
pivotTable.resumeLayout();
},
updateCalcItem: function () {
let pivotTable = this.pivotTable;
pivotTable.suspendLayout();
let fieldName = this.sourceFieldName;
let calcItemName = this.calcItemName;
let formula = this.formula;
let priority = this.priority;
if (isNullOrUndefined(fieldName) || isNullOrUndefined(calcItemName)) {
return;
}
pivotTable.updateCalcItem(fieldName, calcItemName, formula, +priority);
pivotTable.resumeLayout();
},
updateAgg: function () {
let pivotTable = this.pivotTable;
pivotTable.suspendLayout();
pivotTable.options.calcItemAggregation = this.calcItemAgg ? 1 : 0;
pivotTable.resumeLayout();
},
listCalculatedItems: function () {
let spread = this.spread, pivotTable = this.pivotTable;
spread.suspendPaint();
var sheet = new GC.Spread.Sheets.Worksheet();
let name = "CalcItemList";
let count = 1;
while (spread.getSheetFromName(name + count)) {
count++;
}
sheet.name(name + count);
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());
spread.resumePaint();
},
},
computed: {
dataSource() {
return getData();
},
},
});
function isNullOrUndefined (value) {
if (value === undefined || value === null) {
return true;
}
return false;
}
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%;
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;
}
(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);
