Zastavte tiché prepočítavanie formúl pri XLS ukladaní

HotXLS, natívna Excel knižnica pre Delphi a C++Builder, ukladá klasický BIFF8 .xls workbook cache first: TXLSWorksheet.WriteFormula sa pýta TXLSWorkbook.TryGetCachedFormulaValue na hodnotu, ktorú Excel uložil vedľa každej formuly, a evaluator zavolá len vtedy, keď tá cache chýba alebo je invalidovaná. Workbook, ktorý ste otvorili a nikdy sa ho nedotkli, uloží späť tie isté čísla, a čerstvé výsledky vyžadujú jedno explicitné volanie Recalculate namiesto toho, aby boli skrytým vedľajším efektom SaveAs

Bug, ktorý vytiahol tento kontrakt na svetlo, bol trápne malý. Corpus súbor s menom nested-subtotals.xls drží grand total v R2C4, ktorého cached hodnota je 37. Otvorte ho v HotXLS, opýtajte sa TryGetCachedFormulaValue na tú bunku, dostanete 37. Uložte ho bez zmeny jedinej bunky, otvorte uloženú kópiu, opýtajte sa na to isté, dostanete 67. Nikto API nežiadal nič počítať, a predsa sa číslo v súbore posunulo presne o 30 — a 30 je náhodou súčet dvoch skupinových subtotalov, 10 a 20, ktoré sedia vnútri rozsahu, ktorý grand total pokrýva

Prečo uloženie XLS súboru zmení hodnotu formuly?

Aby sa z tej 37 stal 67, museli sa zísť dva samostatné defekty, a oprava len jedného z nich by skryla ten druhý. Prvý bol štrukturálny: klasický writer prepočítaval každú formulu pri každom uložení. Druhý bol test typu, ktorý nikdy nemohol platiť pre formulu načítanú z disku, a to spôsobilo, že evaluator počítal vnorené bunky SUBTOTAL dvakrát. Corpus súbor bol jednoducho prvý vstup, kde prepočet pri ukladaní dal inú odpoveď než Excel a niekto tie dve porovnal. Štrukturálny defekt sa dá povedať ľahko: pred v2.382.3 získavali TXLSWorksheet.WriteFormula a jeho shared-formula súrodenec WriteFormulaWithTExp osembajtové pole FormulaValue každého Formula recordu volaním TXLSWorkbook.GetFormulaValue, čo je evaluator. Cache, ktorú ParseFormula pri načítaní starostlivo dekódoval zo zdrojového súboru, sa na ceste von nikdy nekonzultovala. Každé uloženie tak bolo v podstate plný prepočet s obídeným workbook-level recalc API, takže nič, čo by ste na workbooku nastavili, by to nezastavilo. Každé miesto, kde sa evaluator HotXLS rozchádzal s Excelom — či už o legitímne nepodporovanú funkciu alebo o obyčajný bug — sa stalo tichou zmenou dát pri uložení

Druhý defekt žil v nested-subtotal callbacku, ktorý evaluator používa. Excel definuje každú formu SUBTOTAL tak, že ignoruje bunky, ktorých vlastná formula je iný SUBTOTAL, takže kalkulátor v lxCalc.pas počas agregácie vyzbrojí FIgnoreSubtotalCells a pýta sa workbooku cez TXLSWorkbook.GetClassicIsSubtotalCell, či je každá bunka v rozsahu taká. Ten callback vytiahol text formuly ako Variant a testoval ho cez VarType(f) = varOleStr. Text sa z GetUnCompiledFormula vracia ako Delphi String a String priradený do Variantu je varUString, nikdy varOleStr. Predikát bol nepravdivý pre každú bunku v každom načítanom súbore, skupinové subtotaly sa do grand totalu pripočítali druhýkrát a pri uložení, ktoré všetko prepočítalo, sa z 10 + 20 + 7 stalo 67

// HotXLS 2.381 a staršie: Variant formuly postavený zo String
// je varUString, takže toto porovnanie nikdy neuspelo
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr akceptuje varString, varOleStr aj varUString,
// a AGGREGATE je z nadradených subtotalov vylúčený tak ako v Exceli
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 priniesol opravu VarIsStr a popri tom v tej istej funkcii naučil callback, že bunky AGGREGATE sú z nadradených subtotalov tiež vylúčené. To samo o sebe spravilo corpus assertion zelenou, pretože prepočítaná 37 teraz sedela s načítanou 37. Knižnicu to však neurobilo čestnou: uloženie stále prepočítavalo a test bol zelený len preto, že evaluator náhodou s Excelom na tom konkrétnom súbore súhlasil. Pravidlá, ktoré bunky SUBTOTAL a AGGREGATE preskakujú, vrátane skrytých riadkov, pokrýva článok o SUBTOTAL a AGGREGATE so skrytými riadkami; tu ide o to, že žiadny evaluator by nemal mať hlas pri súbore, ktorý ste ho nežiadali počítať

Čo Excel garantuje o cached hodnotách pri uložení?

Excel berie uloženie ako snímku, nie ako výpočtovú udalosť. Hodnota zapísaná do poľa FormulaValue Formula recordu ([MS-XLS] §2.4.127, rozloženie v §2.5.133) je to, čo bunka práve zobrazuje, čo môže byť v manuálnom režime výpočtu roky staré, a Excel ju aj tak verne zapíše. Prepočet je samostatná operácia s vlastným spúšťačom. HotXLS teraz pre klasické uloženia dodržiava to isté pravidlo: WriteFormula a WriteFormulaWithTExp najprv zavolajú TryGetCachedFormulaValue, vezmú CacheInfo.Value, keď je stav xlfcsLoaded alebo xlfcsCalculated, a na GetFormulaValue padnú len pri xlfcsMissing a xlfcsInvalidated. Read-side polovicu tohto kontraktu, vrátane toho, čo ktorý stav znamená a prečo cached prázdna hodnota alebo False stále platia ako hodnota, opisuje čítanie Excel cached hodnôt formúl v Delphi bez prepočtu

Fallback cesta je zámerne ponechaná, nie odstránená. Formula, ktorú ste v tejto session priradili cez Cells[Row, Col].Formula, prichádza bez cache a formula, ktorú ste nahradili na načítanej bunke, je _SetCompiledFormula označená ako xlfcsInvalidated; obe sa pri ukladaní vyhodnotia presne ako predtým, takže generovaný workbook sa v Exceli stále otvorí s číslami. Keď hodnotu nedokáže vyprodukovať ani evaluator, writer emituje nulový payload a nastaví fAlwaysCalc (grbit bit 0 z §2.4.127), aby Excel bunku pri otvorení prepočítal namiesto toho, aby dôveroval placeholderu

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 1-based hárok, riadok a stĺpec: R2C4 na prvom hárku
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // cached bunky bez účasti evaluatora
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 pre nested-subtotals.xls
    // Uloženie, ktoré by prepočítavalo, by tu zapísalo 67
  finally
    Book.Free;
  end;
end;

Kde drží BIFF shared formula root svoju cached hodnotu?

Vo svojom vlastnom Formula recorde, ako každá iná formula bunky, a práve to spravilo z root bunky shared skupiny jediné miesto, kde cache-first ukladanie stále strácalo. Shared formula v BIFF8 je uložená ako ShrFmla record ([MS-XLS] §2.4.260), ktorý nasleduje za Formula recordom bunky vľavo hore, a každá členská bunka, vrátane rootu, nesie rgce pozostávajúce z jediného PtgExp tokenu (§2.5.198): prvý bajt naparsovaného výrazu je $01, za ním riadok a stĺpec root bunky. Follower bunky sú sebestačné — HotXLS prečíta FormulaValue každej z nich a výraz rozlíši vyhľadaním skompilovanej formuly rootu. Root bunka je iná, pretože v čase, keď sa parsuje jej Formula record, výraz ešte neexistuje; prichádza o record neskôr

Do tej jednorecordovej medzery sa cache stratila. TXLSReader.ParseFormula dekóduje cached hodnotu a keď vidí PtgExp, ktorého súradnice sa rovnajú súradniciam bunky samej, zapamätá si bunku v FSharedFormulaRow a FSharedFormulaCol a publikuje cache do bunky. Keď príde ShrFmla record ($04BC), ParseSharedFormula skompiluje výraz a nainštaluje ho cez _SetCompiledFormula, a _SetCompiledFormula spraví to, čo musí pri každej zmene formuly: vyčistí FCachedFormulaValue a vráti stav na xlfcsMissing. Načítaná 37 rootu sa tak zahodila skôr, než ju niekto stihol prečítať, TryGetCachedFormulaValue nahlásil root ako bez cache a cache-first writer poslušne padol na evaluator presne pri bunke, na ktorú sa všetci pozerali. Array record (§2.4.4) zdieľa to isté poradie a mal tú istú dieru

Oprava vo v2.382.3 pridáva tretie pole, FSharedFormulaCachedValue, vedľa čakajúcich súradníc rootu. ParseFormula doň odloží dekódovanú cache, keď rozpozná root, a ParseSharedFormula aj ParseArrayFormula ju prehrajú cez _SetCellCachedFormulaValue hneď po nainštalovaní skompilovaného výrazu a potom odkladací priestor vynulujú na Unassigned. String variant cache tým všetkým nie je dotknutý, pretože jeho payload prichádza v samostatnom String recorde a smeruje sa podľa súradníc bunky, nie podľa poradia recordov. Ak pracujete s OOXML stranou toho istého konceptu, článok o XLSX shared formula si expansion vysvetľuje, prečo package formát nemá ekvivalentný problém s poradím, ale má vlastné expanzné pasce

Prečo shared formula followery potrebujú relatívny posun?

Pretože výraz uložený v ShrFmla je zapísaný relatívne k root bunke a follower, ktorý ho použije doslovne, vyhodnocuje referencie rootu namiesto svojich. Starý reader inštaloval na každý follower Value.GetCopy(), teda hlbokú kópiu bez posunu, takže skupina s rootom v B1 s =A1*3 dala každému followeru tiež =A1*3. Cache-first ukladanie to pri načítaných súboroch vlastne maskovalo, keďže followery mali svoje vlastné FormulaValue a na správne uloženie výraz nikdy nepotrebovali; vynorilo sa v momente, keď niečo prepočítalo. Reader teraz inštaluje TXLSCompiledFormula.GetCopy(row - srow, col - scol), ktorý prejde syntax tree a posunie každú relatívnu referenciu o vzdialenosť followera od rootu, takže follower v B2 vlastní skutočné =A2*3

Regresný test, ktorý pripína obe správania, stojí za prečítanie, pretože nedovolí, aby prešla náhoda. Postaví workbook s =A1*3 a =A2*3 nad vstupmi 2 a 4 a potom cez _SetCellCachedFormulaValue vstrekne zámerne nesprávne cache 999 a 888, raz so zapnutým UseSharedFormulas a raz s vypnutým. Po uložení a znovunačítaní musia obe bunky stále hlásiť 999 a 888 — dôkaz, že uloženie sa nedotklo ani cache rootu, ani cache followera. Až po explicitnom Recalculate sa musia stať 6 a 12, dôkaz, že posunutý výraz followera je správny. Test, ktorý by seedoval správne hodnoty, by prešiel aj pod starým writerom, a práve v tom je celý zmysel seedovania nesprávnych

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

    // Načítané cache závislých formúl sa literálnou úpravou NEinvalidujú,
    // takže obyčajné SaveAs by podržalo staré čísla.
    // O prepočet si povedzte, keď naozaj chcete čerstvé výsledky:
    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;

Čo pre vás cache-first kontrakt nerobí

Cache-first ukladanie zachováva to, čo sa načítalo; nesleduje, či to, čo sa načítalo, je ešte pravda. Zmena literálu, od ktorého závisí formula, označí dependency graf pre evaluator ako dirty, ale cached xlfcsLoaded závislej bunky nechá na mieste, a klasický writer tú zastaranú hodnotu s radosťou zapíše, ak predtým nezavoláte Recalculate alebo neprečítate Value tej bunky, čo ju spočíta a presunie stav na xlfcsCalculated. Je to ten istý kompromis, aký robí Excel v manuálnom režime výpočtu, a je správny pre pipeline, ktorá otvára cudzie súbory, upraví pár popiskov a ukladá — znamená to však, že workbook, ktorý edituje vstupy, musí svoj krok prepočtu vlastniť explicitne. Politika RecalcBeforeSave XLSX writera touto prácou nie je dotknutá a má vlastný manuálny režim, ktorý cache zachováva v tom istom duchu. Z toho plynú dve menšie hranice: cache-first cesta pomôže len bunkám, ktorých stav je xlfcsLoaded alebo xlfcsCalculated; generátor, ktorý píše formuly a nikdy ich nevyhodnotí, stále platí jedno vyhodnotenie na bunku pri ukladaní, presne ako predtým. A oprava nested-subtotalu napravuje to, ktoré bunky evaluator preskakuje, nie každú funkciu, ktorú evaluator implementuje — súbor, ktorého formuly HotXLS nedokáže spočítať rovnako ako Excel, sa teraz dá bezpečne round-tripnúť nedotknutý, ale zámerný Recalculate na takom súbore stále vyprodukuje odpoveď knižnice a nie Excelu, a než prepočítanému uloženiu uveríte, tie dve porovnajte

Cache-first klasické uloženia, obnovené cache rootov shared a array formúl, posun relatívnych referencií pre shared followery a opravené pravidlá vnorenia SUBTOTAL a AGGREGATE — to všetko je v štandardnom HotXLS Delphi Spreadsheet Component pre Delphi a C++Builder, bez závislosti na Exceli alebo akomkoľvek OLE automation serveri; produktová stránka nesie plnú API referenciu pre workbook, cache reader a vstupné body prepočtu, ktoré sa tu používajú