Validnost XLSX pivot polja po šemi u Delphi-ju uz HotXLS

HotXLS upisuje definicije XLSX pivot tabela čiji se elementi pivotField i cacheField validiraju prema ECMA-376 Part 1 §18.10 šemi: axis atributi koriste ST_Axis tokene axisRow, axisCol i axisPage, polja vrednosne oblasti nose dataField="1", liste stavki nikada nisu prazne, a keš polja čuvaju numerički numFmtId. Od v2.384.33 čitač poštuje i podrazumevane vrednosti iz šeme koje je ranije pogrešno čitao

Bugovi iza ovog čišćenja dele neugodno svojstvo: nijedan nikada nije pao na testu. HotXLS napiše pivot, HotXLS ga pročita nazad, svako polje dospe na pravu osu, i round-trip suite godinama je ostajao zelen. Problem je bio što su se pisac i čitač tiho dogovorili oko privatnog dijalekta. Pivot građen iz Delphi-ja delovao je uredan komponenti koja ga je napravila, dok je provera protiv CT_PivotField i CT_CacheField otkrivala nevalidne enumeracijske tokene, prazan element koji šema zabranjuje i zastavice koje Excel očekuje a nikada nije dobio. Ako pivot-ove generišete na serveru i šaljete ljudima koji ih otvaraju u Excelu ili ih sipaju u svoje parsere, jedini ugovor koji se računa je šema, a ne ono što vaš čitač slučajno oprosti

Zašto HotXLS round trip nikada nije uhvatio pogrešne axis tokene?

HotXLS round trip nikada nije uhvatio pogrešne axis tokene jer je čitač prihvatao oba načina pisanja. Stari XlsxPivotAxisAttr emitovao je axis="rowAxis", colAxis i pageAxis, što se prirodno čita na engleskom ali u šemi ne postoji; ST_Axis definiše tačno četiri vrednosti, axisRow, axisCol, axisPage i axisValues. U međuvremenu je PivotAxisFromToken u lxPivotXml.pas poklapao i šemski token i izmišljeni, pa je svaki samotest prolazio. Pisac sada emituje samo šemske tokene, a čitač i dalje prihvata stara pisanja, da bi fajlovi sačuvani ranijim HotXLS verzijama i dalje mogli da se učitaju sa netaknutim rasporedom

<!-- pre v2.384.33: nevalidna ST_Axis vrednost, prazan 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>

Šta CT_PivotField zahteva, a šta je stari pisac preskakao?

CT_PivotField traži tri stvari koje je stari BuildPivotTableXml izostavljao ili kvario. Prvo, polje agregirano u oblasti vrednosti mora to da kaže na svojoj sopstvenoj definiciji sa dataField="1"; pisac sada postavlja tu zastavicu na svako polje koje referencira stavka iz DataFields-a, a ne samo u <dataFields> listi. Drugo, CT_Items treba bar jedan item, pa polje bez stavki više ne dobija prazan <items count="0"> nego se ceo element jednostavno izostavi. Treće, svaka stavka čuva svoje stanje: h="1" za skrivenu stavku (TXLSPivotItem.IsHidden) i sd="0" za skupljene detalje (IsDetailHidden), što je stari pisac odbacivao pri svakom čuvanju

Suptilan deo su prateće subtotal stavke. Kada polje ima stavki, Excel iza stavki podataka nabraja po jedan dodatni item po subtotal funkciji, tipiziran sa ST_ItemType: <item t="default"/> za automatski subtotal, zatim sum, countA, avg, max, min, product, count, stdDev, stdDevP, var i varP za eksplicitne. HotXLS te unose izvodi iz TXLSPivotField.Subtotals pri čuvanju i ubraja ih u items count. Polja stvorena u AddPivotTable-u startuju sa praznim Subtotals skupom, što upisuje defaultSubtotal="0" bez prateće stavke, pa tražite subtotal-e eksplicitno kada ih izveštaj treba. Primetite i zamku u imenovanju: xlpsCount se preslikava na countA (sve unose) a xlpsCountNums na count (samo brojeve)

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, kao u XLS engine-u
    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 ako nema takvog polja
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // postavlja Revenue dataField="1"

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

Kako HotXLS sada čita subtotal stavke i šemske podrazumevane vrednosti?

HotXLS čitač sada preskače svaki item čiji je t atribut prisutan a različit od data, jer subtotal, grand-total i prazni unosi nemaju keš indeks. Pre v2.384.34 ti unosi učitavani su kao obične stavke sa CacheItemIndex postavljenim na -1, pa je pivot napravljen u Excelu vraćan sa fantomskim članovima koji nigde nisu pokazivali, i svaki kod koji je šetao kroz Items morao ih je izdvajati rukom. Pošto pisac prateće unose gradi iznova iz Subtotals-a, posao čitača je da ih preslika u taj skup, a ne da ih čuva kao podatke

Druga popravka čitača tiče se atributa koji izostanu. U šemi defaultSubtotal na CT_PivotField-u i containsString na CT_SharedItems-u podrazumevaju se na true, i Excel ih izostavlja kad nose tu podrazumevanu vrednost. HotXLS je nedostajući atribut čitao kao false, što je značilo da svaki pivot sačuvan u Excelu tiho gubi svoj default subtotal pri učitavanju, a čisto tekstualno keš polje klasifikovalo se kao mešano umesto kao string. To je ogledalo axis buga: pisac koji uvek izgovara svaki atribut nikada ne prolazi kroz putanju podrazumevane vrednosti, pa je jedino otkrivaju fajlovi drugog proizvođača

Zašto je numFmtId="General" bilo nevalidno na keš poljima?

Vrednost numFmtId="General" bila je nevalidna jer je ST_NumFmtId neoznačen ceo broj, a ne ime formata. Stari pisac keša hard-kodirao je taj string na svakom cacheField-u, pozajmivši ime koje korisnici vide u Format Cells dijalogu. HotXLS sada upisuje NumberFormat keš polja kao broj, koji je 0 (ugrađeni General format) osim ako ga je nešto postavilo. Strogi parser koji tipizira atribute iz šeme staru vrednost odbacuje bez razmatranja, a baš je to klasa kvarova koja se pretvara u dijalog za popravku; članak o OPC i markup pravilima iza Excelovog poziva na popravku pokriva kako se ti dijalozi aktiviraju

Zašto su pivot tabele ispod reda 65535 odsećane?

XLSX pivot tabele postavljene na ili ispod reda 65536 odsecane su jer je zajednički pivot model čuvao FirstRow, LastRow, FirstHeaderRow, FirstDataRow i njihove parnjake za kolone kao Word, a kod za pomeranje redova stegao ih je sa Min(.., High(Word)). To je zaostatak BIFF8 SxView zapisa, gde je 16 bitova dovoljno, ali XLSX list ide do 1.048.576 redova. Od v2.384.37 ta svojstva na TXLSPivotTable-u su Integer, stega je nestala, a vrednosti sužava samo BIFF8 pisac. TXLSXWorksheet.AddPivotTable i AddPivotTableCopy sada vraćaju nil za sidro van 1..1048576 puta 1..16384, ili za kopiju čiji opseg bi pobegao sa mreže

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Red 70001 omatao se u 16-bitni opseg; sada preživi čuvanje i učitavanje
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // sidro van lista ili nerazrešiv izvorni opseg
  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;

Klasični XLS engine dobio je odgovarajuću popravku u v2.384.38. Njegov model čuvao je sirove 0-bazirane SxView i DConRef vrednosti i prosleđivao AddPivotTable sidra direktno, dok su dokumentacija, demoi i XLSX engine svi koristili ćelije od 1 poput Cells[Row, Col]. Oba engine-a sada čuvaju pozicije od 1 u modelu, BIFF8 čitač dodaje 1 a pisac oduzima 1 na granici zapisa, pa kod koji je sidrio na (0, 0) mora preći na (1, 1), jer klasični AddPivotTable sada vraća nil za sidro van 1..65536 puta 1..256; novi poziv upisuje iste bajtove kao stari. Raspored zapisa sam po sebi nepromenjen i opisan je u tekstu o BIFF8 SX zapisima iza klasičnih .xls pivot tabela

Validirajte prema šemi, ne prema sopstvenom čitaču

Pouka segeneralizuje van pivotova: blag čitač krije prekršaje pisca, pa round trip kroz vaš sopstveni kod dokazuje usklađenost, a ne ispravnost. Svaki bug ovde preživeo je jer su tolerantna i pokvarena strana živele u istoj biblioteci. Provere koje zaista hvataju ovu klasu defekata su šemska validacija generisanih delova, fajlovi proizvedeni u Excelu propušteni kroz vaš čitač sa atributima izostavljenim na podrazumevanim vrednostima, i fixture-i koji fiksiraju tačan token umesto parsiranog rezultata. Pivot-ove koje gradite kroz API, uključujući calculated fields, calculated items i percent-of-total rasporede prikazane u tekstu o građenju i osvežavanju XLSX pivot tabela sa calculated fields, ispravljeni XML stiže bez ikakve izmene koda, dok pivot-ovi učitani iz Excel fajlova i dalje repriziraju svoje izvorne delove sve dok ih ne izmenite

Sve ove popravke stižu u trenutnu HotXLS Delphi spreadsheet komponentu, koja iz Delphi-ja i C++Builder-a čita i upisuje XLS, XLSX i pivot tabele bez Excela ili COM automation-a na mašini