[]
피벗 테이블의 설정을 수정하여 더 이해하기 쉽게 만들 수 있습니다.
autoFitColumn 메서드를 사용하면 데이터에 따라 피벗 테이블 열을 자동으로 맞춤 설정할 수 있습니다. 아래 GIF는 피벗 테이블에서 열 자동 맞춤을 보여줍니다.
다음 코드 샘플은 피벗 테이블에서 열을 자동 맞춤하는 방법을 보여줍니다.
// 열 자동 맞춤
myPivotTable.autoFitColumn();
피벗 테이블의 값 필드 영역에 사용자 정의 필드를 추가하여 수식을 사용하여 값을 계산할 수 있습니다. 수식에는 상수를 사용하거나 피벗 테이블의 다른 필드를 참조할 수 있습니다.
계산 필드는 addCalcField 메서드를 사용하여 추가할 수 있습니다. 또한 getCalcFields 및 removeCalcField 메서드를 사용하여 계산 필드 정보를 가져오거나 제거할 수 있습니다.
아래 이미지는 "Tax"라는 계산 필드가 피벗 테이블에 추가된 예시입니다.
다음 코드 샘플은 피벗 테이블에 계산 필드를 추가, 가져오기 및 제거하는 방법을 보여줍니다.
// 계산 필드 "Tax" 추가, 값은 UnitPrice 필드의 0.08배로 계산
// UnitPrice는 피벗 테이블의 측정 필드 중 하나
myPivotTable.addCalcField("Tax", "=UnitPrice*0.08");
myPivotTable.add("Tax", "Tax Calculated is 0.08 percent of Unit Price", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
// 계산 필드 정보 가져오기
calcFieldsInfo = myPivotTable.getCalcFields();
// 새로운 시트에 계산 필드 이름 추가
for (var i = 0; i < calcFieldsInfo.length; i++) {
calcFieldSheet.setValue(i + 1, 0, calcFieldsInfo[i].fieldName);
calcField = calcFieldsInfo[i].fieldName;
calcFieldSheet.setValue(i + 1, 1, calcFieldsInfo[i].formula);
}
// 계산 필드 제거
myPivotTable.removeCalcField(calcField);
피벗 테이블의 행 또는 열 필드 영역에 계산 항목을 추가하여 지정된 수식을 사용해 특정 항목을 계산할 수 있습니다. 수식에는 상수를 사용하거나 피벗 테이블의 다른 필드를 참조할 수 있습니다.
addCalcItem
메서드를 사용하여 계산 항목을 추가할 수 있습니다. 또한 getCalcItems
, updateCalcItem
, removeCalcItem
메서드를 통해 계산 항목 정보를 가져오고, 수정하고, 제거할 수 있습니다.
계산 항목은 피벗 테이블 보고서 영역의 여러 필드에 대해 구현할 수 없습니다.
계산 항목의 수식은 워크시트의 데이터가 아닌 피벗 테이블의 데이터를 참조할 수 있습니다.
SpreadJS는 그룹화된 필드에 계산 항목을 추가하고, 해당 그룹 필드에 기본 정보를 추가할 수 있습니다.
숫자 그룹 필드나 날짜 그룹 필드가 있는 경우, 선택된 그룹에만 계산 항목을 추가할 수 있습니다.
피벗 테이블에 대한 모든 참조는 "SourceFieldName[itemName]"
형식을 따라야 합니다.
피벗 보고서의 모든 값 필드의 "계산 유형(Calculation Type)"은 다음 중 하나여야 합니다: TypeSum
, Count
, Max
, Min
, Product
, Count Numbers
.
계산 항목에 사용되는 필드는 피벗 보고서의 필터 영역(Filter area)에 존재할 수 없습니다.
SpreadJS는 계산 항목의 값이 소계(Subtotal)에 집계될지 여부를 설정할 수 있습니다.
아래 이미지는 피벗 테이블에 계산 항목 "Category[Bakery+Beverages]"
가 추가된 예시입니다.
다음 코드 샘플은 피벗 테이블에 계산 항목을 추가하고, 업데이트하며, 제거하는 방법을 보여줍니다.
// 계산 항목 "Category[Bakery+Beverages]"를 추가, 이는 "Category[Bakery] + Category[Beverages]"의 합
function addCalcItem(myPivotTable) {
myPivotTable.addCalcItem("Category", "Category[Bakery + Beverages]", "=Category[Bakery] + Category[Beverages]");
myPivotTable.autoFitColumn();
return myPivotTable;
}
// 계산 항목 업데이트
function updateCalcItem(myPivotTable) {
myPivotTable.updateCalcItem("Category", "Category[Bakery + Beverages]", "=Category[Bakery] - Category[Beverages]", 1);
myPivotTable.options.calcItemAggregation = GC.Spread.Pivot.CalcItemAggregation.include;
myPivotTable.autoFitColumn();
return myPivotTable;
}
// 계산 항목 제거
function deleteCalcItem(myPivotTable) {
myPivotTable.removeCalcItem("Category", "Category[Bakery + Beverages]");
myPivotTable.autoFitColumn();
return myPivotTable;
}
addConditionalRule
메서드를 사용하여 피벗 영역에 조건부 규칙을 적용할 수 있습니다. 또한, getConditionalRules
및 removeConditionalRule
메서드를 사용하여 기존 조건부 규칙을 가져오거나 제거할 수 있습니다. 조건부 규칙은 피벗 테이블 레이아웃이 변경되어도 정상적으로 작동합니다.
아래 이미지는 값 셀에 색상 서식 조건부 규칙이 적용된 예시입니다.
다음 코드 샘플은 피벗 테이블에 조건부 규칙을 적용하는 방법을 보여줍니다.
// 조건부 규칙 추가
myPivotTable.addConditionalRule([{ dataOnly: true, references: [{ fieldName: "City", items: ["Seattle"] }] }], new GC.Spread.Sheets.ConditionalFormatting.ScaleRule(GC.Spread.Sheets.ConditionalFormatting.RuleType.threeScaleRule, 1 /* LowestValue */, 0, "lightblue", 0 /* Number */, 500, "yellow", 2 /* HighestValue */, 2000, "green"));
mergeItem
옵션을 사용하여 레이블이 포함된 셀을 병합하고 가운데 정렬할 수 있습니다. 이 옵션은 불리언 값을 허용하며, 아래와 같이 정의된 피벗 테이블 레이아웃에 따라 작동합니다:
옵션 | 컴팩트 형식 | 아웃라인 형식 | 테이블 형식 |
---|---|---|---|
병합 | 열 헤더만 | 열 헤더만 | 열 및 행 헤더 모두 |
가운데 정렬 | 열 헤더만 | 열 및 행 헤더 모두 | 열 및 행 헤더 모두 |
아래 GIF는 mergeItem
옵션을 사용하여 레이블이 포함된 셀을 병합하고 가운데 정렬한 예시입니다.
다음 코드 샘플은 피벗 테이블에서 레이블이 포함된 셀을 병합하고 가운데 정렬하는 방법을 보여줍니다.
// 레이블이 있는 셀 병합 및 가운데 정렬
myPivotTable.options.mergeItem = true;
피벗 테이블에서 툴팁을 활성화하면 데이터에 대한 추가 정보를 쉽게 파악할 수 있습니다. 마우스를 피벗 데이터 위에 올리면 툴팁 정보가 표시됩니다. 단, 값 필드에 명확한 정보가 없을 경우에는 툴팁이 표시되지 않습니다. showToolTip
옵션을 사용하면 행/열 헤더 영역, 데이터 영역, 소계/총계 영역에서 툴팁을 표시할 수 있습니다.
아래 이미지는 마우스를 피벗 데이터 위에 올렸을 때 툴팁이 표시되는 예시입니다.
다음 코드 샘플은 피벗 테이블에서 툴팁을 활성화하는 방법을 보여줍니다.
// 피벗 테이블에서 툴팁 표시
myPivotTable.options.showToolTip = true;
serialize
메서드를 사용하여 피벗 테이블 데이터를 직렬화하고 저장할 수 있으며, deserialize
메서드를 사용하여 직렬화된 데이터를 기존 피벗 테이블에 복원할 수 있습니다. 직렬화는 다음과 같은 피벗 테이블 데이터 요소를 지원합니다: 필드, 옵션, 필터, 정렬, 레이아웃, 피벗 테이블 위치, 테마.
다음 코드 샘플은 피벗 테이블 데이터를 직렬화하고 저장한 후, 직렬화 정보를 출력하고 해당 데이터를 기존 피벗 테이블에 복원하는 방법을 보여줍니다.
// serialize 메서드를 사용하여 피벗 테이블 데이터 저장
serialization = myPivotTable.serialize();
// 직렬화 정보 출력
info = JSON.stringify(serialization, null, 2);
console.log(info);
// deserialize 메서드를 사용하여 데이터 복원
myPivotTable.deserialize(serialization);
SpreadJS는 피벗 테이블 데이터 원본에 빈 값이 존재할 경우, 해당 셀에 사용자 지정 값을 표시할 수 있는 옵션을 제공합니다.
showMissing
: 실제 값이 비어있을 때 값을 표시할지 여부를 나타냅니다. 불리언 값을 허용하며 기본값은 true
입니다.
missingCaption
: 실제 값이 비어있을 때 표시할 값을 지정합니다. 문자열 및 숫자 값을 허용하며 기본값은 비어 있음입니다.
아래 이미지는 피벗 테이블에서 빈 값을 "No Sales"로 표시한 예시입니다.
다음 코드 샘플은 빈 셀에 사용자 정의 문자열 값을 표시하는 방법을 보여줍니다.
// 빈 셀에 사용자 정의 값 표시
myPivotTable.options.showMissing = true;
myPivotTable.options.missingCaption = "No Sales";
showNoData
메서드를 사용하여 피벗 테이블에서 데이터가 없는 항목도 표시할 수 있으며, 필드 내의 빈 셀을 표시할지 여부를 선택할 수 있습니다. 이 메서드는 필드 이름과 항목을 표시할지 여부를 나타내는 불리언 값을 매개변수로 사용합니다.
이 설정은 사용자가 피벗 테이블 내 항목의 가시성을 제어함으로써 데이터 원본의 항목들을 비교하는 데 도움이 됩니다. 데이터의 가시성은 해당 항목에 값이 있는지 여부와 관계없이 적용됩니다.
아래 이미지는 판매가 없는 기간을 포함한 각 매장의 데이터를 표시하는 예시입니다.
다음 코드 샘플은 데이터가 없는 항목을 표시하는 방법을 보여줍니다.
function initPivotTable(sheet) {
myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.dark3);
myPivotTable.suspendLayout();
myPivotTable.add("City", "City", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("Product", "Product", GC.Spread.Pivot.PivotTableFieldType.rowField);
let groupInfo = { originFieldName: "OrderDate", dateGroups: [{ by: GC.Pivot.DateGroupType.months }] };
myPivotTable.group(groupInfo);
myPivotTable.add("Quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
// Set showNoData to true for displaying products which doesn't have any quantity
myPivotTable.showNoData("Product", true);
myPivotTable.resumeLayout();
myPivotTable.autoFitColumn();
return myPivotTable;
}
showDataAs
메서드를 사용하여 피벗 테이블 데이터를 다양한 계산 방식으로 표시할 수 있습니다. 이 메서드는 필드 이름과 IPivotShowDataAsInfo
객체 속성을 매개변수로 사용합니다. 값 필드는 PivotShowDataAs
열거형을 통해 다양한 계산 방식으로 설정할 수 있습니다.
아래 GIF는 "Years" 필드에 대해 누적 합계(Running Total)를 표시하는 예시입니다.
다음 코드 샘플은 Years 필드에 대해 누적 합계 값을 표시하는 방법을 보여줍니다.
// showDataAs 메서드 사용
myPivotTable.showDataAs("Sum of quantity", { showDataAs: GC.Pivot.PivotShowDataAs.runTotal, baseFieldName: "Years" });
showHeaders
속성을 사용하여 피벗 테이블이 행 및 열 방향에서 필드 이름을 표시할지 여부를 설정할 수 있습니다. 기본값은 true
입니다.
아래 GIF는 피벗 테이블에서 행 및 열 헤더를 표시하거나 숨기는 예시를 보여줍니다.
다음 코드 샘플은 피벗 테이블의 헤더를 숨기는 방법을 보여줍니다.
// showHeader 옵션을 false로 설정하여 피벗 테이블의 헤더를 숨김
pt.options.showHeaders = false;
스프레드시트를 JSON 형식으로 가져오거나 내보낼 때 피벗 테이블 스타일을 유지할지 또는 제외할지 선택할 수 있습니다. toJSON
및 fromJSON
메서드의 ignorestyle
매개변수를 true
로 설정하면, 피벗 캐시 및 피벗 테이블은 제외되며 피벗 테이블의 값만 유지됩니다.
아래 이미지는 JSON 파일에서 가져온 후 스타일이 없는 피벗 테이블의 예시입니다.
다음 코드 샘플은 스타일 없이 JSON 파일을 가져오거나 내보내는 방법을 보여줍니다.
function bindExcelIOEvent(spread) {
// IgnoreStyle 매개변수를 true로 설정하여 ssjson 파일 가져오기
var openButton = document.getElementById('openButton');
openButton.addEventListener('click', function () {
var file = document.getElementById("importFileName").files[0];
var fileName = file.name;
var suffix = fileName.substr(fileName.lastIndexOf('.'));
if (suffix === '.ssjson' || suffix === 'json') {
var reader = new FileReader();
reader.onload = function () {
var spreadJson = JSON.parse(this.result);
spread.fromJSON(spreadJson, { ignoreStyle: true });
};
reader.readAsText(file);
}
});
// IgnoreStyle 옵션을 true로 설정하여 ssjson 파일 내보내기
var saveSSJSONButton = document.getElementById('saveSSJSONButton');
saveSSJSONButton.addEventListener('click', function () {
var fileName = "ExportedFile";
var index = fileName.lastIndexOf('.');
if (index >= 0) {
fileName = fileName.substr(0, index) + '.ssjson';
} else {
fileName += '.ssjson';
}
var json = spread.toJSON({ includeBindingSource: true, ignoreStyle: true }),
text = JSON.stringify(json);
saveAs(new Blob([text], { type: "text/plain;charset=utf-8" }), fileName);
});
}
SpreadJS는 피벗 테이블 셀을 참조할 때 기본적으로 GETPIVOTDATA
함수를 사용하여 셀 참조를 생성합니다. 예를 들어,
=GETPIVOTDATA("price",$A$1,"salesperson","Alan","car","Mercedes","quantity",3)
처럼 작성됩니다.
그러나 pivotAreaReference
옵션을 cellReference
로 설정하면, SpreadJS는 피벗 테이블 셀에 대해 일반 셀 참조를 생성합니다. 예를 들어, =F6
과 같이 표시됩니다.
어떤 방식을 사용하더라도, 셀 참조를 사용하는 수식의 결과는 동일합니다. 이 기능을 사용하면 피벗 테이블이 변경되더라도 수식을 변경하지 않고 특정 셀의 참조를 유지할 수 있습니다.
또한 일반 셀 참조는 사용자가 수식 참조를 여러 셀에 드래그하여 채우고자 할 때 유용합니다. 예를 들어, 위의 수식을 아래 방향으로 드래그하면 =F7
, =F8
등으로 이어집니다. 이 동작은 GETPIVOTDATA
함수에서는 불가능합니다.
다음 표는 GETPIVOTDATA
함수와 일반 셀 참조의 셀 참조 방식 차이를 보여줍니다:
GETPIVOTDATA 함수 | Normal 셀 참조 |
---|---|
// 피벗 테이블 셀에 대해 일반 셀 참조 사용
workbook.options.pivotAreaReference= GC.Spread.Pivot.PivotAreaReference.cellReference;
일반 피벗 테이블 참조는 JSON 가져오기 및 내보내기를 지원합니다. 그러나 이 기능은 Excel에서는 작동하지 않습니다. Excel은 해당 옵션을 저장하지 않기 때문입니다.