Validita schématu polí kontingenční tabulky XLSX a HotXLS

HotXLS zapisuje definice kontingenčních tabulek XLSX, jejichž elementy pivotField a cacheField validují proti schématu ECMA-376 Part 1 §18.10: atributy osy používají tokeny ST_Axis axisRow, axisCol a axisPage, pole v hodnotové oblasti nesou dataField="1", seznamy items nejsou nikdy prázdné a cache pole ukládají číselné numFmtId. Od v2.384.33 reader navíc respektuje defaulty schématu, které dřív dostával špatně

Bugy za tímhle úklidem sdílejí nelichotivou vlastnost: ani jeden nikdy neprošel testem. HotXLS napsal pivot, HotXLS si ho přečetl zpět, každé pole dopadlo na správnou osu a round-trip suite byla zelená roky. Problém byl v tom, že se writer a reader potichu domluvili na privátním dialektu. Pivot postavený z Delphi vypadal pro komponentu, která ho vytvořila, dobře, zatímco kontrola proti CT_PivotField a CT_CacheField vyzvedla nevalidní enumerační tokeny, prázdný element, který schéma zakazuje, a flagy, které Excel očekává, ale nikdy je nedostal. Pokud generujete pivoty na serveru a posíláte je lidem, kteří je otevírají v Excelu nebo je pouštějí do vlastních parserů, jediný kontrakt, který se počítá, je schéma — ne to, čemu vaše vlastní reader náhodou odpustí

Proč round trip HotXLS nikdy nechytil špatné tokeny os?

Round trip HotXLS nikdy nechytil špatné tokeny os, protože reader přijímal obě hláskování. Starý XlsxPivotAxisAttr vypouštěl axis="rowAxis", colAxis a pageAxis, což se v angličtině čte přirozeně, ale ve schématu neexistuje; ST_Axis definuje přesně čtyři hodnoty, axisRow, axisCol, axisPage a axisValues. Mezitím PivotAxisFromToken v lxPivotXml.pas matchoval obojí, token schématu i vymyšlený, takže každý self-test prošel. Writer teď vypouští jen tokeny schématu a reader stará hláskování dál přijímá, aby soubory uložené staršími verzemi HotXLS pořád načítaly se zachovaným rozložením

<!-- před v2.384.33: nevalidní hodnota ST_Axis, prázdné CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- od v2.384.33 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

Co požaduje CT_PivotField a starý writer vynechal?

CT_PivotField požaduje tři věci, které starý BuildPivotTableXml vynechal nebo pokazil. Za prvé, pole agregované v hodnotové oblasti to musí říct na své vlastní definici pomocí dataField="1"; writer teď nastavuje tenhle flag na každém poli, na které odkazuje položka v DataFields, ne jen v seznamu <dataFields>. Za druhé, CT_Items potřebuje aspoň jeden item, takže pole bez položek už nedostává prázdné <items count="0"> a celý element se prostě vynechá. Za třetí, každá položka si drží svůj stav: h="1" pro skrytou položku (TXLSPivotItem.IsHidden) a sd="0" pro sbalené detaily (IsDetailHidden), obojí věci, které starý writer při každém uložení upustil

Jemnější částí jsou závěrečné položky mezisoučtů. Když má pole items, Excel vypisuje po datových položkách jeden extra item na každou funkci mezisoučtu, typovaný přes ST_ItemType: <item t="default"/> pro automatický mezisoučet, pak sum, countA, avg, max, min, product, count, stdDev, stdDevP, var a varP pro explicitní. HotXLS odvozuje tyhle položky z TXLSPivotField.Subtotals v okamžiku uložení a počítá je do items count. Pole vytvořená přes AddPivotTable startují s prázdnou množinou Subtotals, což zapíše defaultSubtotal="0" bez závěrečné položky, takže si mezisoučty explicitně vyžádejte, když je report potřebuje. Všimněte si pojmenovací pastky: xlpsCount se mapuje na countA (všechny položky) a xlpsCountNums na count (jen čísla)

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // od 1, jako engine XLS
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // nil, pokud takové pole není
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // nastaví Revenue dataField="1"

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

Jak teď HotXLS čte položky mezisoučtů a defaulty schématu?

Reader HotXLS teď přeskočí každý item, jehož atribut t je přítomný a nerovná se data, protože položky mezisoučtů, celkových součtů a prázdné položky nenesou žádný cache index. Před v2.384.34 se tyhle položky načítaly jako obyčejné s CacheItemIndex nastaveným na -1, takže pivot od Excelu se vracel s fantomovými členy, které nikam neukazovaly, a každý kód procházející Items si je musel odfiltrovat ručně. Protože writer závěrečné položky znovu staví z Subtotals, úkolem readeru je přeložit je do téhle množiny, ne držet je jako data

Druhá oprava readeru se týká atributů, které chybí. Ve schématu mají defaultSubtotal na CT_PivotField a containsString na CT_SharedItems oba default true a Excel je vynechává, když drží tenhle default. HotXLS četl chybějící atribut jako false, což znamenalo, že každý pivot uložený Excelem tiše ztratil při načtení defaultní mezisoučet a obyčejné textové cache pole se klasifikovalo jako míšené místo stringového. Tohle je zrcadlový obraz bugu s osou: writer, který vždy vypíše každý atribut, nikdy nevyzkouší cestu defaultu, takže ji vystaví jen soubory od jiného producenta

Proč bylo numFmtId="General" na cache polích nevalidní?

Hodnota numFmtId="General" byla nevalidní, protože ST_NumFmtId je bezznaménkové celé číslo, ne název formátu. Starý cache writer hard-codoval tenhle string na každém cacheField a půjčil si název, který uživatelé vidí v dialogu Formát buněk. HotXLS teď zapisuje NumberFormat cache pole jako číslo, což je 0 (vestavěný formát General), pokud ho něco nenastavilo. Přísný parser, který typuje atributy ze schématu, starou hodnotu odmítne rovnou a přesně tahle třída selhání se mění v opravovací dialog; článek o pravidlech OPC a markupu za opravovacím dialogem Excelu rozebírá, jak se tyhle dialogy spouštějí

Proč se kontingenční tabulky pod řádkem 65535 uřezávaly?

Kontingenční tabulky XLSX umístěné na řádku 65536 a níže se uřezávaly, protože sdílený pivot model ukládal FirstRow, LastRow, FirstHeaderRow, FirstDataRow a jejich sloupcové protějšky jako Word a kód posouvající řádky je clampoval přes Min(.., High(Word)). To je pozůstatek záznamu BIFF8 SxView, kde 16 bitů stačí, ale list XLSX jede do 1 048 576 řádků. Od v2.384.37 jsou tyhle properties na TXLSPivotTable Integer, clamps jsou pryč a zužuje hodnoty jen writer BIFF8. TXLSXWorksheet.AddPivotTable a AddPivotTableCopy teď vracejí nil pro kotvu mimo 1..1048576 krát 1..16384, nebo pro kopii, jejíž rozsah by přeběhl přes mřížku

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Řádek 70001 se dřív zabalil do 16bitového rozsahu; teď přežije uložení i načtení
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // kotva mimo list nebo nevyřešitelný zdrojový rozsah
  Pivot.AddRowField('Region');
  Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Book.SaveAs('late.xlsx');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

Classic engine XLS dostal odpovídající opravu ve v2.384.38. Jeho model dřív ukládal syrové 0-based hodnoty SxView a DConRef a pouštěl kotvy AddPivotTable rovnou dál, zatímco dokumentace, dema i XLSX engine používaly buňky od 1 jako Cells[Row, Col]. Oba engine teď drží v modelu pozice od 1, reader BIFF8 přičítá 1 a writer na hranici záznamu odečítá 1, takže kód, který kotvil na (0, 0), se musí přestěhovat na (1, 1), protože classic AddPivotTable teď vrací nil pro kotvu mimo 1..65536 krát 1..256; nové volání zapíše tytéž byty jako staré. Samotné rozložení záznamu se nezměnilo a popisuje ho Zápis kontingenčních tabulek BIFF8 v Delphi: SXDB a SXLI

Validujte proti schématu, ne proti vlastnímu readeru

Lekce se generalizuje za pivoty: shovívavý reader schovává prohřešky writeru, takže round trip přes vlastní kód dokazuje konzistenci, ne správnost. Každý bug tady přežil proto, že tolerantní strana a vadná strana bydlely ve stejné knihovně. Kontrolami, které tuhle třídu defektů doopravdy chytí, jsou schématická validace generovaných částí, soubory produkované Excelem pouštěné vaším readerem s atributy vynechanými na defaultech a fixture připíchnuté na přesný token místo vyparsovaného výsledku. Pivoty stavěné přes API, včetně calculated fields, calculated items a rozložení procent z celku ukázaných v Tvorba a obnova kontingenčních tabulek v Delphi s HotXLS, dostanou opravené XML bez změny kódu, zatímco pivoty načtené ze souborů Excelu si pořád přehrávají své původní části, dokud je nezměníte

Všechny tyhle opravy shipují v aktuální komponentě HotXLS pro Excel v Delphi, která čte a zapisuje XLS, XLSX a kontingenční tabulky z Delphi a C++Builderu bez Excelu a COM automation na stroji