ピボットテーブルは、さまざまな計算の種類を提供して、計算の値をすばやく表示します。
14種類の計算の種類を利用できます。
計算の種類
列挙型の値
必要なプロパティ
計算なし
normal
showDataAs
総計に対する比率
percentOfTotal
showDataAs
列集計に対する比率
percentOfRow
showDataAs
行集計に対する比率
percentOfCol
showDataAs
基準値に対する比率
percent
showDataAs, baseFieldName, baseFieldItemType, baseFieldItem?
親行集計に対する比率
percentOfParentRow
showDataAs
親列集計に対する比率
percentOfParentCol
showDataAs
親集計に対する比率
percentOfParent
showDataAs, baseFieldName
基準値との差分
difference
showDataAs, baseFieldName, baseFieldItemType, baseFieldItem?
基準値との差分の比率
percentDiff
showDataAs, baseFieldName, baseFieldItemType, baseFieldItem?
累計
runTotal
showDataAs, baseFieldName
比率の累計
percentOfRunningTotal
showDataAs, baseFieldName
昇順での順位
rankAscending
showDataAs, baseFieldName
降順での順位
rankDescending
showDataAs, baseFieldName
指数 (インデックス)
index
showDataAs
サンプル：
import { Component, NgModule, enableProdMode } from '@angular/core';
import { bootstrapApplication, BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { FormsModule } from '@angular/forms';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import '@mescius/spread-sheets-resources-ja';
GC.Spread.Common.CultureManager.culture("ja-jp");
import './styles.css';
@Component({
standalone: true,
imports: [SpreadSheetsModule, BrowserModule, FormsModule],
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
hostStyle = {
width: 'calc(100% - 300px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
spread: any;
pt: any;
showValueAs = "0";
showFieldPanel: boolean;
showItemPanel: boolean;
showValueAsDialogLabel: string;
baseField: any;
baseItems: any;
baseFields: any[];
baseItem: any;
constructor() {
}
initSpread($Event: any) {
let spread = $Event.spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = this.getDataSource(sheet2, pivotSales);
let pivotTable = this.initPivotTable(sheet1, tableName);
this.pt = pivotTable;
this.spread = spread;
this.showFieldPanel = false;
this.showItemPanel = false;
spread.resumePaint();
}
getDataSource(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();
}
initPivotTable(sheet: GC.Spread.Sheets.Worksheet, source: any) {
sheet.name("ShowDataAs");
sheet.setRowCount(1000);
let option = {
showRowHeader:true,
showColumnHeader:true,
bandRows:true,
bandColumns:true
};
let pivotTable = sheet.pivotTables.add("pivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8,option);
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("四半期 (date)", "四半期 (date)",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
return pivotTable;
}
onShowValueAsChange(event: any) {
let selectIndex = this.showValueAs;
this.showFieldPanel = false;
this.showItemPanel = false;
this.showValueAsDialogLabel = "";
if (["4", "7", "8", "9", "10", "11", "12", "13"].indexOf(selectIndex) > -1) {
this.showFieldPanel = true;
this.baseFields = [...this.pt.getFieldsByArea(1), ...this.pt.getFieldsByArea(2)];
this.baseField = "0";
this.showValueAsDialogLabel = "計算: " + event.target.selectedOptions[0].text;
if (["4", "8", "9"].indexOf(selectIndex) > -1) {
this.showItemPanel = true;
let text = this.baseFields[0].fieldName;
this.baseItems = [...this.pt.getItemsByField(text)];
this.baseItems.unshift("next");
this.baseItems.unshift("previous");
this.baseItem = "0";
}
}
}
onBaseFieldChange(event: any) {
this.baseItems = [...this.pt.getItemsByField(this.baseFields[parseInt(this.baseField)].fieldName)];
this.baseItems.unshift("next");
this.baseItems.unshift("previous");
this.baseItem = "0";
}
applyShowDataAsStyle(pivotTable: GC.Spread.Pivot.PivotTable, fieldName: string, showValueAsType: GC.Pivot.PivotShowDataAs) {
let style, valueFieldArea = {
dataOnly: true,
references: [{
fieldName: "値",
items: [fieldName]
}]
};
style = pivotTable.getStyle(valueFieldArea);
if (!style) {
style = new GC.Spread.Sheets.Style();
}
let needApplyStyle = [GC.Pivot.PivotShowDataAs.percentOfTotal, GC.Pivot.PivotShowDataAs.percentOfRow, GC.Pivot.PivotShowDataAs.percentOfCol, GC.Pivot.PivotShowDataAs.percent, GC.Pivot.PivotShowDataAs.percentOfParentRow,
GC.Pivot.PivotShowDataAs.percentOfParentCol, GC.Pivot.PivotShowDataAs.percentOfParent, GC.Pivot.PivotShowDataAs.percentDiff, GC.Pivot.PivotShowDataAs.percentOfRunningTotal].indexOf(showValueAsType) > -1;
if (needApplyStyle) {
style.formatter = "0.00%";
} else {
style = null;
}
pivotTable.setStyle(valueFieldArea, style);
}
applySetting(event: any) {
let showValueAsIndex = this.showValueAs;
let fieldName = this.pt.getFieldsByArea(3)[0].fieldName, showDataAsType = parseInt(showValueAsIndex, 10), baseFieldName;
if (this.baseFields && this.baseFields[this.baseField]) {
baseFieldName = this.baseFields[this.baseField].fieldName;
}
if (["4", "8", "9"].indexOf(showValueAsIndex) > -1) {
let baseIndex = this.baseItem;
let baseFieldItem = this.baseItems[this.baseItem];
if (baseIndex === "0") {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName, baseFieldItemType: 2 });
} else if (baseIndex === "1") {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName, baseFieldItemType: 1 });
} else {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName, baseFieldItemType: 0, baseFieldItem: baseFieldItem });
}
} else if (["7", "10", "11", "12", "13"].indexOf(this.showValueAs) > -1) {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType, baseFieldName });
} else {
this.pt.showDataAs(fieldName, { showDataAs: showDataAsType });
}
this.applyShowDataAsStyle(this.pt, fieldName, showDataAsType);
}
}
enableProdMode();
bootstrapApplication(AppComponent);
<!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$/ja/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$/ja/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/ja/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/ja/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$/ja/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-worksheet>
</gc-worksheet>
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container" id="container">
<div class="option-row">
<label>計算の種類：</label>
<select id="showValueAs" ngDefaultControl [(ngModel)]="showValueAs" (change)="onShowValueAsChange($event)">
<option value="0" >No Calculation</option>
<option value="1">% of Grand Total</option>
<option value="2">% of Column Total</option>
<option value="3">% of Row Total</option>
<option value="4">% Of ...</option>
<option value="5">% of Parent Row Total</option>
<option value="6">% of Parent Column Total</option>
<option value="7">% of Parent Total ...</option>
<option value="8">Difference From ...</option>
<option value="9">% Of Difference From ...</option>
<option value="10">Running Total In ...</option>
<option value="11">% Running Total In ...</option>
<option value="12">Rank Smallest to Largest ...</option>
<option value="13">Rank Largest to Smallest ...</option>
<option value="14">Index</option>
</select>
</div>
<label style="padding: 5px; margin-top: 10px;" id="showValueAsDialogLabel" ngDefaultControl>{{showValueAsDialogLabel}}</label>
<div class="option-row" id="showValueAsBaseFieldPanel" ngDefaultControl *ngIf="showFieldPanel">
<label id="showValueAsBaseFieldLabel">基準フィールド:</label>
<select id="showValueAsBaseField" ngDefaultControl [(ngModel)]="baseField" (change)="onBaseFieldChange($event)">
<option ngDefaultControl *ngFor="let field of baseFields let i = index" value={{i}}>{{field.fieldName}}</option>
</select>
</div>
<div class="option-row" id="showValueAsBaseItemPanel" ngDefaultControl *ngIf="showItemPanel">
<label id="showValueAsBaseItemLabel">基準アイテム:</label>
<select id="showValueAsBaseItem" ngDefaultControl [(ngModel)]="baseItem">
<option ngDefaultControl *ngFor="let item of baseItems let i = index" value={{i}}>{{item}}</option>
</select>
</div>
<div class="option-row">
<input type="button" value="設定を適用する" id="applySetting" (click)="applySetting($event)" />
</div>
</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;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
p{
padding:2px 10px;
background-color:#F4F8EB;
}
input, select {
width: 100%;
padding: 4px 6px;
box-sizing: border-box;
}
label {
display:block;
margin-bottom: 6px;
}
input[type="checkbox"], input[type="radio"] {
display: inline-block;
width: auto;
}
input[type="checkbox"]+label, input[type="radio"]+label {
display: inline-block;
}
input[type="button"] {
display: block;
margin: 0 0 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
(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',
'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',
'tslib':'npm:tslib/tslib.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-resources-ja': 'npm:@mescius/spread-sheets-resources-ja/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',
meta: {
"*.component.ts": {
loader: "system.component-loader.js"
}
}
},
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);
