XLS spremanje bez tihog preračunavanja formula u Delphiju

HotXLS, izvorna Delphi i C++Builder Excel biblioteka, sprema klasičnu BIFF8 .xls radnu knjigu prvo iz cachea: TXLSWorksheet.WriteFormula pita TXLSWorkbook.TryGetCachedFormulaValue za vrijednost koju je Excel pohranio uz svaku formulu i poziva evaluator samo kada taj cache nedostaje ili je invalidiran. Radna knjiga koju ste otvorili i nikad je niste dirali sprema iste brojeve natrag, a svježi rezultati traže jedan izričit poziv Recalculate, umjesto da budu skrivena nuspojava SaveAs-a

Bug koji je iznio taj ugovor na vidjelo bio je neugodno malen. Corpus datoteka imenom nested-subtotals.xls drži glavni zbroj u R2C4 čija je keširana vrijednost 37. Otvorite je HotXLS-om, pitajte TryGetCachedFormulaValue za tu ćeliju, dobijete 37. Spremite je bez promjene ijedne ćelije, otvorite spremljenu kopiju, postavite isto pitanje, dobijete 67. Ništa u API-ju nije bilo zamoljeno da bilo što izračuna, a broj u datoteci pomaknuo se točno za 30 — a 30 je slučajno zbroj dvaju podzbrojeva grupa, 10 i 20, koji sjede unutar raspona koji glavni zbroj pokriva

Zašto spremanje XLS datoteke promijeni vrijednost formule?

Dva neovisna defekta morala su se poklopiti da 37 postane 67, i popravak samo jednoga sakrio bi drugi. Prvi je bio strukturni: klasični pisac preračunavao je svaku formulu pri svakom spremanju. Drugi je bila provjera tipa koja nikad nije mogla biti istinita za formulu učitanu s diska, što je natjeralo evaluator da ugniježđene SUBTOTAL ćelije broji dvaput. Corpus datoteka bila je jednostavno prvi ulaz na kojem je preračunavanje pri spremanju dalo drugačiji odgovor od Excela i netko je te dvije usporedio. Strukturni defekt lako je opisati: prije v2.382.3 TXLSWorksheet.WriteFormula i njegov srodnik za dijeljene formule WriteFormulaWithTExp dobivali su osam bajtova polja FormulaValue svakog Formula zapisa pozivom TXLSWorkbook.GetFormulaValue, a to je evaluator. Cache koji je ParseFormula pažljivo dekodirao iz izvorne datoteke pri učitavanju nikad nije konzultiran na izlazu. Učinkovito, svako je spremanje bilo puno preračunavanje uz zaobiđen recalc API na razini radne knjige, pa ništa što biste postavili na radnoj knjizi ne bi ga zaustavilo. Svako mjesto gdje se HotXLS evaluator nije slagao s Excelom, bila to legitimno nepodržana funkcija ili običan bug, postalo je tiha promjena podataka pri spremanju

Drugi defekt živio je u callbacku za ugniježđene podzbrojeve koji evaluator koristi. Excel definira svaki oblik SUBTOTAL tako da ignorira ćelije čija je vlastita formula drugi SUBTOTAL, pa kalkulator u lxCalc.pas aktivira FIgnoreSubtotalCells tijekom agregacije i pita radnu knjigu, kroz TXLSWorkbook.GetClassicIsSubtotalCell, je li svaka ćelija u rasponu takva. Taj callback dohvaćao je tekst formule kao Variant i testirao ga s VarType(f) = varOleStr. Tekst se vraća iz GetUnCompiledFormula kao Delphi String, a String dodijeljen Variantu je varUString, nikad varOleStr. Predikat je bio neistinit za svaku ćeliju u svakoj učitanoj datoteci, podzbrojevi grupa uvaljani su u glavni zbroj drugi put, i pri spremanju koje je preračunalo sve, 10 + 20 + 7 postalo je 67

// HotXLS 2.381 i starije: Variant formule izgrađen iz Stringa
// je varUString, pa ova usporedba nikad nije uspjela
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr prihvaća varString, varOleStr i varUString,
// a AGGREGATE je isključen iz okolnih podzbrojeva kao u Excelu
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čio je popravak s VarIsStr i, dok je bio u istoj funkciji, naučio callback da su i AGGREGATE ćelije isključene iz okolnih podzbrojeva. Samo je to učinilo da corpus tvrdnja prođe, jer se preračunati 37 sada poklopio s učitanim 37. To nije učinilo biblioteku iskrenom: spremanje je i dalje preračunavalo, a test je bio zelen samo zato što se evaluator slučajno slagao s Excelom na toj konkretnoj datoteci. Pravila o tome koje ćelije SUBTOTAL i AGGREGATE preskaču, uključujući skrivene retke, obrađena su u članku o skrivenim recima u SUBTOTAL-u i AGGREGATE-u; ovdje je važno da nijedan evaluator ne smije imati pravo glasa o datoteci koju niste tražili da izračuna

Što Excel garantira za keširane vrijednosti pri spremanju?

Excel spremanje tretira kao snimku stanja, a ne kao događaj izračuna. Vrijednost upisana u polje FormulaValue Formula zapisa ([MS-XLS] §2.4.127, izgled u §2.5.133) je ono što ćelija trenutno prikazuje, što u ručnom načinu izračuna može biti staro godinama, i Excel je i dalje vjerno zapisuje. Preračunavanje je zasebna operacija s vlastitim okidačem. HotXLS sada slijedi isto pravilo za klasična spremanja: WriteFormula i WriteFormulaWithTExp prvo pozivaju TryGetCachedFormulaValue, uzimaju CacheInfo.Value kada je stanje xlfcsLoaded ili xlfcsCalculated, i padaju na GetFormulaValue samo za xlfcsMissing i xlfcsInvalidated. Strana čitanja tog ugovora, uključujući što svako stanje znači i zašto se keširana prazna vrijednost ili False i dalje računaju kao vrijednost, opisana je u čitanju keširanih vrijednosti formula iz Excela u Delphiju bez preračunavanja

Putanja pada namjerno je zadržana, a ne uklonjena. Formula koju ste dodijelili u ovoj sesiji kroz Cells[Row, Col].Formula dolazi bez cachea, a formula koju ste zamijenili na učitanoj ćeliji označena je kao xlfcsInvalidated od _SetCompiledFormula; obje se evaluiraju pri spremanju točno kao prije, pa se generirana radna knjiga i dalje otvara u Excelu s brojevima. Kada čak ni evaluator ne može proizvesti vrijednost, pisac emitira nulti payload i postavlja fAlwaysCalc (grbit bit 0 iz §2.4.127) da Excel preračuna ćeliju pri otvaranju, umjesto da vjeruje placeholderu

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // List, redak i stupac 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
    // Spremanje koje bi preračunavalo upisalo bi ovdje 67
  finally
    Book.Free;
  end;
end;

Gdje BIFF dijeljena formula korijena drži keširanu vrijednost?

U vlastitom Formula zapisu, kao i svaka druga ćelija s formulom, i upravo je to činilo korijensku ćeliju dijeljene grupe jedinim mjestom gdje je cache-first spremanje još gubilo. Dijeljena formula u BIFF8 pohranjena je kao ShrFmla zapis ([MS-XLS] §2.4.260) koji slijedi Formula zapis gornje lijeve ćelije, a svaka članica grupe, uključujući korijen, nosi rgce koji se sastoji od jednog PtgExp tokena (§2.5.198): prvi bajt parsiranog izraza je $01, nakon čega slijede redak i stupac korijenske ćelije. Sljedbene ćelije samostalne su — HotXLS čita FormulaValue svake od njih i razrješava izraz traženjem kompilirane formule korijena. Korijenska ćelija je drukčija, jer u trenutku kada se njen Formula zapis parsira izraz još ne postoji; stiže jedan zapis kasnije

Taj razmak od jednog zapisa mjesto je gdje je cache otišao. TXLSReader.ParseFormula dekodira keširanu vrijednost i, videći PtgExp čije se koordinate poklapaju s koordinatama same ćelije, pamti ćeliju u FSharedFormulaRow i FSharedFormulaCol i objavljuje cache ćeliji. Kada stigne ShrFmla zapis ($04BC), ParseSharedFormula kompilira izraz i instalira ga s _SetCompiledFormula, a _SetCompiledFormula radi ono što mora raditi za svaku promjenu formule: briše FCachedFormulaValue i vraća stanje na xlfcsMissing. Učitani 37 korijena stoga je odbačen prije nego ga je itko mogao pročitati, TryGetCachedFormulaValue prijavio je korijen kao nekeširan, a cache-first pisac je poslušno pao na evaluator upravo za ćeliju koju su svi gledali. Array zapis (§2.4.4) dijeli isti redoslijed i imao je istu rupu

Popravak u v2.382.3 dodaje treće polje, FSharedFormulaCachedValue, uz koordinate korijena koje čekaju. ParseFormula tamo sprema dekodirani cache kada prepozna korijen, a i ParseSharedFormula i ParseArrayFormula reproduciraju ga kroz _SetCellCachedFormulaValue odmah nakon instaliranja kompiliranog izraza, zatim vraćaju stash na Unassigned. String varijanta cachea nije pogođena ničim od toga jer njen payload stiže u zasebnom String zapisu i usmjerava se po koordinatama ćelije, a ne po redoslijedu zapisa. Ako radite s OOXML stranom istog koncepta, članak o si ekspanziji dijeljenih formula u XLSX-u objašnjava zašto format paketa nema ekvivalentan problem s redoslijedom, ali ima vlastite zamke pri ekspanziji

Zašto sljedbene ćelije dijeljenih formula trebaju relativni pomak?

Zato što je izraz pohranjen u ShrFmla napisan relativno prema korijenskoj ćeliji, a sljedbenica koja ga ponovno koristi doslovno evaluira reference korijena umjesto svojih. Stari reader instalirao je Value.GetCopy() na svaku sljedbenicu, duboku kopiju bez pomaka, pa je grupa s korijenom u B1 i =A1*3 svakoj sljedbenici dala također =A1*3. Cache-first spremanje zapravo je maskiralo to za učitane datoteke, jer su sljedbenice imale vlastiti FormulaValue i nikad im nije trebao izraz da bi se ispravno spremile; isplivalo je u trenutku kada je bilo što preračunalo. Reader sada instalira TXLSCompiledFormula.GetCopy(row - srow, col - scol), koji prolazi sintaksnim stablom i pomiče svaku relativnu referencu za udaljenost sljedbenice od korijena, pa sljedbenica u B2 posjeduje pravi =A2*3

Regresijski test koji prikiva oba ponašanja vrijedi pročitati jer ne dopušta da slučajnost prođe. Gradi radnu knjigu s =A1*3 i =A2*3 nad ulazima 2 i 4, zatim ubacuje namjerno pogrešne cachee 999 i 888 kroz _SetCellCachedFormulaValue, jednom s uključenim UseSharedFormulas i jednom isključenim. Nakon spremanja i ponovnog učitavanja, obje ćelije moraju i dalje prijavljivati 999 i 888 — dokaz da spremanje nije dotaklo ni cache korijena ni onaj sljedbenice. Tek nakon izričitog Recalculate moraju postati 6 i 12, dokaz da je pomaknuti izraz sljedbenice ispravan. Test koji bi posijao prave vrijednosti prošao bi i pod starim piscem, što je i cijela poanta sijanja 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;   // promijeni ulaz

    // Učitani keševi zavisnih formula NISU invalidirani doslovnim
    // uređivanjem, pa bi običan SaveAs zadržao stare brojeve.
    // Zatražite preračunavanje kada doista želite svjež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;

Što cache-first ugovor ne radi za vas

Cache-first spremanje čuva ono što je učitano; ne prati je li ono što je učitano još istinito. Promjena literala o kojem formula ovisi označava graf ovisnosti prljavim za evaluator, ali ostavlja xlfcsLoaded cache zavisne ćelije na mjestu, i klasični pisac će s veseljem zapisati tu zastarjelu vrijednost osim ako ne pozovete Recalculate ili prvo ne pročitate Value ćelije, što je izračuna i pomakne stanje na xlfcsCalculated. To je ista razmjena koju Excel pravi u ručnom načinu izračuna, i prava je za pipeline koji otvara datoteke trećih strana, uređuje nekoliko oznaka i sprema — ali znači da radna knjiga koja uređuje ulaze mora sama izričito odraditi svoj korak preračunavanja. Politika RecalcBeforeSave XLSX pisca nije promijenjena ovim radom i ima vlastiti ručni način koji čuva keševe u istom duhu. Dvije manje granice slijede iz toga: cache-first putanja pomaže samo ćelijama čije je stanje xlfcsLoaded ili xlfcsCalculated; generator koji piše formule i nikad ih ne evaluira i dalje plaća jedno vrednovanje po ćeliji pri spremanju, točno kao i prije. A popravak ugniježđenih podzbrojeva ispravlja koje ćelije evaluator preskače, a ne svaku funkciju koju evaluator implementira — datoteka čije formule HotXLS ne može izračunati identično Excelu sada je sigurna za round trip nedirnuta, ali izričit Recalculate na toj datoteci i dalje će dati odgovor biblioteke, a ne Excelov, i te dvije vrijednosti treba usporediti prije nego povjerujete preračunanom spremanju

Cache-first klasična spremanja, vraćeni keševi korijena dijeljenih i array formula, pomak relativnih referenci za sljedbenice dijeljenih formula i ispravljena pravila ugnježđivanja SUBTOTAL i AGGREGATE isporučuju se u standardnoj HotXLS Delphi Spreadsheet Component za Delphi i C++Builder, bez ovisnosti o Excelu ili bilo kojem OLE automation serveru; stranica proizvoda nosi punu API referencu za radnu knjigu, čitač cachea i ulazne točke preračunavanja korištene ovdje