LlosLab Software Development BlogNote tecniche su PDF, Delphi e software documentale per sviluppatori

Articolo tecnico

Perché Excel ignora gli errori #SPILL! e #CALC! in cache

HotXLS Delphi Component

Excel si fida di una #SPILL! o una #CALC! in cache solo quando il workbook porta l'intero contratto di errore moderno: metadati Rich Data il cui URI di estensione XLRICHVALUE è il GUID minuscolo, una fileVersion di 7, un calcId di almeno 191029, e la feature di calcolo microsoft.com:RD. HotXLS, il componente XLSX nativo Delphi, emette tutti e quattro ogni volta che una cella salvata contiene uno di quegli errori

Perdete un pezzo e il fallimento raramente è rumoroso. A volte Excel 16 rifiuta del tutto di aprire il file. Più spesso si apre bene e mostra #VALUE! dove il vostro motore di calcolo ha prodotto #SPILL!, il che sembra un bug di calcolo e vi manda a caccia nel codice delle formule che è perfettamente corretto. Premete F9 e l'errore giusto appare, così il problema tende a chiudersi come «funziona sulla mia macchina» finché un cliente non apre il report con il calcolo automatico spento. Questo post percorre cosa fa ciascun pezzo, la trappola del case del GUID che sembra non poter contare per niente, e la regola della cache di testo array che fallisce esattamente allo stesso modo silenzioso

Perché Excel mostra #VALUE! invece di una #SPILL! in cache?

Excel mostra #VALUE! perché è letteralmente ciò che la cella conserva. Il tipo di errore legacy in ECMA-376 SpreadsheetML ha un elenco fisso di letterali di errore, e #SPILL! e #CALC! non ci sono. Una cella con t="e" può dire solo #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! o #N/A, quindi le versioni Excel più recenti scrivono #VALUE! come ripiego compatibile all'indietro e appengono il vero tipo di errore a un indice di value-metadata

Quell'indice, l'attributo vm, punta in xl/metadata.xml, che punta a un valore Rich Data che descrive l'errore vero. I lettori più vecchi vedono #VALUE! e vanno avanti. L'Excel corrente segue la catena e mostra l'errore moderno, ma solo se decide che la catena è fidabile. Ecco come appare su disco una formula spilled e bloccata:

<!-- sheet1.xml: letterale legacy più un puntatore value-metadata -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml: errorType 8 è #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS scrive il tipo di errore 8 con offset di colonna e riga per #SPILL!, e il tipo di errore 13 senza offset per #CALC!. Il <v> legacy resta #VALUE! in entrambi i casi apposta, perché è il valore che qualunque lettore che ignora Rich Data deve vedere

Il GUID minuscolo che Excel 16 controlla davvero

Il blocco future-metadata XLRICHVALUE deve usare l'URI di estensione {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} in minuscolo, carattere per carattere. Mettete in maiuscolo le lettere esadecimali e la build locale di Excel 16 rifiutava di aprire il workbook normalmente, nonostante ogni parte XML si parsasse, ogni relazione si risolvesse, e ogni conteggio e indice tornasse

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

Chiunque abbia lavorato con COM o il registro sa che i GUID si confrontano case-insensitive, e quell'abitudine è esattamente ciò che morde qui. L'abbiamo isolato alla maniera lenta: costruito un package minimo che Excel rifiutava, poi scambiato una cosa alla volta. Sostituire i content type non l'ha corretto. Aggiungere una parte dei tipi rich value non l'ha corretto. Cambiare i prefissi dei namespace o riscrivere i dati rich value non l'ha corretto. Mettere in minuscolo quel singolo attributo uri l'ha corretto, e lo stesso package allora si apriva e mostrava sia #CALC! sia #SPILL!. Trattate l'URI di estensione come un letterale stringa opaco, copiatelo dalla definizione del formato, e non fate mai passare un formatter GUID che sceglie il proprio case

C'è un corollario pratico per chi scrive a mano package diagnostici. Fate le modifiche con un parser XML, non con un replace di testo globale sull'XML serializzato. Una sostituzione a tappeto puntata a un token può corrompere entità come &gt; altrove nella parte, e allora state debuggando una sonda rotta invece della domanda originale

Che altro serve a Excel per leggere un errore moderno in cache?

Le parti di metadati e le loro relazioni sono necessarie ma non sufficienti. Con una catena Rich Data perfettamente valida al suo posto, Excel ignorava comunque l'errore in cache finché la parte workbook non si dichiarava come proveniente da un motore di calcolo moderno. HotXLS scrive tre dichiarazioni in workbook.xml ogni volta che una cella salvata contiene #SPILL! o #CALC!:

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

L'elemento fileVersion è semplice metadato workbook ECMA-376 Part 1, e lastEdited / lowestEdited di 7 dicono a Excel che il file è stato salvato l'ultima volta da una generazione di applicazione che capisce Rich Data. Il calcId su calcPr identifica la versione del motore di calcolo, e 191029 è il minimo che abbiamo trovato accettare a Excel per queste cache. La voce microsoft.com:RD nell'estensione delle feature di calcolo, nel namespace http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, dichiara che il motore che ha prodotto i valori in cache ha valutato con semantica Rich Data. Fornire solo il sottotipo, solo la parte dei tipi, o solo prefissi e percorsi di relazione diversi non cambiava l'esito. Tutte e tre le dichiarazioni dovevano stare insieme

Tre dettagli in come HotXLS li applica meritano di essere conosciuti se impostate questi valori voi:

  • TXLSXWorkbook.CalcId non viene sovrascritto. Lo scrittore alza il valore emesso a 191029 solo nell'output, così un chiamante che ha configurato un CalcId più alto lo conserva, e la proprietà in memoria resta esattamente ciò che avete assegnato
  • Le feature di calcolo esistenti e le estensioni workbook non correlate arrivate con un file aperto vengono conservate, non sostituite
  • Lo scrittore controlla l'esistenza di una feature microsoft.com:RD prima di aggiungerne una, così salvare ripetutamente lo stesso workbook non impila mai dichiarazioni duplicate
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 spills tre righe; A2 è occupata, quindi il risultato è #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Un CalcId più alto sopravvive; lo scrittore lo alza a 191029 solo se più basso
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // parti di errore rich scritte automaticamente
  finally
    Book.Free;
  end;
end;

Voi non costruite mai le parti Rich Data. Prima di scrivere, TXLSXWorkbook scansiona ogni cella per valori #SPILL! o #CALC!, crea o estende xl/metadata.xml e le due parti rich value, e butta i collegamenti value-metadata stanti dalle celle il cui errore è nel frattempo cambiato o azzerato. L'assegnazione esplicita di CalcId sopra è opzionale e mostrata solo per far capire che viene rispettata

Le cache di testo array stanno in t="str", non nelle shared strings

Lo stesso fallimento silenzioso si manifesta per i risultati di testo dentro gli array. HotXLS salva il testo in cache di una cella formula, e di ogni cella follower in un intervallo CSE o array dinamico, come t="str" con la stringa letterale in <v>. Scrivere quelle cache come shared strings con t="s" fa mostrare a Excel #VALUE! nelle celle array quando legge il file in cache

<!-- Corretto: cache di testo letterale su una cella follower di array -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Sbagliato: indice shared-string, Excel mostra #VALUE! dalla cache -->
<c r="B3" t="s"><v>17</v></c>

Questa è facile sbagliarla perché t="s" è la scelta giusta per le celle di valore ordinarie, e la maggior parte degli scrittori instrada ogni stringa attraverso la tabella shared string per predefinito. La trappola è nella verifica. Ricalcolate in Excel e le celle array mostrano il testo corretto, il che prova che la formula è a posto e non prova nulla sulla cache che avete scritto. Se i vostri utenti aprono report con calcolo manuale, o uno strumento a valle legge i valori in cache senza calcolare, la versione shared-string è semplicemente rotta

Sintassi di conservazione delle formule: uguale iniziale, virgole e _xlws

Una pulizia correlata atterra nello stesso scrittore: le formule che vanno in OOXML sono conservate nella grammatica del file, non nella grammatica che un utente ha battuto. HotXLS conserva qualunque testo abbiate assegnato a Formula per la visualizzazione round trip, ma la formula serializzata butta l'uguale iniziale =, usa virgole fra gli argomenti delle funzioni, e lascia in pace i separatori di riga delle costanti array e il testo tra virgolette. FILTER e SORT sono conservate con il prefisso _xlfn._xlws. che Excel si aspetta per quelle funzioni solo worksheet, mentre altre future functions come SEQUENCE prendono il semplice prefisso _xlfn. davanti al nome

// Battuta con separatori punto e virgola, conservata come
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // nessuna riga sopra 1000 dà #CALC!, salvata con Rich Data

L'ordine conta dentro lo scrittore. L'escaping XML gira dopo la normalizzazione della sintassi, mai prima, per lo stesso motivo per cui i package diagnostici hanno bisogno di un parser: una riscrittura dei separatori applicata a testo già escapato rovinerebbe entità come &gt; negli operatori di confronto

Come verificare un errore in cache senza ingannare voi stessi

L'unico test fidabile è aprire il file in Excel con il calcolo già impostato su manuale, leggere le celle e solo allora ricalcolare. Excel eredita la modalità di calcolo dal primo workbook aperto in una sessione, così il nostro harness di accettazione crea prima il suo workbook seme, lo commuta su manuale, poi apre il file sotto test. Le sonde che saltavano il passo del seme provavano solo che il file si apriva e calcolava correttamente. Quello che etichettavano come in cache era già stato ricalcolato

Quella distinzione è l'articolo intero in miniatura. Una cache di errore moderna è corretta quando Excel la mostra prima che qualunque calcolo giri, e ogni requisito sopra, dal GUID minuscolo alle celle follower di testo t="str", è stato trovato controllando quello stato invece di quello ricalcolato. Per il contesto su come HotXLS dimensiona e possiede gli intervalli spill in partenza, vedere formule spill di array dinamici in Delphi con HotXLS. Se il vostro sintomo è un prompt di riparazione invece di un errore sbagliato, le regole a livello di package in perché Excel ripara un XLSX valido sono il punto di partenza migliore

FAQ

HotXLS ha bisogno di qualche impostazione per salvare #SPILL! e #CALC! correttamente?

No. HotXLS rileva i valori #SPILL! e #CALC! al momento del salvataggio e scrive le parti Rich Data, la fileVersion, il calcId minimo e la feature microsoft.com:RD automaticamente

Perché l'XML della cella contiene ancora #VALUE!?

Il tipo di errore legacy SpreadsheetML non ha un letterale per #SPILL! o #CALC!, quindi #VALUE! è il ripiego compatibile all'indietro. L'Excel corrente legge l'errore vero attraverso il collegamento value-metadata vm

HotXLS abbasserà un CalcId che ho impostato io?

No. TXLSXWorkbook.CalcId viene alzato a 191029 nel file scritto solo quando è presente un errore moderno, e un valore più alto che avete configurato resta com'è

HotXLS legge e scrive XLSX, XLSM e XLS legacy da Delphi e C++Builder con il proprio motore di calcolo, array dinamici inclusi. Dettagli e download di prova sono sulla pagina del componente di fogli di calcolo HotXLS Delphi