[]
        
(Showing Draft Content)

피벗 테이블 설정

피벗 테이블의 설정을 수정하여 더 이해하기 쉽게 만들 수 있습니다.

열 자동 맞춤

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 메서드를 사용하여 피벗 영역에 조건부 규칙을 적용할 수 있습니다. 또한, getConditionalRulesremoveConditionalRule 메서드를 사용하여 기존 조건부 규칙을 가져오거나 제거할 수 있습니다. 조건부 규칙은 피벗 테이블 레이아웃이 변경되어도 정상적으로 작동합니다.

아래 이미지는 값 셀에 색상 서식 조건부 규칙이 적용된 예시입니다.



다음 코드 샘플은 피벗 테이블에 조건부 규칙을 적용하는 방법을 보여줍니다.

// 조건부 규칙 추가
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 입출력

스프레드시트를 JSON 형식으로 가져오거나 내보낼 때 피벗 테이블 스타일을 유지할지 또는 제외할지 선택할 수 있습니다. toJSONfromJSON 메서드의 ignorestyle 매개변수를 true로 설정하면, 피벗 캐시 및 피벗 테이블은 제외되며 피벗 테이블의 값만 유지됩니다.

아래 이미지는 JSON 파일에서 가져온 후 스타일이 없는 피벗 테이블의 예시입니다.

Pivot_table_without_style.png


다음 코드 샘플은 스타일 없이 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 셀 참조

image-20230301-065943

image-20230301-065903

// 피벗 테이블 셀에 대해 일반 셀 참조 사용
workbook.options.pivotAreaReference= GC.Spread.Pivot.PivotAreaReference.cellReference;

일반 피벗 테이블 참조는 JSON 가져오기 및 내보내기를 지원합니다. 그러나 이 기능은 Excel에서는 작동하지 않습니다. Excel은 해당 옵션을 저장하지 않기 때문입니다.