HotXLS는 TXLSPivotField의 두 속성으로 Delphi에서 PivotTable 항목을 값으로 정렬합니다. SortType이 오름차순이나 내림차순을 고르고, SortDataField가 순서를 결정하는 0부터 시작하는 데이터 필드를 이름 짓습니다. HotXLS가 Excel이 만든 XLSX를 열 때는 autoSortScope 요소에서 그 데이터 필드를 회복하는데, Excel은 Values 필드를 -2의 부호 없는 표기인 field="4294967294"로 저장합니다
값으로 정렬은 모든 판매 보고서가 결국 받는 요청입니다. "지역을 큰 것부터 작은 것 순으로 보여 달라"죠. Excel UI에서는 클릭 두 번입니다. 파일 형식에서는 속성 하나, 중첩된 피벗 영역, 예약된 필드 인덱스, 선택된 데이터 필드 서수이며, 이 조각들 중 하나라도 빠뜨린 리더는 불평 없이 파일을 엽니다. 그저 잘못된 기준으로 정렬할 뿐입니다
Excel은 값 기준 정렬 측정값을 어디에 저장하나
Excel은 정렬 측정값을 필드 자체가 아니라 오직 autoSortScope 안에 저장합니다. <pivotField>의 sortType 속성(ECMA-376 Part 1의 ST_FieldSortType 값인 manual, ascending, descending)이 어느 방향으로 정렬할지 말합니다. 무엇을 기준으로 정렬할지는 자식 <autoSortScope>에 살고, 이것은 데이터 축 참조가 하나의 데이터 필드를 고르는 <pivotArea>를 감쌉니다
<pivotField axis="axisRow" showAll="0" sortType="descending">
<items count="5"> ... </items>
<autoSortScope>
<pivotArea dataOnly="0" outlineLevel="all" fieldPosition="0">
<references count="1">
<reference field="4294967294" count="1" selected="0">
<x v="0"/>
</reference>
</references>
</pivotArea>
</autoSortScope>
</pivotField>reference 요소는 평소에는 인덱스로 피벗 필드를 가리킵니다. 필드 인덱스 -2는 ECMA-376의 PivotTable 파트에서 Values 의사 필드로 예약되어 있는데, 두 개 이상의 측정값이 축에 앉으면 Excel이 "Values"로 보여 주는 바로 그것입니다. 속성은 부호 없는 32비트 정수로 타입되어 있으므로, -2는 전송상 4294967294로 갑니다. 그 아래의 <x v="0"/>은 데이터 필드 0, 즉 <dataFields>의 첫 항목을 고릅니다. <pivotField>에는 rankBy 속성도 있지만, 비교한 파일들에서 Excel은 그것을 빼고 스코프에만 의존했습니다
정렬 측정값은 왜 -1로 돌아왔을까
HotXLS 리더가 원래 정렬 측정값을 잃은 이유는 4294967294가 부호 있는 32비트 정수에 맞지 않기 때문입니다. 속성 헬퍼는 StrToIntDef의 얇은 래퍼인데 범위 밖 값은 조용히 기본값을 돌려주므로, 데이터 축 검사는 결코 일치하지 않았고 SortDataField는 -1에 머물렀으며, 필드는 측정값 없이 sortType="descending"만 쥐고 있었습니다
실패는 최악의 방식으로 조용했습니다. 측정값 없는 내림차순 필드는 여전히 정렬됩니다. 항목 레이블의 역순으로 정렬할 뿐이죠. 그래서 Excel이 총계로 정렬했던 보고서가 HotXLS에서는 항목 순서만 뒤집힌 채 돌아왔습니다. 아무것도 예외를 던지지 않았고 모든 셀의 숫자는 정확했습니다. 행 순서만 틀렸고, Excel이 만든 파일에서만 그랬습니다
lxPivotXml.pas의 수정은 어떤 정수 변환 전에 raw 속성 토큰을 문자열로 비교합니다. rankBy가 이미 측정값을 정했다면 그것이 이기고, 그 외에는 field 토큰이 정확히 '4294967294'인 참조가 선택된 x 값에서 SortDataField를 해석합니다. 평가 쪽은 바꿀 필요가 없었습니다. 비교자는 이미 행과 열 축에서 멤버별 총계를 확정하고, 내림차순을 다루고, 무승부를 깨고 있었습니다. 데이터가 단지 도달하지 않았을 뿐입니다
일반 교훈은 Delphi로 쓴 어떤 OOXML 리더에도 적용됩니다. ECMA-376은 여러 인덱스 속성을 xsd:unsignedInt로 타입한 뒤 범위의 꼭대기를 센티널로 쓰므로, 기본값 폴백을 가진 부호 있는 리더는 예약된 값을 "속성 없음"으로 바꿉니다. 필드 인덱스가 Values를 의미할 수 있는 곳이라면 어디서든 토큰을 비교하거나 Int64나 Cardinal로 파싱하십시오
Delphi 코드에서 측정값으로 피벗 항목 정렬하기
HotXLS 피벗 필드를 값으로 정렬하려면 SortType을 xlpfstAscending이나 xlpfstDescending으로 하고 SortDataField를 표의 DataFields 목록에서 측정값의 0부터 시작하는 인덱스로 설정하십시오. 기본값인 SortType = xlpfstManual에서는 SortDataField가 무시되고 항목들은 캐시나 프로그램 순서를 유지합니다
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Region: TXLSPivotField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Region';
Data.Cells[1, 2].Value := 'Product';
Data.Cells[1, 3].Value := 'Units';
// ... 2..41 행 채우기 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
Region := Pivot.AddRowField('Region');
Pivot.AddDataFieldByName('Units', xlpaSum); // 데이터 필드 0
Region.SortType := xlpfstDescending; // 가장 큰 지역 먼저
Region.SortDataField := 0; // Units의 합으로 정렬
Region.AutoSortScope := '0'; // Excel의 autoSortScope도 함께 내보냄
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot layout was rejected');
Book.SaveAs('units-by-region.xlsx');
finally
Book.Free;
end;
end;두 디테일은 주목할 가치가 있습니다. HotXLS 라이터는 SortDataField를 rankBy로 내보내고, AutoSortScope가 비어 있지 않으면 4294967294 데이터 축 참조를 담은 autoSortScope 블록도 써서 속성 문자열을 x v 슬롯에 넣습니다. 적재 시 AutoSortScope는 같은 x v 토큰을 받으므로, 값 정렬에서는 속성이 데이터 필드 서수를 텍스트로 쥡니다. 따라서 '0'으로 설정하면 측정값을 두 곳에 실은 파일이 나오는데, Excel 자신이 쓰는 것과 가장 가까운 일치입니다. MaterializeNativePivotTable은 그다음 정렬된 그리드와 그 레이아웃 메타데이터를 함께 스테이징하고 성공하면 1을, 실패하면 진단과 함께 -1을 반환합니다
정렬되는 것은 항목 행만이 아닙니다. 열 필드도 같은 비교자를 거치며 행별 총계 대신 열별 총계를 쓰므로, SortType = xlpfstDescending과 유효한 SortDataField를 가진 열 필드는 자기 열들을 정확히 같은 방식으로 값 순으로 배열합니다
Excel 통합 문서가 이미 갖고 있는 정렬 들여다보기
Excel이 만든 파일의 경우 HotXLS는 피벗 정의를 적재하면서 SortType, SortDataField, AutoSortScope를 채우므로, 표를 다시 만들지 않고도 보고서의 정렬을 감사하거나 바꿀 수 있습니다. 아래 스니펫은 측정값으로 정렬하는 모든 필드를 나열하고 그 측정값을 DisplayName으로 이름 지어 보여 줍니다
var
Book: TXLSXWorkbook;
Pivot: TXLSPivotTable;
Field: TXLSPivotField;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('sales-report.xlsx');
Pivot := Book.Sheets.SheetByName('Report').PivotTables[0];
for I := 0 to Pivot.FieldCount - 1 do
begin
Field := Pivot.Fields[I];
if (Field.SortType <> xlpfstManual) and (Field.SortDataField >= 0) then
Writeln(Field.Name, ' sorted by ',
Pivot.DataFields[Field.SortDataField].DisplayName);
end;
// Region 정렬을 오름차순으로 뒤집고 기존 그리드를 다시 만듭니다
Field := Pivot.FindFieldByName('Region');
if Field <> nil then
Field.SortType := xlpfstAscending;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');
Book.SaveAs('sales-report-asc.xlsx');
finally
Book.Free;
end;
end;MaterializeNativePivotTable의 두 번째 인자는 HotXLS에게 피벗의 기존 출력 범위를 충돌로 취급하는 대신 채택하라고 말합니다. 같은 패턴이 HotXLS의 Show Values As 순위와 누적 합계 가이드에도 나오는데, 누적 합계가 표시 순서로 쌓이므로 정렬 순서가 중요합니다
두 항목의 값이 같을 때 HotXLS는 무승부를 어떻게 깨나
HotXLS는 항목 레이블로 무승부를 결정론적으로 깨는데, Excel과의 알려진 차이입니다. 두 멤버의 총계가 같으면 비교자는 캡션 비교로 폴백하고, 내림차순 플래그는 그 비교마저 뒤집습니다. 측정 지표가 유효한 숫자가 아닌 항목들은 오름차순에서 유효한 것들 뒤로 정렬되며, 내림차순 플래그는 그 묶음을 포함해 전체 비교를 뒤집습니다
Excel은 무승부 순서를 전혀 약속하지 않습니다. 네 멤버 A, B, C, D가 모두 같은 총계를 가진 테스트 픽스처에서 Excel의 오름차순 정렬은 B, C, A, D를 냈습니다. 저장된 메타데이터에는 그 순열을 예측하는 것이 없습니다. 불안정한 정렬의 부산물이며 파일마다 다를 수 있습니다. HotXLS는 같은 입력에 A, B, C, D를 돌려줍니다. 그 사례의 검증은 순서가 아니라 멤버 집합과 멤버별 값을 확인하고, 15/15 무승부가 있는 내림차순 픽스처는 Excel과 셀 단위로 일치했습니다. 다운스트림 코드가 동점 행의 정확한 위치에 의존한다면 두 번째 측정값으로 정렬하거나 값을 구별되게 만드십시오
접힌 멤버와 가장 안쪽 필드
접힌 멤버는 HotXLS에서 접힌 항목마다 하나의 집계 행으로 구체화됩니다. Excel이 항목의 세부사항을 숨기면 그 <item>에 sd="0"을 쓰고, HotXLS는 이를 TXLSPivotItem.IsDetailHidden으로 노출합니다. 접힌 항목은 평범한 레이블로 렌더되고 자기 서브트리 전체를 대체하며, 접기는 모든 부모 아래에서 그 항목에 적용되는 반면 둘러싼 블록들은 자기 소계를 유지합니다
var
Category: TXLSPivotField;
begin
// 피벗은 행에 Category(바깥)와 Product(안쪽)를 둠
Category := Pivot.FindFieldByName('Category');
if (Category <> nil) and (Category.ItemCount > 0) then
Category.Items[0].IsDetailHidden := True; // 첫 카테고리 접기
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Collapse was rejected');
end;접기는 정렬과 예측 가능하게 상호작용합니다. 접힌 행은 여전히 자기 필드의 멤버이므로 다른 항목처럼 총계에서 자기 위치를 가져옵니다. 가장 안쪽 필드는 다릅니다. 안쪽 레벨이 없으므로 접을 것이 없고, 이전 수정 이후로는 defaultSubtotal 플래그가 설정돼 있어도 소계 행을 만들지 않습니다. Excel은 단일 필드와 다중 레벨 축에서 똑같이 동작하며, 그 변경 전의 HotXLS는 가장 안쪽 필드 아래 유령 Total 행을 내보낼 수 있었습니다. 여기 관련된 필드 수준 속성에 대해서는 HotXLS의 CT_PivotField 스키마 유효성 워크스루가 <pivotField>와 그 항목들에 스키마가 요구하는 것을 다룹니다
Excel 자동화와 대조할 때의 함정
COM 자동화로 HotXLS 정렬을 Excel과 교차 확인할 때는 측정값의 지역화된 캡션을 Excel이 보여 주는 것과 정확히 같은 철자로 AutoSort에 넘기십시오. 중국어 Excel에서 합산된 Units 필드의 기본 캡션은 '求和项:Units'입니다. 그 기계에서 'Sum of Units'를 넘기면 오류도 없고 아무것도 바뀌지 않습니다. 영어 Excel에서 통과하던 같은 스크립트가 다른 곳에서는 조용히 no-op가 되므로, 캡션을 하드코딩하는 대신 데이터 필드에서 읽으십시오
- 테스트한 Excel 빌드에서는
PivotField.SortType을 COM으로 읽어 올 수 없었으므로, 정렬은 저장된 XML이나 렌더된 셀로 확인하십시오 - 그 빌드에서 정렬된 필드 밖 같은 축에 수동 그룹 부모 필드를 두면 값에 의한 멤버 정렬이 억제됐습니다. 수동 그룹 부모 아래의 정렬된 렌더링은 검증되지 않았습니다
PivotField.Outline과PivotField.LayoutForm에 대한 쓰기도 그 빌드에서 거부됐으므로, 픽스처의 아웃라인 레이아웃은 대신PivotTable.RowAxisLayout으로 설정하십시오
그 별난 점들 중 어느 것도 파일 형식을 바꾸지 않지만, 회귀 테스트가 무엇을 증명하는지를 결정합니다. 잘못된 캡션으로 AutoSort를 부르는 테스트는 한 번도 정렬되지 않은 피벗에 대해 통과합니다
FAQ
PivotTable XML 파일에서 필드 4294967294는 무슨 뜻인가요?
필드 4294967294는 필드 인덱스 -2의 부호 없는 32비트 인코딩이며, ECMA-376이 Values 의사 필드로 예약한 것입니다. autoSortScope 안에서는 데이터 축 참조를 표시하고, 그 x 자식이 항목을 어떤 데이터 필드로 정렬할지 고릅니다
HotXLS는 Excel 없이도 피벗 항목을 값으로 정렬하나요?
네. HotXLS는 피벗 그리드를 구체화할 때 정렬 자체를 평가하며, 선택된 데이터 필드의 멤버별 총계를 쓰고, Excel이 새로 고침 뒤에도 같은 순서를 유지하도록 sortType, rankBy, 선택적으로 autoSortScope를 씁니다. 같은 값들 사이의 무승부 순서만 Excel과 다를 수 있습니다
피벗 항목이 값이 아니라 알파벳 순으로 정렬되는 이유는요?
보통 필드에 정렬 방향은 있지만 측정값이 없기 때문입니다. SortType이 xlpfstManual이 아니고 SortDataField가 0에서 DataFieldCount - 1 사이인지 확인하십시오. -1이면 HotXLS는 요청된 방향으로 레이블 순서로 배열합니다
값 정렬, 접힌 멤버, 네이티브 피벗 파이프라인의 나머지는 XLS와 XLSX를 위한 Delphi 스프레드시트 컴포넌트인 HotXLS에 들어 있으며, Excel이 설치되지 않은 상태에서 Excel 통합 문서를 읽고 편집하고 씁니다. 피벗을 처음부터 만드는 중이라면 HotXLS의 피벗 테이블 만들기와 새로 고침 튜토리얼이 순서 정하기 이전에 캐시, 필드, 새로 고침을 다룹니다