HotXLS vytváří a aktualizuje nativní kontingenční tabulky (pivot tables) XLSX z Delphi a C++Builderu bez nutnosti instalace Excelu a bez COM automatizace na stroji. Zavoláte `AddPivotTable` se zdrojovým rozsahem, umístíte pole na řádkové, sloupcové, stránkové a datové osy, navrstvíte vypočítávané položky nebo zobrazení procent z celkového součtu, a komponenta zapíše části `pivotCacheDefinition` a `pivotTableDefinition`, které Excel otevře jako živou, aktualizovatelnou kontingenční tabulku
Scénářem, kvůli kterému to stojí za námahu, je reportovací server. Každou noc generujete stovky sešitů, z nichž každý obsahuje kontingenční tabulku shrnující jeden účet, a příští měsíc se čísla posunou a každý soubor musí odrážet nové zdrojové řádky. Ovládání Excelu ze služby Windows je křehké a nelicencované pro použití na serveru a ruční psaní XML kontingenční tabulky je projekt archeologie specifikací, který nikdy nekončí. HotXLS stojí mezi těmito dvěma slepými uličkami: typový objektový model nad částmi kontingenční tabulky OOXML, takže stejný kód v Pascalu, který vyplňuje buňky, také deklaruje kontingenční tabulku a v rámci stejného procesu obnovuje její mezipaměť (cache)
Jak vytvořit kontingenční tabulku v Delphi bez Excelu?
Vytvoříte ji jediným voláním a poté umístíte pole na osy. `AddPivotTable` přijímá zdrojový rozsah v notaci A1, cílovou buňku, kde se tabulka ukotví, a název; analyzuje rozsah, prohledá každý sloupec za účelem odvození jeho datového typu, sestaví mezipaměť kontingenční tabulky (nebo znovu použije mezipaměť již vázanou na stejný rozsah) a vrátí objekt `TXLSPivotTable`, jehož pole začínají mimo osy. Odtud pohodlné metody `AddRowField`, `AddColumnField`, `AddPageField` a `AddDataFieldByName` zapojí rozvržení, přičemž každé datové pole přijímá 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 reportu (indexovaný od 1 v engine XLSX)
// Zdroj A1:E500 na listu 'Data'; ukotvit kontingenční tabulku na řádku 3, sloupci 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 mezipaměť a tabulka dvě samostatné části
Kontingenční tabulka se skládá ze dvou artefaktů, které na sebe odkazují, a pochopení tohoto rozdělení pomáhá udržet si v tom jasno. `pivotCacheDefinition` je datový snímek: `worksheetSource` odkazující na zdrojový rozsah a jedno pole `cacheField` pro každý sloupec, které obsahuje jeho odlišné hodnoty (jeho `sharedItems`) plus odvozené hranice. `pivotTableDefinition` je zobrazení (view): které pole mezipaměti leží na které ose, datová pole a jejich agregace, přepínače rozvržení. Zobrazení se váže k mezipaměti pomocí `cacheId` prostřednictvím vztahu `pivotCaches` sešitu, přesně tak, jak to definuje specifikace ECMA-376 Part 1 §18.10 a [MS-XLSX]
Tato nepřímá adresace (indirection) není byrokracií, přináší dvě výhody. Jedna mezipaměť může zálohovat několik tabulek, takže jediné obnovení mezipaměti aktualizuje každé zobrazení z ní vykreslené. A mezipaměť ukládá hodnoty každého sloupce deduplikované pomocí indexové tabulky pro každý záznam, namísto hrubé mřížky, což je důvod, proč vytvoření kontingenční tabulky znamená skenování zdroje, nikoli kopírování buněk. HotXLS to modeluje stejně pro oba enginy, takže výše uvedený kód je téměř identický s klasickou cestou dokumentovanou u rozvržení binárních záznamů SX u klasických kontingenčních tabulek .xls. Pokud váš zdroj žije na jiném listu nebo na něj odkazujete přes název, prefix rozsahu a definované názvy a mezilistové odkazy se řídí obvyklými pravidly A1, přičemž pro názvy obsahující mezery jsou povoleny sheet názvy v uvozovkách
Vypočítávané položky nejsou vypočítávaná pole
Tyto tři termíny označují tři různé věci a jejich záměna je klasickou chybou při práci s kontingenčními tabulkami. Vypočítávaná položka (calculated item) žije uvnitř jednoho pole a kombinuje položky tohoto pole podle názvu, takže v rámci pole Region můžete definovat syntetický řádek `CoreMarkets` rovný North plus South; HotXLS to vystavuje jako `TXLSPivotField.AddCalculatedItem` a zapisuje `
var
Region: TXLSPivotField;
Member: TXLSPivotCalculatedMember;
begin
// Vypočítávaná POLOŽKA kombinuje položky jednoho pole podle názvu.
Region := Pivot.AddRowField('Region');
Region.AddCalculatedItem('CoreMarkets', '=North+South');
// Vypočítávaný ČLEN se deklaruje na úrovni tabulky. Typ členu
// 'data' jej označuje jako měřítko; prázdný typ znamená člen dimenze.
Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
Member.MemberType := 'data';
end;Jedna upřímná hranice platí pro všechny tři případy. HotXLS emituje text vzorce do definičního XML; nevyhodnocuje jej. Excel vypočítá vypočítávanou položku, pole nebo člen při otevření souboru, stejně jako počítá každý agregát v mřížce. HotXLS zapisuje instrukce, nikoli výsledky, takže vzorce, které dodáte, musí být platnými vzorci kontingenční tabulky v dialektu Excelu a odkazovat na názvy polí a položek tak, jak by to dělal Excel
Jak zobrazit hodnoty jako procento z celkového součtu?
Nastavíte režim zobrazení hodnot v datovém poli, místo abyste čísla transformovali sami. `TXLSPivotDataField.ShowDataAs` přijímá výčet `TXLSPivotShowDataAs`, který zrcadlí hodnoty OOXML `ST_ShowDataAs`: `xlpsdaNormal`, `xlpsdaDifference`, `xlpsdaPercent`, `xlpsdaPercentDiff`, `xlpsdaRunTotal`, `xlpsdaPercentOfRow`, `xlpsdaPercentOfCol`, `xlpsdaPercentOfTotal` a `xlpsdaIndex`. Běžným trikem je umístit stejný zdrojový sloupec na datovou osu dvakrát, jednou jako hrubý součet a jednou jako podíl na celkovém součtu, takže report zobrazuje jak číslo, tak 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 relativní k položce vyžadují základ pro porovnání. Průběžný součet
// dolů po 'Quarter' (index pole mezipaměti 3) by se četl takto:
// Share.ShowDataAs := xlpsdaRunTotal;
// Share.BaseField := 3; // index pole mezipaměti, nad kterým transformace běží
// Share.BaseItem := $7FFD; // $7FFD = "(All)"
end;`xlpsdaPercentOfTotal` nevyžaduje žádný základ (base), protože je relativní k celkovému součtu (grand total), ale režimy relativní k položce jej potřebují. `xlpsdaDifference`, `xlpsdaPercentDiff` a `xlpsdaRunTotal` vyžadují `BaseField` (index pole mezipaměti, nad kterým porovnání běží) a u forem ukotvených k položce index `BaseItem`, kde `$7FFD` představuje sentinel (All). HotXLS je zapisuje jako atributy `
Seskupování dat a čísel a filtry stránkových polí
Seskupování se konfiguruje v poli mezipaměti, nikoli v poli kontingenční tabulky, protože mění způsob, jakým se zdrojová doména dělí do kbelíků (buckets). Nastavte `HasGroup := True` v poli mezipaměti vráceném metodou `Cache.FindFieldByName`, poté vyberte buď knoflíky číselného rozsahu (`GroupStartNum`, `GroupEndNum`, `GroupInterval`), nebo příznaky hierarchie dat (`GroupByDate` s `GroupMonths`, `GroupQuarters`, `GroupYears` a rozpětím `GroupStartDate` / `GroupEndDate`). HotXLS zapíše odpovídající prvek `
Stránková pole (page fields) jsou rozbalovací seznamy filtrů nad tabulkou. `AddPageField` umístí pole na stránkovou osu a `PageItemIndex` předvolí jedinou položku mezipaměti, přičemž výchozí hodnota je `xlPageItemAll` (`$7FFD`, což znamená (All)). Chcete-li čtenáři umožnit zaškrtnout několik položek najednou, nastavte `MultipleItemSelectionAllowed := True`, což HotXLS zapíše jako `
Jak aktualizovat mezipaměť kontingenční tabulky při změně zdroje?
Metoda `RefreshPivotCache` v `TXLSXWorkbook` znovu prohledá zdrojový rozsah a aktualizuje mezipaměť na místě, což je přesně to, co dávkový pipeline potřebuje po úpravě podkladových řádků. Předejte ID mezipaměti a metoda znovu načte každou buňku ve zdrojovém rozsahu (záhlaví na prvním řádku, data od druhého), obnoví doménu sdílených položek každého pole opětovnou deduplikací hodnot a opětovným odvozením hranic podle typu a přepíše indexy položek pro každý záznam. Vrací `1` při úspěchu a `-1`, pokud je ID mezipaměti neznámé nebo zdrojový list chybí
var
Rc: Integer;
begin
// ...zdrojová data se od vytvoření kontingenční tabulky změnila...
Sheet := Book.Sheets[1];
Sheet.Cells[2, 5].Value := 128000; // opravený údaj o Revenue
// Znovu sestavit sdílené položky mezipaměti a indexy záznamů ze zdroje.
Rc := Book.RefreshPivotCache(Pivot.CacheId); // 1 = aktualizováno, -1 = mezipaměť/zdroj chybí
if Rc = 1 then
Book.SaveAs('sales-pivot-refreshed.xlsx');
end;Sémantickou hranici zde stojí za to uvést na rovinu. Předtím, než tato metoda existovala, se HotXLS spoléhalo na příznak `refreshOnLoad="1"` a ponechávalo veškerou práci s obnovením na Excelu při příštím otevření, což je v pořádku, když soubor otevře člověk, ale nepoužitelné v headless pipeline, která musí předávat správná data. `RefreshPivotCache` aktualizuje uloženou mezipaměť v rámci procesu, a protože se tabulky vážou k mezipaměti pomocí ID, každá kontingenční tabulka vykreslená z této mezipaměti aktualizaci uvidí. Co však nedělá, je vykreslení nebo agregace viditelné mřížky — Excel stále přepočítává vykreslenou tabulku z aktualizované mezipaměti při otevření souboru
Co HotXLS zapisuje a co Excel počítá
Mějte na paměti rozdělení práce a nic vás nepřekvapí. HotXLS je zapisovačem definic: vytváří `pivotCacheDefinition`, záznamy mezipaměti a `pivotTableDefinition`, včetně os, agregací, vypočítávaných položek a členů, zobrazení hodnot, seskupování a filtrů. Excel je kalkulátor: při otevření materializuje skupiny, vyhodnocuje vzorce, aplikuje transformace showDataAs a agreguje tělo. Hodnoty, které uživatel vidí, jsou ty z Excelu, vytvořené podle instrukcí, které HotXLS zapsalo, což je důvod, proč každý vzorec a výchozí index musí být správný v době zápisu, a nikoli kontrolován při vykreslování
Omezení a doporučení
Dvě omezení stojí za to znát, než kolem této funkce navrhnete architekturu. `AddPivotTable` analýzuje obdélníkový rozsah A1 s volitelným listovým prefixem; zdroje s pojmenovaným rozsahem a externím sešitem jsou mimo to, co builder dokáže rozlišit, ačkoli mezipaměti načtené ze souboru vytvořeného v Excelu si svůj zdroj s pojmenovaným rozsahem při obousměrném přenosu zachovávají. A kontingenční tabulky vytvořené v Excelu, které HotXLS načte z disku, se při uložení přehrávají bajt po bajtu, takže zde popsané typové úpravy se čistě vztahují na kontingenční tabulky, které sestavíte v kódu, zatímco stávající tabulky zůstávají bezestrátové. Pro vstupní stranu reportu — validované buňky a filtrované tabulky, které kontingenční tabulka shrnuje — se podívejte na validaci dat, AutoFilter a strukturované tabulky
Model kontingenční tabulky zde ukázaný je součástí standardní komponenty HotXLS Delphi Excel Component pro Delphi a C++Builder, která čte a zapisuje jak typové části kontingenční tabulky XLSX, tak i klasické záznamy BIFF8 ze stejného objektového modelu