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".
import { Component, NgModule, enableProdMode } from "@angular/core";
import { BrowserModule } from "@angular/platform-browser";
import { platformBrowserDynamic } from "@angular/platform-browser-dynamic";
import { SpreadSheetsModule } from "@mescius/spread-sheets-angular";
import { FormsModule } from "@angular/forms";
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import "./styles.css";
@Component({
selector: "app-component",
templateUrl: "src/app.component.html",
})
export class AppComponent {
dataSource: any[];
spread: GC.Spread.Sheets.Workbook;
hostStyle = {
width: "calc(100% - 280px)",
height: "100%",
overflow: "hidden",
float: "left",
};
pivotTable: any;
fieldName = "Salesperson";
formatter = "";
constructor() { }
initSpread($Event: any) {
let spread = $Event.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(sheet: GC.Spread.Sheets.Worksheet, tableSource: any) {
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();
}
initArea(fieldName: string, pt: any) {
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],
},
],
};
}
}
addPivotTable(sheet: GC.Spread.Sheets.Worksheet, source: any) {
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;
}
changeFormatter() {
let area = this.initArea(this.fieldName, this.pivotTable);
let style = this.pivotTable.getStyle(area);
this.formatter = style && style.formatter ? style.formatter : "";
}
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;
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule, FormsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent],
})
export class AppModule { }
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
</gc-spread-sheets>
<div class="options-container">
<div>Field Name:</div>
<select name="fieldName" id="fieldName" class="field-name" [(ngModel)]="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" [(ngModel)]="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>
.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: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.js',
'css': 'npm:systemjs-plugin-css/css.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-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
