API kontingenčních tabulek klasického XLS

HotXLS podporuje dva klasické pracovní postupy kontingenčních tabulek XLS. Sešity otevřené z Excelu si ponechají původní zobrazení kontingenční tabulky i záznamy mezipaměti kontingenční tabulky pro zpětné uložení. Kód může také zkoumat typovaný model kontingenční tabulky a vytvářet nové klasické kontingenční tabulky XLS přes

Přenos a typizovaný model

Když sešit už obsahuje kontingenční tabulky, čtečka zachová nezpracované záznamy BIFF a zároveň zpřístupní nejlepší odhad typovaného modelu. Tabulky a mezipaměti načtené ze souboru si ponechají FromRawBlobs = True, takže uložený výstup přehraje původní bajty pro co nejvyšší věrnost. Programově vytvořené kontingenční tabulky používají typovaný zapisovač

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;
      

Vstupní body listu a sešitu

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 zaregistruje řetězec číselného formátu a vrátí jeho index ifmt podle BIFF8, který očekávají TXLSPivotDataField.NumberFormat a TXLSPivotCacheField.NumberFormat ; opakovaná registrace téhož řetězce vrátí tentýž index

DestRow a DestCol se počítají od 1, stejně jako Cells[Row, Col], takže AddPivotTable('Data!A1:C9', 3, 1, 'SalesPivot') ukotví kontingenční tabulku do A3. Pozice na TXLSPivotTable (FirstRow .. FirstDataCol) i zdrojová oblast na TXLSPivotCache se počítají také od 1, stejně jako v XLSX enginu; ukotvení za hranicemi listu 65,536 x 256 vrátí nil

Sestavení rozvržení kontingenční tabulky drag-and-drop

TXLSPivotDesigner je nevizuální model rozvržení, který může podkládat field-list rozhraní ve VCL, FMX, na webu nebo v příkazové řádce, aniž by vázal logiku kontingenční tabulky na jednu sadu UI komponent

Každé pole hlásí dostupnou zónu, zónu řádků, sloupců, filtrů nebo hodnot plus svou deterministickou pozici; MoveField aplikuje přetažení, SetAggregation změní hodnotové pole, Validate hlásí nekompletní rozvržení a Apply výsledek zvaliduje a materializuje běžným zapisovačem kontingenčních tabulek

Nová číselná a datumová hodnotová pole mají výchozí xlpaSum, zatímco textová, logická, chybová, prázdná a smíšená pole mají výchozí 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;
      

Vytvoření kontingenční tabulky

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;
      

Zobrazení hodnot jako

Nastavte ShowDataAs u každého datového pole před voláním Make nebo před uložením sešitu. Režimy Difference, Percenta PercentDiff vyžadují index pole kontingenční tabulky BaseField od nuly plus BaseItem. RunTotal potřebuje jen BaseField. Řádkový, sloupcový, součtový a indexový režim si jmenovatele odvozují ze zdrojových záznamů s nastavenou agregací

Explicitní BaseItem je index položky od nuly uvnitř základního pole kontingenční tabulky. Pro porovnání sousedních položek použijte xlPivotBaseItemPrevious nebo xlPivotBaseItemNext . Chybějící průsečíky porovnání zůstanou prázdné, zatímco existující nulový jmenovatel vyprodukuje #DIV/0!

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

Filtrování, seskupování a řazení

Make nejprve před stavbou domény aplikuje skryté položky a výběry stránek a poté vyhodnotí filtry podle titulku, data, počtu, procent, součtu a hodnoty. Číselné rozsahy, kalendářní skupiny a diskrétní skupiny používají před agregací metadata seskupení pole mezipaměti. SortType volí stabilní pořadí podle popisků a SortDataField určuje hodnotové pole od nuly pro stabilní pořadí podle datových hodnot

Počítané výrazy

Počítaná pole mezipaměti se vyhodnocují jednou pro každý zdrojový záznam, počítané položky přes agregáty sousedních položek a počítané členy s MemberType = 'data' přidávají virtuální hodnotová pole po agregaci zdroje. Názvy vzorců nerozlišují velikost písmen a mohou být uzavřeny v hranatých závorkách nebo v uvozovkách. Podporovaná je aritmetika, porovnání, procenta, závorky a běžné číselné i logické funkce, přičemž pro acyklické počítané definice se průchod závislostmi opakuje

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

Mezisoučty a celkové součty

Každé řádkové nebo sloupcové pole může přes Subtotals a SubtotalTopmaterializovat libovolnou nastavenou funkci mezisoučtu nad nebo pod svými podrobnými položkami. RowGrandTotals a ColumnGrandTotals řídí politiku ukládaného sešitu, zatímco MaterializeGrandTotalRow a MaterializeGrandTotalColumn zapnou explicitní výstup celkových součtů ve výsledných zpětných voláních bez opakovaného proskenu zdrojových záznamů

Rozšířené režimy zobrazení hodnot

ExtendedShowDataAs nabízí procento z nadřazeného prvku, procento z nadřazeného řádku, procento z nadřazeného sloupce, procento z běžícího součtu a pořadí (rank) vzestupně či sestupně. Tyto režimy používají rozšiřující model XLSX a negenerují nepodporované hodnoty datových polí klasického BIFF

Kontrola existujícího sešitu s kontingenčními tabulkami

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;
      

Související témata

• IXLSWorkbook rozhraní
• TXLSWorksheet třída
• InteractiveFeatures demo
• Classic XLS sešit metadata preservation