You can format fields using the Pivot Table Number Format Dialog, which can help you set pivot table styles without API.
For example, in PivotTable.setStyle(), you should first construct the PivotArea of the Field you want to set:
You can then use the format dialog in the field setting dialog. You only need to set a format string like "0.00".
<template>
<div class="sample-tutorial">
<gc-spread-sheets
class="sample-spreadsheets"
@workbookInitialized="initSpread"
>
</gc-spread-sheets>
<div class="options-container">
<div>Field Name:</div>
<select
name="fieldName"
id="fieldName"
class="field-name"
v-model="fieldName"
@change="changeFormatter"
>
<option value="Salesperson" selected>Salesperson</option>
<option value="Cars">Cars</option>
<option value="Quarters (date)">Date</option>
<option value="Quantity">Quantity</option>
</select>
<div>Formatter:</div>
<input
type="text"
class="label-filter-input filter-input"
id="formatter"
v-model="formatter"
/>
<input
type="button"
class="format-button"
value="Set"
id="set"
@click="setFormatter"
/>
<input
type="button"
class="format-button"
value="Reset"
id="reset"
@click="resetFormatter"
/>
</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,
fieldName: "Salesperson",
formatter: "",
};
},
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.getSource(sheet2, pivotSales);
let pivotTable = this.addPivotTable(sheet1, tableName);
this.pivotTable = pivotTable;
spread.resumePaint();
},
getSource: function (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.name();
},
addPivotTable: function (sheet, source) {
sheet.suspendPaint();
sheet.name("PivotTable");
sheet.setRowCount(10000);
let pivotTable = sheet.pivotTables.add(
"PivotTable",
source,
1,
1,
GC.Spread.Pivot.PivotTableLayoutType.outline,
GC.Spread.Pivot.PivotTableThemes.light8
);
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(
"quantity",
"Quantity",
GC.Spread.Pivot.PivotTableFieldType.valueField,
GC.Pivot.SubtotalType.sum
);
let carsStyle = new GC.Spread.Sheets.Style();
carsStyle.formatter = "[red]@";
let valueStyle = new GC.Spread.Sheets.Style();
valueStyle.formatter = "#0.00";
pivotTable.setStyle(this.initArea("Cars", pivotTable), carsStyle);
pivotTable.setStyle(this.initArea("Quantity", pivotTable), valueStyle);
pivotTable.resumeLayout();
sheet.resumePaint();
pivotTable.autoFitColumn();
return pivotTable;
},
initArea: function (fieldName, pt) {
let fieldArea = pt.getField(fieldName).pivotArea;
if (fieldArea === 1 || fieldArea === 2) {
return {
labelOnly: true,
references: [
{
fieldName,
},
],
};
} else if (fieldArea === 3) {
return {
dataOnly: true,
references: [
{
fieldName: "Values",
items: [fieldName],
},
],
};
}
},
setFormatter() {
let area = this.initArea(this.fieldName, this.pivotTable);
let style =
this.pivotTable.getStyle(area) || new GC.Spread.Sheets.Style();
style.formatter = this.formatter;
this.pivotTable.setStyle(area, style);
},
resetFormatter() {
let area = this.initArea(this.fieldName, this.pivotTable);
let style = this.pivotTable.getStyle(area);
let formatter = (style && style.formatter) || "";
this.formatter = formatter;
},
changeFormatter (e) {
let area = this.initArea(e.target.value, this.pivotTable);
let style = this.pivotTable.getStyle(area);
this.formatter = style && style.formatter ? style.formatter : "";
},
bindEvent: function (pivotTable, spread) {
document
.getElementById("set-label-filter")
.addEventListener("click", function (e) {
spread.suspendPaint();
let labelType, labelFilterValue1, labelFilterValue2;
let labelNode = document.getElementById("labelFilter");
labelType = labelNode.selectedIndex;
if (labelType) {
labelFilterValue1 = document.getElementById("label-val1").value;
labelFilterValue2 = document.getElementById("label-val2").value;
if (labelType < 13) {
labelFilterValue2 = null;
}
pivotTable.labelFilter("Salesperson", {
condition: {
conType: GC.Pivot.PivotConditionType.caption,
val: [labelFilterValue1, labelFilterValue2],
operator: labelType - 1,
},
});
} else {
pivotTable.labelFilter("Salesperson", null);
}
spread.resumePaint();
});
document
.getElementById("set-value-filter")
.addEventListener("click", function (e) {
spread.suspendPaint();
let valueType, valueFilterValue1, valueFilterValue2;
let valueNode = document.getElementById("valueFilter");
valueType = valueNode.selectedIndex;
if (valueType) {
valueFilterValue1 = document.getElementById("value-val1").value;
valueFilterValue2 = document.getElementById("value-val2").value;
if (valueType < 7) {
valueFilterValue2 = null;
}
pivotTable.valueFilter("Salesperson", {
condition: {
conType: GC.Pivot.PivotConditionType.value,
val: [valueFilterValue1, valueFilterValue2],
operator: valueType - 1,
},
conditionByName: "Quantity",
});
} else {
pivotTable.valueFilter("Salesperson", null);
}
spread.resumePaint();
});
document
.getElementById("sort-type")
.addEventListener("click", function (e) {
spread.suspendPaint();
if (e.target.classList.contains("sort-asc")) {
pivotTable.sort("Salesperson", { sortType: GC.Pivot.SortType.asc });
} else {
pivotTable.sort("Salesperson", {
sortType: GC.Pivot.SortType.desc,
});
}
spread.resumePaint();
});
},
},
computed: {
dataSource() {
return getData();
},
},
});
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% - 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;
}
.filter-input {
width: 200px;
height: 20px;
display: block;
/* margin-left: 15px; */
margin-top: 10px;
}
.format-button {
width: 45%;
margin-top: 20px;
/* float: right; */
display: inline-block;
}
.field-name {
width: 200px;
height: 25px;
display: block;
margin-bottom: 10px;
float: left;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.whole-field {
margin-bottom: 10px;
}
(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);
