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".
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 = getSource(sheet2, pivotSales);
let pivotTable = addPivotTable(sheet1, tableName);
bindEvent(pivotTable,spread);
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 _isNullOrUndefined (obj) {
return obj === null || obj === undefined;
}
function bindEvent(pivotTable,spread) {
_get('fieldName').addEventListener('change', (e) => {
let fieldName = e.target.value;
let area = initArea(fieldName, pivotTable);
let style = pivotTable.getStyle(area);
_get('formatter').value = (style && style.formatter) ? style.formatter : "";
})
_get('set').addEventListener('click', (e) => {
let fieldName = _get('fieldName').value;
let formatter = _get('formatter').value;
let area = initArea(fieldName, pivotTable);
let style = pivotTable.getStyle(area) || new GC.Spread.Sheets.Style();
style.formatter = formatter;
pivotTable.setStyle(area, style);
})
_get('reset').addEventListener('click', (e) => {
let fieldName = _get('fieldName').value;
let area = initArea(fieldName, pivotTable);
let formatter = pivotTable.getStyle(area)?.formatter || "";
_get('formatter').value = formatter;
})
}
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 _get (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 id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="whole-field">The following operations all work on <b>Salesperson</b> Field:</div>
<div>Field Name:</div>
<select name="fieldName" id="fieldName" class="field-name">
<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" />
<input type="button" class="format-button" value="Set" id="set">
<input type="button" class="format-button" value="Reset" id="reset">
</div>
</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;
}
