Platnosť schémy pivot polí XLSX v Delphi s HotXLS

HotXLS zapisuje definície pivot tabuliek XLSX, ktorých elementy pivotField a cacheField prejdú schémou ECMA-376 Part 1 §18.10: axis atribúty používajú tokeny ST_Axis axisRow, axisCol a axisPage, polia vo value area nesú dataField="1", zoznamy items nikdy nie sú prázdne a cache polia ukladajú číselné numFmtId. Od v2.384.33 reader tiež rešpektuje schémové predvolené hodnoty, ktoré dovtedy čítal zle

Bugy za týmto upratovaním zdieľajú nelichotivú črtu: žiaden z nich nikdy neporušil test. HotXLS napísal pivot, HotXLS si ho načítal späť, každé pole pristalo na správnej osi a round-trip suite bola zelená roky. Problém bol v tom, že writer a reader sa poticho dohodli na súkromnom dialekte. Pivot postavený z Delphi vyzeral pre komponent, ktorý ho vytvoril, dobre, zatiaľ čo kontrola proti CT_PivotField a CT_CacheField vyzdvihla neplatné enumeračné tokeny, prázdny element, ktorý schéma zakazuje, a flagy, ktoré Excel očakáva, ale nikdy ich nedostal. Ak pivots generujete na serveri a posielate ľuďom, ktorí ich otvárajú v Exceli alebo ich púšťajú do vlastných parserov, jediná zmluva, ktorá sa počíta, je schéma, nie to, čomu váš vlastný reader prípadne odpustí

Prečo round tripy HotXLS nikdy nezachytili zlé axis tokeny?

Round tripy HotXLS nikdy nezachytili zlé axis tokeny preto, lebo reader prijímal oba hláskovania. Starý XlsxPivotAxisAttr emitoval axis="rowAxis", colAxis a pageAxis, čo sa v angličtine čítalo prirodzene, ale v schéme neexistujú; ST_Axis definuje presne štyri hodnoty, axisRow, axisCol, axisPage a axisValues. Zároveň PivotAxisFromToken v lxPivotXml.pas zachytával aj schémový token aj vymyslený, takže každý self-test prešiel. Writer teraz emituje len schémové tokeny a reader naďalej prijíma staré hláskovania, aby súbory uložené staršími verziami HotXLS stále načítali so zachovaným rozložením

<!-- pred v2.384.33: neplatná hodnota ST_Axis, prázdne 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>

Čo vyžaduje CT_PivotField, čo starý writer vynechával?

CT_PivotField vyžaduje tri veci, ktoré starý BuildPivotTableXml vynechal alebo mal zle. Po prvé, pole agregované vo value area to musí povedať na vlastnej definícii cez dataField="1"; writer teraz nastavuje tento flag na každom poli referencovanom položkou v DataFields, nie len v zozname <dataFields>. Po druhé, CT_Items potrebuje aspoň jeden item, takže pole bez itemov už nedostane prázdne <items count="0"> a celý element sa jednoducho vynechá. Po tretie, každý item si drží stav: h="1" pre skrytý item (TXLSPivotItem.IsHidden) a sd="0" pre skryté detaily (IsDetailHidden), čo starý writer pri každom uložení zahodil

Jemnejšia časť sú trailing subtotal itemy. Keď pole má itemy, Excel vypíše po dátových itemoch jeden extra item na každú subtotal funkciu, typovaný cez ST_ItemType: <item t="default"/> pre automatický subtotal, potom sum, countA, avg, max, min, product, count, stdDev, stdDevP, var a varP pre explicitné. HotXLS odvodzuje tieto položky z TXLSPivotField.Subtotals pri ukladaní a počíta ich do items count. Polia vytvorené cez AddPivotTable začínajú s prázdnou množinou Subtotals, čo zapisuje defaultSubtotal="0" bez žiadneho trailing itemu, takže si subtotals explicitne vyžiadajte, keď ich report potrebuje. Všímajte si pascu v pomenovaní: xlpsCount sa mapuje na countA (všetky položky) a xlpsCountNums na count (len čí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];                  // 1-based, ako XLS engine
    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, ak také pole neexistuje
    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;

Ako HotXLS teraz číta subtotal itemy a schémové predvolené hodnoty?

Reader HotXLS teraz preskočí každý item, ktorého atribút t je prítomný a nie je data, lebo položky subtotal, grand-total a blank nesú žiadny cache index. Pred v2.384.34 sa tieto položky načítali ako obyčajné itemy s CacheItemIndex nastaveným na -1, takže pivot od Excelu sa vrátil s fantomovými členmi ukazujúcimi do prázdna a každý kód prechádzajúci Items ich musel odfiltrovať ručne. Keďže writer prestavia trailing položky z Subtotals, úlohou readera ich preložiť do tejto množiny, nie ich držať ako dáta

Druhá oprava readera sa týka atribútov, ktoré chýbajú. V schéme majú defaultSubtotal na CT_PivotField a containsString na CT_SharedItems predvolenú hodnotu true a Excel ich vynecháva, keď ju nesú. HotXLS čítal chýbajúci atribút ako false, čo znamenalo, že každý pivot uložený Excelom poticho stratil pri načítaní default subtotal a čisté textové cache pole sa klasifikovalo ako mixed namiesto string. Toto je zrkadlový obraz bugu s osami: writer, ktorý vždy vypíše každý atribút, nikdy nevyužije cestu s predvolenou hodnotou, takže ju odhalia len súbory od iného producenta

Prečo bolo numFmtId="General" na cache poliach neplatné?

Hodnota numFmtId="General" bola neplatná, pretože ST_NumFmtId je bezznamienkové celé číslo, nie meno formátu. Starý cache writer túto hodnotu hard-codoval na každý cacheField a požičiaval si meno, ktoré užívatelia vidia v dialógu Format Cells. HotXLS teraz zapisuje NumberFormat cache poľa ako číslo, ktoré je 0 (vstavaný General formát), pokiaľ ho niečo nenastavilo. Prísny parser, ktorý typuje atribúty zo schémy, starú hodnotu odmietne rovno a presne táto trieda zlyhaní sa mení na repair dialóg; článok o pravidlách OPC a markup za repair dialógom Excelu popisuje, ako sa tie dialógy spúšťajú

Prečo boli pivot tabuľky pod riadkom 65535 odrezané?

Pivot tabuľky XLSX umiestnené na riadku 65536 a nižšie sa odrezali, pretože zdieľaný pivot model ukladal FirstRow, LastRow, FirstHeaderRow, FirstDataRow a ich stĺpcové náprotivky ako Word a kód posúvajúci riadky ich zvieral cez Min(.., High(Word)). To je pozostatok BIFF8 recordu SxView, kde 16 bitov stačí, ale hárok XLSX beží do 1 048 576 riadkov. Od v2.384.37 sú tieto vlastnosti na TXLSPivotTable typu Integer, zvieranie zmizlo a zužuje už len BIFF8 writer. TXLSXWorksheet.AddPivotTable a AddPivotTableCopy teraz vracajú nil pre kotvu mimo 1..1048576 × 1..16384 alebo pre kópiu, ktorej rozsah by prebehol mimo mriežky

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Riadok 70001 sa predtým zabál do 16-bitového rozsahu; teraz prežije uloženie aj načítanie
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // kotva mimo hárka alebo nevyriešiteľný zdrojový range
  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;

Klasický XLS engine dostal zodpovedajúcu opravu vo v2.384.38. Jeho model ukladal surové 0-based hodnoty SxView a DConRef a kotvy AddPivotTable prenášal priamo, zatiaľ čo dokumentácia, demá aj XLSX engine používali 1-based bunky ako Cells[Row, Col]. Oba enginey teraz držia v modeli 1-based pozície, BIFF8 reader pripočíta 1 a writer odpočíta 1 na hranici recordu, takže kód kotviaci na (0, 0) sa musí presunúť na (1, 1), lebo klasické AddPivotTable vracia teraz nil pre kotvu mimo 1..65536 × 1..256; nové volanie zapisuje tie isté bajty ako staré. Samotné rozloženie recordov sa nezmenilo a je popísané v BIFF8 SX recordoch za klasickými pivot tabuľkami .xls

Validujte proti schéme, nie proti vlastnému readeru

Lekcia sa generalizeje za pivots: zhovievavý reader skrýva previnenia writeru, takže round trip cez vlastný kód dokazuje konzistentnosť, nie správnosť. Každý bug tu prežil, pretože tolerantná a pokazená strana žili v tej istej knižnici. Kontroly, ktoré túto triedu defektov naozaj chytia, sú schémová validácia generovaných častí, súbory od Excelu pustené cez váš reader s atribútmi vynechanými na predvolených hodnotách a fixtúry pripichujúce presný token namiesto vyparsovaného výsledku. Pivots postavené cez API, vrátane calculated fields, calculated items a rozložení percent-of-total ukázaných v stavbe a refreshovaní pivot tabuliek XLSX s calculated fields, dostanú opravené XML bez zmeny kódu, zatiaľ čo pivots načítané z excelových súborov prehrávajú svoje pôvodné časti, dokiaľ ich nezmeníte

Všetky tieto opravy idú v aktuálnom HotXLS Delphi spreadsheet component, ktorý číta a zapisuje XLS, XLSX a pivot tabuľky z Delphi a C++Builderu bez Excelu alebo COM automation na stroji