HotXLS를 사용하여 Delphi에서 Excel 피벗 테이블 빌드 및 새로 고침

HotXLS는 로컬 시스템에 Excel이 설치되어 있지 않거나 COM 자동화 라이브러리를 사용하지 않는 환경에서도 Delphi 및 C++Builder에서 네이티브 XLSX 피벗 테이블을 빌드하고 새로 고침(Refresh)합니다. 소스 범위와 함께 AddPivotTable을 호출하고 행, 열, 페이지 및 데이터 축에 필드를 배치하며, 계산된 항목이나 합계 비율 표시 등을 추가하면 컴포넌트가 Excel이 실시간으로 새로 고칠 수 있는 라이브 피벗으로 인식하여 열 수 있도록 pivotCacheDefinition 및 pivotTableDefinition 파트를 작성합니다

이 기능이 필수적으로 활용되는 대표적인 시나리오는 보고서 생성 서버입니다. 매일 밤 수백 개의 통합 문서를 생성하고 각 문서마다 특정 계정을 요약하는 피벗 테이블이 포함되는데, 다음 달 데이터가 변경되면 모든 파일이 새로운 소스 행을 반영해야 합니다. Windows 서비스 환경에서 Excel 프로세스를 직접 구동하는 방식은 아키텍처상 취약하고 서버 용도 라이선스에도 맞지 않으며, 피벗 XML을 수동으로 직접 작성하는 것은 방대한 스펙 문서의 미궁에 빠지는 것과 같습니다. HotXLS는 이 두 가지 한계 사이에서 해결책을 제공합니다: OOXML 피벗 파트 상에 정형화된 객체 모델을 제공하므로, 셀 데이터를 채우는 동일한 Pascal 코드로 피벗을 선언하고 동일한 프로세스 내부에서 캐시를 재구축할 수 있습니다

Excel 없이 Delphi에서 피벗 테이블을 어떻게 생성하나요?

단 한 번의 호출로 피벗 테이블을 생성한 뒤 필드를 각 축에 배치할 수 있습니다. AddPivotTable은 A1 표기법 형태의 소스 범위, 테이블이 고정될 대상 셀, 테이블 이름을 입력받습니다. 이 메서드는 범위를 파싱하고, 각 열을 스캔하여 데이터 형식을 유추하며, 피벗 캐시를 작성하거나 동일 범위에 이미 바인딩된 캐시를 재사용하고, 모든 필드가 비활성 축 상태에서 시작하는 TXLSPivotTable을 반환합니다. 여기에서 편의 메서드인 AddRowField, AddColumnField, AddPageField, AddDataFieldByName을 사용해 레이아웃을 구성하며, 각 데이터 필드는 TXLSPivotAggregation 열거형(xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)이 정의하는 11가지 집계 방식 중 하나를 적용받습니다.

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // 보고서 시트 (XLSX 엔진에서는 1부터 시작합니다)

    // 'Data' 시트의 A1:E500이 소스이며, 행 3, 열 1 위치에 피벗을 고정합니다
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

왜 캐시와 테이블은 서로 독립된 구조로 분리되어 있나요?

피벗 테이블은 실제로 서로를 참조하는 두 가지 개별 구조물로 구성되며, 이 물리적 분리를 이해해야 나머지 개념들을 정립할 수 있습니다. pivotCacheDefinition은 데이터 스냅샷입니다: 소스 범위를 가리키는 worksheetSource와, 해당 열의 고유 값(sharedItems) 및 유도된 한계값을 유지하는 열당 하나의 cacheField를 포함합니다. pivotTableDefinition은 뷰(View)입니다: 어떤 캐시 필드가 어떤 축에 올라가 있는지, 데이터 필드와 이들의 집계 방식, 레이아웃 옵션 등을 나타냅니다. 이 뷰는 ECMA-376 Part 1 §18.10 및 [MS-XLSX]의 명세에 따라, 통합 문서의 pivotCaches 관계를 통해 cacheId로 캐시에 바인딩됩니다

이러한 간접 연결 구조는 아키텍처상의 번거로움이 아니라 두 가지 확실한 장점을 가져다줍니다. 첫째, 하나의 캐시가 여러 테이블을 지원할 수 있으므로 캐시를 단 한 번 새로 고치는 것만으로 해당 캐시에서 그려진 모든 뷰가 자동으로 업데이트됩니다. 둘째, 캐시는 각 열의 값을 원시 그리드 데이터로 중복 저장하는 대신 레코드별 인덱스 테이블을 통해 중복이 제거된 형태로 기록합니다. 이 때문에 피벗을 빌드하는 작업은 단순히 셀을 복사하는 것이 아니라 소스 데이터를 스캔하는 동작에 가깝습니다. HotXLS는 두 엔진 모두에 대해 동일한 방식으로 이를 모델링하므로, 위 코드는 클래식 .xls 피벗의 바이너리 SX 레코드 레이아웃에서 다루는 레거시 경로와 거의 일치합니다. 소스 데이터가 다른 시트에 있거나 정의된 이름을 통해 접근하는 경우, 범위 접두사 및 정의된 이름과 교차 시트 참조는 일반적인 A1 규칙을 따르며 공백이 포함된 시트명을 위해 따옴표 사용도 지원됩니다

계산된 항목(Calculated item)과 계산된 필드(Calculated field)의 차이

이 세 단어는 완전히 다른 세 개의 개념을 지칭하며, 이들을 섞어서 사용하는 것은 흔히 일어나는 실수입니다. 계산된 항목(Calculated item)은 하나의 필드 내부에서 활성화되어 해당 필드의 항목들을 이름을 기준으로 조합합니다. 예를 들어 Region 필드 내에서 North와 South의 합과 같은 가상의 CoreMarkets 행을 정의할 수 있습니다. HotXLS는 이를 TXLSPivotField.AddCalculatedItem 메서드로 제공하며 해당 필드의 <calculatedItems> 하위에 <calculatedItem>을 작성합니다. 계산된 필드(Calculated field)는 다릅니다: Revenue와 Cost로부터 마진(Margin)을 계산하듯 다른 열로부터 유도되는 완전히 새로운 값이며, TXLSPivotCacheField.Formula를 통해 캐시 필드 상의 수식으로 전송됩니다. TXLSPivotTable.AddCalculatedMember로 추가하는 계산된 멤버(Calculated member)는 주로 OLAP 형태의 피벗에서 측정값(멤버 형식 data) 또는 차원 멤버로 작동하는 테이블 수준의 커스텀 멤버입니다

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // 계산된 항목(Calculated item)은 단일 필드의 항목들을 이름 기준으로 결합합니다
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // 계산된 멤버(Calculated member)는 테이블 수준에서 선언됩니다. 멤버 형식을
  // 'data'로 지정하면 측정값으로 처리되며, 비워두면 차원 멤버가 됩니다
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

여기에 정의된 모든 계산 기능에 대해서는 한 가지 명확한 한계가 존재합니다. HotXLS는 포뮬러 텍스트를 구성 사양 XML에 그대로 출력할 뿐, 이를 직접 계산하지는 않습니다. Excel이 파일을 열 때 계산된 항목, 필드 및 멤버를 실시간으로 다시 계산하며, 이는 시트 내부의 통계 집계 연산 방식과 동일합니다. HotXLS는 결과가 아닌 구조 명세서만 작성하므로, 수식 문자열은 Excel이 해석할 수 있는 형식이어야 하며 필드 및 항목 이름 또한 Excel 표준 명칭과 완벽하게 일치해야 합니다

값을 총합 비율(Percent of total)로 표시하려면 어떻게 해야 하나요?

수치를 수동으로 직접 계산해서 채우는 대신 데이터 필드 상의 값 표시 모드를 구성할 수 있습니다. TXLSPivotDataField.ShowDataAs는 OOXML ST_ShowDataAs 값을 투영한 TXLSPivotShowDataAs 열거형(xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, xlpsdaIndex)을 매개변수로 취합니다. 자주 쓰이는 유용한 기법은 동일한 소스 열을 데이터 축에 두 번 배치하는 것입니다: 한 번은 원시 수량 합계로 지정하고, 다른 한 번은 총합 대비 비율로 설정하면 보고서에 총량 수치와 함께 비중 비가 동시에 표시됩니다

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // 항목 상대 모드는 비교 대상이 될 기준(Base)을 지정해야 합니다. 'Quarter' (캐시
  // 필드 인덱스 3)를 따르는 누계 매출액 연산은 다음과 같습니다:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // 변환 연산이 처리되는 대상 캐시 필드 인덱스
  //   Share.BaseItem   := $7FFD;     // $7FFD는 전체를 의미하는 "(All)" 기호입니다
end;

xlpsdaPercentOfTotal은 그랜드 토탈(Grand total)을 기준으로 동작하므로 별도의 기준 필드가 필요 없으나, 다른 비교 모드에서는 기준 값이 필수입니다. xlpsdaDifference, xlpsdaPercentDiff, xlpsdaRunTotal 등은 기준 필드 인덱스인 BaseField를 명시해야 하며, 항목 기반 형태의 비교에서는 BaseItem 인덱스 역시 명시해야 합니다. 여기서 $7FFD는 (All) 의미로 통용되는 예약 값입니다. HotXLS는 이 구조를 <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> 형태로 기록하고 산술 연산 자체는 Excel에 위임합니다

날짜 및 숫자 그룹화와 페이지 필드 필터

그룹화 기능은 소스 데이터 도메인을 버킷(Bucket)으로 분할하는 방식을 결정하므로 피벗 필드가 아닌 캐시 필드(Cache field) 상에서 설정됩니다. Cache.FindFieldByName이 반환하는 캐시 필드에 HasGroup := True를 지정하고, 숫자 범위 파라미터(GroupStartNum, GroupEndNum, GroupInterval) 또는 날짜 계층 구조 플래그(GroupByDate와 함께 GroupMonths, GroupQuarters, GroupYears 및 GroupStartDate / GroupEndDate 범위)를 선택할 수 있습니다. HotXLS는 그에 맞는 <fieldGroup><rangePr groupBy="months"/> 요소를 출력하므로 월 단위 또는 1000 단위 폭의 숫자로 그룹화된 필드가 Excel에서 생성했던 것과 동일한 구조로 열립니다

페이지 필드는 피벗 테이블 위에 표시되는 필터 드롭다운입니다. AddPageField는 페이지 축에 필드를 배치하며, PageItemIndex는 단일 캐시 항목을 사전에 선택하고 기본값은 전체를 나타내는 xlPageItemAll($7FFD)입니다. 사용자가 한 번에 여러 항목을 체크할 수 있도록 설정하려면 MultipleItemSelectionAllowed := True를 적용하십시오. HotXLS는 이를 <pivotField multipleItemSelectionAllowed="1"/>로 파일에 기록합니다. 수동 선택 이외의 추가 필터 연산을 위해, 모든 필드는 OOXML ST_FilterType 규격군을 아우르는 정형화된 Filters 컬렉션(개수, 비율, 합계, 캡션, 값 및 날짜 필터 등)을 보유하며 각 필터 항목은 해당 필터 방식과 비교 조건 값의 쌍으로 구성됩니다

소스 데이터가 변경되었을 때 피벗 캐시를 어떻게 새로 고치나요?

TXLSXWorkbook의 RefreshPivotCache 메서드는 소스 범위를 다시 스캔하고 해당 캐시 영역을 프로세스 내에서 재구성합니다. 이는 배치 파이프라인에서 기본 행 데이터들을 수정한 뒤 동기화하는 데 유용합니다. 캐시 ID를 전달하면 메서드는 소스 범위 내의 모든 셀(첫 번째 행은 헤더, 두 번째 행부터는 데이터)을 다시 읽어들이고, 값들을 재분류하여 고유 값 도메인을 빌드하고 필드 데이터 형식별 영역 한계를 다시 정의하며 레코드별 항목 인덱스를 새로 작성합니다. 성공 시 1을 반환하고 캐시 ID를 찾지 못했거나 소스 시트가 유실된 경우 -1을 반환합니다.

var
  Rc: Integer;
begin
  // ...피벗이 생성된 이후 소스 데이터가 변경되었습니다...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // 교정된 매출액 수치

  // 소스로부터 캐시의 고유 항목들과 레코드 인덱스들을 재구축합니다
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1은 갱신 성공, -1은 캐시 또는 소스가 누락됨을 의미합니다
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

여기에 수반되는 기능적 경계를 명확하게 이해할 필요가 있습니다. 이 메서드가 구현되기 전까지 HotXLS는 단순히 refreshOnLoad="1" 플래그만 파일에 기록하여 다음 사용자가 Excel로 파일을 열 때 새로 고쳐지도록 연산을 위임했습니다. 이는 사람이 직접 열어볼 때는 문제없지만 자동화된 백엔드 서비스 환경에서는 출력 데이터의 신뢰성을 담보할 수 없게 만듭니다. RefreshPivotCache는 저장할 캐시를 인프로세스 상에서 실시간으로 새로 구성하며, 모든 피벗 테이블이 캐시 ID를 매개로 연결되므로 동일 캐시 상의 모든 뷰가 동시에 업데이트됩니다. 다만 본 메서드는 최종 요약 렌더링 그리드를 다시 레이아웃하지는 않습니다. 최종 화면 집계는 문서를 열 때 Excel 프로세스가 캐시 데이터를 바탕으로 최종 전개합니다

HotXLS가 작성하는 것과 Excel이 계산하는 것

각 프로그램의 명확한 역할 분담을 인지하고 있어야 구현 시 헷갈리지 않습니다. HotXLS는 피벗의 사양을 기술하는 작성기(Writer)입니다: 축 배치, 집계 방식, 계산된 항목 및 멤버, 값 표시 방식, 그룹화 및 필터가 완벽하게 구성된 pivotCacheDefinition, 캐시 레코드 및 pivotTableDefinition을 파일에 직접 출력합니다. Excel은 연산을 처리하는 계산기(Calculator)입니다: 파일을 여는 시점에 그룹 버킷을 구현하고, 계산된 수식들을 연산하며, showDataAs 변환을 적용하고 테이블 본문을 요약 집계합니다. 따라서 사용자가 보는 화면 상의 수치들은 HotXLS가 작성한 제어 사양에 의거해 Excel이 연산한 결과물이며, 렌더링 검사 단계가 아닌 작성 단계에서 모든 수식과 기준 인덱스를 정교하게 구성해 두어야 하는 이유가 바로 여기에 있습니다

이 기능에 맞춰 설계를 진행하기 전 두 가지 제약 사항을 꼭 파악해 두십시오. AddPivotTable은 선택적인 시트 명칭 접두사를 포함하는 직사각형 A1 범위만 파싱할 수 있습니다: 이름이 정의된 범위(Named range) 또는 외부 통합 문서 소스는 빌더가 직접 처리할 수 없는 영역이지만, Excel이 정상적으로 작성한 파일로부터 캐시를 읽을 때 이름이 정의된 범위 데이터가 왕복 전송 과정에서 손실 없이 보존됩니다. 또한 디스크에서 읽어들인 Excel 생성 피벗 테이블은 저장 시 바이트 단위 그대로 다시 기록되므로, 여기에 설명된 피벗 데이터 수정 연산은 코드로 직접 빌드하는 피벗 테이블에 안정적으로 적용되며 기존의 피벗 구조를 깨뜨리지 않습니다. 피벗 테이블이 요약하는 보고서의 입력 부류(데이터 유효성 검사 셀 및 필터링된 구조적 표)에 대해서는 데이터 유효성 검사, 자동 필터 및 구조적 표 가이드를 참조하십시오

여기에 수록된 피벗 모델은 동일한 객체 구조를 통해 XLSX 정형 피벗 파트와 레거시 BIFF8 레코드 파일을 모두 읽고 쓰는 Delphi 및 C++Builder용 표준 HotXLS Delphi Excel 컴포넌트의 일부로 제공됩니다