There are 3 layouts for PivotTables.
Compact Form
Outline Form
Tabular Form
By default, the layout is Compact Form.
For the each layout type:
Compact Form
1.Row Labels
In Compact Form, each Row label is in a separate row.
The Row field label is always above the labels for the inner fields.
Each Row label is slightly indented from the field above it, to differentiate the fields.
The Row labels cannot be repeated.
The indentation can be changed in PivotTable Options, on the Layout & Format tab
2.Row Fields
In Compact Form, all the Row fields are in a single column.
There is a generic heading, Row Labels, in the Row Field column.
3.Subtotals
In Compact Form, Subtotals can be shown at the Top or Bottom of each group.
This setting affects all the Row fields.
For Column fields, Subtotals are always shown at the Bottom.
The Row label remains at the top, even if Subtotals are at the bottom.
Outline Form
1.Row Labels
In Outline Form, each Row label is in a separate row.
The Row field label is always above the labels for the inner fields.
The Row labels can be repeated.
2.Row Fields
In Outline Form, each Row field is in a separate column.
Each Row field shows its name in the column heading.
3.Subtotals
In Outline Form, Subtotals can be shown at the Top or Bottom of each group.
This setting affects all the Row fields.
For Column fields, Subtotals are always shown at the Bottom.
Tabular Form
1.Row Labels
In Tabular Form, Row labels for the outer fields are on the same row as the first label for the related inner fields.
The Row labels can be repeated.
2.Row Fields
In Tabular Form, each Row field is in a separate column.
Each Row field shows its name in the column heading.
3.Subtotals
In Tabular Form, Subtotals can only be shown at the Bottom of each group.
This setting affects all the Row fields.
For Column fields, Subtotals are always shown at the Bottom.
User can get and set the layout type of the PivotTable.
And the PivotTableLayoutType is the enumeration defined below:
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 });
initSpread(spread);
var pivotLayoutSheet = spread.getSheet(0);
var pt = initPivotTable(pivotLayoutSheet);
bindEvent(spread, pt);
};
function initSpread(spread) {
spread.suspendPaint();
var sheet = spread.getSheet(1);
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");
sheet.setArray(0, 0, pivotSales);
let table = sheet.tables.add('tableSales', 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"]);
var sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
spread.resumePaint();
}
function initPivotTable(sheet) {
var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
myPivotTable.suspendLayout();
myPivotTable.options.showRowHeader = true;
myPivotTable.options.showColumnHeader = true;
myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
myPivotTable.group(groupInfo);
myPivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
myPivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let itemList = ["Alan","John", "Tess"];
myPivotTable.labelFilter("Salesperson", { textItem: { list: itemList, isAll: false } });
myPivotTable.sort("Salesperson", { sortType: GC.Pivot.SortType.asc });
let carList = ["Audi","BMW","Mercedes"];
myPivotTable.labelFilter("Cars", { textItem: { list: carList, isAll: false } });
myPivotTable.sort("Cars", { sortType: GC.Pivot.SortType.asc });
let style = new GC.Spread.Sheets.Style();
style.formatter = "$ #,##0";
myPivotTable.setStyle({dataOnly: true}, style);
myPivotTable.resumeLayout();
myPivotTable.autoFitColumn();
return myPivotTable;
}
function bindEvent (spread, pt) {
_getElementById("layoutType").addEventListener("change", () => {
if (spread) {
var type = parseInt(document.getElementById("layoutType").value, 10);
pt.layoutType(type);
pt.autoFitColumn();
}
});
}
function _getElementById(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/data/pivot-data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.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="option-row">
<label><b>Pivot Layout</b>
</label></div>
<hr>
<div class="option-row">
<label>Select a new pivot table layout option below:
</label></div>
<div class="option-row">
<select id="layoutType">
<option value="0">Compact Form</option>
<option value="1" selected>Outline Form</option>
<option value="2">Tabular Form</option>
</select>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
}
.sample-spreadsheets {
width: calc(100% - 210px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 210px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
