Pivottabells-API

HotXLS stödjer typad granskning, upphov och materialisering av pivottabellsresultat för XLS- och XLSX-arbetsböcker. Relationsdriven XLSX-inläsning löser upp godtyckliga paketdelnamn och återställer cacheposter, delade objekt, fältmetadata, filter, gruppering, beräknade definitioner, format och tillägg i den publika modellen. Klassiska XLS-källor behåller sina ursprungliga pivotposter för trogna spar-roundtrips

Roundtrip och typad modell

När en klassisk arbetsbok redan innehåller pivottabeller bevarar läsaren de råa BIFF-posterna och exponerar samtidigt en typad modell. Tabeller och cacheposter som lästs från en klassisk fil behåller FromRawBlobs = True, så att sparutdata spelar upp originalbytena för trohet. XLSX-pivoter läses in och sparas via sina ägande paketrelationer, medan programmatiskt skapade pivoter använder den typade skrivaren

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;
      

Startpunkter i kalkylblad och arbetsbok

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 registrerar en talformatsträng och returnerar dess BIFF8-ifmt-index, vilket är vad TXLSPivotDataField.NumberFormat och TXLSPivotCacheField.NumberFormat förväntar sig; registreras samma sträng två gånger returneras samma index

DestRow och DestCol är ettbaserade, precis som Cells[Row, Col], så AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') förankrar pivottabellen i cell A3. Även positionerna på TXLSPivotTable (FirstRow .. FirstDataCol) och källområdet på TXLSPivotCache är ettbaserade, precis som i XLSX-motorn; en förankring utanför 65,536 x 256-kalkylbladet returnerar nil

Bygg en dra-och-släpp-layout för pivottabellen

TXLSPivotDesigner är en icke-visuell layoutmodell som kan ligga bakom VCL-, FMX-, webb- eller kommandoradsgränssnitt för fältlistor, utan att koppla pivotlogiken till ett enskilt UI-ramverk

Varje fält redovisar en tillgänglig-, rad-, kolumn-, filter- eller värdezonsplacering plus sin deterministiska position; MoveField genomför ett släpp, SetAggregation ändrar ett värdefält, Validate rapporterar ofullständiga layouter, och Apply validerar och materialiserar resultatet via den normala pivotskrivaren

Nya numeriska och datumbaserade värdefält får xlpaSum som standard, medan text-, Boolean-, fel-, tomma och blandade fält får xlpaCount som standard

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;
      

Skapa en pivottabell

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;
      

Visa värden som

Sätt ShowDataAs på varje värdefält innan du anropar Make eller sparar arbetsboken. Difference, Percent och PercentDiff kräver ett nollbaserat BaseField-pivotfältindex plus en BaseItem. RunTotal kräver bara BaseField. Rad-, kolumn-, total- och indexlägena härleder sina nämnare från källposterna med den konfigurerade aggregeringen

En uttrycklig BaseItem är ett nollbaserat objektindex inuti baspivotfältet. Använd xlPivotBaseItemPrevious eller xlPivotBaseItemNext för jämförelser med angränsande objekt. Saknade jämförelsesnitt lämnas tomma, medan en befintlig nollnämnare ger #DIV/0!

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

Filtrering, gruppering och sortering

Make tillämpar dolda objekt och sidourval innan domänen byggs upp, och utvärderar därefter rubrik-, datum-, antal-, procent-, summa- och värdefilter. Numeriska intervall, kalendergrupper och diskreta grupper använder cachefältens grupperingsmetadata före aggregeringen. SortType väljer stabil etikettordning, medan SortDataField väljer det nollbaserade värdefältet för stabil ordning efter datavärden

Beräknade uttryck

Beräknade cachefält körs en gång per källpost, beräknade objekt körs över syskonobjektens aggregat, och beräknade medlemmar med MemberType = 'data' lägger till virtuella värdefält efter källaggregeringen. Formelnamn är skiftlägesokänsliga och får vara hakparenteserade eller citerade. Aritmetik, jämförelser, procent, parenteser och vanliga numeriska och logiska funktioner stöds, med upprepade beroendepass för acykliska beräknade definitioner

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

Delsummor och totalsummor

Varje rad- eller kolumnfält kan materialisera vilken som helst av de konfigurerade delsummfunktionerna ovanför eller under sina detaljobjekt via Subtotals och SubtotalTop. RowGrandTotals och ColumnGrandTotals styr den sparade arbetsbokens policy, medan MaterializeGrandTotalRow och MaterializeGrandTotalColumn låter resultatanropen få uttryckliga totalsummor utan att källposterna behöver ses över igen

Utökade visa-värden-som-lägen

ExtendedShowDataAs tillhandahåller procent av överordnad, procent av överordnad rad, procent av överordnad kolumn, procent av löpande summa samt stigande eller fallande rang. Dessa lägen använder XLSX-tilläggsmodellen och skriver inte ut klassiska BIFF-värdefältvärden som inte stöds

Inspektera en befintlig pivottabellsarbetsbok

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;
      

Relaterade ämnen

• gränssnitt IXLSWorkbook
• klass TXLSWorksheet
• InteractiveFeatures demo
• Classic XLS workbook metadata preservation