Classic XLS pivot table API
HotXLS supports two classic XLS pivot-table workflows. Workbooks opened from Excel keep their original pivot view and pivot cache records for save round-trips. Code can also inspect the typed pivot model and build new classic XLS pivot tables through
Round-trip and typed model
When a workbook already contains pivot tables, the reader preserves the raw BIFF records and also exposes a best-effort typed model. Tables and caches loaded from a file keep FromRawBlobs = True, so save output replays the original bytes for fidelity. Programmatically created pivots use the typed 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;
Worksheet and workbook entry points
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 registers a number-format string and returns its BIFF8 ifmt index, which is what TXLSPivotDataField.NumberFormat and TXLSPivotCacheField.NumberFormat expect; registering the same string twice returns the same index
DestRow and DestCol are 1-based, like Cells[Row, Col], so AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') anchors the pivot table at A3. The positions on TXLSPivotTable (FirstRow .. FirstDataCol) and the source range on TXLSPivotCache are 1-based too, the same as in the XLSX engine; an anchor outside the 65,536 x 256 sheet returns nil
Build a drag-and-drop pivot layout
TXLSPivotDesigner is a non-visual layout model that can back VCL, FMX, web, or command-line field-list interfaces without coupling pivot logic to one UI toolkit
Each field reports an available, row, column, filter, or value zone plus its deterministic position; MoveField applies a drop, SetAggregation changes a value field, Validate reports incomplete layouts, and Apply validates and materialises the result through the normal pivot writer
New numeric and date value fields default to xlpaSum, whilst text, Boolean, error, empty, and mixed fields default to 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;
Create a pivot table
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;
Show values as
Set ShowDataAs on each data field before calling Make or saving the workbook. Difference, Percent, and PercentDiff require a zero-based BaseField pivot-field index plus a BaseItem. RunTotal requires only BaseField. Row, column, total, and index modes derive their denominators from the source records with the configured aggregation
An explicit BaseItem is a zero-based item index inside the base pivot field. Use xlPivotBaseItemPrevious or xlPivotBaseItemNext for adjacent-item comparisons. Missing comparison intersections remain blank, whilst a present zero denominator produces #DIV/0!
DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
DataField.ShowDataAs := xlpsdaPercentDiff;
DataField.BaseField := 0;
DataField.BaseItem := xlPivotBaseItemPrevious;
CellsWritten := Pivot.Make(ResultWriter.WriteCell);
Filtering, grouping, and sorting
Make applies hidden items and page selections before domain construction, then evaluates caption, date, count, percent, sum, and value filters. Numeric ranges, calendar groups, and discrete groups use cache-field grouping metadata before aggregation. SortType selects stable label ordering, whilst SortDataField selects the zero-based value field for stable data-value ordering
Calculated expressions
Calculated cache fields execute once per source record, calculated items execute across sibling item aggregates, and calculated members with MemberType = 'data' add virtual value fields after source aggregation. Formula names are case-insensitive and may be bracketed or quoted. Arithmetic, comparisons, percentages, parentheses, and common numeric and logical functions are supported, with repeated dependency passes for acyclic calculated definitions
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 and grand totals
Each row or column field can materialise any configured subtotal function above or below its detail items through Subtotals and SubtotalTop. RowGrandTotals and ColumnGrandTotals control saved workbook policy, whilst MaterializeGrandTotalRow and MaterializeGrandTotalColumn opt result callbacks into explicit grand-total output without rescanning source records
Extended show-value modes
ExtendedShowDataAs provides percent-of-parent, percent-of-parent-row, percent-of-parent-column, percent-of-running-total, and ascending or descending rank. These modes use the XLSX extension model and do not emit unsupported classic BIFF data-field values
Inspect an existing pivot workbook
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;