Zapis XLS bez cichego przeliczania formuł w Delphi

HotXLS, natywna biblioteka Excela dla Delphi i C++Buildera, zapisuje klasyczny skoroszyt .xls w formacie BIFF8 najpierw z cache: TXLSWorksheet.WriteFormula pyta TXLSWorkbook.TryGetCachedFormulaValue o wartość, którą Excel zapisał obok każdej formuły, i woła ewaluator tylko wtedy, gdy tego cache brakuje albo został unieważniony. Skoroszyt, który otworzyłeś i nigdy nie tknąłeś, zapisuje z powrotem te same liczby, a świeże wyniki wymagają jednego jawnego wywołania Recalculate, zamiast być ukrytym efektem ubocznym SaveAs

Błąd, który wypchnął ten kontrakt na światło dzienne, był żenująco mały. Plik korpusu o nazwie nested-subtotals.xls trzyma sumę końcową w R2C4, której zapisana wartość to 37. Otwórz go w HotXLS, zapytaj TryGetCachedFormulaValue o tę komórkę, dostaniesz 37. Zapisz go bez zmiany choćby jednej komórki, otwórz zapisaną kopię, zadaj to samo pytanie, dostaniesz 67. Nigdzie w API nie poproszono o żadne obliczenia, a liczba w pliku przesunęła się dokładnie o 30 — a 30 to przypadkiem suma dwóch sum częściowych grup, 10 i 20, które siedzą w zakresie obejmowanym przez sumę końcową

Dlaczego zapis pliku XLS zmienia wartość formuły?

Aby 37 zamieniło się w 67, musiały się złożyć dwa niezależne defekty, a naprawa któregokolwiek z osobna ukryłaby ten drugi. Pierwszy był strukturalny: klasyczny zapisujący przeliczał każdą formułę przy każdym zapisie. Drugi to sprawdzenie typu, które nigdy nie mogło być prawdziwe dla formuły wczytanej z dysku, przez co ewaluator liczył zagnieżdżone komórki SUBTOTAL dwa razy. Plik z korpusu był po prostu pierwszym wejściem, w którym przeliczanie przy zapisie dało inny wynik niż Excel i ktoś te dwa wyniki porównał. Defekt strukturalny łatwo opisać: przed v2.382.3 TXLSWorksheet.WriteFormula i jego bliźniak od formuł współdzielonych WriteFormulaWithTExp zdobywały ośmiobajtowe pole FormulaValue każdego rekordu Formula, wołając TXLSWorkbook.GetFormulaValue, czyli ewaluator. Cache, który ParseFormula tak starannie zdekodował z pliku źródłowego przy wczytywaniu, nie był na wyjściu w ogóle sprawdzany. W praktyce każdy zapis był pełnym przeliczeniem z pominięciem API przeliczania na poziomie skoroszytu, więc nic, co mogłeś ustawić na skoroszycie, by tego nie zatrzymało. Każde miejsce, w którym ewaluator HotXLS nie zgadzał się z Excelem — czy to legalnie nieobsługiwana funkcja, czy zwykły błąd — stawało się cichą zmianą danych przy zapisie

Drugi defekt mieszkał w callbacku zagnieżdżonych sum częściowych, którego używa ewaluator. Excel definiuje każdą formę SUBTOTAL tak, że ignoruje komórki, których własna formuła jest innym SUBTOTAL, więc kalkulator w lxCalc.pas uzbraja FIgnoreSubtotalCells na czas agregacji i pyta skoroszyt, przez TXLSWorkbook.GetClassicIsSubtotalCell, czy każda komórka w zakresie jest taką komórką. Ten callback pobierał tekst formuły jako Variant i testował go przez VarType(f) = varOleStr. Tekst wraca z GetUnCompiledFormula jako String w Delphi, a String przypisany do Varianta to varUString, nigdy varOleStr. Predykat był fałszywy dla każdej komórki w każdym wczytanym pliku, sumy częściowe grup wliczały się do sumy końcowej drugi raz, a przy zapisie, który przeliczał wszystko, 10 + 20 + 7 dawało 67

// HotXLS 2.381 i wcześniejsze: Variant formuły zbudowany z String
// to varUString, więc to porównanie nigdy nie zachodziło
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr przyjmuje varString, varOleStr i varUString,
// a AGGREGATE jest wykluczany z obejmujących sum częściowych, jak w 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 dostarczyła poprawkę z VarIsStr i przy okazji, w tej samej funkcji, nauczyła callback, że komórki AGGREGATE też są wykluczane z obejmujących sum częściowych. To samo sprawiło, że asercja korpusu przeszła, bo przeliczone 37 zgadzało się teraz z wczytanym 37. Nie czyniło to jednak biblioteki uczciwą: zapis wciąż przeliczał, a test był zielony tylko dlatego, że ewaluator przypadkiem zgadzał się z Excelem na tym konkretnym pliku. Reguły mówiące, które komórki pomijają SUBTOTAL i AGGREGATE, wraz z ukrytymi wierszami, omawia artykuł o SUBTOTAL, AGGREGATE i ukrytych wierszach; tutaj liczy się to, że żaden ewaluator nie powinien mieć głosu w sprawie pliku, o którego przeliczenie nie poprosiłeś

Co Excel gwarantuje w kwestii wartości z cache przy zapisie?

Excel traktuje zapis jako migawkę, a nie zdarzenie obliczeniowe. Wartość wpisana w pole FormulaValue rekordu Formula ([MS-XLS] §2.4.127, układ w §2.5.133) to to, co komórka aktualnie pokazuje — w trybie obliczeń ręcznych może być nieaktualna o lata — i Excel i tak zapisuje ją wiernie. Przeliczanie to osobna operacja z własnym wyzwalaczem. HotXLS stosuje teraz tę samą regułę przy zapisach klasycznych: WriteFormula i WriteFormulaWithTExp najpierw wołają TryGetCachedFormulaValue, biorą CacheInfo.Value, gdy stan to xlfcsLoaded albo xlfcsCalculated, a do GetFormulaValue schodzą tylko dla xlfcsMissing i xlfcsInvalidated. Połowę odczytową tego kontraktu — co znaczy każdy stan i dlaczego zapisany blank albo False wciąż liczy się jako wartość — opisuje odczyt wartości formuł z cache Excela bez przeliczania

Ścieżka awaryjna jest celowo zachowana, a nie usunięta. Formuła przypisana w tej sesji przez Cells[Row, Col].Formula przychodzi bez cache, a formuła zastąpiona na wczytanej komórce jest oznaczana jako xlfcsInvalidated przez _SetCompiledFormula; obie są liczone przy zapisie dokładnie jak wcześniej, więc wygenerowany skoroszyt wciąż otwiera się w Excelu z liczbami w środku. Gdy nawet ewaluator nie potrafi wyprodukować wartości, zapisujący emituje zerowy payload i ustawia fAlwaysCalc (bit 0 grbit z §2.4.127), żeby Excel przeliczył komórkę przy otwarciu, zamiast zaufać placeholderowi

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // arkusz, wiersz i kolumna liczone od 1: R2C4 na pierwszym arkuszu
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // dla komórek z cache ewaluator nie bierze udziału
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 dla nested-subtotals.xls
    // Zapis, który by przeliczał, wpisałby tu 67
  finally
    Book.Free;
  end;
end;

Gdzie komórka-korzeń formuły współdzielonej BIFF trzyma zapisaną wartość?

W swoim własnym rekordzie Formula, tak jak każda inna komórka z formułą, i właśnie dlatego komórka-korzeń grupy współdzielonej była jedynym miejscem, w którym zapis cache-first wciąż gubił wartość. Formuła współdzielona w BIFF8 jest zapisana jako rekord ShrFmla ([MS-XLS] §2.4.260) następujący po rekordzie Formula komórki lewej górnej, a każda komórka członkowska, łącznie z korzeniem, niesie rgce złożone z jednego tokenu PtgExp (§2.5.198): pierwszy bajt sparsowanego wyrażenia to $01, po którym idą wiersz i kolumna komórki-korzenia. Komórki-następniki są samowystarczalne — HotXLS czyta FormulaValue każdej z nich i rozwiązuje wyrażenie, sięgając po skompilowaną formułę korzenia. Komórka-korzeń jest inna, bo gdy parsowany jest jej rekord Formula, wyrażenie jeszcze nie istnieje; przychodzi jeden rekord później

W tej jednorekordowej luce cache przepadł. TXLSReader.ParseFormula dekoduje zapisaną wartość i widząc PtgExp, którego współrzędne są równe współrzędnym samej komórki, zapamiętuje komórkę w FSharedFormulaRow i FSharedFormulaCol oraz publikuje cache w komórce. Gdy nadchodzi rekord ShrFmla ($04BC), ParseSharedFormula kompiluje wyrażenie i instaluje je przez _SetCompiledFormula, a _SetCompiledFormula robi to, co musi zrobić przy każdej zmianie formuły: czyści FCachedFormulaValue i resetuje stan do xlfcsMissing. Wczytane 37 korzenia zostało więc wyrzucone, zanim ktokolwiek zdążył je odczytać, TryGetCachedFormulaValue raportował korzeń jako bez cache, a zapisujący cache-first posłusznie schodził do ewaluatora dokładnie dla tej komórki, na którą wszyscy patrzyli. Rekord Array (§2.4.4) ma tę samą kolejność i miał tę samą dziurę

Poprawka w v2.382.3 dodaje trzecie pole, FSharedFormulaCachedValue, obok oczekujących współrzędnych korzenia. ParseFormula odkłada tam zdekodowany cache, gdy rozpozna korzeń, a zarówno ParseSharedFormula, jak i ParseArrayFormula odtwarzają go przez _SetCellCachedFormulaValue zaraz po zainstalowaniu skompilowanego wyrażenia, a potem resetują schowek do Unassigned. Wariant String tego cache nie jest tym wszystkim dotknięty, bo jego payload przychodzi w osobnym rekordzie String i jest kierowany współrzędnymi komórki, a nie kolejnością rekordów. Jeśli pracujesz z tą samą koncepcją po stronie OOXML, artykuł o rozwijaniu si formuł współdzielonych w XLSX wyjaśnia, dlaczego format pakietowy nie ma równoważnego problemu z kolejnością, ale ma własne pułapki przy rozwijaniu

Dlaczego następniki formuły współdzielonej potrzebują przesunięcia względnego?

Bo wyrażenie zapisane w ShrFmla jest napisane względem komórki-korzenia, a następnik, który użyje go dosłownie, policzy odwołania korzenia zamiast własnych. Stary czytnik instalował w każdym następniku Value.GetCopy(), czyli głęboką kopię bez przesunięcia, więc grupa zakorzeniona w B1 z =A1*3 dawała każdemu następnikowi też =A1*3. Zapis cache-first faktycznie maskował to dla wczytanych plików, bo następniki miały własne FormulaValue i nie potrzebowały wyrażenia, by zapisać się poprawnie; problem wychodził w chwili, gdy cokolwiek przeliczało. Czytnik instaluje teraz TXLSCompiledFormula.GetCopy(row - srow, col - scol), który przechodzi drzewo składni i przesuwa każde odwołanie względne o odległość następnika od korzenia, więc następnik w B2 ma prawdziwe =A2*3

Test regresyjny, który przypina oba zachowania, warto przeczytać, bo nie pozwala przejść przypadkowi. Buduje skoroszyt z =A1*3 i =A2*3 nad wejściami 2 i 4, a potem wstrzykuje celowo błędne cache 999 i 888 przez _SetCellCachedFormulaValue, raz z włączonym UseSharedFormulas, raz bez. Po zapisie i ponownym wczytaniu obie komórki muszą wciąż raportować 999 i 888 — dowód, że zapis nie tknął ani cache korzenia, ani następnika. Dopiero po jawnym Recalculate muszą stać się 6 i 12, co dowodzi, że przesunięte wyrażenie następnika jest poprawne. Test, który zasiałby prawdziwe wartości, przeszedłby także pod starym zapisującym, i właśnie dlatego zasiewa się błędne

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

    // Wczytane cache formuł zależnych NIE są unieważniane przez
    // edycję literału, więc zwykłe SaveAs zachowałoby stare liczby.
    // Poproś o przeliczenie, gdy naprawdę chcesz świeżych wyników:
    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;

Czego kontrakt cache-first za ciebie nie zrobi

Zapis cache-first zachowuje to, co zostało wczytane; nie śledzi, czy wczytane wartości są wciąż prawdziwe. Zmiana literału, od którego zależy formuła, oznacza graf zależności jako brudny dla ewaluatora, ale zostawia cache xlfcsLoaded komórki zależnej na miejscu, a klasyczny zapisujący ochoczo wpisze tę nieświeżą wartość, chyba że najpierw wywołasz Recalculate albo odczytasz Value komórki, co ją policzy i przeniesie stan do xlfcsCalculated. To ta sama wymiana, na którą idzie Excel w trybie obliczeń ręcznych, i słuszna dla potoku, który otwiera pliki z zewnątrz, poprawia kilka etykiet i zapisuje — ale oznacza, że skoroszyt zmieniający wejścia musi sam jawnie zadbać o przeliczenie. Polityka RecalcBeforeSave zapisującego XLSX nie zmienia się przez tę pracę i ma własny tryb ręczny, który zachowuje cache w tym samym duchu. Wynikają z tego dwie mniejsze granice: ścieżka cache-first pomaga tylko komórkom o stanie xlfcsLoaded albo xlfcsCalculated, a generator, który pisze formuły i nigdy ich nie liczy, wciąż płaci za jedno obliczenie na komórkę przy zapisie, dokładnie jak wcześniej. Poprawka zagnieżdżonych sum częściowych prostuje z kolei to, które komórki ewaluator pomija, a nie każdą funkcję, którą ewaluator implementuje — plik, którego formuł HotXLS nie policzy identycznie jak Excel, można teraz bezpiecznie przepuścić przez round-trip bez zmian, ale świadome Recalculate na tym pliku wciąż da odpowiedź biblioteki, a nie Excela, i warto te dwie porównać, zanim zaufasz przeliczonemu zapisowi

Zapis klasyczny cache-first, przywrócone cache korzeni formuł współdzielonych i tablicowych, przesunięcie odwołań względnych dla następników współdzielonych oraz poprawione reguły zagnieżdżania SUBTOTAL i AGGREGATE są w standardowym HotXLS Delphi Spreadsheet Component dla Delphi i C++Buildera, bez zależności od Excela czy jakiegokolwiek serwera automatyzacji OLE; strona produktu niesie pełne API dla użytych tu punktów wejścia skoroszytu, czytnika cache i przeliczania