피벗 테이블 항목 슬라이서는 표 슬라이서와 동일하게 SlicerCollection(WorkSheet.slicers)에 의해 관리됩니다.
항목 슬라이서는 모든 필드에 추가할 수 있습니다(Calc 필드 제외).
항목 슬라이서를 변경하는 것은 레이블 필터의 "textItems"를 의미하는 수동 필터를 사용하는 것과 같습니다.
슬라이서 추가
피벗 테이블 항목 슬라이서를 추가하려면 "pt"라는 이름의 피벗 테이블을 만들면 됩니다.
initPivotTable의 구체적인 구현 방법은 이 문서 끝 부분에서 확인할 수 있습니다.
그런 다음 "name" 필드에 항목 슬라이서를 추가합니다.
항목 상태
항목을 두 가지 상태로 정의했습니다.
selected: 항목이 필터에 의해 선택되었는지 여부.
noData: 항목이 다른 필터에 의해 필터링되어 있다면 선택 여부에 관계없이 해당 항목이 적용되지 않음을 의미하며, 이 상태를 noData로 정의했습니다.
이 두 가지 상태가 항목 상태를 구성합니다.
예: "selected && noData" 또는 "unselected && hasData"
슬라이서 사용
그런 다음 slicer_name을 정의할 수 있습니다.
예를 들어 두 개의 열에서 항목을 표시하려는 경우:
항목의 높이를 정의하려는 경우:
noData 상태인 항목을 표시하지 않으려는 경우:
피벗 테이블 만들기 샘플
사용자 정의 항목 슬라이서 테마
SpreadJS는 피벗 테이블 항목 슬라이서의 테마를 사용자 정의하는 기능을 지원합니다. 다음 코드를 사용합니다.
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="ko-kr"/>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/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="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>
<option value="custom1">custom1</option>
<option value="custom2">custom2</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;
}
