피벗 테이블 항목 슬라이서는 표 슬라이서와 동일하게 SlicerCollection(WorkSheet.slicers)에 의해 관리됩니다.
항목 슬라이서는 모든 필드에 추가할 수 있습니다(Calc 필드 제외).
항목 슬라이서를 변경하는 것은 레이블 필터의 "textItems"를 의미하는 수동 필터를 사용하는 것과 같습니다.
슬라이서 추가
피벗 테이블 항목 슬라이서를 추가하려는 경우
"pt"라는 피벗 테이블을 만듭니다.
(initpivottable의 구체적인 구현 방법은 이 문서 끝 부분에서 확인할 수 있습니다.)
그런 다음 "name" 필드에 항목 슬라이서를 추가합니다.
항목 상태
항목이 다음 두 가지 상태가 된다고 정의했습니다.
selected: 필터에 따라 항목이 선택되는지 여부.
noData: 항목이 다른 필터에 따라 필터링된 경우. 즉, 항목 슬라이서가 항목의 선택 여부에 영향을 미치지 못하고 이러한 경우를 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="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>
</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;
}