Câmpuri pivot XLSX valide conform schemei în HotXLS Delphi

HotXLS scrie definiții de tabele pivot XLSX ale căror elemente pivotField și cacheField validează conform schemei ECMA-376 Part 1 §18.10: atributele de axă folosesc token-urile ST_Axis axisRow, axisCol și axisPage, câmpurile din zona de valori poartă dataField="1", listele de item-uri nu sunt niciodată goale, iar câmpurile din cache stochează un numFmtId numeric. Din v2.384.33, cititorul onorează și valorile implicite ale schemei pe care obișnuia să le citească greșit

Bug-urile din spatele acestei curățenii împărtășesc o trăsătură neflătătoare: niciunul nu a picat vreodată un test. HotXLS scria un pivot, HotXLS îl citea înapoi, fiecare câmp ateriza pe axa corectă, iar suita de round-trip a rămas verde ani de zile. Problema era că scriitorul și cititorul căzuseră de acord, în tăcere, asupra unui dialect privat. Un pivot construit din Delphi părea în regulă componentei care l-a făcut, în timp ce o verificare contra CT_PivotField și CT_CacheField scoatea la iveală token-uri de enumerare invalide, un element vid pe care schema îl interzice și fanioane pe care Excel le așteaptă dar nu le primea niciodată. Dacă generați pivoturi pe un server și le trimiteți unor oameni care le deschid în Excel sau le dau propriilor parsere, singurul contract care contează este schema, nu ceea ce propriul cititor se întâmplă să ierte

De ce round-trip-urile HotXLS nu prindeau niciodată token-urile de axă greșite?

Round-trip-urile HotXLS nu prindeau niciodată token-urile de axă greșite pentru că cititorul accepta ambele ortografii. Vechiul XlsxPivotAxisAttr emitea axis="rowAxis", colAxis și pageAxis, care se citesc natural în engleză dar nu există în schemă; ST_Axis definește exact patru valori, axisRow, axisCol, axisPage și axisValues. Între timp, PivotAxisFromToken din lxPivotXml.pas potrivea atât token-ul din schemă, cât și cel inventat, deci fiecare auto-test trecea. Scriitorul emite acum doar token-urile din schemă, iar cititorul continuă să accepte vechile ortografii, astfel încât fișierele salvate de versiuni HotXLS mai vechi se încarcă în continuare cu aranjamentul intact

<!-- înainte de v2.384.33: valoare ST_Axis invalidă, CT_Items vid -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- din 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>

Ce cere CT_PivotField și ce omitea vechiul scriitor?

CT_PivotField cere trei lucruri pe care vechiul BuildPivotTableXml le omitea sau le greșea. În primul rând, un câmp agregat în zona de valori trebuie să spună asta în propria definiție cu dataField="1"; scriitorul setează acum fanionul acela pe fiecare câmp referențiat de o intrare din DataFields, nu doar în lista <dataFields>. În al doilea rând, CT_Items are nevoie de cel puțin un item, deci un câmp fără item-uri nu mai primește un <items count="0"> vid, iar întregul element este pur și simplu omis. În al treilea rând, fiecare item își păstrează starea: h="1" pentru un item ascuns (TXLSPivotItem.IsHidden) și sd="0" pentru detalii restrânse (IsDetailHidden), ambele pe care vechiul scriitor le arunca la fiecare salvare

Partea subtilă sunt item-urile finale de subtotal. Când un câmp are item-uri, Excel listează câte un item în plus per funcție de subtotal după item-urile de date, tipizate cu ST_ItemType: <item t="default"/> pentru subtotalul automat, apoi sum, countA, avg, max, min, product, count, stdDev, stdDevP, var și varP pentru cele explicite. HotXLS derivează acele intrări din TXLSPivotField.Subtotals la salvare și le numără în items count. Câmpurile create de AddPivotTable pornesc cu un set Subtotals vid, ceea ce scrie defaultSubtotal="0" și niciun item final, deci cereți explicit subtotalurile când raportul are nevoie de ele. Atenție la capcana de numire: xlpsCount se mapează la countA (toate intrările), iar xlpsCountNums se mapează la count (doar numere)

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];                  // cu bază 1, ca motorul 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 dacă nu există un asemenea câmp
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // setează pe Revenue dataField="1"

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

Cum citește HotXLS acum item-urile de subtotal și valorile implicite ale schemei?

Cititorul HotXLS sare acum peste orice item al cărui atribut t este prezent și diferit de data, pentru că intrările de subtotal, de total general și cele vide nu poartă niciun index de cache. Înainte de v2.384.34, acele intrări erau încărcate ca item-uri obișnuite cu CacheItemIndex setat la -1, deci un pivot făcut în Excel se întorcea cu membri fantomă care nu arătau nicăieri, iar orice cod care parcurgea Items trebuia să le filtreze de mână. Pentru că scriitorul reconstruiește intrările finale din Subtotals, treaba cititorului este să le traducă în acel set, nu să le păstreze ca date

A doua reparare a cititorului ține de atributele absente. În schemă, defaultSubtotal pe CT_PivotField și containsString pe CT_SharedItems au ambele implicit true, iar Excel le omite când țin valoarea implicită. HotXLS citea un atribut lipsă ca false, ceea ce însemna că fiecare pivot salvat de Excel își pierdea în tăcere subtotalul implicit la încărcare, iar un câmp de cache text simplu era clasificat mixt în loc de șir. Aceasta e imaginea în oglindă a bug-ului de axă: un scriitor care scrie mereu fiecare atribut nu exersează niciodată calea valorii implicite, deci doar fișierele produse de alt generator o expun

De ce era numFmtId="General" invalid pe câmpurile de cache?

Valoarea numFmtId="General" era invalidă pentru că ST_NumFmtId este un întreg fără semn, nu un nume de format. Vechiul scriitor de cache hard-coda acel șir pe fiecare cacheField, împrumutând numele pe care utilizatorii îl văd în dialogul Format Cells. HotXLS scrie acum NumberFormat-ul câmpului de cache ca număr, adică 0 (formatul General integrat), în afară de cazul în care cineva l-a setat. Un parser strict care tipizează atributele din schemă respinge vechiul frontal, și exact această clasă de defecțiune se transformă într-un dialog de reparare; articolul despre regulile OPC și de markup din spatele promptului de reparare Excel acoperă cum se declanșează acele dialoguri

De ce tabelele pivot plasate sub rândul 65535 erau tăiate?

Tabelele pivot XLSX plasate la sau sub rândul 65536 erau tăiate pentru că modelul de pivot comun stoca FirstRow, LastRow, FirstHeaderRow, FirstDataRow și omologii lor de coloană ca Word, iar codul de deplasare a rândurilor îi limita cu Min(.., High(Word)). Este o rămășiță a înregistrării SxView din BIFF8, unde 16 biți ajung, dar o foaie XLSX ajunge la 1.048.576 de rânduri. Din v2.384.37, proprietățile acelea pe TXLSPivotTable sunt Integer, limitele au dispărut, iar doar scriitorul BIFF8 le îngustează. TXLSXWorksheet.AddPivotTable și AddPivotTableCopy întorc acum nil pentru o ancoră în afara lui 1..1048576 pe 1..16384, sau pentru o copie a cărei întindere ar ieși din grilă

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Rândul 70001 se suprapunea cândva în intervalul pe 16 biți; acum supraviețuiește salvării și încărcării
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // ancoră în afara foii sau interval sursă nerezolvabil
  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;

Motorul XLS clasic a primit repararea potrivită în v2.384.38. Modelul lui stoca valorile brute cu bază 0 din SxView și DConRef și trecea ancorele AddPivotTable direct, în timp ce documentația, demo-urile și motorul XLSX foloseau toate celule cu bază 1 precum Cells[Row, Col]. Ambele motoare țin acum poziții cu bază 1 în model, cititorul BIFF8 adaugă 1, iar scriitorul scade 1 la granița de înregistrare, deci codul care ancora la (0, 0) trebuie să treacă la (1, 1), pentru că AddPivotTable-ul clasic întoarce acum nil pentru o ancoră în afara lui 1..65536 pe 1..256; apelul nou scrie exact aceiași octeți ca cel vechi. Aranjamentul de înregistrare în sine rămâne neschimbat și este descris în înregistrările SX din BIFF8 din spatele tabelelor pivot .xls clasice

Validați contra schemei, nu contra propriului cititor

Lecția se generalizează dincolo de pivoturi: un cititor indulgent ascunde încălcările scriitorului, deci un round trip prin propriul cod dovedește consistență, nu corectitudine. Fiecare bug de aici a supraviețuit pentru că partea tolerantă și partea defectă trăiau în aceeași bibliotecă. Verificările care prind efectiv această clasă de defecte sunt o validare de schemă a pieselor generate, fișiere produse de Excel trecute prin propriul cititor cu atribute omise la valorile lor implicite, și fixturi care fixează token-ul exact, nu rezultatul parsat. Pivoturile construite prin API, inclusiv câmpurile calculate, item-urile calculate și aranjamentele procent-din-total arătate în construirea și reîmprospătarea tabelelor pivot XLSX cu câmpuri calculate, primesc XML-ul corectat fără nicio schimbare de cod, în timp ce pivoturile încărcate din fișiere Excel continuă să redea piesele lor originale până le modificați

Toate aceste reparări sosesc în actuala componentă de spreadsheet HotXLS pentru Delphi, care citește și scrie XLS, XLSX și tabele pivot din Delphi și C++Builder fără Excel sau automatizare COM pe mașină