소개
피벗 테이블은 데이터 영역에서 값 편집을 지원합니다.
피벗 노드 정보
피벗 테이블의 값을 편집하기 위한 첫 번째 단계는 셀을 설명하는 것입니다.
피벗 테이블 노드의 필드 및 값 정보를 설명할 수 있는 PivotNodeInfo라는 인터페이스를 정의했습니다.
값 덮어쓰기
피벗 테이블에서 값을 덮어쓰면 해당 값이 계산에서 업데이트됩니다.
계산에 포함할 [ sum, count, countNums, max, min ] 유형만 지원합니다. 소계의 다른 유형은 소계 값만 변경합니다.
덮어쓴 값이 소계이면 자식 노드 값은 바뀌지 않고 부모 노드는 자식 노드의 결과를 계산하는 대신 덮어쓴 값을 사용하여 직접 계산합니다.
소스를 업데이트하면 설정된 값이 제거됩니다.
API 목록
window.onload = function () {
let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2});
let detailsSpread = new GC.Spread.Sheets.Workbook(document.getElementById("pivot-details"), {sheetCount: 1});
initSpread(spread, detailsSpread);
};
function initSpread(spread, detailsSpread) {
spread.suspendPaint();
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let table = getDataSource(sheet1, pivotSales);
let pivotTable = initPivotTable(sheet2, table.name());
spread.setActiveSheet("PivotTable");
let detailsSheet = detailsSpread.getSheet(0);
detailsSheet.name("PivotTable Details");
addEvent(sheet2, detailsSheet, pivotTable, table);
spread.resumePaint();
window.sheet = sheet1;
window.table = table;
window.pt = pivotTable;
}
function addEvent(sheet2, detailsSheet, pivotTable, table) {
detailsSheet.setRowCount(700);
detailsSheet.setColumnCount(10);
detailsSheet.addSpan(0, 0, 2, 5);
let style = new GC.Spread.Sheets.Style();
style.wordWrap = true;
detailsSheet.setStyle(0, 0, style);
detailsSheet.setValue(0, 0, "In PivotTable of SpreadJS, pivot table show detail will find all merged data in data source");
sheet2.addSpan(0, 0, 2, 5);
sheet2.setStyle(0, 0, style);
sheet2.setValue(0, 0, "You Can Edit Cell Values in Data Area.\nuncheck the 'Enable Edit Value in Data Area' to close edit ablilty");
sheet2.bind(GC.Spread.Sheets.Events.CellClick, (sender, args) => {
_getElementById("nodeInfo").innerHTML = "";
updateDetailSheet(detailsSheet, args.row, args.col, pivotTable);
});
_getElementById("setNodeValue").addEventListener("click", () => {
let value = parseInt(_getElementById("nodeValue").value, 10);
let nodeInfo = _getElementById("nodeInfo").innerHTML;
if (value !== undefined && value !== null && !isNaN(value) && nodeInfo) {
nodeInfo = JSON.parse(nodeInfo);
if (nodeInfo) {
pivotTable.setNodeValue(nodeInfo, value);
}
}
});
_getElementById("enableDataValueEditing").addEventListener("change", (e) => {
pivotTable.options.enableDataValueEditing = !!e.target.checked;
});
_getElementById("syncValue").addEventListener("click", (e) => {
syncValue(pivotTable, table, sheet2.parent.getSheet(0))
_getElementById("nodeValue").value = "";
updateDetailSheet(detailsSheet, sheet2.getActiveRowIndex(), sheet2.getActiveColumnIndex(), pivotTable);
});
}
function updateDetailSheet (detailsSheet, row, col, pivotTable) {
detailsSheet.suspendPaint();
let dataSource = getPivotDetails(row, col, pivotTable);
detailsSheet.tables.remove("details");
if (dataSource) {
var table = detailsSheet.tables.add('details', 2, 0, dataSource.length, dataSource[0].length);
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
}
detailsSheet.getCell(-1, 0).formatter("YYYY-mm-DD");
detailsSheet.getRange(-1, 4, 0, 2).formatter("$ #,##0");
detailsSheet.setColumnWidth(0, 120);
detailsSheet.setArray(2, 0, dataSource);
detailsSheet.resumePaint();
}
function syncValue (pivotTable, table, sheet) {
let list = pivotTable.getOverwriteList();
let tableRange = table.dataRange();
sheet.clearFormula(tableRange.row, 5, tableRange.rowCount, 1, function () {return true});
if (list && list.length > 0) {
let columnNames = [];
for (let i = tableRange.col; i < tableRange.col + tableRange.colCount; i ++) {
columnNames[i] = table.getColumnName(i);
}
let filter = table.rowFilter();
let compareType = GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo;
list.forEach((info) => {
let fieldInfos = info.fieldInfos;
let details = pivotTable.getPivotDetails(fieldInfos);
fieldInfos.forEach((fieldInfo) => {
let expected = fieldInfo.fieldItem;
let ptField = pivotTable.getField(fieldInfo.fieldName);
let fieldType = ptField.dataType;
let sourceName = ptField.sourceName;
let columnIndex = columnNames.indexOf(sourceName);
if (fieldType === GC.Pivot.PivotDataType.date) {
for (let i = 1; i < details.length; i ++) {
let dateValue = GC.Spread.Sheets.CellTypes.Base.prototype.format(new Date(details[i][columnIndex]), "YYYY-mm-DD");
let condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {compareType: compareType, expected: dateValue});
filter.addFilterItem(columnIndex, condition);
}
} else {
filter.addFilterItem(columnIndex, new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {compareType: compareType, expected: expected}));
}
});
filter.filter();
let valueColIndex = columnNames.indexOf((info.valueInfo.sourceName));
let leftRowIndexes = [];
for (let i = tableRange.row; i < tableRange.row + tableRange.rowCount; i ++) {
if (!filter.isRowFilteredOut(i)) {
leftRowIndexes.push(i);
}
}
let avgValue = info.value / leftRowIndexes.length;
for (let i = 0; i < leftRowIndexes.length; i ++) {
sheet.setValue(leftRowIndexes[i], valueColIndex, avgValue);
}
filter.reset();
})
}
pivotTable.updateSource();
}
function getPivotDetails(row, col, pivotTable) {
let pivotInfo = pivotTable.getPivotInfo(row, col),
detailsObj = [];
if (! pivotInfo || pivotInfo.area !== 4) {
return void 0;
}
let colInfo = pivotInfo.colInfos;
let rowInfo = pivotInfo.rowInfos;
setDetails(colInfo, detailsObj);
setDetails(rowInfo, detailsObj);
let dataSource = pivotTable.getPivotDetails(detailsObj);
updateNodeInfo(pivotTable, row, col);
return dataSource;
}
function updateNodeInfo (pivotTable, row, col) {
let nodeInfo = pivotTable.getNodeInfo(row, col);
if (!nodeInfo) {
return;
}
_getElementById("nodeInfo").innerHTML = JSON.stringify(nodeInfo, null, 4);
_getElementById("nodeValue").value = pivotTable.getNodeValue(nodeInfo) || "";
_getElementById("enableDataValueEditing").checked = pivotTable.options.enableDataValueEditing;
}
function setDetails(rowOrColInfo, detailsObj) {
if (rowOrColInfo && rowOrColInfo.length > 0) {
for (let item of rowOrColInfo) {
if (item.isGrandTotal) {
break;
}
detailsObj.push({fieldName: item.fieldName, fieldItem: item.itemName});
}
}
}
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;
}
function initPivotTable(sheet, tableName) {
sheet.name("PivotTable");
sheet.setRowCount(1000);
let option = {
showRowHeader: true,
showColumnHeader: true,
bandRows: true,
bandColumns: true,
enableDataValueEditing: true
};
let pivotTable = sheet.pivotTables.add("pivotTable", tableName, 2, 0, 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);
pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
let groupInfo = {
originFieldName: "date",
dateGroups: [
{
by: GC.Pivot.DateGroupType.quarters
}
]
};
pivotTable.group(groupInfo);
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let style = new GC.Spread.Sheets.Style();
style.formatter = "$ #,##0";
pivotTable.setStyle({
dataOnly: true
}, style);
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
return pivotTable;
}
function _getElementById (id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8"/>
<meta name="spreadjs culture" content="ko-kr" />
<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-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.min.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="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div class="sjss">
<div id="ss" class="sample-spreadsheets"></div>
<div id="pivot-details" class="sample-spreadsheets-details"></div>
</div>
<div class="sample-panel">
<div id="config">
<h2>Overwrite Panel</h2>
<div class="option node">
<label class="control-label">
<input id="enableDataValueEditing" type="checkbox" style="display: none;" checked>
<div class="check"></div>
<div class="circle"></div>
</label>
<label for="enableDataValueEditing" class="label-text">Enable Edit Value in Data Area</label>
</div>
<div class="node">
<div class="textarea-block">
<span>Active Cell Node Info:</span>
<textarea name="nodeInfo" id="nodeInfo" cols="20" rows="10"></textarea>
</div>
<div class="label-block">
<span>Node Value:</span>
<input type="number" name="nodeValue" id="nodeValue" placeholder="value">
</div>
<button id="setNodeValue">SET</button>
</div>
<div class="sync-value">
<h3>Notice:</h3>
<div>
"Sync Value" will sync changing data back to the orginal data table by average.
</div>
<div>
The PivotTable will update source and the overwrite info will be cleared.
</div>
<button id="syncValue">Sync Value</button>
</div>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
}
.sjss {
width: 70%;
height: 100%;
display: inline-block;
}
.sample-spreadsheets {
width: 100%;
height: 58%;
overflow: hidden;
}
.sample-spreadsheets-details{
width: 100%;
height: 40%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
overflow: hidden;
}
#config {
width: 75%;
margin-left: 10%;
}
.sample-panel {
position: relative;
float: right;
width: 30%;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow-y: auto;
overflow-x: hidden;
}
#app {
height: 100%;
}
label{
position: relative;
}
.check{
width: 2rem;
height: 1rem;
border-radius: 100rem;
border: 1px solid #a19b9b;
transition: .3s;
}
.circle{
width: 1rem;
height: 1rem;
border-radius: 50%;
background: #a19b9b;
position: absolute;
left: 1px;
top: 1px;
transform: translateX(0rem);
transition: .3s;
}
input:checked ~.check{
background: #0099CC;
transition: .3s;
border-color: #0099CC;
}
input:checked ~ .circle{
transform: translateX(1rem);
transition: .3s;
background: #EEEEEE;
}
.control-label {
display: inline-block;
top: 3px;
}
#config h2 {
width: 100%;
margin-top: 0;
text-align: center;
}
.label-text {
user-select: none;
line-height: 20px;
white-space: nowrap;
}
.option {
font-size: 15px;
vertical-align: middle;
white-space: nowrap;
}
.node {
width: 100%;
position: relative;
}
.textarea-block textarea {
outline: none;
resize: none;
border: none;
width: 100%;
border-top: 1px solid #0099CC;
font-size: 12px;
font-family: 'Calibri', 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
background-color: transparent;
}
#config button {
line-height: 18px;
padding: 8px 4px;
font-size: 13px;
border: 1px solid #0099CC;
cursor: pointer;
background: transparent;
color: #0099CC;
text-align: center;
-webkit-transition-duration: 0.3s; /* Safari */
transition-duration: 0.3s;
text-decoration: none;
text-transform: uppercase;
}
#config button:hover {
background-color: #008CBA;
color: white;
}
#setNodeValue {
position: absolute;
width: 30%;
right: 0;
}
.label-block {
position: absolute;
left: 0;
border: 1px solid #0099CC;
padding: 8px 4px;
color: #0099CC;
width: 50%;
white-space:nowrap;
font-size: 13px;
overflow: hidden;
line-height: 18px;
}
.label-block input {
display: inline-block;
border: none;
outline: none;
width: 48%;
}
.label-block span {
display: inline-block;
width: 48%;
}
.textarea-block {
display: block;
margin-bottom: 10px;
margin-top: 10px;
padding: 8px 8px;
color: #0e84ab;
border: 1px solid #0099CC;
}
input[type=number]::-webkit-inner-spin-button,
input[type=number]::-webkit-outer-spin-button {
-webkit-appearance: none;
margin: 0;
}
input[type=number] {
-moz-appearance:textfield;
}
#syncValue {
display: inline-block;
margin-left: 25%;
margin-top: 10px;
width: 50%;
}
.sync-value {
margin-top: 60px;
}