数据透视表值显示方式 | 表格插件 | SpreadJS JavaScript 学习指南

SpreadJS数据透视表提供showDataAs方法来以不同方式快速展示数据。这里有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 示例:

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 {
delete style.formatter;
}
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>ShowValueAs:</label>
<select id="showValueAs">
<option value='0' selected>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"></label>
<div class="option-row" id="showValueAsBaseFieldPanel" style="display: none;">
<label id="showValueAsBaseFieldLabel">Base Field:</label>
<select id="showValueAsBaseField">
</select>
</div>
<div class="option-row" id="showValueAsBaseItemPanel" style="display: none;">
<label id="showValueAsBaseItemLabel">Base Item:</label>
<select id="showValueAsBaseItem">
</select>
</div>
<div class="option-row">
<input type="button" value="Apply Setting" 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;
}