Odczyt buforowanych formuł Excela w Delphi bez przeliczania

HotXLS, natywna biblioteka Excel dla Delphi i C++Buildera, odczytuje wartość, którą Excel już zapisał obok formuły, przez TryGetCachedFormulaValue i IXLSFormulaCacheReader. Żaden z tych punktów wejścia nie wywołuje kalkulatora, nie dekompiluje tokenów formuł, nie aktualizuje stanu dirty ani nie zapisuje niczego z powrotem do modelu, więc skoroszyt, który tylko czytasz, zostaje dokładnie taki, jak go otworzyłeś

Scenariusz, który to napędza, jest nudny i skrajnie powszechny. Nocne zadanie otwiera kilkaset skoroszytów wyprodukowanych przez kogoś innego, wyciąga z każdego jedną kolumnę sum i pcha liczby do hurtowni danych. Sumy już siedzą w plikach — Excel je policzył i zapisał. A jednak w chwili, gdy zadanie pyta komórkę formuły o jej wartość, biblioteka, która ma tylko jedną odpowiedź na to pytanie, buduje graf zależności i wylicza cały arkusz, i zadanie, które powinno być ograniczone przez I/O, zamienia się w benchmark obliczeń

Dlaczego odczyt komórki formuły kosztuje pełne przeliczenie?

Bo getter wartości na komórce formuły to prośba o wytworzenie wartości, a jedyny uniwersalnie poprawny sposób, by ją wytworzyć, to wyliczenie formuły. To właściwy domyślny dla aplikacji, która edytuje skoroszyty, i zły domyślny dla potoku, który je ekstrahuje. Gorzej, wyliczenie nie jest wolne od efektów ubocznych: zapisuje wyniki z powrotem do komórek, przerzuca flagi dirty i może rozwiązać się inaczej niż w aplikacji produkującej, gdy funkcja jest niewspierana albo zewnętrzne odwołanie jest zepsute. Zadanie, które opisałeś zespołowi operacyjnemu jako tylko-do-odczytu, po cichu produkuje skoroszyt, który już nie pasuje do tego na dysku, a jeśli cokolwiek później go zapisze, plik na dysku też się zmienia

Odczyt wartości buforowanych to druga połowa kontraktu. Odpowiada na węższe pytanie — co aplikacja produkująca tu zapisała? — i odmawia odpowiedzi na cokolwiek innego. Kiedy naprawdę chcesz świeżych liczb, HotXLS nadal daje ci przeliczanie przyrostowe napędzane grafem zależności; chodzi o to, że ekstrakcja i wyliczenie powinny być dwoma różnymi wywołaniami, a nie jednym wywołaniem z dwoma nastrojami

Trzy ortogonalne fakty o jednej komórce

Najpierw wniosek: buforowana wartość formuły niesie trzy niezależne fakty, i zgniecenie ich w pojedynczy Variant gubi informację, której potrzebujesz. TXLSFormulaCacheInfo trzyma je osobno jako State, Kind i Value. TXLSFormulaCacheState zapisuje pochodzenie w pięciu przypadkach — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated i xlfcsInvalidated — podczas gdy TXLSFormulaCacheValueKind klasyfikuje ładunek jako xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean lub xlfcvError. To rozdzielenie jest tym, co pozwala raportować obecność uczciwie: buforowana pustka, buforowany pusty ciąg, buforowane False, buforowane zero i buforowany błąd to wszystkie prawdziwe wartości, więc obecności nigdy nie wolno wywnioskować z VarIsEmpty ani VarIsNull. TryGetCachedFormulaValue zwraca True tylko dla xlfcsLoaded i xlfcsCalculated, a mimo to wypełnia stan nadający się do diagnozy, gdy zwraca False

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row i Col są tutaj wszystkie od jedynki
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

Dlaczego buforowana wartość jest nieobecna?

Są dokładnie cztery powody, dla których TryGetCachedFormulaValue oddaje False, i stan mówi ci, który się stosuje. xlfcsNotFormula znaczy, że komórka trzyma literał albo nic w ogóle, a współrzędne poza zakresem zbijają się w tę samą odpowiedź. xlfcsMissing znaczy, że komórka naprawdę jest formułą, ale producent nie zapisał dla niej ładunku wartości — częsty skutek, gdy generator zapisuje formuły i pozwala Excelowi wypełnić wyniki przy pierwszym otwarciu. xlfcsInvalidated znaczy, że tekst formuły został zastąpiony po wczytaniu, więc wartość, która tam była, opisuje wyrażenie, które już nie istnieje. xlfcsCalculated, przeciwnie, to przypadek sukcesu: oznacza wartość, którą twój własny kod albo ewaluator HotXLS wytworzył w tej sesji, w przeciwieństwie do xlfcsLoaded, która przyszła z pliku

Szczerość co do nieobecnego bufora znaczy więcej niż jego zaklejanie. HotXLS odmawia wymyślania wartości, a przy zapisie jest równie surowy — tylko xlfcsLoaded i xlfcsCalculated emitują buforowaną wartość, podczas gdy xlfcsMissing i xlfcsInvalidated zapisują samą formułę, zamiast zamrażać niestarą liczbę w pliku. Zostają ci trzy rozsądne reakcje w potoku: pomiń wiersz i zanotuj lukę, przelicz ten jeden skoroszyt świadomie i zaakceptuj koszt, albo wylicz i uzgodnij. Jeśli wyliczona liczba nie zgadza się z tym, co aplikacja produkująca by zapisała, tracer wyliczania formuł to narzędzie do znalezienia miejsca, w którym oba obliczenia się rozchodzą, a nie zgadywanie z wyniku

Jeden czytnik przez silniki klasyczny, OOXML i ODF

Potok nie powinien przejmować się tym, czy plik, który właśnie otworzył, był BIFF, OOXML czy ODF. IXLSFormulaCacheReader to jedyny punkt wejścia tylko-do-odczytu dla wszystkich trzech: zarówno TXLSWorkbook.CreateFormulaCacheReader, jak i TXLSXWorkbook.CreateFormulaCacheReader zwracają lekki adapter nad rzadkim wyszukiwaniem komórek, którego każdy silnik już używa, z identycznymi współrzędnymi arkusza, wiersza i kolumny od jedynki. Klasy skoroszytu celowo nie implementują same interfejsu — referencja interfejsu do skoroszytu zmieniłaby jego semantykę własności i pozwoliłaby wywołującym przeskoczyć dzierżawę czasu życia. Zamiast tego zniszczenie skoroszytu czyści surowy wskaźnik wewnątrz tej dzierżawy, a każdy czytnik, którego twój kod jeszcze trzyma, rzuca EXLSFormulaCacheReaderInvalidated przy następnej kwerendzie, zamiast dereferencjonować uwolnioną pamięć. To fail-fast sprawdzanie czasu życia, nie gwarancja współbieżności

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // Żaden kalkulator nie działał, żadna flaga dirty nie drgnęła, Book jest niezmieniony
end;

Gdzie buforowane bajty faktycznie mieszkają

Dla klasycznych plików .xls buforem jest pole FormulaValue rekordu Formula, osiem bajtów opisanych przez [MS-XLS] §2.5.133. Gdy górne słowo równa się $FFFF, ładunek nie jest double IEEE 754, lecz otagowanym wariantem, i układ jest łatwy do subtelnej pomyłki: typ wariantu siedzi w val[0], a ładunek boolean lub BErr siedzi w val[2], z val[1] niezdefiniowanym. HotXLS wcześniej czytał ładunek z val[1], co jest takim off-by-one, które wychodzi na jaw tylko na specyficznych plikach buforujących boolean albo błąd zamiast liczby. Czytnik i zapisujący formuły współdzielone zgadzają się teraz co do tych samych odstępów, więc buforowane TRUE przeżywa odczyt i zapis nietknięte, zamiast gnić w szum

Wierność typów w formatach pakietowych to osobny problem z własną pułapką. W OOXML buforowana wartość wisi na elemencie c jako <v>, z atrybutem t nazywającym typ według ECMA-376 Part 1 §18.3.1.4. HotXLS czyta t="e" prosto do Variantu varError i mapuje go z powrotem na standardowy tekst błędu przy zapisie, więc błędy nigdy nie podszywają się pod zwykłe liczby całkowite — ale RTL Delphi nie pomoże ci tutaj, bo VarAsType(Integer, varError) rzuca wyjątek konwersji. Działająca konstrukcja ustawia TVarData.VType i TVarData.VError wprost. Daty trzymają tę samą dyscyplinę w przeciwną stronę: t="d" i ODF-owy typ wartości daty są jawnymi deklaracjami typu i stają się varDate, podczas gdy numeryczny bufor BIFF nie niesie żadnej flagi daty w ogóle i dlatego zostaje Double. HotXLS nigdy nie zgaduje daty z formatu liczbowego komórki, bo format liczbowy to prezentacja, a bufor to dane. ODF dodaje jeszcze jeden przypadek wart znajomości — office:value-type="void" wyraża bufor, który jest obecny, ale nie niesie wartości, a ponieważ ODF nie ma typu wartości błędu, tekst wyglądający jak błąd jest zachowywany jako tekst, zamiast być awansowany na błąd

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

Czy formuły współdzielone dzielą swoje buforowane wartości?

Nie, i założenie odwrotne to sposób, w jaki przemiatanie kończy z raportowaniem tej samej liczby dla całej kolumny. OOXML-owa formuła współdzielona dzieli wyłącznie wyrażenie formuły i optymalizację przechowywania; każda komórka członkowska nadal ma własne <v>. HotXLS nigdy więc nie propaguje bufora korzenia na naśladowcę, który przybył bez wartości, a naśladowca wczytany jako xlfcsMissing nadal raportuje xlfcsMissing po zapisie i ponownym otwarciu. Jeśli zastanawiasz się, jak grupa jest w ogóle przechowywana i rozwijana, mechanika atrybutu si formuły współdzielonej i jej rozwinięcia jest omówiona osobno; dla odczytu bufora reguła sprowadza się do jednej linii — pytaj każdą komórkę, nie ufaj niczemu, o co nie zapytałeś

Odczyt wartości buforowanych, zunifikowany czytnik między silnikami i silnik przeliczania, którego możesz wybrać, by nie wywoływać, wszystkie wychodzą w standardowym komponencie arkuszowym HotXLS Delphi dla Delphi i C++Buildera, bez zależności od Excela albo od jakiegokolwiek serwera automatyzacji OLE; strona produktu niesie pełną referencję API punktów wejścia skoroszytu i czytnika pokazanych tutaj