XLS: zaustavite tiho preračunavanje formula u Delphiju

HotXLS, nativna Delphi i C++Builder Excel biblioteka, čuva klasičnu BIFF8 .xls radnu svesku tako što prvo ide na keš: TXLSWorksheet.WriteFormula pita TXLSWorkbook.TryGetCachedFormulaValue za vrednost koju je Excel smestio uz svaku formulu i poziva evaluator samo kada taj keš nedostaje ili je invalidiran. Radna sveska koju ste otvorili i nikad je niste dirali upisuje iste brojeve nazad, a sveži rezultati traže jedan eksplicitni poziv Recalculate umesto da budu skriveni sporedni efekat SaveAs-a

Bug koji je iznudio ovaj ugovor na videlo bio je neprijatno mali. Corpus fajl po imenu nested-subtotals.xls drži grand total u R2C4 čija keširana vrednost je 37. Otvorite ga u HotXLS-u, pitajte TryGetCachedFormulaValue za tu ćeliju, dobijete 37. Sačuvajte ga bez izmene ijedne ćelije, otvorite sačuvanu kopiju, postavite isto pitanje, dobijete 67. Ništa u API-ju nije bilo pozvano da bilo šta izračuna, a broj u fajlu pomerio se tačno za 30 — a 30 je slučajno zbir dva subtotala grupa, 10 i 20, koji stoje unutar opsega koji grand total pokriva

Zašto čuvanje XLS fajla menja vrednost formule?

Dva nezavisna defekta morala su se poklopiti da 37 postane 67, i ispravka bilo kog od njih sama bi sakrila onaj drugi. Prvi je bio strukturni: klasični pisac preračunavao je svaku formulu pri svakom čuvanju. Drugi je bila provera tipa koja nikad nije mogla biti tačna za formulu učitanu sa diska, što je evaluator teralo da ćelije sa ugnježdenim SUBTOTAL broji dvaput. Corpus fajl je jednostavno bio prvi ulaz gde je preračunavanje pri čuvanju dalo odgovor drugačiji od Excel-a i gde je neko uporedio ta dva. Strukturni defekt je lako izreći: pre v2.382.3 TXLSWorksheet.WriteFormula i njegov rođak za shared formule WriteFormulaWithTExp dobijali su osmobajtno polje FormulaValue svakog Formula zapisa pozivom TXLSWorkbook.GetFormulaValue, a to je evaluator. Keš koji je ParseFormula pažljivo dekodirao iz izvornog fajla pri učitavanju nikad nije konsultovan na izlazu. Efektivno je svako čuvanje bilo potpuno preračunavanje sa zaobiđenim recalc API-jem na nivou radne sveske, pa ništa što biste postavili na radnoj svesci to ne bi zaustavilo. Svako mesto gde se HotXLS evaluator ne slaže sa Excel-om, bila to legitimno nepodržana funkcija ili običan bug, postalo je tiha izmena podataka pri čuvanju

Drugi defekt živeo je u callback-u za ugnježdene subtotale koji evaluator koristi. Excel definiše svaki oblik SUBTOTAL tako da ignoriše ćelije čija je sopstvena formula drugi SUBTOTAL, pa kalkulator u lxCalc.pas uključuje FIgnoreSubtotalCells tokom agregacije i pita radnu svesku, preko TXLSWorkbook.GetClassicIsSubtotalCell, da li je svaka ćelija u opsegu takva. Taj callback dohvatao je tekst formule kao Variant i testirao ga sa VarType(f) = varOleStr. Tekst se vraća iz GetUnCompiledFormula kao Delphi String, a String dodeljen u Variant je varUString, nikad varOleStr. Predikat je bio netačan za svaku ćeliju u svakom učitanom fajlu, subtotali grupa uračunati su u grand total drugi put, i pri čuvanju koje je preračunavalo sve, 10 + 20 + 7 postalo je 67

// HotXLS 2.381 i ranije: Variant formule izgrađen iz String-a
// je varUString, pa ovo poređenje nikad nije uspelo
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr prihvata varString, varOleStr i varUString,
// a AGGREGATE je izuzet iz okružujućih subtotala kao što Excel radi
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 isporučila je ispravku VarIsStr i, dok je bila u istoj funkciji, naučila callback da se i ćelije AGGREGATE izuzimaju iz okružujućih subtotala. To samo po sebi učinilo je corpus tvrdnju prolaznom, jer se preračunata 37 sada poklapala sa učitanom 37. Nije učinilo biblioteku iskrenom: čuvanje je i dalje preračunavalo, a test je bio zelen samo zato što se evaluator na tom konkretnom fajlu slučajno slagao sa Excel-om. Pravila o tome koje ćelije SUBTOTAL i AGGREGATE preskaču, uključujući skrivene redove, pokrivena su u članku o SUBTOTAL-u, AGGREGATE-u i skrivenim redovima; ovde je važno da nijedan evaluator ne treba da ima glas o fajlu koji niste tražili da izračuna

Šta Excel garantuje o keširanim vrednostima pri čuvanju?

Excel tretira čuvanje kao snimak stanja, ne kao događaj izračunavanja. Vrednost upisana u polje FormulaValue Formula zapisa ([MS-XLS] §2.4.127, raspored u §2.5.133) je ono što ćelija trenutno prikazuje, što u režimu ručnog izračunavanja može biti zastarelo godinama, i Excel je i tada verno upisuje. Preračunavanje je odvojena operacija sa sopstvenim okidačem. HotXLS sada sledi isto pravilo za klasična čuvanja: WriteFormula i WriteFormulaWithTExp prvo pozivaju TryGetCachedFormulaValue, uzimaju CacheInfo.Value kada je stanje xlfcsLoaded ili xlfcsCalculated, a na GetFormulaValue padaju samo za xlfcsMissing i xlfcsInvalidated. Čitalačka polovina tog ugovora, uključujući šta svako stanje znači i zašto se keširano prazno ili False i dalje računa kao vrednost, opisana je u tekstu čitanje keširanih vrednosti formula iz Excel-a u Delphiju bez preračunavanja

Putanja pada namerno je zadržana, ne uklonjena. Formula koju ste dodelili u ovoj sesiji preko Cells[Row, Col].Formula dolazi bez keša, a formula koju ste zamenili u učitanoj ćeliji označena je kao xlfcsInvalidated od strane _SetCompiledFormula; obe se evaluiraju pri čuvanju tačno kao pre, pa se generisana radna sveska i dalje otvara u Excel-u sa brojevima. Kada čak i evaluator ne može da proizvede vrednost, pisac emituje nulti payload i postavlja fAlwaysCalc (grbit bit 0 iz §2.4.127) tako da Excel preračuna ćeliju pri otvaranju umesto da veruje placeholderu

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // list, red i kolona sa indeksom od 1: R2C4 na prvom listu
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // evaluator nije uključen za keširane ćelije
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 za nested-subtotals.xls
    // Čuvanje koje preračunava upisalo bi ovde 67
  finally
    Book.Free;
  end;
end;

Gde koren BIFF shared formule čuva svoju keširanu vrednost?

U svom sopstvenom Formula zapisu, kao i svaka druga ćelija sa formulom, i upravo to je učinilo koren ćeliju shared grupe jedinim mestom gde je čuvanje iz keša i dalje gubilo. Shared formula u BIFF8 čuva se kao ShrFmla zapis ([MS-XLS] §2.4.260) koji sledi posle Formula zapisa gornje leve ćelije, i svaka ćelija članica, uključujući koren, nosi rgce koji se sastoji od jednog PtgExp tokena (§2.5.198): prvi bajt parsiranog izraza je $01, a zatim red i kolona korenske ćelije. Ćelije sledbenice su samo-sadržane — HotXLS čita FormulaValue svake od njih i razrešava izraz tražeći kompajliranu formulu korena. Korenska ćelija je drugačija, jer kada se njen Formula zapis parsira izraz još ne postoji; stiže jedan zapis kasnije

U toj praznini od jednog zapisa keš je i otišao. TXLSReader.ParseFormula dekodira keširanu vrednost i, kada vidi PtgExp čije se koordinate poklapaju sa koordinatama same ćelije, pamti ćeliju u FSharedFormulaRow i FSharedFormulaCol i objavljuje keš ćeliji. Kada ShrFmla zapis ($04BC) stigne, ParseSharedFormula kompajlira izraz i instalira ga preko _SetCompiledFormula, a _SetCompiledFormula radi ono što mora za svaku izmenu formule: briše FCachedFormulaValue i vraća stanje na xlfcsMissing. Učitana 37 korena zato je odbačena pre nego što je iko mogao da je pročita, TryGetCachedFormulaValue prijavio je koren kao nekeširan, a pisac koji ide na keš prvo poslušno je pao na evaluator za tačno onu ćeliju koju su svi gledali. Array zapis (§2.4.4) deli isti redosled i imao je istu rupu

Ispravka u v2.382.3 dodaje treće polje, FSharedFormulaCachedValue, pored koordinata korena koje čekaju. ParseFormula ostavlja dekodirani keš tamo kada prepozna koren, a i ParseSharedFormula i ParseArrayFormula reprodukuju ga preko _SetCellCachedFormulaValue odmah po instaliranju kompajliranog izraza, a zatim vraćaju ostavu na Unassigned. String varijanta keša nije pogođena svim ovim jer njen payload stiže u odvojenom String zapisu i usmerava se po koordinatama ćelije, a ne po redosledu zapisa. Ako radite sa OOXML stranom istog koncepta, članak o ekspanziji si u XLSX shared formulama objašnjava zašto format paketa nema ekvivalentan problem sa redosledom ali ima svoje zamke sa ekspanzijom

Zašto sledbenicima shared formule treba relativni pomak?

Zato što je izraz smešten u ShrFmla napisan relativno prema korenskoj ćeliji, a sledbenik koji ga iskoristi verbatim evaluira reference korena umesto svoje. Stari reader instalirao je Value.GetCopy() na svakog sledbenika, duboku kopiju bez pomaka, pa je grupa sa korenom u B1 i =A1*3 davala svakom sledbeniku takođe =A1*3. Čuvanje iz keša to je zapravo maskiralo za učitane fajlove, jer su sledbenici imali sopstveni FormulaValue i nikad im izraz nije trebao za ispravno čuvanje; isplivalo je u trenutku kada je bilo šta preračunalo. Reader sada instalira TXLSCompiledFormula.GetCopy(row - srow, col - scol), koji prolazi kroz sintaksno stablo i pomera svaku relativnu referencu za rastojanje sledbenika od korena, pa sledbenik u B2 poseduje pravu =A2*3

Regresioni test koji prikiva oba ponašanja vredi pročitati jer ne dopušta da slučajnost prođe. Gradi radnu svesku sa =A1*3 i =A2*3 nad ulazima 2 i 4, zatim ubacuje namerno pogrešne keševe 999 i 888 preko _SetCellCachedFormulaValue, jednom sa uključenim UseSharedFormulas i jednom bez. Posle čuvanja i ponovnog učitavanja, obe ćelije moraju i dalje da prijavljuju 999 i 888 — dokaz da čuvanje nije dotaklo ni keš korena ni keš sledbenika. Tek posle eksplicitnog Recalculate moraju postati 6 i 12, dokaz da je pomereni izraz sledbenika tačan. Test koji bi sejao prave vrednosti prošao bi i pod starim piscem, i to je cela poenta sejanja pogrešnih

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

    // Učitani keševi zavisnih formula NISU invalidirani
    // izmenom literala, pa bi običan SaveAs zadržao stare brojeve.
    // Zatražite preračunavanje kada zaista želite sveže rezultate:
    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;

Šta ugovor keš prvo ne radi za vas

Čuvanje iz keša čuva ono što je učitano; ono ne prati da li je ono što je učitano još tačno. Izmena literala od kojeg formula zavisi označava graf zavisnosti kao prljav za evaluator, ali ostavlja keš xlfcsLoaded zavisne ćelije na mestu, i klasični pisac će sa zadovoljstvom upisati tu zastarelu vrednost osim ako ne pozovete Recalculate ili prvo pročitate Value te ćelije, što je izračuna i prebaci stanje u xlfcsCalculated. To je ista trgovina koju Excel pravi u režimu ručnog izračunavanja, i ona je prava za pipeline koji otvara fajlove trećih strana, menja nekoliko labela i čuva — ali znači da radna sveska koja menja ulaze mora eksplicitno da poseduje svoj korak preračunavanja. Politika RecalcBeforeSave XLSX pisca nije izmenjena ovim radom i ima sopstveni ručni režim koji čuva keševe u istom duhu. Dve manje granice slede iz ovoga: putanja keš prvo pomaže samo ćelijama čije je stanje xlfcsLoaded ili xlfcsCalculated; generator koji piše formule i nikad ih ne evaluira i dalje plaća po jednu evaluaciju za svaku ćeliju pri čuvanju, tačno kao i pre. A ispravka ugnježdenih subtotala ispravlja koje ćelije evaluator preskače, ne svaku funkciju koju evaluator implementira — fajl čije formule HotXLS ne može da izračuna identično Excel-u sada je bezbedan za netaknut round trip, ali namerni Recalculate nad tim fajlom i dalje će dati odgovor biblioteke a ne Excel-a, i ta dva vredi uporediti pre nego što se pouzdate u preračunato čuvanje

Čuvanje klasičnih fajlova iz keša, vraćeni keševi korena shared i array formula, pomak relativnih referenci za shared sledbenike i ispravljena pravila ugnježđavanja SUBTOTAL i AGGREGATE sve se isporučuju u standardnom HotXLS Delphi Spreadsheet Component za Delphi i C++Builder, bez zavisnosti od Excel-a ili bilo kog OLE automation servera; stranica proizvoda nosi punu API referencu za ulazne tačke radne sveske, čitača keša i preračunavanja koje su ovde korišćene