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 TXLSWorksheet.AddPivotTable
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
type
TXLSPivotFieldAxis = (xlpfaNone, xlpfaRowField, xlpfaColumnField, xlpfaPageField, xlpfaDataField);
TXLSPivotAggregation = (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP);
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 funktio Add: TXLSPivotCache;
function FindByCacheId(ACacheId: Integer): TXLSPivotCache;
property Count: Integer;
property Items[Index: Integer]: TXLSPivotCache;
end;
TXLSPivotField = class funktio AddItem: TXLSPivotItem;
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;
end;
TXLSPivotDataField = class
property Field: TXLSPivotField;
property Aggregation: TXLSPivotAggregation;
property NumberFormat: Word;
property DisplayName: WideString;
end;
TXLSPivotSupplementalRecord = class
property RecID: Word;
property BodyLength: Integer;
property Body[Index: Integer]: Byte;
end;
TXLSPivotTable = class funktio 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 AddSupplementalRecord(ARecID: Word; const ABody: TBytes): TXLSPivotSupplementalRecord;
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 SupplementalRecordCount: Integer;
property SupplementalRecords[Index: Integer]: TXLSPivotSupplementalRecord;
property FromRawBlobs: Boolean;
end;
TXLSPivotTables = class funktio 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 funktio 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;
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;
Related topics
Copyright © 2010-2026 losLab Software