Validità schema dei campi pivot XLSX in Delphi con HotXLS

HotXLS scrive definizioni di tabelle pivot XLSX i cui elementi pivotField e cacheField validano contro lo schema ECMA-376 Part 1 §18.10: gli attributi axis usano i token ST_Axis axisRow, axisCol e axisPage, i campi dell'area valori portano dataField="1", le liste di item non sono mai vuote, e i campi di cache memorizzano un numFmtId numerico. Dalla v2.384.33 il reader onora anche i default dello schema che prima sbagliava

I bug dietro questa pulizia condividono un tratto poco lusinghiero: nessuno di loro ha mai fatto fallire un test. HotXLS scriveva una pivot, HotXLS la rileggeva, ogni campo atterrava sull'axis giusto, e la suite di round trip restava verde per anni. Il problema era che writer e reader si erano messi d'accordo in silenzio su un dialetto privato. Una pivot costruita da Delphi sembrava a posto al componente che l'aveva fatta, mentre un controllo contro CT_PivotField e CT_CacheField tirava fuori token di enumerazione invalidi, un elemento vuoto che lo schema vieta e flag che Excel si aspetta ma non ha mai ricevuto. Se generate pivot su un server e le spedite a persone che le aprono in Excel o le passano ai loro parser, l'unico contratto che conta è lo schema, non quel che per caso perdona il vostro reader

Perché i round trip HotXLS non beccavano mai i token axis sbagliati?

I round trip HotXLS non beccavano mai i token axis sbagliati perché il reader accettava entrambe le grafie. Il vecchio XlsxPivotAxisAttr emetteva axis="rowAxis", colAxis e pageAxis, che si leggono naturale in inglese ma non esistono nello schema; ST_Axis definisce esattamente quattro valori, axisRow, axisCol, axisPage e axisValues. Nel frattempo PivotAxisFromToken in lxPivotXml.pas accettava sia il token dello schema sia quello inventato, quindi ogni self-test passava. Il writer ora emette solo i token dello schema, e il reader continua ad accettare le vecchie grafie così che i file salvati da versioni HotXLS precedenti si carichino ancora col layout intatto

<!-- prima della v2.384.33: valore ST_Axis invalido, CT_Items vuoto -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

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

Che cosa esige CT_PivotField che il vecchio writer saltava?

CT_PivotField esige tre cose che il vecchio BuildPivotTableXml tralasciava o sbagliava. Primo, un campo aggregato nell'area valori deve dirlo sulla propria definizione con dataField="1"; il writer ora imposta quel flag su ogni campo referenziato da una voce in DataFields, non solo nella lista <dataFields>. Secondo, CT_Items ha bisogno di almeno un item, quindi un campo senza item non riceve più un <items count="0"> vuoto e l'intero elemento viene semplicemente omesso. Terzo, ogni item conserva il proprio stato: h="1" per un item nascosto (TXLSPivotItem.IsHidden) e sd="0" per i dettagli collassati (IsDetailHidden), entrambi scartati dal vecchio writer a ogni salvataggio

La parte sottile sono gli item subtotal in coda. Quando un campo ha item, Excel elenca un item extra per funzione subtotal dopo gli item dati, tipizzato con ST_ItemType: <item t="default"/> per il subtotal automatico, poi sum, countA, avg, max, min, product, count, stdDev, stdDevP, var e varP per quelli espliciti. HotXLS ricava quelle voci da TXLSPivotField.Subtotals al salvataggio e le conteggia dentro items count. I campi creati da AddPivotTable partono con un insieme Subtotals vuoto, che scrive defaultSubtotal="0" e nessun item in coda, quindi chiedete esplicitamente i subtotal quando il report li vuole. Notate la trappola sui nomi: xlpsCount mappa a countA (tutte le voci) e xlpsCountNums mappa a count (solo numeri)

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];                  // in base 1, come l'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 se il campo non esiste
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // imposta dataField="1" su Revenue

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

Come legge HotXLS ora gli item subtotal e i default dello schema?

Il reader HotXLS ora salta ogni item il cui attributo t è presente e diverso da data, perché le voci subtotal, grand total e vuote non portano alcun indice di cache. Prima della v2.384.34 quelle voci venivano caricate come item ordinari con CacheItemIndex a -1, così una pivot fatta da Excel tornava con membri fantasma che non puntavano a nulla, e qualsiasi codice che percorreva Items doveva filtrarli a mano. Dato che il writer ricostruisce le voci in coda da Subtotals, il compito del reader è tradurle in quell'insieme, non tenerle come dati

La seconda correzione del reader riguarda gli attributi assenti. Nello schema, defaultSubtotal su CT_PivotField e containsString su CT_SharedItems valgono entrambi true per default, ed Excel li omette quando portano quel default. HotXLS leggeva un attributo mancante come false, il che significa che ogni pivot salvata da Excel perdeva in silenzio il suo subtotal predefinito al caricamento, e un campo cache di testo puro veniva classificato come misto invece che stringa. È l'immagine speculare del bug degli axis: un writer che scrive sempre ogni attributo per esteso non esercita mai il percorso dei default, quindi solo file di un altro produttore lo espongono

Perché numFmtId="General" era invalido sui campi cache?

Il valore numFmtId="General" era invalido perché ST_NumFmtId è un intero senza segno, non un nome di formato. Il vecchio writer di cache scriveva quella stringa hard-coded su ogni cacheField, prendendo a prestito il nome che gli utenti vedono nella finestra Formato celle. HotXLS ora scrive il NumberFormat del campo cache come numero, che è 0 (il formato General incorporato) salvo impostazioni diverse. Un parser severo che tipizza gli attributi dallo schema rifiuta seccamente il vecchio valore, ed è esattamente la famiglia di guasti che diventa una finestra di riparazione; l'articolo su le regole OPC e markup dietro la richiesta di riparazione di Excel copre come quelle finestre vengono innescate

Perché le pivot sotto la riga 65535 venivano tagliate?

Le tabelle pivot XLSX piazzate alla riga 65536 o sotto venivano tagliate perché il modello pivot condiviso memorizzava FirstRow, LastRow, FirstHeaderRow, FirstDataRow e le controparti di colonna come Word, e il codice di spostamento righe le clampava con Min(.., High(Word)). È un avanzo del record SxView BIFF8, dove 16 bit bastano, ma un foglio XLSX arriva a 1.048.576 righe. Dalla v2.384.37 quelle proprietà su TXLSPivotTable sono Integer, i clamp sono spariti, e solo il writer BIFF8 restringe i valori. TXLSXWorksheet.AddPivotTable e AddPivotTableCopy ora restituiscono nil per un'ancora fuori da 1..1048576 per 1..16384, o per una copia la cui estensione uscirebbe dalla griglia

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // La riga 70001 una volta finiva nel wrap del range a 16 bit; ora sopravvive a salvataggio e caricamento
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // ancora fuori dal foglio o intervallo sorgente irrisolvibile
  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;

L'engine XLS classico ha ricevuto la correzione corrispondente nella v2.384.38. Il suo modello memorizzava i valori grezzi in base 0 di SxView e DConRef e faceva passare le ancore di AddPivotTable direttamente, mentre la documentazione, le demo e l'engine XLSX usavano tutti celle in base 1 come Cells[Row, Col]. Entrambi gli engine ora tengono posizioni in base 1 nel modello, il reader BIFF8 aggiunge 1 e il writer toglie 1 al confine del record, quindi il codice che ancorava a (0, 0) deve passare a (1, 1), perché il classico AddPivotTable ora restituisce nil per un'ancora fuori da 1..65536 per 1..256; la nuova chiamata scrive gli stessi byte della vecchia. Il layout dei record in sé è invariato ed è descritto in i record SX BIFF8 dietro le pivot dei .xls classici

Validare contro lo schema, non contro il vostro reader

La lezione generalizza oltre le pivot: un reader tollerante nasconde le violazioni del writer, quindi un round trip attraverso il vostro codice prova coerenza, non correttezza. Ogni bug qui è sopravvissuto perché il lato tollerante e il lato difettoso vivevano nella stessa libreria. I controlli che beccano davvero questa classe di difetti sono una validazione dello schema delle parti generate, file prodotti da Excel passati nel vostro reader con attributi omessi ai loro default, e fixture che inchiodano il token esatto anziché il risultato analizzato. Le pivot costruite tramite l'API, compresi i campi calcolati, gli item calcolati e i layout percent-of-total mostrati in costruire e aggiornare tabelle pivot XLSX con campi calcolati, ricevono l'XML corretto senza modifiche al codice, mentre le pivot caricate da file Excel continuano a riprodurre le loro parti originali finché non le modificate

Tutte queste correzioni sono nella versione attuale del componente foglio di calcolo HotXLS per Delphi, che legge e scrive XLS, XLSX e tabelle pivot da Delphi e C++Builder senza Excel né automazione COM sulla macchina