概述
本 Demo 展示了数据透视表的值显示方式功能，通过 showDataAs 方法将值字段以不同的计算方式显示。Demo 提供了交互式界面，用户可以选择 14 种不同的显示方式，包括占总计百分比、差异、排名、累计等，实时查看数据透视表的显示效果变化。
实现思路
创建数据源表格，包含销售员、日期、车型、数量、单价和总金额等销售数据
初始化数据透视表，设置行字段（销售员、车型）、列字段（季度分组）、值字段（总金额汇总）
绑定下拉框的 change 事件，根据用户选择的显示方式动态显示或隐藏参数面板
对于需要额外参数的显示方式（如百分比、差异），动态加载基本字段和基本项的下拉选项
应用设置时，调用 showDataAs 方法并根据显示类型自动应用百分比格式
代码解析
初始化数据透视表
这段代码创建了数据透视表的基本结构。首先添加行字段（销售员和车型），然后使用 group 方法对日期字段按季度分组并添加为列字段，最后添加值字段进行求和汇总。
应用值显示方式
showDataAs 方法接受两个参数：值字段名称和显示配置对象。配置对象包含 showDataAs（显示类型枚举值）、baseFieldName（基本字段名）、baseFieldItemType（基本项类型枚举值）和 baseFieldItem（基本项名称）。
baseFieldItemType 使用 GC.Pivot.PivotShowDataAsBaseItemType 枚举，包含三个值：previous（前一项）、next（后一项）、item（指定项）。在代码中，previous 对应值 1，next 对应值 2，item 对应值 0。
自动应用百分比格式
对于百分比类型的显示方式，需要手动设置单元格格式为百分比格式，以确保数据以正确的格式显示。
动态显示参数面板
根据用户选择的显示方式类型，动态显示或隐藏参数面板。某些显示方式需要指定基本字段，某些还需要指定基本项。
运行效果
页面加载后显示一个数据透视表，默认显示原始的求和汇总值
从下拉框中选择不同的值显示方式，例如"差异"、"百分比"、"排名"等
选择需要参数的显示方式时，会动态显示"基本字段"和/或"基本项"下拉框
点击"应用设置"按钮后，数据透视表的值字段会按照选择的方式重新计算显示
百分比类型的显示方式会自动应用百分比格式
用户可以对比不同显示方式下的数据呈现效果
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 = getDataSource(sheet2, pivotSales);
let pivotTable = initPivotTable(sheet1, tableName);
bindEvent(pivotTable, spread);
spread.resumePaint();
}
function getDataSource(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 initPivotTable(sheet, source) {
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;
}
function applyShowDataAsStyle(pivotTable, fieldName, showValueAsType) {
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);
}
function bindEvent(pivotTable, spread) {
document.getElementById("showValueAs").addEventListener("change", function (event) {
let selectIndex = event.target.value;
let showValueAsBaseFieldPanel = document.getElementById("showValueAsBaseFieldPanel"),
showValueAsDialogLabel = document.getElementById("showValueAsDialogLabel"),
showValueAsBaseItemPanel = document.getElementById("showValueAsBaseItemPanel");
showValueAsBaseFieldPanel.style.display = "none";
showValueAsBaseItemPanel.style.display = "none";
showValueAsDialogLabel.innerText = "";
let showValueAsBaseItem = document.getElementById("showValueAsBaseItem");
showValueAsBaseItem.innerHTML = "";
let showValueAsBaseField = document.getElementById("showValueAsBaseField");
showValueAsBaseField.innerHTML = "";
if (["4", "7", "8", "9", "10", "11", "12", "13"].indexOf(selectIndex) > -1) {
showValueAsBaseFieldPanel.style.display = null;
let baseField = [...pivotTable.getFieldsByArea(1), ...pivotTable.getFieldsByArea(2)];
showValueAsDialogLabel.innerText = "Calculation: " + event.target.selectedOptions[0].text;
baseField.forEach((item, index) => {
let option = document.createElement('option');
option.value = index;
option.innerText = item.fieldName;
showValueAsBaseField.appendChild(option);
});
if (["4", "8", "9"].indexOf(selectIndex) > -1) {
showValueAsBaseItemPanel.style.display = null;
let text = showValueAsBaseField.selectedOptions[0].text;
let baseItems = pivotTable.getItemsByField(text);
baseItems.unshift("next"); baseItems.unshift("previous");
baseItems.forEach((item, index) => {
let option = document.createElement('option');
option.value = index;
option.innerText = item;
showValueAsBaseItem.appendChild(option);
});
}
}
});
document.getElementById("showValueAsBaseField").addEventListener("change", function (event) {
let text = event.target.selectedOptions[0].text;
let showValueAsBaseItem = document.getElementById("showValueAsBaseItem");
showValueAsBaseItem.innerHTML = "";
let baseItems = pivotTable.getItemsByField(text);
baseItems.unshift("next");
baseItems.unshift("previous");
baseItems.forEach((item, index) => {
let option = document.createElement('option');
option.value = index;
option.innerText = item;
showValueAsBaseItem.appendChild(option);
});
});
document.getElementById("applySetting").addEventListener('click', () => {
let showValueAsIndex = parseInt(document.getElementById("showValueAs").value, 10);
let valueFieldName = pivotTable.getFieldsByArea(3)[0].fieldName;
if ([4, 8, 9].indexOf(showValueAsIndex) > -1) {
let fileName = document.getElementById("showValueAsBaseField").selectedOptions[0].text;
let showValueAsBaseItem = document.getElementById("showValueAsBaseItem");
let baseIndex = showValueAsBaseItem.value;
let baseFieldItem = showValueAsBaseItem.selectedOptions[0].text;
if (baseIndex === "0") {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 2 });
} else if (baseIndex === "1") {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 1 });
} else {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName, baseFieldItemType: 0, baseFieldItem: baseFieldItem });
}
} else if ([7, 10, 11, 12, 13].indexOf(showValueAsIndex) > -1) {
let fileName = document.getElementById("showValueAsBaseField").selectedOptions[0].text;
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex, baseFieldName: fileName });
} else {
pivotTable.showDataAs(valueFieldName, { showDataAs: showValueAsIndex });
}
applyShowDataAsStyle(pivotTable, valueFieldName, showValueAsIndex);
pivotTable.refresh();
});
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/data.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="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.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>值显示方式：</label>
<select id="showValueAs">
<option value='0' selected>无计算</option>
<option value='1'>占总计的百分比</option>
<option value='2'>占列总计的百分比</option>
<option value='3'>占行总计的百分比</option>
<option value='4'>占…的百分比</option>
<option value='5'>占父行总计的百分比</option>
<option value='6'>占父列总计的百分比</option>
<option value='7'>占父级总计的百分比…</option>
<option value='8'>差异…</option>
<option value='9'>差异百分比…</option>
<option value='10'>按…累计</option>
<option value='11'>按…累计百分比</option>
<option value='12'>升序排名…</option>
<option value='13'>降序排名…</option>
<option value='14'>指数</option>
</select>
</div>
<label style="padding: 5px; margin-top: 10px;" id="showValueAsDialogLabel"></label>
<div class="option-row" id="showValueAsBaseFieldPanel" style="display: none;">
<label id="showValueAsBaseFieldLabel">基本字段：</label>
<select id="showValueAsBaseField">
</select>
</div>
<div class="option-row" id="showValueAsBaseItemPanel" style="display: none;">
<label id="showValueAsBaseItemLabel">基本项：</label>
<select id="showValueAsBaseItem">
</select>
</div>
<div class="option-row">
<input type="button" value="应用设置" id="applySetting" />
</div>
</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;
}
.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;
}
