Pivot table API

HotXLS ondersteunt getypeerde inspectie, authoring en resultaatmaterialisatie van draaitabellen voor XLS- en XLSX-werkmappen. Relatiegedreven XLSX-lading herleidt willekeurige package-part-namen en herstelt cacherecords, gedeelde items, veldmetadata, filters, groepering, berekende definities, indelingen en extensies in het publieke model. Klassieke XLS-invoer houdt zijn oorspronkelijke draaitabelrecords beschikbaar voor getrouwe save-round-trips

Round-trip en getypeerd model

Wanneer een klassieke werkmap al draaitabellen bevat, bewaart de reader de raw BIFF-records en stelt daarnaast een getypeerd model bloot. Tabellen en caches die uit een klassiek bestand zijn geladen, houden FromRawBlobs = True, zodat de save-uitvoer voor getrouwheid de oorspronkelijke bytes herspeelt. XLSX-draaitabellen worden geladen en opgeslagen via hun eigen package-relaties, terwijl programmatisch aangemaakte draaitabellen de getypeerde writer gebruiken

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- en werkmap-toegangspunten

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 registreert een getalnotatiestring en geeft zijn BIFF8-ifmt-index terug, precies wat TXLSPivotDataField.NumberFormat en TXLSPivotCacheField.NumberFormat verwachten; dezelfde string tweemaal registreren geeft dezelfde index terug

DestRow en DestCol zijn 1-based, net als Cells[Row, Col], dus AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') verankert de draaitabel op A3. Ook de posities op TXLSPivotTable (FirstRow .. FirstDataCol) en het bronbereik op TXLSPivotCache zijn 1-based, net als in de XLSX-engine; ligt het anker buiten het werkblad van 65,536 x 256, dan geeft de functie nil terug

Bouw een draaitabelindeling met slepen en neerzetten

TXLSPivotDesigner is een niet-visueel lay-outmodel dat veldlijstinterfaces voor VCL, FMX, web of de commandoregel kan ondersteunen, zonder de draaitabellogica aan één UI-toolkit te koppelen

Elk veld meldt een beschikbare rij-, kolom-, filter- of waardezone plus zijn deterministische positie; MoveField verwerkt een drop, SetAggregation wijzigt een waardeveld, Validate rapporteert onvolledige indelingen en Apply valideert en materialiseert het resultaat via de normale pivot-writer

Nieuwe numerieke waardevelden en datum-waardevelden staan standaard op xlpaSum, terwijl tekst-, boolean-, fout-, lege en gemengde velden standaard op xlpaCount staan

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;
      

Maak een draaitabel

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;
      

Toon waarden als

Zet ShowDataAs op elk gegevensveld voordat je Make aanroept of de werkmap opslaat. Difference, Percent en PercentDiff vereisen een op 0 gebaseerde BaseField-draaitabelveldindex plus een BaseItem. RunTotal vereist alleen BaseField. De modi row, column, total en index leiden hun noemers uit de bronrecords af, met de geconfigureerde aggregatie

Een expliciete BaseItem is een op 0 gebaseerde itemindex binnen het basisveld van de draaitabel. Gebruik xlPivotBaseItemPrevious of xlPivotBaseItemNext voor vergelijkingen met aangrenzende items. Ontbrekende vergelijkingsintersecties blijven leeg, terwijl een aanwezige noemer nul #DIV/0! oplevert

DataField := Pivot.AddDataFieldByName('Revenue', xlpaSum);
DataField.ShowDataAs := xlpsdaPercentDiff;
DataField.BaseField := 0;
DataField.BaseItem := xlPivotBaseItemPrevious;
CellsWritten := Pivot.Make(ResultWriter.WriteCell);
      

Filteren, groeperen en sorteren

Make past verborgen items en paginaselecties toe vóór de domeinconstructie en evalueert daarna caption-, datum-, count-, percent-, sum- en value-filters. Numerieke bereiken, kalendergroepen en discrete groepen gebruiken de grouping-metadata van cachevelden vóór de aggregatie. SortType kiest stabiele labelvolgorde, terwijl SortDataField het op 0 gebaseerde waardeveld kiest voor stabiele sortering op gegevenswaarden

Berekende expressies

Berekende cachevelden worden één keer per bronrecord uitgevoerd, berekende items over de aggregaten van zusterelementen heen, en berekende members met MemberType = 'data' voegen virtuele waardevelden toe na de bronaggregatie. Formulenamen zijn case-insensitive en mogen tussen rechte haken of aanhalingstekens staan. Rekenkunde, vergelijkingen, percentages, haakjes en gangbare numerieke en logische functies worden ondersteund, met herhaalde dependency-passes voor acyclische berekende definities

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';
      

Subtotalen en grand totalen

Elk rij- of kolomveld kan elke geconfigureerde subtotalfunctie boven of onder zijn detailitems materialiseren via Subtotals en SubtotalTop. RowGrandTotals en ColumnGrandTotals sturen het opslagbeleid van de werkmap, terwijl MaterializeGrandTotalRow en MaterializeGrandTotalColumn resultaatcallbacks expliciete grand-total-uitvoer mee laten leveren zonder de bronrecords opnieuw te scannen

Uitgebreide show-value-modi

ExtendedShowDataAs biedt percent-of-parent, percent-of-parent-row, percent-of-parent-column, percent-of-running-total en oplopende of aflopende rank. Deze modi gebruiken het XLSX-extensiemodel en schrijven geen niet-ondersteunde gegevensveldwaarden naar klassieke BIFF

Inspecteer een bestaande draaitabelwerkmap

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;
      

Verwante onderwerpen

• IXLSWorkbook interface
• TXLSWorksheet class
• InteractiveFeatures demo
• Classic XLS workbook metadata preservation