Ustavite tiho preračunavanje formul pri shranjevanju XLS

HotXLS, izvorna knjižnica Excel za Delphi in C++Builder, klasičen delovni zvezek BIFF8 .xls shrani tako, da najprej pogleda predpomnilnik: TXLSWorksheet.WriteFormula od TXLSWorkbook.TryGetCachedFormulaValue zahteva vrednost, ki jo je Excel shranil ob vsaki formuli, in ovrednotenje pokliče le, kadar tega predpomnilnika ni ali je razveljavljen. Delovni zvezek, ki ste ga odprli in se ga niste dotaknili, shrani nazaj iste številke, sveži rezultati pa zahtevajo en izrecen klic Recalculate, namesto da bi bili skrita stranska posledica SaveAs

Hrošč, ki je to pogodbo potisnil na plano, je bil nerodno majhen. Datoteka korpusa nested-subtotals.xls vsebuje skupno vsoto v R2C4, katere predpomnjena vrednost je 37. Odprite jo s HotXLS, vprašajte TryGetCachedFormulaValue za to celico, dobite 37. Shranite jo, ne da bi spremenili eno samo celico, odprite shranjeno kopijo, vprašajte isto, dobite 67. Od API-ja ni bilo zahtevano, da bi kar koli izračunal, pa se je številka v datoteki premaknila natanko za 30 — in 30 je slučajno vsota obeh vmesnih vsot skupin, 10 in 20, ki ležita znotraj obsega, ki ga skupna vsota pokriva

Zakaj shranjevanje datoteke XLS spremeni vrednost formule?

Da je iz 37 nastalo 67, sta se morala poravnati dva neodvisna defekta, in popravilo samo enega bi drugega skrilo. Prvi je bil strukturni: klasični pisec je ob vsakem shranjevanju preračunal vsako formulo. Drugi je bilo preverjanje vrste, ki za formulo, naloženo z diska, ni moglo biti nikoli resnično, zato je ovrednotenje gnezdene celice SUBTOTAL štelo dvakrat. Datoteka korpusa je bila preprosto prvi vnos, pri katerem je preračun ob shranjevanju dal drugačen odgovor od Excela in je nekdo oba primerjal. Strukturni defekt je lahko opisati: pred različico 2.382.3 sta TXLSWorksheet.WriteFormula in njen sorodnik za deljene formule WriteFormulaWithTExp osem-bajtno polje FormulaValue vsakega zapisa Formula dobila s klicem TXLSWorkbook.GetFormulaValue, to pa je ovrednotenje. Predpomnilnik, ki ga je ParseFormula ob nalaganju skrbno dekodiral iz izvorne datoteke, na poti ven ni bil nikoli upoštevan. V praksi je bilo vsako shranjevanje celoten preračun z obhodom API-ja za preračun na ravni delovnega zvezka, zato tega ne bi ustavilo nič, kar bi lahko nastavili na delovnem zvezku. Vsako mesto, kjer se ovrednotenje HotXLS ni strinjalo z Excelom, pa naj je šlo za zakonito nepodprto funkcijo ali navaden hrošč, je ob shranjevanju postalo tiha sprememba podatkov

Drugi defekt je živel v povratnem klicu za gnezdene vmesne vsote, ki ga uporablja ovrednotenje. Excel vsako obliko SUBTOTAL definira tako, da prezre celice, katerih lastna formula je spet SUBTOTAL, zato kalkulator v lxCalc.pas med združevanjem oboroži FIgnoreSubtotalCells in delovni zvezek prek TXLSWorkbook.GetClassicIsSubtotalCell vpraša, ali je vsaka celica v obsegu taka. Ta povratni klic je besedilo formule pridobil kot Variant in ga preveril z VarType(f) = varOleStr. Besedilo se iz GetUnCompiledFormula vrne kot String v Delphiju, String, dodeljen Variantu, pa je varUString in nikoli varOleStr. Predikat je bil neresničen za vsako celico v vsaki naloženi datoteki, vmesne vsote skupin so se v skupno vsoto prištele še enkrat, in ob shranjevanju, ki je preračunalo vse, je iz 10 + 20 + 7 nastalo 67

// HotXLS 2.381 in starejše: Variant formule, zgrajen iz String,
// je varUString, zato ta primerjava ni nikoli uspela
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr sprejme varString, varOleStr in varUString,
// AGGREGATE pa je izločen iz objemajočih vmesnih vsot, kot dela 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(');

Različica 2.382.0 je prinesla popravek VarIsStr in povratni klic v isti funkciji še naučila, da so tudi celice AGGREGATE izločene iz objemajočih vmesnih vsot. Že to je trditev korpusa spravilo v zeleno, ker se je preračunanih 37 zdaj ujemalo z naloženimi 37. Knjižnice pa to ni naredilo poštene: shranjevanje je še vedno preračunavalo, test pa je bil zelen le zato, ker se je ovrednotenje slučajno strinjalo z Excelom pri tej konkretni datoteki. Pravila o tem, katere celice SUBTOTAL in AGGREGATE preskočita, vključno s skritimi vrsticami, pokriva članek o skritih vrsticah pri SUBTOTAL in AGGREGATE; tu je pomembno, da ovrednotenje ne sme imeti glasu pri datoteki, ki je niste zahtevali izračunati

Kaj Excel jamči glede predpomnjenih vrednosti ob shranjevanju?

Excel shranjevanje obravnava kot posnetek stanja in ne kot dogodek izračuna. Vrednost, zapisana v polje FormulaValue zapisa Formula ([MS-XLS] §2.4.127, postavitev v §2.5.133), je tisto, kar celica trenutno prikazuje, kar je v ročnem načinu izračuna lahko zastarelo tudi leta, in Excel jo kljub temu zvesto zapiše. Preračun je ločena operacija s svojim sprožilcem. HotXLS zdaj pri klasičnih shranjevanjih sledi istemu pravilu: WriteFormula in WriteFormulaWithTExp najprej pokličeta TryGetCachedFormulaValue, vzameta CacheInfo.Value, kadar je stanje xlfcsLoaded ali xlfcsCalculated, in padeta na GetFormulaValue le pri xlfcsMissing in xlfcsInvalidated. Bralno polovico te pogodbe, vključno s tem, kaj pomeni vsako stanje in zakaj se predpomnjeni praznik ali False še vedno šteje kot vrednost, opisuje branje predpomnjenih vrednosti formul Excel v Delphiju brez preračuna

Pot za nazaj je namenoma obdržana, ne odstranjena. Formula, ki ste jo v tej seji dodelili prek Cells[Row, Col].Formula, prispe brez predpomnilnika, formulo, ki ste jo zamenjali na naloženi celici, pa _SetCompiledFormula označi kot xlfcsInvalidated; obe se ob shranjevanju ovrednotita natanko kot prej, zato se ustvarjeni delovni zvezek v Excelu še vedno odpre s številkami. Ko tudi ovrednotenje ne more dati vrednosti, pisec odda ničeln tovor in nastavi fAlwaysCalc (grbit, bit 0 iz §2.4.127), da Excel celico ob odpiranju znova preračuna, namesto da bi zaupal nadomestni vrednosti

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // list, vrstica in stolpec po principu 1-indeksiranja: R2C4 na prvem listu
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // pri predpomnjenih celicah ni vključeno nobeno ovrednotenje
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 pri nested-subtotals.xls
    // Shranjevanje, ki bi preračunalo, bi tu zapisalo 67
  finally
    Book.Free;
  end;
end;

Kje hrani koren deljene formule BIFF svojo predpomnjeno vrednost?

V svojem zapisu Formula, kot vsaka druga celica s formulo, in prav zato je bila korenska celica skupine edino mesto, kjer je shranjevanje s predpomnilnikom najprej še vedno izgubilo vrednost. Deljena formula v BIFF8 je shranjena kot zapis ShrFmla ([MS-XLS] §2.4.260), ki sledi zapisu Formula celice zgoraj levo, in vsaka članica skupine, vključno s korenom, nosi rgce, sestavljen iz enega samega žetona PtgExp (§2.5.198): prvi bajt razčlenjenega izraza je $01, sledita pa vrstica in stolpec korenske celice. Sledilne celice so samozadostne — HotXLS prebere FormulaValue vsake od njih in izraz razreši tako, da poišče prevedeno formulo korena. Korenska celica je drugačna, ker v trenutku, ko se razčlenjuje njen zapis Formula, izraz še ne obstaja; prispe en zapis pozneje

V tej vrzeli enega zapisa je predpomnilnik izginil. TXLSReader.ParseFormula dekodira predpomnjeno vrednost in ob žetonu PtgExp, katerega koordinate se ujemajo s koordinatami celice, to celico zapomni v FSharedFormulaRow in FSharedFormulaCol ter predpomnilnik objavi celici. Ko prispe zapis ShrFmla ($04BC), ParseSharedFormula izraz prevede in ga namesti z _SetCompiledFormula, _SetCompiledFormula pa naredi, kar mora narediti ob vsaki spremembi formule: počisti FCachedFormulaValue in stanje vrne na xlfcsMissing. Naloženih 37 korena so torej zavrgli, preden jih je kdor koli lahko prebral, TryGetCachedFormulaValue je koren prijavil kot brez predpomnilnika, pisec s predpomnilnikom najprej pa je ubogljivo padel na ovrednotenje prav pri celici, ki so jo vsi gledali. Zapis Array (§2.4.4) ima isto razporeditev in je imel isto luknjo

Popravek v različici 2.382.3 doda tretje polje, FSharedFormulaCachedValue, poleg čakajočih koordinat korena. ParseFormula tja odloži dekodirani predpomnilnik, ko prepozna koren, ParseSharedFormula in ParseArrayFormula pa ga prek _SetCellCachedFormulaValue predvajata takoj za namestitvijo prevedenega izraza in nato shrambo ponastavita na Unassigned. Različica String tega predpomnilnika ostaja nedotaknjena, ker njen tovor prispe v ločenem zapisu String in se usmerja po koordinatah celice in ne po vrstnem redu zapisov. Če delate z isto idejo na strani OOXML, članek o razširitvi si pri deljenih formulah XLSX razloži, zakaj format paketa nima enakega problema z razporeditvijo, ima pa svoje pasti pri razširjanju

Zakaj sledilne celice deljene formule potrebujejo relativni premik?

Ker je izraz, shranjen v ShrFmla, zapisan relativno na korensko celico, sledilna celica, ki ga uporabi dobesedno, pa ovrednoti sklice korena in ne svojih. Stari bralnik je na vsako sledilno celico namestil Value.GetCopy(), globoko kopijo brez premika, zato je skupina s korenom v B1 in =A1*3 vsaki sledilni celici dala prav tako =A1*3. Shranjevanje s predpomnilnikom najprej je to pri naloženih datotekah pravzaprav prikrilo, saj so sledilne celice imele svoj FormulaValue in izraza za pravilno shranjevanje sploh niso potrebovale; pokazalo se je v trenutku, ko je kar koli preračunalo. Bralnik zdaj namesti TXLSCompiledFormula.GetCopy(row - srow, col - scol), ki prehodi drevo sintakse in vsak relativni sklic premakne za razdaljo sledilne celice od korena, zato ima sledilna celica v B2 resničen =A2*3

Regresijski test, ki pripne obe vedenji, si velja prebrati, ker ne dovoli, da bi slučajnost šla skozi. Zgradi delovni zvezek z =A1*3 in =A2*3 nad vhodoma 2 in 4, nato pa prek _SetCellCachedFormulaValue vstavi namerno napačna predpomnilnika 999 in 888, enkrat z vklopljenim UseSharedFormulas in enkrat brez. Po shranjevanju in ponovnem nalaganju morata obe celici še vedno poročati 999 in 888 — dokaz, da se shranjevanje ni dotaknilo ne predpomnilnika korena ne sledilne celice. Šele po izrecnem Recalculate morata postati 6 in 12 — dokaz, da je premaknjeni izraz sledilne celice pravilen. Test, ki bi vsejal prave vrednosti, bi šel skozi tudi pri starem piscu, in prav zato so vsejane napačne

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

    // Naloženi predpomniki odvisnih formul se ob urejanju literala NE
    // razveljavijo, zato bi navaden SaveAs obdržal stare številke.
    // Preračun zahtevajte, ko res ž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;

Česa pogodba predpomnilnik najprej ne naredi za vas

Shranjevanje s predpomnilnikom najprej ohrani, kar je bilo naloženo; ne spremlja pa, ali je naloženo še vedno resnično. Sprememba literala, od katerega je formula odvisna, za ovrednotenje označi graf odvisnosti kot umazan, a predpomnilnik xlfcsLoaded odvisne celice pusti na mestu, klasični pisec pa bo to zastarelo vrednost z veseljem zapisal, razen če pokličete Recalculate ali najprej preberete Value celice, kar jo izračuna in stanje premakne na xlfcsCalculated. To je ista izmenjava, kot jo Excel naredi v ročnem načinu izračuna, in prava za cevovod, ki odpira datoteke tretjih oseb, popravi nekaj oznak in shrani — pomeni pa, da mora delovni zvezek, ki ureja vhode, svoj korak preračuna prevzeti izrecno. Politika RecalcBeforeSave pisca XLSX s tem delom ostaja nespremenjena in ima svoj ročni način, ki predpomnilnike ohranja v istem duhu. Iz tega sledita dve manjši meji: pot predpomnilnik najprej pomaga le celicam, katerih stanje je xlfcsLoaded ali xlfcsCalculated; generator, ki piše formule in jih nikoli ne ovrednoti, ob shranjevanju še vedno plača eno ovrednotenje na celico, natanko kot prej. In popravek gnezdenih vmesnih vsot popravi, katere celice ovrednotenje preskoči, ne pa vsake funkcije, ki jo ovrednotenje izvaja — datoteko, katere formul HotXLS ne izračuna enako kot Excel, je zdaj varno peljati skozi obhod nedotaknjeno, bo pa namerni Recalculate na tej datoteki še vedno dal odgovor knjižnice in ne Excelov, zato oba primerjajte, preden zaupate preračunanemu shranjevanju

Klasična shranjevanja s predpomnilnikom najprej, obnovljeni predpomniki korenov deljenih in matričnih formul, premik relativnih sklicev za sledilne celice deljenih formul ter popravljena pravila gnezdenja SUBTOTAL in AGGREGATE — vse to izhaja v standardni komponenti HotXLS Delphi Spreadsheet Component za Delphi in C++Builder, brez odvisnosti od Excela ali katerega koli strežnika avtomatizacije OLE; stran izdelka nosi celotno referenco API-ja za delovni zvezek, bralnik predpomnilnika in vstopne točke preračuna, uporabljene tukaj