Salvare XLS fără recalculare tacită în Delphi

HotXLS, librăria Excel nativă pentru Delphi și C++Builder, salvează un registru BIFF8 clasic .xls cu prioritate pe cache: TXLSWorksheet.WriteFormula cere lui TXLSWorkbook.TryGetCachedFormulaValue valoarea pe care Excel a stocat-o lângă fiecare formulă și apelează evaluatorul doar când acel cache lipsește sau este invalidat. Un registru pe care l-ați deschis și nu l-ați atins salvează înapoi aceleași numere, iar rezultatele proaspete cer un apel explicit de Recalculate, în loc să fie un efect secundar ascuns al lui SaveAs

Bug-ul care a scos acest contract la iveală era jenant de mic. Un fișier din corpus numit nested-subtotals.xls conține un total general în R2C4 a cărui valoare din cache este 37. Deschideți-l cu HotXLS, cereți lui TryGetCachedFormulaValue celula, primiți 37. Salvați-l fără să schimbați vreo celulă, deschideți copia salvată, puneți aceeași întrebare, primiți 67. Nimănui din API nu i s-a cerut să calculeze nimic, și totuși un număr din fișier se mutase exact cu 30 — iar 30 se întâmplă să fie suma celor două subtotaluri de grup, 10 și 20, care stau în intervalul acoperit de totalul general

De ce schimbă salvarea unui fișier XLS o valoare de formulă?

Două defecte independente au trebuit să se alinieze ca acel 37 să devină 67, iar repararea doar a unuia ar fi ascuns-o pe cealaltă. Primul era structural: scriitorul clasic recalcula fiecare formulă la fiecare salvare. Al doilea era o verificare de tip care nu putea fi niciodată adevărată pentru o formulă încărcată de pe disc, ceea ce făcea evaluatorul să numere de două ori celulele SUBTOTAL imbricate. Fișierul din corpus a fost pur și simplu prima intrare în care o recalculare la salvare producea un răspuns diferit de Excel și cineva le-a comparat. Defectul structural este ușor de enunțat: înainte de v2.382.3, TXLSWorksheet.WriteFormula și fratele lui pentru formule partajate, WriteFormulaWithTExp, obțineau câmpul de opt octeți FormulaValue al fiecărei înregistrări Formula apelând TXLSWorkbook.GetFormulaValue, care este evaluatorul. Cache-ul pe care ParseFormula îl decodase cu grijă din fișierul sursă la încărcare nu era consultat niciodată la ieșire. În efect, fiecare salvare era o recalculare completă cu API-ul de recalculare la nivel de registru ocolit, așa că nimic din ce ați fi putut seta pe registru nu ar fi oprit-o. Orice loc în care evaluatorul HotXLS nu era de acord cu Excel, fie o funcție neacceptată în mod legitim, fie un simplu bug, devenea o schimbare tacită de date la salvare

Al doilea defect trăia în callback-ul de subtotal imbricat pe care îl folosește evaluatorul. Excel definește fiecare formă de SUBTOTAL ca ignorând celulele a căror formulă este ea însăși un alt SUBTOTAL, așa că calculatorul din lxCalc.pas activează FIgnoreSubtotalCells în timpul agregării și întreabă registrul, prin TXLSWorkbook.GetClassicIsSubtotalCell, dacă fiecare celulă din interval este una. Acel callback lua textul formulei ca Variant și îl testa cu VarType(f) = varOleStr. Textul vine de la GetUnCompiledFormula ca String Delphi, iar un String atribuit unui Variant este varUString, niciodată varOleStr. Predicatul era fals pentru fiecare celulă din fiecare fișier încărcat, subtotalurile de grup erau însumate în totalul general a doua oară, iar la o salvare care recalcula tot, 10 + 20 + 7 devenea 67

// HotXLS 2.381 și mai vechi: un Variant de formulă construit dintr-un String
// este varUString, așa că această comparație nu reușea niciodată
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr acceptă varString, varOleStr și varUString,
// iar AGGREGATE este exclus din subtotalurile care îl cuprind, ca în Excel
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

v2.382.0 a livrat reparația VarIsStr și, aflându-se în aceeași funcție, a învățat callback-ul că nici celulele AGGREGATE nu sunt incluse în subtotalurile care le cuprind. Doar asta a făcut aserțiunea din corpus să treacă, pentru că 37 recalculat se potrivea acum cu 37 încărcat. Nu a făcut librăria onestă: salvarea tot recalcula, iar testul era verde doar pentru că evaluatorul se nimerea să fie de acord cu Excel pe acel fișier anume. Regulile pentru ce celule sar SUBTOTAL și AGGREGATE, inclusiv rândurile ascunse, sunt acoperite în articolul despre rândurile ascunse la SUBTOTAL și AGGREGATE; ce contează aici este că niciun evaluator nu ar trebui să aibă vot asupra unui fișier pe care nu i l-ați cerut să îl calculeze

Ce garantează Excel despre valorile din cache la salvare?

Excel tratează salvarea ca pe o fotografie, nu ca pe un eveniment de calcul. Valoarea scrisă în câmpul FormulaValue al unei înregistrări Formula ([MS-XLS] §2.4.127, structura în §2.5.133) este ce afișează celula în acel moment, care în modul de calcul manual poate fi veche de ani, iar Excel o scrie fidel și atunci. Recalcularea este o operație separată, cu propriul declanșator. HotXLS respectă acum aceeași regulă pentru salvările clasice: WriteFormula și WriteFormulaWithTExp apelează mai întâi TryGetCachedFormulaValue, iau CacheInfo.Value când starea este xlfcsLoaded sau xlfcsCalculated și cad pe GetFormulaValue doar pentru xlfcsMissing și xlfcsInvalidated. Jumătatea de citire a acestui contract, inclusiv ce înseamnă fiecare stare și de ce un blanc sau un False din cache contează totuși ca valoare, este descrisă în Citirea valorilor de formulă din cache Excel în Delphi fără recalculare

Calea de rezervă este păstrată deliberat, nu eliminată. O formulă pe care ați atribuit-o în această sesiune prin Cells[Row, Col].Formula vine fără cache, iar o formulă pe care ați înlocuit-o pe o celulă încărcată este marcată xlfcsInvalidated de _SetCompiledFormula; ambele se evaluează la salvare exact ca înainte, așa că un registru generat se deschide în continuare în Excel cu numere în el. Când nici evaluatorul nu poate produce o valoare, scriitorul emite un payload zero și setează fAlwaysCalc (grbit bit 0 din §2.4.127), ca Excel să recalculeze celula la deschidere în loc să aibă încredere în substituent

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // foaie, rând și coloană indexate de la 1: R2C4 pe prima foaie
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // niciun evaluator implicat pentru celulele cu cache
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 pentru nested-subtotals.xls
    // O salvare care recalcula ar fi scris 67 aici
  finally
    Book.Free;
  end;
end;

Unde își ține valoarea din cache rădăcina unei formule partajate BIFF?

În propria înregistrare Formula, ca orice altă celulă cu formulă, și exact asta a făcut din celula rădăcină a unui grup partajat singurul loc în care salvarea cu prioritate pe cache pierdea încă ceva. O formulă partajată în BIFF8 este stocată ca o înregistrare ShrFmla ([MS-XLS] §2.4.260) care urmează înregistrării Formula a celulei din colțul stânga-sus, iar fiecare celulă membră, inclusiv rădăcina, poartă un rgce format dintr-un singur token PtgExp (§2.5.198): primul octet al expresiei parsate este $01, urmat de rândul și coloana celulei rădăcină. Celulele urmăritoare sunt autonome — HotXLS citește FormulaValue al fiecăreia și rezolvă expresia căutând formula compilată a rădăcinii. Celula rădăcină este diferită, pentru că în momentul în care înregistrarea ei Formula este parsată expresia nu există încă; ea sosește o înregistrare mai târziu

Acest gol de o înregistrare este locul în care s-a dus cache-ul. TXLSReader.ParseFormula decodează valoarea din cache și, văzând un PtgExp ale cărui coordonate sunt egale cu ale celulei însăși, reține celula în FSharedFormulaRow și FSharedFormulaCol și publică cache-ul către celulă. Când sosește înregistrarea ShrFmla ($04BC), ParseSharedFormula compilează expresia și o instalează cu _SetCompiledFormula, iar _SetCompiledFormula face ce trebuie să facă pentru orice schimbare de formulă: curăță FCachedFormulaValue și resetează starea la xlfcsMissing. Astfel, 37-ul încărcat al rădăcinii era aruncat înainte ca cineva să îl poată citi, TryGetCachedFormulaValue raporta rădăcina ca fiind fără cache, iar scriitorul cu prioritate pe cache cădea ascultător pe evaluator exact pentru celula pe care o urmăreau toți. Înregistrarea Array (§2.4.4) are aceeași ordonare și avea aceeași gaură

Reparația din v2.382.3 adaugă un al treilea câmp, FSharedFormulaCachedValue, lângă coordonatele rădăcinii în așteptare. ParseFormula pune acolo cache-ul decodat când recunoaște o rădăcină, iar atât ParseSharedFormula, cât și ParseArrayFormula îl redau prin _SetCellCachedFormulaValue imediat după instalarea expresiei compilate, apoi resetează depozitul la Unassigned. Varianta String a cache-ului nu este afectată de toate acestea, pentru că payload-ul ei sosește într-o înregistrare String separată și este rutat după coordonatele celulei, nu după ordinea înregistrărilor. Dacă lucrați cu latura OOXML a aceluiași concept, articolul despre formulele partajate XLSX și expansiunea SI explică de ce formatul de pachet nu are o problemă echivalentă de ordonare, dar are propriile capcane de expansiune

De ce au nevoie urmăritoarele formulelor partajate de o deplasare relativă?

Pentru că expresia stocată în ShrFmla este scrisă relativ la celula rădăcină, iar un urmăritor care o refolosește identic evaluează referințele rădăcinii în loc de ale lui. Vechiul reader instala pe fiecare urmăritor Value.GetCopy(), o copie profundă fără deplasare, așa că un grup cu rădăcina în B1 și =A1*3 dădea fiecărui urmăritor tot =A1*3. Salvarea cu prioritate pe cache a mascat de fapt acest lucru pentru fișierele încărcate, pentru că urmăritorii aveau propriul FormulaValue și nu aveau nevoie de expresie ca să se salveze corect; a ieșit la iveală în momentul în care ceva a recalculat. Reader-ul instalează acum TXLSCompiledFormula.GetCopy(row - srow, col - scol), care parcurge arborele de sintaxă și deplasează fiecare referință relativă cu distanța urmăritorului față de rădăcină, așa că urmăritorul din B2 deține un =A2*3 autentic

Testul de regresie care fixează ambele comportamente merită citit pentru că refuză să lase o coincidență să treacă. Construiește un registru cu =A1*3 și =A2*3 peste intrările 2 și 4, apoi injectează cache-urile intenționat greșite 999 și 888 prin _SetCellCachedFormulaValue, o dată cu UseSharedFormulas activat și o dată oprit. După o salvare și o reîncărcare, ambele celule trebuie să raporteze în continuare 999 și 888 — dovadă că salvarea nu a atins nici cache-ul rădăcinii, nici pe cel al urmăritorului. Abia după un Recalculate explicit trebuie să devină 6 și 12, dovadă că expresia deplasată a urmăritorului este corectă. Un test care ar fi semănat valorile adevărate ar fi trecut și cu vechiul scriitor, exact de aceea se seamănă unele greșite

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // schimbă o intrare

    // Cache-urile încărcate ale formulelor dependente NU sunt invalidate de o
    // editare de literal, așa că un SaveAs simplu ar păstra numerele vechi.
    // Cere o recalculare atunci când chiar vrei rezultate proaspete:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

Ce nu face contractul de prioritate pe cache pentru voi

Salvarea cu prioritate pe cache păstrează ce s-a încărcat; nu urmărește dacă ce s-a încărcat mai este adevărat. Schimbarea unui literal de care depinde o formulă marchează graful de dependențe ca murdar pentru evaluator, dar lasă în loc cache-ul xlfcsLoaded al celulei dependente, iar scriitorul clasic va scrie bucuros acea valoare învechită dacă nu apelați Recalculate sau nu citiți mai întâi Value al celulei, ceea ce o calculează și mută starea la xlfcsCalculated. Acesta este același compromis pe care îl face Excel în modul de calcul manual și este cel corect pentru un pipeline care deschide fișiere terțe, editează câteva etichete și salvează — dar înseamnă că un registru care editează intrări trebuie să își asume explicit pasul de recalculare. Politica RecalcBeforeSave a scriitorului XLSX este neschimbată de această muncă și are propriul mod manual care păstrează cache-urile în același spirit. Două limite mai mici decurg de aici: calea cu prioritate pe cache ajută doar celulele a căror stare este xlfcsLoaded sau xlfcsCalculated; un generator care scrie formule și nu le evaluează niciodată plătește în continuare o evaluare per celulă la salvare, exact ca înainte. Iar reparația subtotalului imbricat corectează ce celule sare evaluatorul, nu fiecare funcție pe care o implementează evaluatorul — un fișier ale cărui formule HotXLS nu le poate calcula identic cu Excel este acum sigur de trecut prin round-trip neatins, dar un Recalculate deliberat pe acel fișier va produce în continuare răspunsul librăriei, nu al Excel, și ar trebui să le comparați pe cele două înainte de a avea încredere într-o salvare recalculată

Salvările clasice cu prioritate pe cache, cache-urile restaurate ale rădăcinilor de formule partajate și matriciale, deplasarea referințelor relative pentru urmăritorii partajați și regulile corectate de imbricare pentru SUBTOTAL și AGGREGATE sunt toate livrate în HotXLS Delphi Spreadsheet Component standard pentru Delphi și C++Builder, fără nicio dependență de Excel sau de vreun server de automatizare OLE; pagina produsului conține referința completă de API pentru registru, cititorul de cache și punctele de intrare de recalculare folosite aici