Suma ogólna, którą Excel pokazuje jako 37, może po tym, jak program otworzy plik .xls i zapisze go bez edycji jakiejkolwiek komórki, wrócić jako 67. Dodatkowe 30 nie jest losowe. To dokładnie suma dwóch sum częściowych grup, 10 i 20, siedzących w zakresie objętym sumą ogólną. Żeby ta liczba zmieniła wartość, dwie rzeczy muszą pójść źle naraz: zapis musi przeliczyć każdą formułę na wyjściu, a to, co przelicza, musi zapomnieć, że SUBTOTAL pomija inne komórki SUBTOTAL. Usuń którąkolwiek z tych rzeczy, a plik przeżywa — dlatego pierwszy problem może siedzieć niezauważony za drugim
Dlaczego zapis pliku XLS zmienia wartość formuły?
Zapis zmienia wartość formuły wtedy, gdy pisarz wylicza liczbę, którą zapisuje obok każdej formuły, zamiast skopiować tę wczytaną. Każdy rekord Formula w BIFF8 niesie ośmiobajtowe pole FormulaValue ([MS-XLS] §2.4.127, układ wartości w §2.5.133), które trzyma wynik, jaki Excel ostatnio pokazał dla tej komórki. Pisarz musi w te osiem bajtów coś wstawić, a najkrótsza ścieżka kodu to oddanie każdej formuły silnikowi obliczeniowemu. Gdy tak się stanie, każdy zapis jest pełnym przeliczeniem, o które nikt nie prosił, a każde miejsce, w którym ten silnik i Excel się nie zgadzają, staje się zmienioną liczbą w pliku, który użytkownik uważa za nietknięty
Niezgodność wokół SUBTOTAL to dobry przykład, bo reguła arkuszowa jest prosta, a pułapka siedzi w typowaniu Variant w Delphi. Excel definiuje każdy numer funkcji SUBTOTAL tak, by ignorował komórki, których własną formułą jest inny SUBTOTAL. Dla arkusza, którego wiersze szczegółów sumują się do 37 (wiersze pod dwiema grupami z sumami częściowymi 10 i 20, plus 7 poza jakąkolwiek grupą), suma ogólna wynosi 37. Silnik, który nie wypatrzy zagnieżdżonych sum częściowych, policzy 10 i 20 drugi raz i wyląduje na 67. Wypatrzenie ich zwykle znaczy krótkie sprawdzenie tekstu formuły, a oczywista wersja delphiowa kompiluje się, działa i nigdy niczego nie dopasowuje:
// Helper, który piszesz sam, na przykład przy skanowaniu arkusza
// pod kątem formuł, które obejmujący SUBTOTAL ma pomijać
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// Kuszący test na Unicode'owym Delphi nigdy nie zachodzi: String
// przypisany do Varianta to varUString, nie varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // przyjmuje varString, varOleStr i varUString
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(');
end;
// Użycie z HotXLS (arkusze, wiersze i kolumny liczone od 1):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr to test, który ewaluator HotXLS stosuje wobec tego samego pytania, i traktuje on też komórki AGGREGATE jako wykluczone z obejmującej sumy częściowej, jak w Excelu. Sama poprawka predykatu to jednak nie cała historia. Przy poprawnym sprawdzeniu przeliczający zapis wpisze 37 tylko dlatego, że silnik z przypadku zgodził się z Excelem na tym konkretnym pliku. Następna formuła, którą silnik obsłuży odrobinę inaczej, znowu się zmieni. Reguły, które komórki pomijają SUBTOTAL i AGGREGATE, łącznie z ukrytymi wierszami, opisuje artykuł o ukrytych wierszach SUBTOTAL i AGGREGATE. Przy zapisie cache-first plik, którego obliczeń nie zlecałeś, wraca na dysk z 37 w komórce, cokolwiek silnik by z niego zrobił
Co klasyczny zapis XLS wpisuje do każdego rekordu Formula
HotXLS zapisuje skoroszyt BIFF8 cache first: dla każdej komórki z formułą TXLSWorkbook.SaveAs wpisuje wartość z cache, ilekroć TryGetCachedFormulaValue by ją zgłosiła, i uruchamia ewaluator tylko wtedy, gdy by nie zgłosiła. Decyduje stan cache. Komórka w stanie xlfcsLoaded (wartość przyszła z pliku źródłowego) albo xlfcsCalculated (HotXLS policzył ją w tej sesji) ma CacheInfo.Value zapisane bajt w bajt. Komórka w stanie xlfcsMissing albo xlfcsInvalidated jest wyliczana przy zapisie. To się zgadza z tym, jak Excel traktuje zapis: migawka tego, co każda komórka pokazuje — w trybie obliczeń ręcznych potrafi być mocno nieaktualna — zapisywana wiernie mimo to
Ścieżka wyliczania zostaje z dobrych powodów. Formuła przypisana w kodzie przez Cells[Row, Col].Formula nie ma jeszcze cache, a przypisanie nowej formuły komórce wczytanej przenosi ją do xlfcsInvalidated, bo stary wynik należy do starego wyrażenia. Obie są liczone przy zapisie, więc skoroszyt generowany od zera otwiera się w Excelu z policzonymi liczbami zamiast zer. Gdy nawet ewaluator nie potrafi wyprodukować wartości (niewspierana funkcja, zepsute łącze zewnętrzne), pisarz zapisuje zero i ustawia fAlwaysCalc, bit 0 flag rekordu z §2.4.127, żeby Excel przeliczył komórkę przy otwarciu, zamiast ufać placeholderowi
Jedna właściwość wygląda na powiązaną i nią nie jest. TXLSWorkbook.RecalcOnSave odbija excelową opcję „Recalculate workbook before saving” z rekordu CalcSaveRecalc ([MS-XLS] §2.4.37). Ma znaczenie tylko dla Excela i tylko w trybie obliczeń ręcznych, a HotXLS po prostu wpisuje ją do pliku. SaveAs nie konsultuje jej, gdy decyduje, co trafi do FormulaValue. Poniższy round-trip to najszybszy sposób, żeby zobaczyć kontrakt na jednym z własnych plików:
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// arkusz, wiersz i kolumna liczone od 1
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // komórki z cache są zapisywane tak, jak są trzymane
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Oczekiwane: After.Value równe Before.Value, a After.State to
// ponownie xlfcsLoaded, bo przyszło z zapisanego pliku
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;Stronę odczytową tego kontraktu — co znaczy każdy stan i dlaczego zapisany blank albo False wciąż liczy się jako wartość — opisuje artykuł o odczycie wartości formuł z cache Excela bez przeliczania. Puść powyższe sprawdzenie na komórce sumy ogólnej skoroszytu z zagnieżdżonymi sumami częściowymi, a obie linie wypiszą tę samą liczbę
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łą, ale wyrażenie przychodzi jeden rekord później — i właśnie w tej luce nieostrożny czytnik gubi cache. BIFF8 trzyma formułę współdzieloną jako rekord ShrFmla ([MS-XLS] §2.4.260) położony zaraz za rekordem Formula lewej górnej komórki grupy. Każda komórka członkowska, łącznie z korzeniem, niesie rgce złożone z jednego tokenu PtgExp (§2.5.198): bajt $01, po którym idą wiersz i kolumna korzenia. Komórki-następniki są łatwe, bo gdy wypadają, wyrażenie korzenia już istnieje, a ich własne FormulaValue siedzi w ich własnych rekordach. Korzeń nie jest łatwy. Gdy jego rekord Formula jest czytany, ma zapisaną wartość 37 i jeszcze nie ma wyrażenia
Zainstalowanie wyrażenia na komórce czyści jej cache i tak ma być, bo zastąpiona formuła faktycznie czyni stary wynik bezsensownym. Przeniesiony bezmyślnie na rekord ShrFmla ten sam reguła wyrzuca jednak 37 korzenia w chwili, gdy wyrażenie nadchodzi, TryGetCachedFormulaValue zgłasza korzeń jako xlfcsMissing, a zapis schodzi do ewaluatora dokładnie dla komórki, na którą użytkownik najpewniej spojrzy. Czytnik HotXLS trzyma zdekodowaną wartość korzenia, dopóki czeka na rekord ShrFmla, instaluje wyrażenie, po czym odkłada wczytaną wartość z powrotem ze stanem xlfcsLoaded. Formuły tablicowe (rekord Array, §2.4.4) trzymają tę samą kolejność rekordów i dostają to samo traktowanie. Wariant String tego cache nigdy nie miał tego problemu, bo jego tekst przychodzi w osobnym rekordzie String i jest kierowany do komórki po wierszu i kolumnie, więc jego pozycja w strumieniu nigdy nie miała znaczenia
Jeśli obsługujesz też stronę OOXML, artykuł o rozwijaniu si formuł współdzielonych w XLSX wyjaśnia, dlaczego format pakietowy nie ma luki w kolejności rekordów, ale ma własne pułapki przy rozwijaniu
Następniki formuły współdzielonej posiadają przesuniętą kopię wyrażenia
Wyrażenie w rekordzie ShrFmla jest zapisane względem komórki-korzenia, więc każdy następnik potrzebuje własnej kopii przesuniętej o jego odległość od korzenia. Skopiuj je dosłownie, a grupa zakorzeniona w B1 z =A1*3 wręczy to samo =A1*3 B2 i B3. HotXLS daje każdemu następnikowi kopię, której odwołania względne są przesunięte o odległość następnika w wierszach i kolumnach, więc B2 posiada prawdziwe =A2*3, a B3 posiada =A3*3
Dlatego też zwykły round-trip nie powie ci, czy wyrażenia następników są poprawne. Następniki bronią swoich wczytanych wartości przez zapis, więc dosłowna kopia przeszłaby test. Sprawdzenie, które cokolwiek dowodzi, musi zmienić wejście i przeliczyć. Poniższa procedura zapisuje grupę współdzieloną z włączonym UseSharedFormulas (domyślnie False, co pisze jeden rekord Formula na komórkę), otwiera plik ponownie, edytuje A2 i obserwuje B2:
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 stają się jedną grupą ShrFmla
Book.SaveAs(FileName); // jeszcze bez cache: wyliczone na 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // edytuj wejście następnika
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Oczekiwane: 12, wciąż wczytane
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Oczekiwane: 30, z =A2*3
finally
Book.Free;
end;
end;Następnik, który użyłby ponownie =A1*3, wypisałby w drugiej linii 6. Pierwsza linia znaczy tyle samo: pokazuje, że edycja literału zostawia wczytaną wartość formuły zależnej na miejscu, dopóki coś nie przeliczy
Co kontrakt cache-first zostawia tobie
Zapis cache-first zachowuje to, co zostało wczytane. Nie śledzi, czy wczytane jest wciąż prawdziwe. Zmiana komórki, od której formuła zależy, oznacza graf zależności do przeliczenia, ale komórka zależna trzyma swoją wartość xlfcsLoaded, a SaveAs wpisze tę nieświeżą liczbę, chyba że najpierw wołasz Recalculate albo odczytasz Value komórki, co liczy formułę i przenosi ją do xlfcsCalculated. Recalculate zwraca liczbę komórek z formułami, których nie potrafił wyliczyć, więc niezerowy wynik warto zalogować przed zapisem
W typowym potoku program otwiera skoroszyt zbudowany przez kogoś innego, uzupełnia kilka etykiet albo nagłówek i zapisuje. Nic w tym pliku nie powinno zmienić wartości, a przy zapisie cache-first nic nie zmienia. Program, który edytuje wejścia, sam posiada krok przeliczania — i ten krok należy do jednego widocznego wywołania Recalculate obok edycji, tam gdzie każdy czytający kod go zobaczy. Excel idzie na tę samą wymianę w trybie obliczeń ręcznych. Pisarz XLSX ma własną politykę, RecalcBeforeSave z trybami xrmManual, xrmIfDirty i xrmAlways, ustawianą na klasie skoroszytu XLSX i niezależną od wszystkiego powyżej
Z projektu płyną dwie granice. Ścieżka cache pomaga tylko komórkom w stanie xlfcsLoaded albo xlfcsCalculated, więc generator, który pisze tysiące nowych formuł i nigdy ich nie liczy, wciąż płaci jedno wyliczenie na komórkę przy zapisie. A kontrakt rządzi tym, co zapis wpisuje, a nie tym, co liczy jawne przeliczenie: nietknięty plik trzyma liczby zapisane przez Excela, podczas gdy Recalculate wpisuje wyniki ewaluatora HotXLS. Porównaj oba podejścia na kilku reprezentatywnych skoroszytach, zanim przełączysz potok na przeliczane zapisy
HotXLS Delphi Spreadsheet Component czyta i zapisuje skoroszyty klasyczne BIFF8 i XLSX z Delphi i C++Buildera bez zainstalowanego Excela ani automatyzacji OLE. Strona produktu dokumentuje wywołania skoroszytowe, odczytu cache i przeliczania użyte w tych przykładach