Tvorba a obnova kontingenčních tabulek v Delphi s HotXLS

HotXLS vytváří a obnovuje nativní kontingenční tabulky XLSX z Delphi a C++Builderu bez instalace Excelu a bez automatizace přes COM na daném počítači. Zavoláte AddPivotTable se zdrojovou oblastí, rozmístíte pole na osu řádků, sloupců, stránky a hodnot, přidáte počítané položky nebo zobrazení v procentech z celku a komponenta zapíše části pivotCacheDefinition a pivotTableDefinition, které Excel otevře jako živou, obnovitelnou kontingenční tabulku

Scénář, kvůli kterému se ta námaha vyplatí, je reportovací server. Každou noc generujete stovky sešitů, každý s kontingenční tabulkou shrnující jeden účet, a příští měsíc se čísla pohnou a každý soubor musí odrážet nové zdrojové řádky. Řídit Excel z windowsové služby je křehké a pro serverové nasazení nelicencované a psát XML kontingenční tabulky ručně je archeologie ve specifikaci, která nikdy neskončí. HotXLS stojí mezi těmito dvěma slepými uličkami: typovaný objektový model nad částmi OOXML pro kontingenční tabulky, takže tentýž Pascal, který plní buňky, také deklaruje kontingenční tabulku a ve stejném procesu přestaví její cache

Jak vytvoříte kontingenční tabulku v Delphi bez Excelu?

Vytvoříte ji jediným voláním a pak rozmístíte pole na osy. AddPivotTable přebírá zdrojovou oblast v zápisu A1, cílovou buňku, ke které se tabulka ukotví, a název; oblast rozparsuje, projde každý sloupec, aby odvodil jeho datový typ, sestaví cache kontingenční tabulky (nebo použije tu, která je už na tutéž oblast navázaná) a vrátí TXLSPivotTable, jejíž pole zatím nestojí na žádné ose. Odtud rozvržení zapojí pohodlné metody AddRowField, AddColumnField, AddPageField a AddDataFieldByName a každé datové pole si bere jednu z jedenácti agregací z výčtu 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];                 // list s reportem (engine XLSX indexuje od 1)

    // Zdroj A1:E500 na listu 'Data'; ukotvení tabulky na řádek 3, sloupec 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;

Proč jsou cache a tabulka dvě samostatné části

Kontingenční tabulka jsou ve skutečnosti dva artefakty, které se navzájem odkazují, a pochopení tohoto rozdělení je to, co drží zbytek pohromadě. pivotCacheDefinition je datový snímek: worksheetSource ukazující na zdrojovou oblast a jeden cacheField na každý sloupec, který nese jeho odlišné hodnoty (jeho sharedItems) plus odvozené meze. pivotTableDefinition je pohled: které pole cache sedí na které ose, datová pole a jejich agregace, přepínače rozvržení. Pohled se na cache váže přes cacheId pomocí vztahu pivotCaches v sešitu, přesně jak to rozvrhuje ECMA-376 část 1 §18.10 a [MS-XLSX]

Tato nepřímost není byrokracie, něco za ni dostanete. Jedna cache může stát za několika tabulkami, takže jediné obnovení cache aktualizuje každý pohled, který z ní čerpá. A cache ukládá hodnoty každého sloupce deduplikovaně, s indexovou tabulkou na záznam místo surové mřížky, a proto sestavení kontingenční tabulky znamená projít zdroj, nikoli kopírovat buňky. HotXLS to modeluje stejně pro oba enginy, takže kód výše je téměř totožný s klasickou cestou popsanou vedle binárního rozvržení záznamů SX za klasickými kontingenčními tabulkami .xls. Pokud váš zdroj leží na jiném listu nebo jej adresujete jménem, řídí se prefix oblasti i definovaná jména a odkazy napříč listy obvyklými pravidly A1 a názvy listů obsahující mezery se uvádějí v apostrofech

Počítané položky nejsou počítaná pole

Tyto tři pojmy pojmenovávají tři různé věci a jejich záměna je klasická chyba u kontingenčních tabulek. Počítaná položka žije uvnitř jednoho pole a kombinuje vlastní položky tohoto pole podle jména, takže v poli Region můžete definovat umělý řádek CoreMarkets rovný součtu North a South; HotXLS ji zpřístupňuje jako TXLSPivotField.AddCalculatedItem a zapisuje <calculatedItem> pod prvek <calculatedItems> daného pole. Počítané pole je něco jiného: je to nová hodnota odvozená z jiných sloupců, třeba Margin z Revenue a Cost, a nese se jako vzorec na poli cache přes TXLSPivotCacheField.Formula. Počítaný člen, přidaný metodou TXLSPivotTable.AddCalculatedMember, je vlastní člen na úrovni tabulky, který může vystupovat jako míra (typ členu data) nebo jako člen dimenze, což je zajímavé hlavně u kontingenčních tabulek ve stylu OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // Počítaná POLOŽKA kombinuje položky jediného pole podle jména.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // Počítaný ČLEN se deklaruje na úrovni tabulky. Typ členu
  // 'data' jej označuje jako míru; prázdný typ je člen dimenze.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Na všechny tři platí jedno poctivé omezení. HotXLS vypíše text vzorce do definičního XML; nevyhodnocuje jej. Počítanou položku, pole nebo člen spočítá Excel ve chvíli, kdy soubor otevře, stejně jako počítá každý agregát v mřížce. HotXLS zapisuje instrukce, ne výsledky, takže vzorce, které dodáte, musí být platné vzorce kontingenční tabulky ve vlastním dialektu Excelu a odkazovat na jména polí a položek tak, jak by to udělal Excel

Jak zobrazíte hodnoty jako procento z celku?

Režim zobrazení hodnot nastavíte na datovém poli, místo abyste čísla přepočítávali sami. TXLSPivotDataField.ShowDataAs přebírá výčet TXLSPivotShowDataAs, který kopíruje hodnoty ST_ShowDataAs z OOXML: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal a xlpsdaIndex. Častým trikem je umístit tentýž zdrojový sloupec na osu hodnot dvakrát, jednou jako prostý součet a jednou jako podíl na celkovém součtu, aby report ukázal číslo i 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;

  // Režimy vztažené k položce potřebují základ pro srovnání. Průběžný součet
  // dolů podle 'Quarter' (index pole cache 3) by vypadal takto:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // index pole cache, nad kterým transformace běží
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal žádný základ nepotřebuje, protože se vztahuje k celkovému součtu, ale režimy vztažené k položce ano. xlpsdaDifference, xlpsdaPercentDiff a xlpsdaRunTotal vyžadují BaseField (index pole cache, nad kterým srovnání běží) a u forem ukotvených k položce také index BaseItem, kde $7FFD zastupuje sentinelovou hodnotu (All). HotXLS je zapisuje jako atributy <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> a stejně jako u počítaných vzorců nechává aritmetiku na Excelu

Seskupování kalendářních dat a čísel a filtry stránkových polí

Seskupování se konfiguruje na poli cache, ne na poli kontingenční tabulky, protože mění, jak se zdrojová doména rozdělí do košů. Na poli cache, které vrátí Cache.FindFieldByName, nastavte HasGroup := True a pak zvolte buď parametry číselného rozsahu (GroupStartNum, GroupEndNum, GroupInterval), nebo příznaky datové hierarchie (GroupByDate spolu s GroupMonths, GroupQuarters, GroupYears a rozpětím GroupStartDate / GroupEndDate). HotXLS vypíše odpovídající prvek <fieldGroup><rangePr groupBy="months"/>, takže pole seskupené po měsících nebo do číselných pásem po 1000 se otevře tak, jak by je seskupil Excel

Stránková pole jsou filtrační rozbalovací seznamy nad tabulkou. AddPageField umístí pole na osu stránky a PageItemIndex předvybere jedinou položku cache, ve výchozím stavu xlPageItemAll ($7FFD, tedy (All)). Aby si čtenář mohl zaškrtnout několik položek najednou, nastavte MultipleItemSelectionAllowed := True, což HotXLS zapíše jako <pivotField multipleItemSelectionAllowed="1"/>. Pro kritéria nad rámec ručního výběru nese každé pole typovanou kolekci Filters, která pokrývá rodiny ST_FilterType z OOXML — filtry podle počtu, procent, součtu, popisku, hodnoty a data — a každá položka páruje typ filtru s jeho porovnávanými hodnotami

Jak obnovíte cache kontingenční tabulky, když se změní zdroj?

RefreshPivotCache na TXLSXWorkbook znovu projde zdrojovou oblast a přestaví cache na místě, což je přesně to, co dávková linka potřebuje poté, co upraví podkladové řádky. Předáte id cache a metoda znovu přečte každou buňku ve zdrojové oblasti (hlavička na prvním řádku, data od druhého), přestaví doménu sdílených položek každého pole novým odstraněním duplicit a novým odvozením mezí podle typu a přepíše indexy položek u jednotlivých záznamů. Vrací 1 při úspěchu a -1, když je id cache neznámé nebo zdrojový list chybí

var
  Rc: Integer;
begin
  // ...zdrojová data se od sestavení kontingenční tabulky změnila...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // opravená hodnota Revenue

  // Přestavění sdílených položek a indexů záznamů cache ze zdroje.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = obnoveno, -1 = chybí cache nebo zdroj
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Sémantickou hranici tu stojí za to říct naplno. Než tato metoda vznikla, spoléhal HotXLS na příznak refreshOnLoad="1" a veškerou obnovu nechával na Excelu při dalším otevření, což je v pořádku, když soubor otevře člověk, ale k ničemu v bezobslužné lince, která musí předat správná data. RefreshPivotCache udělá uloženou cache aktuální přímo v procesu, a protože se tabulky na cache vážou přes id, každá kontingenční tabulka z té cache vidí aktualizaci. Co metoda nedělá, je rozvržení a agregace viditelné mřížky — vykreslenou tabulku Excel při otevření souboru stále přepočítá z obnovené cache

Co zapisuje HotXLS a co počítá Excel

Mějte dělbu práce na očích a nic vás nepřekvapí. HotXLS je zapisovač definic: vytváří pivotCacheDefinition, záznamy cache a pivotTableDefinition včetně os, agregací, počítaných položek a členů, zobrazení hodnot, seskupení a filtrů. Excel je počítadlo: při otevření zhmotní skupinové koše, vyhodnotí počítané vzorce, aplikuje transformace showDataAs a agreguje tělo. Hodnoty, které uživatel vidí, patří Excelu a vznikly z instrukcí, jež zapsal HotXLS, a proto musí být každý vzorec i každý index základu správný už při zápisu, ne až při vykreslení

Než na této funkci postavíte návrh, vyplatí se znát dvě omezení. AddPivotTable rozparsuje obdélníkovou oblast A1 s volitelným prefixem listu; pojmenované oblasti a zdroje z externích sešitů leží mimo to, co builder rozřeší, i když cache načtené ze souboru vytvořeného Excelem si svůj zdroj v podobě pojmenované oblasti při zpětném uložení zachovají. A kontingenční tabulky vytvořené Excelem, které HotXLS načte z disku, se při uložení přehrají bajt po bajtu, takže typované úpravy popsané zde platí čistě pro tabulky, které stavíte v kódu, zatímco stávající tabulky zůstávají beze ztrát. Ke vstupní straně reportu — ověřeným buňkám a filtrovaným tabulkám, které kontingenční tabulka shrnuje — viz ověřování dat, AutoFilter a strukturované tabulky

Model kontingenčních tabulek ukázaný zde je součástí standardní komponenty HotXLS Delphi Excel Component pro Delphi a C++Builder, která ze stejného objektového modelu čte a zapisuje jak typované části kontingenčních tabulek XLSX, tak klasické záznamy BIFF8