Pivot table API
HotXLS supports typed pivot-table inspection, authoring, and result materialization for XLS and XLSX workbooks. Relationship-driven XLSX loading resolves arbitrary package part names and restores cache records, shared items, field metadata, filters, grouping, calculated definitions, formats, and extensions into the public model. Classic XLS input keeps its original pivot records available for faithful save round-trips
Round-trip and typed model
When a classic workbook already contains pivot tables, the reader preserves the raw BIFF records and also exposes a typed model. Tables and caches loaded from a classic file keep FromRawBlobs = True, so save output replays the original bytes for fidelity. XLSX pivots are loaded and saved through their owning package relationships, while 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;
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;
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;
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, while 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, while 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 materialize any configured subtotal function above or below its detail items through Subtotals and SubtotalTop. RowGrandTotals and ColumnGrandTotals control saved workbook policy, while 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;