概述
本 Demo 展示了数据透视表项目切片器的创建与使用方法。Demo 从销售数据创建数据透视表，为地区、国家、城市三个字段分别添加切片器，并提供交互界面让用户动态调整切片器的各项属性，包括列数、标题显示、无数据项处理、排序状态和样式主题。
实现思路
创建数据源工作表，加载销售数据并添加表格
创建数据透视表，配置行字段（地区、国家、城市）和值字段（金额）
使用 slicers.add() 方法为三个字段分别创建项目切片器
创建自定义切片器主题，添加到工作簿的主题集合中
绑定界面控件事件，响应用户操作动态更新切片器属性
代码解析
创建数据透视表并添加切片器
这段代码创建了一个数据透视表，添加了三个行字段和一个值字段。suspendLayout() 和 resumeLayout() 用于批量更新时暂停布局计算，提高性能。
为字段添加项目切片器
slicers.add() 方法用于创建项目切片器，参数依次为：切片器名称、数据透视表名称、字段名称、样式名称和切片器类型。SlicerType.pivotTable 表示这是数据透视表项目切片器。
创建自定义切片器主题
这段代码创建了两个自定义切片器主题。首先基于预置样式创建 SlicerStyle 对象，然后修改其 wholeSlicerStyle 属性设置整体样式，最后通过 customSlicerThemes.add() 添加到工作簿的主题集合中。
动态修改切片器属性
切片器提供了丰富的属性配置方法，可以动态调整显示效果和排序方式。
运行效果
页面加载后显示数据透视表和三个切片器（地区、国家、城市）
点击切片器中的按钮可以筛选数据透视表数据
选中某个切片器后，右侧面板显示当前切片器的属性信息
修改列数、显示标题、隐藏无数据项等选项会立即生效
可以选择不同字段添加新的切片器
可以更改选中切片器的样式主题（包括预置样式和自定义主题）
API 参考
slicers.add() 方法
name：切片器名称
pivotTableName：数据透视表名称
fieldName：要筛选的字段名称
styleName：样式名称
slicerType：切片器类型，项目切片器使用 GC.Spread.Sheets.Slicers.SlicerType.pivotTable
常用切片器属性
showNoDataItems(value)：是否显示无数据项
columnCount(value)：设置显示列数
showHeader(value)：是否显示标题
visuallyNoDataItems(value)：是否视觉标记无数据项
showNoDataItemsInLast(value)：是否将无数据项显示在最后
sortState(value)：排序状态，可使用 GC.Spread.Sheets.SortState.ascending 或 descending
customSlicerThemes.add() 方法
添加自定义切片器主题到工作簿，slicerStyle 为 SlicerStyle 对象。
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), {sheetCount: 2});
initCustomThemes(spread);
initSpread(spread);
var pivotLayoutSheet = spread.getSheet(0);
var pt = initPivotTable(pivotLayoutSheet);
initSlicer(pivotLayoutSheet, pt);
bindEvent(pivotLayoutSheet, pt);
};
function initCustomThemes(spread) {
const theme1 = new GC.Spread.Sheets.Slicers.SlicerStyle();
theme1.fromJSON(GC.Spread.Sheets.Slicers.SlicerStyles.light1().toJSON());
theme1.name('custom1');
theme1.wholeSlicerStyle(new GC.Spread.Sheets.Slicers.SlicerStyleInfo('rgb(225, 245, 254)'));
const theme2 = new GC.Spread.Sheets.Slicers.SlicerStyle();
theme2.fromJSON(GC.Spread.Sheets.Slicers.SlicerStyles.other2().toJSON());
theme2.name('custom2');
const wholeSlicerStyle = new GC.Spread.Sheets.Slicers.SlicerStyleInfo();
wholeSlicerStyle.backColor('#e3f2fd');
theme2.wholeSlicerStyle(wholeSlicerStyle);
spread.customSlicerThemes.add(theme1);
spread.customSlicerThemes.add(theme2);
}
function initSpread(spread) {
spread.suspendPaint();
let sheet = spread.getSheet(1);
sheet.name("DataSource");
sheet.setRowCount(650);
sheet.setColumnWidth(5, 120);
sheet.getCell(-1, 5).formatter("YYYY-mm-DD");
sheet.getRange(-1,4,0,1).formatter("$ #,##0");
sheet.setArray(0, 0, pivotSales);
let table = sheet.tables.add('tableSales', 0, 0, 637, 6);
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
let sheet0 = spread.getSheet(0);
sheet0.name("PivotLayout");
spread.resumePaint();
}
function initPivotTable(sheet) {
sheet.setRowCount(1000);
var option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true
};
var pivotTable = sheet.pivotTables.add("pivotTable", "tableSales", 1, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8.name(), option);
pivotTable.suspendLayout();
pivotTable.add("region", "region", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("country", "countrys", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("city", "city", GC.Spread.Pivot.PivotTableFieldType.rowField);
var groupInfo = {
originFieldName: "date",
dateGroups: [
{
by: GC.Pivot.DateGroupType.quarters
}
]
};
pivotTable.group(groupInfo);
pivotTable.add("amount", "amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.none;
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
return pivotTable;
}
function initSlicer(sheet, pt) {
var slicer_region = sheet.slicers.add("slicer_region", pt.name(), "region", GC.Spread.Sheets.Slicers.SlicerStyles.dark2().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
slicer_region.position(new GC.Spread.Sheets.Point(491, 20));
slicer_region.height(210);
var slicer_country = sheet.slicers.add("slicer_country", pt.name(), "country", GC.Spread.Sheets.Slicers.SlicerStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable)
slicer_country.position(new GC.Spread.Sheets.Point(691, 20));
slicer_country.height(460);
slicer_country.showNoDataItems(false);
var slicer_city = sheet.slicers.add("slicer_city", pt.name(), "city", GC.Spread.Sheets.Slicers.SlicerStyles.other2().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
slicer_city.position(new GC.Spread.Sheets.Point(891, 20));
slicer_city.height(320);
slicer_city.columnCount(2);
}
function bindEvent(sheet, pt) {
var slicer;
var slicerCount = 0;
sheet.bind(GC.Spread.Sheets.Events.SlicerChanged, function () {
var slicers = sheet.slicers.all();
for (var i = 0; i < slicers.length; i++) {
if (slicers[i].isSelected()) {
slicer = slicers[i];
updateSlicerInfo(slicer, pt);
break;
}
}
});
_getElementById("columnCount").addEventListener("change", function (e) {
var value = + e.target.value;
if (value && slicer) {
slicer.columnCount(value);
}
});
_getElementById("showHeader").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.showHeader(checked);
}
});
_getElementById("showNoDataItems").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.showNoDataItems(!checked);
}
});
_getElementById("visuallyNoDataItems").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.visuallyNoDataItems(checked);
}
});
_getElementById("showNoDataItemsInLast").addEventListener("change", function (e) {
var checked = e.target.checked;
if (!_isNullOrUndefined(checked) && slicer) {
slicer.showNoDataItemsInLast(checked);
}
});
_getElementById("ascending").addEventListener("change", function (e) {
var checked = e.target.checked;
if (checked && slicer) {
slicer.sortState(GC.Spread.Sheets.SortState.ascending);
}
});
_getElementById("descending").addEventListener("change", function (e) {
var checked = e.target.checked;
if (checked && slicer) {
slicer.sortState(GC.Spread.Sheets.SortState.descending);
}
});
_getElementById("addSlicerBtn").addEventListener("click", function () {
var fieldName = _getElementById("slicerList").value;
if (fieldName) {
sheet.slicers.add(fieldName + "_" + slicerCount++, pt.name(), fieldName, GC.Spread.Sheets.Slicers.SlicerStyles.light1().name(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
}
});
_getElementById("changeStyle").addEventListener("click", function () {
var styleName = _getElementById("slicerStyle").value;
if (styleName && slicer) {
slicer.style(styleName);
}
});
}
function updateSlicerInfo(slicer, pt) {
_getElementById("columnCount").value = slicer.columnCount();
_getElementById("showHeader").checked = slicer.showHeader();
_getElementById("showNoDataItems").checked = ! slicer.showNoDataItems();
_getElementById("visuallyNoDataItems").checked = slicer.visuallyNoDataItems();
_getElementById("showNoDataItemsInLast").checked = slicer.showNoDataItemsInLast();
const slicerStyleName = slicer.style().name().toLowerCase();
_getElementById("slicerStyle").value = slicerStyleName.includes('custom') ? slicerStyleName : slicerStyleName.substr(11);
var sortState = slicer.sortState();
_getElementById("ascending").checked = sortState === 1;
_getElementById("descending").checked = sortState === 2;
}
function _getElementById(id) {
return document.getElementById(id);
}
function _isNullOrUndefined(o) {
return o === null || o === undefined;
}
<!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-slicers/dist/gc.spread.sheets.slicers.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/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivotSales.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>
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="block slicer-infos">
<div>当前选中切片器信息：</div><br>
<div class="slicer-info">
<label>列数：</label>
<input class="info-input" id="columnCount" type="number"></input>
</div>
<div class="slicer-info">
<input type="checkbox" id="showHeader">
<label for="showHeader">显示标题</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItems">
<label for="showNoDataItems">隐藏无数据项</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="visuallyNoDataItems">
<label for="visuallyNoDataItems">视觉标记无数据项</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItemsInLast">
<label for="showNoDataItemsInLast">无数据项置底</label>
</div>
<div class="slicer-info">
<p>排序状态</p>
<input type="radio" id="ascending" name="sortState" value="1">
<label for="ascending">升序（A 到 Z）</label><br>
<input type="radio" id="descending" name="sortState" value="2">
<label for="descending">降序（Z 到 A）</label><br>
</div>
</div>
<div class="block">
<div>添加切片器</div>
<br />
<select class="select-list" name="slicerList" id="slicerList">
<option value="region">region</option>
<option value="country">country</option>
<option value="city">city</option>
<option value="季度 (date)">季度 (date)</option>
<option value="amount">amount</option>
<option value="id">id</option>
</select>
<button class="select-button" id="addSlicerBtn">添加</button>
</div>
<div class="block">
<div>更改当前切片器样式</div>
<br />
<div class="slicerStyle">
<select class="select-list" name="slicerStyle" id="slicerStyle">
<option value="light1">light1</option>
<option value="light2">light2</option>
<option value="light3">light3</option>
<option value="light4">light4</option>
<option value="light5">light5</option>
<option value="light6">light6</option>
<option value="dark1">dark1</option>
<option value="dark2">dark2</option>
<option value="dark3">dark3</option>
<option value="dark4">dark4</option>
<option value="dark5">dark5</option>
<option value="dark6">dark6</option>
<option value="other1">other1</option>
<option value="other2">other2</option>
<option value="custom1">custom1</option>
<option value="custom2">custom2</option>
</select>
<button class="select-button" id="changeStyle">更改</button>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 330px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 330px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.slicer-info {
margin-top: 5px;
margin-bottom: 5px;
}
.block {
border: 1px solid gray;
padding-left: 5px;
padding-top: 10px;
padding-bottom: 10px;
margin-bottom: 1px;
}
.select-list {
width: 120px;
}
.select-button {
width: 80px;
}
