기술 문서

HotXLS로 보는 Delphi XLSX 피벗 필드 스키마 유효성

pivotField 요소의 axis="rowAxis" 속성은 완벽히 말이 되는 XML처럼 읽히고, 그것을 "행 필드"로 연역하는 리더는 모든 필드를 올바른 축에 얹은 피벗을 불러온다. 그런데 같은 파트를 ECMA-376 Part 1 §18.10 스키마로 만든 검증기에 통과시키면 첫 속성에서 실패한다. ST_Axis에는 정확히 네 개의 토큰, axisRow, axisCol, axisPage, axisValues가 있고 rowAxis는 그중 어느 것도 아니다. 피벗 라이터는 자기 리더가 아니라 스키마를 만족해야 한다. 파일을 여는 이들은 Excel과 각자의 파서를 쓰기 때문이다

피벗 필드 정의에는 저런 규칙이 넘쳐난다. 왕복 테스트를 슬쩍 지나치는 것은 셋이다. 열거형 토큰, items 요소가 절대 비어 있으면 안 된다는 요구, Excel이 데이터 항목 뒤에 나열하는 꼬리 소계 항목. 읽는 쪽에는 둘이 더 있는데, 스키마 기본값을 담고 있어서 Excel이 생략해 버리는 속성들이다. 이 글의 나머지는 각 규칙을 훑고, XML이 어떤 모습이어야 하는지, 그리고 그것을 만들어 내는 Delphi 호출을 보여 준다

왕복은 엉터리 axis 토큰을 못 잡는 이유

왕복은 라이터와 리더가 서로 합의한다는 사실 이상을 증명하지 못한다. 리더가 axisRow와 rowAxis를 둘 다 받아들인다면 엉터리쪽을 내보내는 라이터는 여러분이 쓸 수 있는 모든 저장-다시-불러오기 검사를 통과한다. 리더가 돌아오는 길에 실수를 고쳐 주기 때문이다. 결함은 더 엄격한 소비자가 파트를 열 때만 드러난다. 스키마 검증기, XSD에서 생성된 파서, 파트에 수리가 필요하다고 판단하는 Excel

HotXLS는 두 쪽을 일부러 갈라 놓았다. XLSX 라이터는 네 개의 ST_Axis 토큰만 내보내고, 피벗 리더는 영어처럼 보이는 표기 rowAxis, colAxis, pageAxis, dataAxis도 알맞은 축으로 연역해서, 그것들을 쓰는 생산자의 파일도 배치가 온전한 채로 로드된다. 출력은 스키마에 묶이고 입력은 식별 가능하기만 하면 된다. 반대 조합, 엄격한 리더에 성긴 라이터라면 적어도 여러분 테스트에서 시끄럽게 실패한다. 관대한 리더에 성긴 라이터는 어디에서도 보이지 않게 실패한다

<!-- 잘못된 예: rowAxis는 ST_Axis 값이 아니고 CT_Items는 비어 있을 수 없다 -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- 올바른 예: ST_Axis 토큰, 실제 item, 꼬리 기본 소계 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
잘못된 pivotField XML과 올바른 pivotField XML: 발명된 axis 값 rowAxis와 빈 items 요소는 CT_PivotField를 위반하고, HotXLS는 axisRow 같은 ST_Axis 토큰을 실제 item 항목과 유지된 hidden 플래그, 스키마가 받아들이는 꼬리 기본 소계와 함께 내보낸다
관대한 리더가 두 표기를 다 받아들이면 모든 왕복은 통과하는 동안 파일은 어떤 엄격한 스키마 검사도 깨뜨린다. 네 ST_Axis 토큰만 쓰고 CT_Items가 최소한 item 하나는 실게 하라

축 너머에서 CT_PivotField가 요구하는 것

CT_PivotField에는 버리기 쉬운 요구가 셋 더 있다. 값 영역에서 집계되는 필드는 <dataFields> 목록이 이미 참조하고 있더라도 자기 정의에 dataField="1"으로 그 사실을 말해야 한다. HotXLS는 DataFields의 항목이 가리키는 모든 피벗 필드에 그 플래그를 세팅하므로, AddDataFieldByName으로 추가된 필드는 두 자리 모두에 표시된다

둘째 규칙은 구조적이다. CT_Items는 item 자식을 최소 하나 요구하므로, 잘 형성된 XML이고 어디서든 파싱되더라도 <items count="0"></items>는 잘못됐다. 필드에 항목이 없으면 HotXLS는 items 요소 전체를 생략한다. 셋째 규칙은 항목별 상태 관련이다. 숨겨진 멤버는 h="1"을, 세부가 접힌 멤버는 sd="0"을 싣는다. 둘 다 TXLSPivotItem.IsHidden과 TXLSPivotItem.IsDetailHidden에서 나와 저장 때마다 쓰이므로, 코드에서 건 필터가 Excel까지 살아남는다

꼬리 소계 항목과 count의 함정

필드에 항목이 있으면 Excel은 데이터 항목들 뒤를 소계 함수마다 item 하나씩 잇고, ST_ItemType으로 타입을 매긴다. 자동 소계는 <item t="default"/>이고 명시적 함수는 sum, countA, avg, max, min, product, count, stdDev, stdDevP, var, varP를 쓴다. 이 항목들은 캐시를 가리키는 x 인덱스를 싣지 않고 items의 count 속성에 포함된다. 그것들을 빼먹는 라이터는 같은 요소의 소계 플래그와 더 이상 맞지 않는 items 목록을 만든다

HotXLS는 저장 때 TXLSPivotField.Subtotals에서 꼬리를 유도한다. 집합의 함수마다 열거 순서대로 t="…" 항목 하나씩, count에 더하고, 필드에는 sumSubtotal="1"식의 알맞은 속성을 붙인다. AddPivotTable이 만든 필드는 빈 집합으로 시작하며, 그것은 defaultSubtotal="0"과 꼬리 항목 없음으로 쓰인다. 생성되는 보고서에는 집합을 비워 두어라. 소계 행은 피벗의 높이를 바꾸고 그 아래 놓인 것들을 밀어낸다. Subtotals에 함수는 읽는 이가 추가 행을 필요로 하는 필드에만 넣어라

함수를 고르기 전에 이름은 두 번 봐야 한다. xlpsCount는 모든 비어 있지 않은 항목을 세는 countA로, xlpsCountNums는 숫자만 세는 count로 매핑된다. XML의 "count"를 읽고 xlpsCount에 손을 뻗는 사람은 다른 함수를 얻는다

HotXLS pivot items 목록 해부: 데이터 item 항목 뒤에 TXLSPivotField.Subtotals에서 유도된 t=default, t=avg 같은 꼬리 소계 item이 붙어 items count에 세어지며, xlpsCount는 countA로, xlpsCountNums는 count로 매핑되는 명명 함정이 명시되어 있다
AddPivotTable이 만든 필드는 빈 Subtotals 집합으로 시작해 defaultSubtotal=0과 꼬리 item 없이 기록된다. 원하는 함수를 요청하면 작성기가 함수당 item 하나를 유도해 count에 넣는다

아래 프로그램은 기존 데이터 시트에서 행 필드, 열 필드, 값 필드 하나로 피벗을 만든다. Region 필드는 자동 소계에 평균을 더해 데이터 항목 뒤에 꼬리 항목 둘을 낳고, Revenue에는 dataField="1"이 붙는다

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // Sheets는 1부터 시작한다
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // 그런 필드가 없으면 nil
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // Revenue가 dataField="1"을 받는다

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

Excel이 생략한 것 읽기

리더에는 거울상의 문제가 있다. Excel이 만든 피벗 필드는 소계 항목들을 items에 나열하는데, 그것들을 평범한 멤버로 불러오면 캐시 값 하나를 가리키지 않는 유령 항목들이 필드에 생긴다. HotXLS는 t 속성이 존재하고 data가 아닌 item은 전부 건너뛴다. 소계, 총합계, 빈 항목이 그 범위다. 소계 함수는 필드 자신의 플래그로 도착해 Subtotals에 자리 잡으므로, Items를 걷는 코드는 진짜 멤버만 보고 모든 CacheItemIndex가 캐시 값을 가리킨다

없는 속성이 나머지 절반이다. 스키마에서 CT_PivotField의 defaultSubtotal과 CT_SharedItems의 containsString은 기본값이 true이고 Excel은 그 값을 담을 때 이들을 생략한다. HotXLS는 없는 defaultSubtotal을 true로 읽고 필드에 명시적 소계 플래그가 없으면 Subtotals에 xlpsDefault를 더하는데, Excel 자신이 그런 필드를 보여 주는 방식이 바로 그것이다. 없는 containsString도 true로 읽히므로 평범한 텍스트 캐시 필드는 문자열 데이터로 분류된다. 어느 속성이든 false로 읽으면 Excel이 저장한 모든 피벗이 로드 때 자동 소계를 잃고 텍스트 필드는 다른 타입으로 변한다. 모든 속성을 늘 다 적어 내는 라이터는 이 경로를 전혀 밟지 않는다. 이것을 단련하는 것은 다른 생산자의 파일뿐이다

Excel이 저장한 워크북이 여기서 알맞은 테스트 입력이다. 그 속성들을 생략하는 파일은 이것뿐이기 때문이다. 아래 검사가 하나를 열어 행 필드를 걷는다:

var
  Book : TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Fld  : TXLSPivotField;
  I, J : Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('saved-by-excel.xlsx');
    Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
    if Pivot = nil then
      Exit;
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Fld := Pivot.Fields[I];
      if Fld.Axis <> xlpfaRowField then
        Continue;
      // 소계 속성이 없는 Excel 필드도 기본 소계를 보고한다
      if xlpsDefault in Fld.Subtotals then
        Writeln(Fld.Name, ': automatic subtotal');
      // 소계 항목은 건너뛰어졌으므로 각 item이 캐시 값으로 매핑된다
      for J := 0 to Fld.ItemCount - 1 do
        Assert(Fld.Items[J].CacheItemIndex >= 0);  // 여러분 테스트를 위한 권장 검사
    end;
  finally
    Book.Free;
  end;
end;

numFmtId="General"이 캐시 필드에서 잘못되는 이유

numFmtId="General"이 잘못된 이유는 ST_NumFmtId가 부호 없는 정수이기 때문이다. 셀 서식 대화 상자에서 사용자가 보는 이름을 빌려 오는 것이 아무리 자연스러워도. 스키마에서 속성 타입을 매기는 파서는 그 이름을 즉시 거절하고, 그 계열의 실패가 바로 Excel의 수리 대화 상자로 변한다. Excel 수리 프롬프트 뒤의 OPC와 마크업 규칙 글이 그 대화 상자들이 어떻게 촉발되는지 다룬다. HotXLS는 모든 cacheField에 캐시 필드의 NumberFormat에서 온 숫자 numFmtId를 쓰는데, 무언가 세팅하지 않았다면 그것은 0, 내장 General 서식이다

65535행 아래의 피벗 테이블은 32비트 위치가 필요하다

XLS와 XLSX 엔진이 공유하는 피벗 모델은 BIFF8 SxView 레코드에서 필드 폭을 빌리고 싶어진다. 그 레코드에서는 16비트가 고전 시트가 담을 수 있는 모든 행을 커버한다. XLSX 시트는 1,048,576행까지 간다. FirstRow, LastRow, FirstHeaderRow, FirstDataRow와 열 짝을 Word로 저장하고 Min(Value, High(Word))로 잘라 버리면, 70001행에 앵커된 피벗은 65535에서 잘려 나간다

TXLSPivotTable에서 그 속성들은 Integer이고 레코드 경계에서 폭을 줄이는 것은 BIFF8 라이터뿐이다. TXLSXWorksheet.AddPivotTable은 1..1048576 × 1..16384 밖의 앵커에 nil을 돌려주고, AddPivotTableCopy는 복사본의 범위가 격자를 넘어서면 nil을 돌려준다

16비트 천장에 걸린 70001행 피벗 앵커: FirstRow와 LastRow를 Word로 저장하고 Min으로 High(Word)인 65535에 잘라 버리면 그 선 아래 피벗이 잘리고, HotXLS는 이들을 Integer 필드로 유지해 격자 밖에서는 nil을 돌려준다
Word 폭의 행 필드는 BIFF8 SxView 레코드에는 맞지만 1048576행까지 가는 시트에는 맞지 않는다. 그것들이 있으면 65535행을 넘는 앵커는 65535로 잘려 피벗이 놓인 자리에 더 이상 앉지 않는다
var
  Book, Check: TXLSXWorkbook;
  Pivot      : TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    // 70001행은 16비트 범위를 넘는다
    Pivot := Book.Sheets[1].AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
    if Pivot = nil then
      Exit;  // 시트 밖 앵커 또는 해석 불가한 소스 범위
    Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);
    Book.SaveAs('late.xlsx');
  finally
    Book.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    // Assert는 여러분 테스트를 위한 권장 검사
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

고전 XLS 엔진은 같은 1 기반 관례를 따른다. SxView와 DConRef 레코드는 0 기반 값을 저장하고, 모델은 XLSX 엔진과 Cells[Row, Col]처럼 1 기반 위치를 유지한다. BIFF8 리더는 레코드 경계에서 1을 더하고 라이터는 1을 뺀다. 고전 AddPivotTable은 1..65536 × 1..256 밖의 앵커에 nil을 돌려주므로, 피벗은 (0, 0)이 아니라 (1, 1)에 앵커하라. 레코드 배치 자체는 고전 .xls 피벗 테이블 뒤의 BIFF8 SX 레코드에서 기술된다

자기 리더 대신 스키마로 검증하기

관대한 쪽과 결함 있는 쪽이 같은 라이브러리에 살면 위 함정들 전부가 왕복을 살아남는다. 그것들을 잡는 검사는 셋이다. 생성된 pivotTableN.xml과 pivotCacheDefinitionN.xml 파트의 스키마 검증은 발명된 토큰, 빈 items 요소, 이름으로 적은 포맷 id를 잡는다. Excel이 저장한 파일을 여러분 리더로 통과시키는 것은 Excel이 기본값에서 생략한 속성들을 단련한다. 쓰인 XML에서 정확한 토큰을 못 박는 단정문, 이르면 axis="axisRow",는 라이터가 여러분 리더만 아는 표기로 흘러가는 것을 잡는다. 파싱된 축에 대한 단정은 못 잡는다

API로 만든 피벗, 이르면 계산 필드로 XLSX 피벗 테이블 만들고 새로 고치기에서 보여 주는 계산 필드, 계산 항목, 백분율 레이아웃은 이 규칙들이 적용된 채 여러분 쪽 추가 코드 없이 나온다. 남는 작업은 어느 필드에 소계를 달지, 그리고 count 함수로 무엇을 뜻할지 고르는 것이다

HotXLS Delphi 스프레드시트 컴포넌트는 Delphi와 C++Builder 코드에서 XLS와 XLSX 피벗 테이블을 만들고 읽고 저장하며, 그것을 돌리는 기계에는 Excel 설치도 COM 자동화도 필요 없다