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="fieldNameRef"
@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="formatterRef"
/>
<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 setup>
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-vue";
import { shallowRef } from "vue";
import "@mescius/spread-sheets-pivot-addon";
let spreadRef = shallowRef(null);
let pivotTableRef = shallowRef(null);
let fieldNameRef = shallowRef("Salesperson");
let formatterRef = shallowRef("");
async function initSpread (spread) {
spreadRef.value = spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getSource(sheet2, pivotSales);
let pivotTable = addPivotTable(sheet1, tableName);
pivotTableRef.value = pivotTable;
spread.resumePaint();
}
function getSource (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();
}
function addPivotTable (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(initArea("Cars", pivotTable), carsStyle);
pivotTable.setStyle(initArea("Quantity", pivotTable), valueStyle);
pivotTable.resumeLayout();
sheet.resumePaint();
pivotTable.autoFitColumn();
return pivotTable;
}
function initArea (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],
},
],
};
}
}
function setFormatter() {
let area = initArea(fieldNameRef.value, pivotTableRef.value);
let style =
pivotTableRef.value.getStyle(area) || new GC.Spread.Sheets.Style();
style.formatter = formatterRef.value;
pivotTableRef.value.setStyle(area, style);
}
function resetFormatter() {
let area = initArea(fieldNameRef.value, pivotTableRef.value);
let style = pivotTableRef.value.getStyle(area);
let formatter = (style && style.formatter) || "";
formatterRef.value = formatter;
}
function changeFormatter (e) {
let area = initArea(e.target.value, pivotTableRef.value);
let style = pivotTableRef.value.getStyle(area);
formatterRef.value = style && style.formatter ? style.formatter : "";
}
</script>
<style scoped>
.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;
font-size: 14px;
}
.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;
}
#app {
height: 100%;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'../node_modules/*/package.json',
"../node_modules/@mescius/*/package.json",
"../node_modules/@babel/*/package.json",
"../node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.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",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
