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 {
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;
}
