Vytváranie a aktualizácia kontingenčných tabuliek Excelu v Delphi pomocou HotXLS

HotXLS vytvára a aktualizuje natívne kontingenčné tabuľky XLSX z Delphi a C++Builder bez nutnosti inštalácie Excelu a COM automatizácie na počítači. Zavoláte AddPivotTable so zdrojovým rozsahom, presuniete polia na osi riadkov, stĺpcov, stránok a dát, vrstvíte vypočítané položky alebo zobrazenie percent z celkového počtu, a komponent zapíše časti pivotCacheDefinition a pivotTableDefinition, ktoré Excel otvorí ako živú, aktualizovateľnú kontingenčnú tabuľku

Scenár, kvôli ktorému to stojí za námahu, je reportovací server. Každú noc vygenerujete stovky zošitov, z ktorých každý nesie kontingenčnú tabuľku sumarizujúcu jeden účet, a ďalší mesiac sa čísla zmenia a každý súbor musí odrážať nové zdrojové riadky. Ovládanie Excelu zo služby systému Windows je krehké a nie je licencované na používanie na serveri a ručné písanie XML pre kontingenčné tabuľky je nekonečným projektom v štýle špecifikačnej archeológie. HotXLS sa nachádza medzi týmito dvoma slepými uličkami: typovaný objektový model nad pivot časťami OOXML, takže rovnaký Pascal, ktorý vypĺňa bunky, tiež deklaruje kontingenčnú tabuľku a prebuduje jej cache v rovnakom procese

Ako vytvoriť kontingenčnú tabuľku v Delphi bez Excelu?

Vytvoríte ju jediným volaním a potom umiestnite polia na osi. AddPivotTable preberá zdrojový rozsah v notácii A1, cieľovú bunku, kde sa tabuľka ukotví, a názov. Analyzuje rozsah, naskenuje každý stĺpec na odvodenie jeho dátového typu, vytvorí pivot cache (alebo znova použije cache už viazanú na rovnaký rozsah) a vráti TXLSPivotTable, ktorej polia začínajú mimo osí. Odtiaľ pohodlné metódy AddRowField, AddColumnField, AddPageField a AddDataFieldByName prepoja rozloženie a každé dátové pole prevezme jednu z jedenástich agregácií z enumu TXLSPivotAggregation (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Prečo sú cache a tabuľka dve samostatné časti

Kontingenčná tabuľka sú v skutočnosti dva artefakty, ktoré na seba odkazujú, a pochopenie tohto rozdelenia je kľúčové na pochopenie zvyšku. pivotCacheDefinition je dátový snímok: worksheetSource ukazujúci na zdrojový rozsah a jedno pole cacheField na stĺpec obsahujúce jedinečné hodnoty tohto stĺpca (jeho sharedItems) plus odvodené hranice. pivotTableDefinition je zobrazenie: ktoré pole cache leží na ktorej osi, dátové polia a ich agregácie, a prepínače rozloženia. Zobrazenie sa viaže na cache pomocou cacheId cez reláciu zošita pivotCaches, presne tak, ako to uvádza ECMA-376 časť 1 §18.10 a [MS-XLSX]

Táto nepriama väzba nie je byrokraciou, prináša dve veci. Jedna cache môže slúžiť ako základ pre niekoľko tabuliek, takže jediné obnovenie cache aktualizuje každé zobrazenie, ktoré je z nej odvodené. A cache ukladá hodnoty každého stĺpca deduplikované pomocou indexovej tabuľky pre každý záznam, a nie v surovej mriežke, čo je dôvod, prečo vytvorenie pivotu znamená skenovanie zdroja a nie kopírovanie buniek. HotXLS to modeluje rovnakým spôsobom pre oba enginy, takže vyššie uvedený kód je takmer identický s klasickou cestou zdokumentovanou popri binárnom rozložení záznamov SX za klasickými pivotmi .xls. Ak váš zdroj žije na inom hárku alebo ho oslovujete prostredníctvom názvu, prefix rozsahu a definované názvy a odkazy medzi hárkami podliehajú obvyklým pravidlám A1, pričom pre názvy obsahujúce medzery sú povolené názvy hárkov v úvodzovkách

Vypočítané položky nie sú vypočítané polia

Tieto tri termíny označujú tri rôzne veci a ich zámena je klasickou chybou pri kontingenčných tabuľkách. Vypočítaná položka (calculated item) žije vo vnútri jedného poľa a spája vlastné položky tohto poľa podľa názvu, takže v rámci poľa Region môžete definovať syntetický riadok CoreMarkets rovnajúci sa North plus South; HotXLS to sprístupňuje ako TXLSPivotField.AddCalculatedItem and writes a <calculatedItem> under that field's <calculatedItems>. Vypočítané pole (calculated field) je iné: je to nová hodnota odvodená od iných stĺpcov, ako napríklad Margin z Revenue a Cost, a nesie sa ako vzorec na poli cache prostredníctvom TXLSPivotCacheField.Formula. Vypočítaný člen (calculated member), pridaný pomocou TXLSPivotTable.AddCalculatedMember, je vlastný člen na úrovni tabuľky, ktorý môže fungovať ako meradlo (typ člena data) alebo člen dimenzie, čo je zaujímavé najmä pre pivoty v tvare OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Pre všetky tri platí jedno úprimné obmedzenie. HotXLS vygeneruje text vzorca do XML definície; nevyhodnocuje ho. Excel vypočíta vypočítanú položku, pole alebo člena pri otvorení súboru rovnakým spôsobom, ako počíta každý agregát v mriežke. HotXLS zapisuje pokyny, nie výsledky, takže vzorce, ktoré poskytnete, musia byť platnými pivot vzorcami vo vlastnom dialekte Excelu, odkazujúcimi na názvy polí a položiek tak, ako by to urobil Excel

Ako zobraziť hodnoty ako percento z celkového počtu?

Nastavíte režim zobrazenia hodnôt v dátovom poli a netransformujete čísla sami. TXLSPivotDataField.ShowDataAs preberá enum TXLSPivotShowDataAs, ktorý zrkadlí hodnoty OOXML ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal a xlpsdaIndex. Bežným trikom je umiestniť rovnaký zdrojový stĺpec na dátovú os dvakrát, raz ako surový súčet a raz ako podiel na celkovom súčte, takže prehľad zobrazuje číslo aj jeho váhu

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal nepotrebuje žiadnu základňu (base), pretože je relatívny k celkovému súčtu, ale režimy relatívne k položkám áno. xlpsdaDifference, xlpsdaPercentDiff a xlpsdaRunTotal vyžadujú BaseField (index poľa cache, nad ktorým prebieha porovnanie) a pre formy ukotvené na položke aj index BaseItem, kde $7FFD znamená zástupný znak (All). HotXLS ich zapisuje ako atribúty <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> a podobne ako pri vypočítaných vzorcoch ponecháva aritmetiku na Excel

Zoskupovanie dátumov a čísel a filtre polí stránky

Zoskupovanie (grouping) sa konfiguruje v poli cache a nie v poli kontingenčnej tabuľky, pretože mení spôsob, akým sa rozdeľuje zdrojová doména. Nastavte HasGroup := True v poli cache vrátenom metódou Cache.FindFieldByName, potom vyberte buď nastavenia číselného rozsahu (GroupStartNum, GroupEndNum, GroupInterval) alebo príznaky hierarchie dátumov (GroupByDate s GroupMonths, GroupQuarters, GroupYears a rozsah GroupStartDate / GroupEndDate). HotXLS vygeneruje zodpovedajúci element <fieldGroup><rangePr groupBy="months"/>, takže pole zoskupené podľa mesiaca alebo podľa číselného pásma so šírkou 1000 sa otvorí tak, ako by ho zoskupil Excel

Polia stránky (page fields) sú rozbaľovacie zoznamy filtrov nad tabuľkou. AddPageField umiestni pole na os stránky a PageItemIndex predvolí jednu položku cache, pričom predvolená hodnota je xlPageItemAll ($7FFD, čo znamená (Všetky)). Ak chcete používateľovi umožniť zaškrtnúť viacero položiek naraz, nastavte MultipleItemSelectionAllowed := True, čo HotXLS zapíše ako <pivotField multipleItemSelectionAllowed="1"/>. Pre kritériá presahujúce manuálny výber nesie každé pole typovanú kolekciu Filters pokrývajúcu rodiny OOXML ST_FilterType — filtre počtu, percent, súčtu, titulkov, hodnôt a dátumov —, pričom každý záznam spája typ filtra s jeho porovnávacími hodnotami

Ako aktualizovať pivot cache, keď sa zmení zdroj?

Metóda RefreshPivotCache v TXLSXWorkbook znova naskenuje zdrojový rozsah a prebuduje cache na mieste, čo je presne to, čo dávkové spracovanie potrebuje po úprave podkladových riadkov. Odovzdajte ID cache a metóda znova načíta každú bunku v zdrojovom rozsahu (hlavička v prvom riadku, dáta od druhého), prebuduje doménu zdieľaných položiek každého poľa deduplikáciou hodnôt a opätovným odvodením hraníc pre jednotlivé typy, a prepíše indexy položiek pre každý záznam. Vracia 1 pri úspechu a -1, keď je ID cache neznáme alebo chýba zdrojový hárok

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Sémantickú hranicu na tomto mieste stojí za to uviesť jasne. Pred existenciou tejto metódy sa HotXLS spoliehal na príznak refreshOnLoad="1" flag and left all refresh work to Excel on the next open, which is fine when a human will open the file but useless in a headless pipeline that has to hand off correct data. Metóda RefreshPivotCache robí uloženú cache aktuálnou priamo v procese a keďže sa tabuľky viažu na cache pomocou ID, každá kontingenčná tabuľka odvodená z tejto cache vidí aktualizáciu. Čo však nerobí, je rozloženie alebo agregovanie viditeľnej mriežky — Excel stále prepočítava vykreslenú tabuľku z aktualizovanej cache pri otvorení súboru

Čo zapisuje HotXLS a čo počíta Excel

Majte na pamäti delenie práce a nič vás neprekvapí. HotXLS je zapisovač definícií: vytvára pivotCacheDefinition, záznamy cache a pivotTableDefinition, vrátane osí, agregácií, vypočítaných položiek a členov, zobrazení hodnôt, zoskupovania a filtrov. Excel je kalkulačka: pri otvorení materializuje zoskupenia, vyhodnocuje vypočítané vzorce, aplikuje transformácie showDataAs a agreguje telo. Hodnoty, ktoré používateľ vidí, sú hodnoty z Excelu, vytvorené podľa pokynov, ktoré HotXLS zapísal, čo je dôvod, prečo každý vzorec a základný index musia byť správne v čase zápisu a nie až pri vykresľovaní

Pred navrhovaním riešenia okolo tejto funkcie je dobré poznať dve obmedzenia. AddPivotTable analyzuje obdĺžnikový rozsah A1 s voliteľným prefixom hárka; zdroje pomenovaných rozsahov a externých zošitov sú mimo toho, čo riešiteľ dokáže vyriešiť, hoci cache načítané zo súboru vytvoreného v Exceli si zachovávajú svoj pomenovaný rozsah zdroja pri obojsmernom prenose. A pivoty vytvorené v Exceli, ktoré HotXLS načíta z disku, sa pri uložení zopakujú bajt po bajte, takže tu popísané typované úpravy sa čistým spôsobom aplikujú na pivoty, ktoré vytvoríte v kóde, zatiaľ čo existujúce pivoty zostávajú bezstratové. Vstupnú stranu reportu — overené bunky a filtrované tabuľky, ktoré pivot sumarizuje — popisuje sprievodca overovaním dát, automatickým filtrom (AutoFilter) a štruktúrovanými tabuľkami

Tu zobrazený model kontingenčnej tabuľky je súčasťou štandardného komponentu HotXLS Delphi Excel Component pre Delphi a C++Builder, ktorý číta a zapisuje typované XLSX pivot časti aj klasické BIFF8 záznamy z rovnakého objektového modelu