Zabránit tichému přepočtu vzorců při uložení XLS v Delphi

HotXLS, nativní Excel knihovna pro Delphi a C++Builder, ukládá klasický sešit BIFF8 .xls nejdřív z cache: TXLSWorksheet.WriteFormula se ptá TXLSWorkbook.TryGetCachedFormulaValue po hodnotě, kterou Excel uložil vedle každého vzorce, a evaluátor volá jen tehdy, když ta cache chybí nebo byla zneplatněná. Sešit, který jste otevřeli a nikdy se nedotkli, uloží zpátky tatáž čísla a čerstvé výsledky vyžadují jedno explicitní volání Recalculate místo toho, aby byly skrytým vedlejším efektem SaveAs

Bug, který tenhle kontrakt vydal na světlo, byl trapně malý. Korpusový soubor pojmenovaný nested-subtotals.xls drží velký součet v R2C4, jehož cachovaná hodnota je 37. Otevřete ho v HotXLS, zeptejte se TryGetCachedFormulaValue na buňku, dostanete 37. Uložte beze změny jediné buňky, otevřete uloženou kopii, zeptejte se na totéž, dostanete 67. Nic v API nebylo požádáno o výpočet čehokoli, přesto se číslo v souboru posunulo přesně o 30 — a 30 je zrovna součet dvou skupinových subtotalů, 10 a 20, které sedí uvnitř rozsahu, který velký součet pokrývá

Proč uložení XLS souboru změní hodnotu vzorce?

Aby se z těch 37 stalo 67, musely se seřadit dvě nezávislé vady a oprava jen jedné by druhou schovala. První byla strukturální: klasický writer přepočítával každý vzorec při každém uložení. Druhá byla typová kontrola, která nemohla být nikdy pravdivá pro vzorec načtený z disku, takže evaluátor počítal vnořené buňky SUBTOTAL dvakrát. Korpusový soubor byl prostě první vstup, u kterého přepočet při uložení dal jinou odpověď než Excel a někdo obě srovnal. Strukturální vadu jde snadno říct: před v2.382.3 braly TXLSWorksheet.WriteFormula a její sdílená sestra WriteFormulaWithTExp osmibajtové pole FormulaValue každého Formula záznamu voláním TXLSWorkbook.GetFormulaValue, což je evaluátor. Cache, kterou ParseFormula pečlivě dekódoval ze zdrojového souboru při načtení, se nikdo nezeptal na cestě ven. V důsledku bylo každé uložení plný přepočet s obejitím přepočtového API na úrovni sešitu, takže by ho nezastavilo nic, co byste na sešitu nastavili. Jakékoli místo, kde se evaluátor HotXLS rozcházel s Excelem, ať legitimně nepodporovaná funkce, nebo obyčejný bug, se stávalo tichou změnou dat při uložení

Druhá vada bydlela v callbacku vnořených subtotalů, který evaluátor používá. Excel definuje každou formu SUBTOTAL jako ignorující buňky, jejichž vlastním vzorcem je jiný SUBTOTAL, takže kalkulátor v lxCalc.pas nastavuje FIgnoreSubtotalCells během agregace a ptá se sešitu přes TXLSWorkbook.GetClassicIsSubtotalCell, zda každá buňka v rozsahu taková je. Ten callback bral text vzorce jako Variant a testoval ho přes VarType(f) = varOleStr. Text se vrací z GetUnCompiledFormula jako Delphi String a String přiřazený do Variantu je varUString, nikdy varOleStr. Predikát byl pro každou buňku v každém načteném souboru false, skupinové subtotaly se sčítaly do velkého součtu podruhé a při uložení, které přepočítalo všechno, se z 10 + 20 + 7 stalo 67

// HotXLS 2.381 a dřív: formulový Variant postavený ze Stringu
// je varUString, takže tohle srovnání nikdy neuspělo
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 i varUString,
// a AGGREGATE se z obklopujících subtotalů vylučuje jako v 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 dodala opravu VarIsStr a, zatímco byla v týž funkci, naučila callback, že buňky AGGREGATE se z obklopujících subtotalů vylučují taky. To samo dalo korpusovému assertu projít, protože přepočítaných 37 teď odpovídalo načteným 37. Nepřimělo to knihovnu být čestná: uložení pořád přepočítávalo a test byl zelený jen proto, že evaluátor náhodou souhlasil s Excelem u toho konkrétního souboru. Pravidla, která buňky SUBTOTAL a AGGREGATE vynechávají, včetně skrytých řádků, rozebírá článek o skrytých řádcích SUBTOTAL a AGGREGATE; tady záleží na tom, že žádný evaluátor nemá hlasovat o souboru, který jste ho nepožádali spočítat

Co Excel garantuje ohledně cachovaných hodnot při uložení?

Excel bere uložení jako snapshot, ne jako výpočtovou událost. Hodnota zapsaná do pole FormulaValue Formula záznamu ([MS-XLS] §2.4.127, rozložení v §2.5.133) je to, co buňka zrovna zobrazuje, co v manuálním módu výpočtu může být letitě zaostalé, a Excel to pořád zapisuje věrně. Přepočet je oddělená operace s vlastním spouštěčem. HotXLS teď následuje totéž pravidlo pro klasická uložení: WriteFormula a WriteFormulaWithTExp volají nejdřív TryGetCachedFormulaValue, berou CacheInfo.Value, když je stav xlfcsLoaded nebo xlfcsCalculated, a propadají k GetFormulaValue jen pro xlfcsMissing a xlfcsInvalidated. Čtecí polovina toho kontraktu, včetně toho, co který stav znamená a proč cachovaná prázdná hodnota nebo False pořád počítá jako hodnota, je popsána v čtení cachovaných hodnot vzorců Excelu v Delphi bez přepočtu

Záložní cesta se záměrně drží, nemaže se. Vzorec, který jste v této session přiřadili přes Cells[Row, Col].Formula, přichází bez cache a vzorec, který jste vyměnili na načtené buňce, označí _SetCompiledFormula jako xlfcsInvalidated; oba se vyhodnocují při uložení přesně jako dřív, takže generovaný sešit se pořád otevře v Excelu s čísly. Když ani evaluátor nedokáže vyprodukovat hodnotu, writer emituje nulový payload a nastaví fAlwaysCalc (bit 0 grbitu §2.4.127), takže Excel přepočítá buňku při otevření místo důvěry v placeholder

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // list, řádek a sloupec od 1: R2C4 na prvním listu
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // pro cachované buňky evaluátor nezasahuje
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 pro nested-subtotals.xls
    // Uložení, které by přepočítalo, by tady zapsalo 67
  finally
    Book.Free;
  end;
end;

Kde si kořen sdíleného vzorce BIFF drží svou cachovanou hodnotu?

Ve vlastním Formula záznamu, jako každá jiná buňka se vzorcem, a přesně to udělalo z kořenové buňky sdílené skupiny jediné místo, kde ukládání cache-first pořád prohrávalo. Sdílený vzorec v BIFF8 se ukládá jako ShrFmla záznam ([MS-XLS] §2.4.260), který následuje po Formula záznamu buňky vlevo nahoře, a každá členská buňka, kořen nevyjímaje, nese rgce sestávající z jediného tokenu PtgExp (§2.5.198): první bajt parsovaného výrazu je $01 následovaný řádkem a sloupcem kořenové buňky. Buňky followerů jsou samostatné — HotXLS čte každého jeho FormulaValue a rozřešuje výraz dohledáním zkompilovaného vzorce kořene. Kořenová buňka je jiná, protože když se parsuje její Formula záznam, výraz ještě neexistuje; přijde o záznam později

Ta mezera jednoho záznamu je místo, kam cache odešla. TXLSReader.ParseFormula dekóduje cachovanou hodnotu a na pohlednutí PtgExp, jehož souřadnice se rovnají vlastním souřadnicím buňky, si buňku zapamatuje do FSharedFormulaRow a FSharedFormulaCol a publikuje cache do buňky. Když přijde ShrFmla záznam ($04BC), ParseSharedFormula zkompiluje výraz a nainstaluje ho přes _SetCompiledFormula a _SetCompiledFormula dělá to, co musí udělat u jakékoli změny vzorce: vymaže FCachedFormulaValue a resetuje stav na xlfcsMissing. Načtených 37 kořene bylo proto vyhozeno, než si je kdokoli mohl přečíst, TryGetCachedFormulaValue hlásila kořen jako necachovaný a writer cache-first povinně propadl k evaluátoru přesně u buňky, na kterou se všichni dívali. Array záznam (§2.4.4) sdílí totéž pořadí a měl tutéž díru

Oprava ve v2.382.3 přidává třetí pole FSharedFormulaCachedValue vedle čekajících souřadnic kořene. ParseFormula tam odloží dekódovanou cache, když rozpozná kořen, a oba, ParseSharedFormula i ParseArrayFormula, ji přehrají přes _SetCellCachedFormulaValue hned po instalaci zkompilovaného výrazu a pak resetují odložené na Unassigned. Variant cache String se vším tímhle netřese, protože její payload přichází v separátním String záznamu a směruje se podle souřadnic buňky, ne podle pořadí záznamů. Pracujete-li s OOXML stranou téhož konceptu, článek o expanzi si u sdílených vzorců XLSX vysvětluje, proč formát balíčku nemá ekvivalentní problém pořadí, ale vlastní pasti expanze

Proč potřebují followeři sdíleného vzorce relativní posun?

Protože výraz uložený v ShrFmla je psaný relativně ke kořenové buňce a follower, který ho znovu použije slovo od slova, vyhodnotí reference kořene místo vlastních. Starý reader instaloval na každého followera Value.GetCopy(), hlubokou kopii bez posunutí, takže skupina zakořeněná v B1 s =A1*3 dala každému followerovi též =A1*3. Ukládání cache-first to u načtených souborů doopravdy maskovalo, protože followeři měli vlastní FormulaValue a výraz nepotřebovali, aby se uložilo správně; vyplulo to v momentě, kdy cokoli přepočítalo. Reader teď instaluje TXLSCompiledFormula.GetCopy(row - srow, col - scol), které projde syntaktický strom a posune každou relativní referenci o vzdálenost followera od kořene, takže follower v B2 vlastní poctivé =A2*3

Regresní test, který přibíjí obě chování, stojí za přečtení, protože odmítá pustit náhodu. Postaví sešit s =A1*3 a =A2*3 nad vstupy 2 a 4, pak vstříkne záměrně špatné cache 999 a 888 přes _SetCellCachedFormulaValue, jednou s UseSharedFormulas zapnutými a jednou vypnutými. Po uložení a znovunačtení musí obě buňky pořád hlásit 999 a 888 — důkaz, že uložení se nedotklo ani cache kořene, ani followera. Až po explicitním Recalculate se musí stát 6 a 12, důkaz, že posunutý výraz followera je správný. Test, který by zasadil pravé hodnoty, by prošel i pod starým writerem, a o to jde při zásazování špatných

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

    // Načtené cache závislých vzorců NEJSOU zneplatněné
    // literální editou, takže prosté SaveAs by drželo stará čísla.
    // Ptejte se na přepočet, když doopravdy 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;

Co kontrakt cache-first za vás neudělá

Ukládání cache-first zachovává to, co bylo načtené; nesleduje, zda to, co bylo načtené, je pořád pravda. Změna literálu, na kterém vzorec závisí, označí graf závislostí dirty pro evaluátor, ale nechává cache xlfcsLoaded závislé buňky na místě a klasický writer rádo zapíše tu zaostalou hodnotu, pokud nezavoláte Recalculate nebo nepřečtete nejdřív Value buňky, což ji spočítá a přesune stav na xlfcsCalculated. To je tentýž trade, který dělá Excel v manuálním módu výpočtu, a je to ten správný pro pipeline, která otevírá soubory třetích stran, edituje pár popisků a ukládá — ale znamená to, že sešit, který edituje vstupy, musí vlastnit svůj krok přepočtu explicitně. Politika RecalcBeforeSave writeru XLSX touto prací zůstala nedotčená a má vlastní manuální mód, který drží cache ve stejném duchu. Z toho plynou dvě menší hranice: cesta cache-first pomáhá jen buňkám, jejichž stav je xlfcsLoaded nebo xlfcsCalculated; generátor, který zapisuje vzorce a nikdy je nevyhodnocuje, pořád platí jedno vyhodnocení na buňku při uložení, přesně jako dřív. A oprava vnořených subtotalů napravuje, které buňky evaluátor vynechává, ne každou funkci, kterou evaluátor implementuje — soubor, jehož vzorce HotXLS nedokáže spočítat identicky s Excelem, je teď bezpečné protáhnout round tripem beze změny, ale záměrný Recalculate nad tím souborem pořád vyprodukuje odpověď knihovny místo Excelovy a měli byste obě srovnat, než budete důvěřovat přepočítanému uložení

Klasická uložení cache-first, obnovené cache kořenů sdílených a array vzorců, posun relativních referencí pro sdílené followery a napravená vnořovací pravidla SUBTOTAL a AGGREGATE všechna dodává standardní HotXLS Delphi Spreadsheet Component pro Delphi a C++Builder, bez závislosti na Excelu nebo jakémkoli OLE automation serveru; produktová stránka nese plnou API referenci pro vstupní body sešitu, cache readeru a přepočtu použité tady