클래식 XLS 피벗 테이블 API
HotXLS는 XLS 및 XLSX 통합 문서를 대상으로 형식화된 피벗 테이블 검사, 작성, 결과 구체화를 지원합니다. 관계 기반 XLSX 로딩은 임의의 패키지 파트 이름을 해석하고 캐시 레코드, 공유 항목, 필드 메타데이터, 필터, 그룹화, 계산 정의, 서식, 확장을 공개 모델로 복원합니다. 클래식 XLS 입력은 원래 피벗 레코드를 그대로 유지하므로 저장 왕복에서 충실도가 유지됩니다
왕복 처리 및 형식화된 모델
통합 문서에 이미 피벗 테이블이 있는 경우 reader는 원시 BIFF 레코드를 보존하는 동시에 형식화된 모델도 노출합니다. 클래식 파일에서 로드한 테이블과 캐시는 FromRawBlobs = True를 유지하므로 저장 출력이 원본 바이트를 그대로 재생해 충실도를 보장합니다. XLSX 피벗은 소속 패키지 관계를 통해 로드·저장되고, 프로그램으로 만든 피벗은 형식화된 writer를 사용합니다
const
xlPivotBaseItemPrevious = $7FFB;
xlPivotBaseItemNext = $7FFC;
type
TXLSPivotFieldAxis = (xlpfaNone, xlpfaRowField, xlpfaColumnField, xlpfaPageField, xlpfaDataField);
TXLSPivotAggregation = (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP);
TXLSPivotShowDataAs = (xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, xlpsdaIndex);
TXLSPivotExtendedShowDataAs = (xlpesdaNone, xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending, xlpesdaRankDescending);
TXLSPivotSubtotal = (xlpsDefault, xlpsSum, xlpsCount, xlpsAverage, xlpsMax, xlpsMin, xlpsProduct, xlpsCountNums, xlpsStdDev, xlpsStdDevP, xlpsVar, xlpsVarP);
TXLSPivotSubtotals = set of TXLSPivotSubtotal;
TXLSPivotDesignerZone = (xlpdzAvailable, xlpdzRows, xlpdzColumns, xlpdzFilters, xlpdzValues);
TXLSPivotCacheValue = record
ValueType: TXLSPivotCacheFieldType;
end;
TXLSPivotCacheField = class
property Name: WideString;
property FieldType: TXLSPivotCacheFieldType;
property ItemCount: Integer;
property Items[Index: Integer]: TXLSPivotCacheItem;
end;
TXLSPivotCache = class
property CacheId: Integer;
property SourceRangeSheet: WideString;
property SourceFirstRow: Integer;
property SourceFirstCol: Integer;
property SourceLastRow: Integer;
property SourceLastCol: Integer;
property RecordCount: Integer;
property FieldCount: Integer;
property Fields[Index: Integer]: TXLSPivotCacheField;
property RecordIndices[RecordIdx, FieldIdx: Integer]: Integer;
property FromRawBlobs: Boolean;
end;
TXLSPivotCaches = class
function Add: TXLSPivotCache;
function FindByCacheId(ACacheId: Integer): TXLSPivotCache;
property Count: Integer;
property Items[Index: Integer]: TXLSPivotCache;
end;
TXLSPivotField = class
function AddItem: TXLSPivotItem;
function AddCalculatedItem(const AName, AFormula: WideString): TXLSPivotCalculatedItem;
property Name: WideString;
property Axis: TXLSPivotFieldAxis;
property CacheFieldIndex: Integer;
property ItemCount: Integer;
property Items[Index: Integer]: TXLSPivotItem;
property SubtotalMask: Word;
property Subtotals: TXLSPivotSubtotals;
property PageItemIndex: Integer;
property SortType: TXLSPivotFieldSortType;
property SortDataField: Integer;
property Filters: TXLSPivotFilters;
end;
TXLSPivotDataField = class
property Field: TXLSPivotField;
property Aggregation: TXLSPivotAggregation;
property NumberFormat: Word;
property DisplayName: WideString;
property ShowDataAs: TXLSPivotShowDataAs;
property ExtendedShowDataAs: TXLSPivotExtendedShowDataAs;
property BaseField: Integer;
property BaseItem: Integer;
end;
TXLSPivotSupplementalRecord = class
property RecID: Word;
property BodyLength: Integer;
property Body[Index: Integer]: Byte;
end;
TXLSPivotTable = class
function AddField(const AName: WideString; ACacheFieldIndex: Integer): TXLSPivotField;
function FindFieldByName(const AName: WideString): TXLSPivotField;
function SetFieldAxis(const AName: WideString; AAxis: TXLSPivotFieldAxis): TXLSPivotField;
function AddRowField(const AName: WideString): TXLSPivotField;
function AddColumnField(const AName: WideString): TXLSPivotField;
function AddPageField(const AName: WideString): TXLSPivotField;
function AddDataField(AField: TXLSPivotField; AAggregation: TXLSPivotAggregation): TXLSPivotDataField;
function AddDataFieldByName(const AName: WideString; AAggregation: TXLSPivotAggregation = xlpaSum): TXLSPivotDataField;
function AddCalculatedMember(const AName, AFormula: WideString): TXLSPivotCalculatedMember;
function AddSupplementalRecord(ARecID: Word; const ABody: TBytes): TXLSPivotSupplementalRecord;
function Make(AWriter: TlxPivotResultWriter): Integer;
property Name: WideString;
property DataCaption: WideString;
property CacheId: Integer;
property Cache: TXLSPivotCache;
property FieldCount: Integer;
property Fields[Index: Integer]: TXLSPivotField;
property DataFieldCount: Integer;
property DataFields[Index: Integer]: TXLSPivotDataField;
property RowGrandTotals: Boolean;
property ColumnGrandTotals: Boolean;
property MaterializeGrandTotalRow: Boolean;
property MaterializeGrandTotalColumn: Boolean;
property SupplementalRecordCount: Integer;
property SupplementalRecords[Index: Integer]: TXLSPivotSupplementalRecord;
property FromRawBlobs: Boolean;
end;
TXLSPivotDesigner = class
constructor Create(APivotTable: TXLSPivotTable = nil);
procedure ClearLayout;
function MoveField(const AName: WideString; AZone: TXLSPivotDesignerZone; APosition: Integer = -1): Boolean;
function SetAggregation(const AName: WideString; AAggregation: TXLSPivotAggregation): Boolean;
function Validate(out ErrorText: WideString): Boolean;
function Apply(AWriter: TlxPivotResultWriter): Integer;
property PivotTable: TXLSPivotTable;
property FieldCount: Integer;
property Fields[Index: Integer]: TXLSPivotDesignerField;
end;
TXLSPivotTables = class
function Add: TXLSPivotTable;
function FindByName(const AName: WideString): TXLSPivotTable;
property Count: Integer;
property Items[Index: Integer]: TXLSPivotTable;
end;
워크시트 및 통합 문서 진입점
type
TXLSWorksheet = class
function AddPivotTable(const SourceRangeA1: WideString; DestRow, DestCol: Integer; const Name: WideString): TXLSPivotTable;
function PivotSetDataFieldFormat(DataField: TXLSPivotDataField; const FormatStr: WideString): Word;
property PivotTables: TXLSPivotTables;
end;
TXLSWorkbook = class
property PivotCaches: TXLSPivotCaches;
function RegisterNumFormat(const FormatStr: WideString): Word;
end;
RegisterNumFormat은 숫자 형식 문자열을 등록하고 BIFF8 ifmt 인덱스를 반환합니다. TXLSPivotDataField.NumberFormat과 TXLSPivotCacheField.NumberFormat이 기대하는 값이 바로 이 인덱스이며, 같은 문자열을 다시 등록하면 동일한 인덱스가 반환됩니다
DestRow와 DestCol은 Cells[Row, Col]과 같은 1 기반이므로, AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot')은 피벗 테이블을 A3에 고정합니다. TXLSPivotTable의 위치 속성(FirstRow .. FirstDataCol)과 TXLSPivotCache의 소스 범위도 XLSX 엔진과 마찬가지로 1 기반입니다. 65,536 x 256 시트 밖에 앵커를 두면 nil이 반환됩니다
드래그 앤 드롭 피벗 레이아웃 만들기
TXLSPivotDesigner는 non-visual 레이아웃 모델로, 피벗 로직을 특정 UI 툴킷에 결합하지 않고도 VCL, FMX, 웹, 커맨드 라인 필드 목록 인터페이스를 뒷받침할 수 있습니다
각 필드는 available, row, column, filter, value 영역과 그 결정론적 위치를 보고합니다. MoveField는 드롭을 적용하고, SetAggregation은 값 필드의 집계 방식을 바꾸고, Validate는 불완전한 레이아웃을 보고하며, Apply는 검증 후 일반 피벗 writer로 결과를 구체화합니다
숫자·날짜 값 필드는 기본값이 xlpaSum이고, 텍스트, Boolean, 오류, 빈 값, 혼합 필드는 기본값이 xlpaCount입니다
var
Designer: TXLSPivotDesigner;
ErrorText: WideString;
begin
Designer := TXLSPivotDesigner.Create(Pivot);
try
Designer.MoveField('Region', xlpdzRows);
Designer.MoveField('Quarter', xlpdzColumns);
Designer.MoveField('Closed', xlpdzFilters);
Designer.MoveField('Revenue', xlpdzValues);
Designer.SetAggregation('Revenue', xlpaAverage);
if Designer.Validate(ErrorText) then
Designer.Apply(ResultWriter.WriteCell);
finally
Designer.Free;
end;
end;
피벗 테이블 만들기
uses lxHandle;
var
Workbook: TXLSWorkbook;
SourceSheet, PivotSheet: TXLSWorksheet;
Pivot: TXLSPivotTable;
RowField: TXLSPivotField;
DataField: TXLSPivotDataField;
begin
Workbook := TXLSWorkbook.Create;
try
SourceSheet := Workbook.Sheets[1];
SourceSheet.Name := 'SalesData';
SourceSheet.Cells[1, 1].Value := 'Region';
SourceSheet.Cells[1, 2].Value := 'Product';
SourceSheet.Cells[1, 3].Value := 'Revenue';
PivotSheet := Workbook.Sheets.Add;
PivotSheet.Name := 'PivotReport';
Pivot := PivotSheet.AddPivotTable('SalesData!A1:C20', 4, 1, 'SalesPivot');
RowField := Pivot.AddRowField('Region');
Pivot.AddColumnField('Product');
DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
RowField.Subtotals := [xlpsSum];
PivotSheet.PivotSetDataFieldFormat(DataField, '$#,##0.00');
Workbook.SaveAs('pivot-report.xls');
finally
Workbook.Free;
end;
end;
값 표시 방식
Make를 호출하거나 통합 문서를 저장하기 전에 각 데이터 필드에 ShowDataAs를 설정합니다. Difference, Percent, PercentDiff는 0 기반 BaseField 피벗 필드 인덱스와 BaseItem이 필요합니다. RunTotal은 BaseField만 있으면 됩니다. row, column, total, index 모드는 구성된 집계 방식으로 소스 레코드에서 분모를 도출합니다
명시적 BaseItem은 기준 피벗 필드 안의 0 기반 항목 인덱스입니다. 인접 항목 비교에는 xlPivotBaseItemPrevious나 xlPivotBaseItemNext를 사용합니다. 비교 지점이 없으면 빈 셀로 남고, 분모가 0이면 #DIV/0!이 기록됩니다
DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
DataField.ShowDataAs := xlpsdaPercentDiff;
DataField.BaseField := 0;
DataField.BaseItem := xlPivotBaseItemPrevious;
CellsWritten := Pivot.Make(ResultWriter.WriteCell);
필터링, 그룹화, 정렬
Make는 도메인 생성 전에 숨겨진 항목과 페이지 선택을 적용한 다음 caption, date, count, percent, sum, value 필터를 평가합니다. 숫자 범위, 달력 그룹, 개별 그룹은 집계에 앞서 캐시 필드 그룹화 메타데이터를 사용합니다. SortType은 안정적인 레이블 정렬을 선택하고, SortDataField는 안정적인 데이터 값 정렬에 쓸 0 기반 값 필드를 선택합니다
계산식
계산된 캐시 필드는 소스 레코드마다 한 번 실행되고, 계산된 항목은 형제 항목 집계를 대상으로 실행되며, MemberType = 'data'인 계산된 멤버는 소스 집계 이후 가상 값 필드를 추가합니다. 수식 이름은 대소문자를 구분하지 않으며 대괄호나 따옴표로 묶을 수 있습니다. 산술, 비교, 백분율, 괄호와 흔히 쓰이는 숫자·논리 함수가 지원되고, 비순환 계산 정의를 위해 의존성 패스를 반복 수행합니다
ProfitField := Pivot.Cache.AddField('Profit', xlpcftNumber);
ProfitField.Formula := '=Revenue-Cost';
ProfitField.DatabaseField := False;
Pivot.AddDataField(Pivot.AddField('Profit', 4), xlpaSum);
RegionField.AddCalculatedItem('Combined', '=North+South');
Pivot.AddCalculatedMember('Margin', '=Profit/Revenue').MemberType := 'data';
소계와 총합계
각 행 또는 열 필드는 Subtotals와 SubtotalTop을 통해 구성된 소계 함수를 상세 항목 위나 아래에 구체화할 수 있습니다. RowGrandTotals와 ColumnGrandTotals는 저장되는 통합 문서 정책을 제어하고, MaterializeGrandTotalRow와 MaterializeGrandTotalColumn은 소스 레코드를 다시 훑지 않고도 결과 콜백에 명시적인 총합계 출력을 포함시킵니다
확장 값 표시 모드
ExtendedShowDataAs는 부모 대비 백분율, 부모 행 대비 백분율, 부모 열 대비 백분율, 누계 백분율, 오름차순·내림차순 순위를 제공합니다. 이 모드들은 XLSX 확장 모델을 사용하며, 지원되지 않는 클래식 BIFF 데이터 필드 값은 생성하지 않습니다
기존 피벗 통합 문서 검사
Workbook.Open('excel-authored-pivot.xls');
for SheetIndex := 1 to Workbook.Sheets.Count do
begin
Sheet := Workbook.Sheets[SheetIndex];
for PivotIndex := 0 to Sheet.PivotTables.Count - 1 do
begin
Pivot := Sheet.PivotTables[PivotIndex];
Writeln(Pivot.Name);
Writeln(Pivot.FieldCount);
if Assigned(Pivot.Cache) then
Writeln(Pivot.Cache.SourceRangeSheet);
end;
end;