数据透视表项切片器由 SlicerCollection(WorkSheet.slicers) 管理，与表切片器相同。
项目切片器可以添加到任何字段（不包括计算字段）。
在 Item Slicer 上所做的更改等同于使用手动过滤器，即标签过滤器中的“textItems”。
添加切片器
如果我们想添加一个数据透视表项切片器。
创建一个名为“pt”的数据透视表。
（initpivottable的具体实现可以在文末找到。）
然后将项目切片器添加到“name”字段。
Item 状态
我们定义了两种item状态：
selected：是否通过过滤器选择项目。
noData：如果一个item已经被其他过滤器过滤了，也就是说无论选择与否都不会生效，我们定义为noData。
这两个状态建立了一个项目状态。
例如，“selected && noData” 或 “unselected && hasData”
使用切片器
然后我们可以控制 slicer_name：
例如，如果您想在两列中显示项目。
如果你想让项目更高。
如果您不想看到处于 noData 状态的项目。
创建数据透视表的示例
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);
initSlicer(pivotLayoutSheet, pt);
bindEvent(pivotLayoutSheet, pt);
};
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, 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(), 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(), 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(), 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(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
}
});
_getElementById("changeStyle").addEventListener("click", function () {
var styleName = _getElementById("slicerStyle").value;
if (styleName && slicer) {
var style = GC.Spread.Sheets.Slicers.SlicerStyles[styleName]();
slicer.style(style);
}
});
}
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();
_getElementById("slicerStyle").value = slicer.style().name().substr(11).toLowerCase();
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>Current Selected Slicer Info:</div><br>
<div class="slicer-info">
<label>Column Count:</label>
<input class="info-input" id="columnCount" type="number"></input>
</div>
<div class="slicer-info">
<input type="checkbox" id="showHeader">
<label for="showHeader">Display Header</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItems">
<label for="showNoDataItems">Hide Items With No Data</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="visuallyNoDataItems">
<label for="visuallyNoDataItems">Visually Indicate Items With No Data</label>
</div>
<div class="slicer-info">
<input type="checkbox" id="showNoDataItemsInLast">
<label for="showNoDataItemsInLast">Show Items With No Data Last</label>
</div>
<div class="slicer-info">
<p>Sort State</p>
<input type="radio" id="ascending" name="sortState" value="1">
<label for="ascending">Ascending(A to Z)</label><br>
<input type="radio" id="descending" name="sortState" value="2">
<label for="descending">Descending(Z to A)</label><br>
</div>
</div>
<div class="block">
<div>Add Slicer</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">Add</button>
</div>
<div class="block">
<div>Change Current Slicer Style</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>
</select>
<button class="select-button" id="changeStyle">Change</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;
}
