Cachované hodnoty vzorců Excelu v Delphi bez přepočtu

HotXLS, nativní Excel knihovna pro Delphi a C++Builder, čte hodnotu, kterou Excel už uložil vedle vzorce, přes TryGetCachedFormulaValue a IXLSFormulaCacheReader. Žádný ze vstupních bodů nezavolá kalkulátor, nedekompiluje tokeny vzorce, neaktualizuje stav špinavosti ani nic nezapisuje zpět do modelu, takže sešit, který jen čtete, zůstává přesně takový, jak jste ho otevřeli

Scénář, který to žene, je nudný a extrémně obvyklý. Noční úloha otevře pár stovek sešitů od někoho jiného, vytáhne z každého jeden sloupec součtů a postrčí čísla do skladu dat. Součty už v souborech leží — Excel je spočítal a uložil. Jenže ve chvíli, kdy úloha poprosí buňku vzorce o hodnotu, knihovna, která na tu otázku má jen jednu odpověď, postaví graf závislostí a vyhodnotí celý list a úloha, která měla být vázaná na I/O, se promění v benchmark výpočtů

Proč čtení buňky vzorce stojí úplný přepočet?

Protože getter hodnoty na buňce vzorce je žádost o vytvoření hodnoty a jediný univerzálně správný způsob, jak jednu vytvořit, je vyhodnotit vzorec. To je správná výchozí volba pro aplikaci, která sešity edituje, a špatná pro pipeline, která je vytahuje. Hůř: vyhodnocení není bez vedlejších účinků — zapisuje výsledky zpět do buněk, překlápí příznaky špinavosti a může se rozřešit jinak než v produkující aplikaci, když funkce není podporovaná nebo externí reference je rozbitá. Úloha, kterou jste provoznímu týmu popsali jako read-only, tiše vyprodukuje sešit, který už nesedí s tím na disku, a když ho cokoliv později uloží, mění se i soubor na disku

Čtení z cachu je druhá půlka smlouvy. Odpovídá na užší otázku — co tady uložila produkující aplikace? — a odmítá odpovídat na cokoliv jiného. Když opravdu chcete čerstvá čísla, HotXLS vám pořád dá inkrementální přepočet řízený grafem závislostí; jde o to, že extrakce a vyhodnocení mají být dvě různá volání, ne jedno volání se dvěma náladami

Tři ortogonální fakta o jedné buňce

Nejdřív závěr: cachovaná hodnota vzorce nese tři nezávislá fakta a jejich sbalení do jediného Variant ztrácí informace, které potřebujete. TXLSFormulaCacheInfo je drží rozebrané jako State, Kind a Value. TXLSFormulaCacheState zaznamenává původ v pěti případech — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated a xlfcsInvalidated — zatímco TXLSFormulaCacheValueKind klasifikuje náklad jako xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean nebo xlfcvError. To rozdělení je to, co dovoluje přítomnost hlásit poctivě: cachovaná mezera, cachovaný prázdný řetězec, cachované False, cachovaná nula a cachovaná chyba jsou všechny reálné hodnoty, takže přítomnost nikdy nelze odvodit z VarIsEmpty ani VarIsNull. TryGetCachedFormulaValue vrací True jen pro xlfcsLoaded a xlfcsCalculated a i při False vyplní diagnostikovatelný stav

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row i Col jsou tu všechny od jedničky
    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;

Proč cachovaná hodnota chybí?

Důvodů, proč TryGetCachedFormulaValue vrátí False, je přesně čtyři a stav vám řekne, který platí. xlfcsNotFormula znamená, že buňka drží literál nebo vůbec nic, a souřadnice mimo rozsah se sbalí do téže odpovědi. xlfcsMissing znamená, že buňka vzorec opravdu je, ale producent pro ni neuložil žádný náklad hodnoty — obvyklý výstup generátoru, který píše vzorce a nechá Excel doplnit výsledky při prvním otevření. xlfcsInvalidated znamená, že text vzorce byl po načtení vyměněn, takže hodnota, která tam bývala, popisuje výraz, který už neexistuje. xlfcsCalculated je naopak případ úspěchu: značí hodnotu, kterou za tohoto sezení vytvořil váš vlastní kód nebo vyhodnocovač HotXLS, na rozdíl od xlfcsLoaded, která přišla ze souboru

Poctivost k chybějícímu cache znamená víc než přetírání přes něj. HotXLS odmítá hodnotu vymyslet a při ukládání je stejně přísný — cachovanou hodnotu vydávají jen xlfcsLoaded a xlfcsCalculated, zatímco xlfcsMissing a xlfcsInvalidated zapíší samotný vzorec místo zmrznutí zvětralého čísla do souboru. Zbývá vám tedy tři zdravé reakce v pipeline: řádek přeskočit a mezeru zaznamenat, ten jeden sešit záměrně přepočítat a nést cenu, nebo vyhodnotit a vyrovnat. Pokud vyhodnocené číslo nesedí s tím, co by zapsala produkující aplikace, tracer vyhodnocování vzorců je nástroj, který zjistí, kde se obě výpočty rozešly, místo hádání z výsledku

Jeden čtenář napříč enginy classic, OOXML a ODF

Pipeline by neměla řešit, jestli soubor, který právě otevřela, byl BIFF, OOXML nebo ODF. IXLSFormulaCacheReader je jediný read-only vstupní bod pro všechny tři: TXLSWorkbook.CreateFormulaCacheReader i TXLSXWorkbook.CreateFormulaCacheReader vrátí lehký adaptér nad řídkým vyhledáváním buněk, které každý engine už používá, s totožnými souřadnicemi listu, řádku a sloupce od jedničky. Třídy sešitů záměrně neimplementují to rozhraní samy — reference rozhraní na sešit by změnila jeho sémantiku vlastnictví a dovolila volajícím uklouznout kolem pronájmu životnosti. Místo toho zničení sešitu vyčistí surový ukazatel uvnitř toho pronájmu a každý čtenář, který váš kód stále drží, při příštím dotazu hodí EXLSFormulaCacheReaderInvalidated místo dereference uvolněné paměti. Je to fail-fast kontrola životnosti, ne záruka souběhu

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);
  // Nespustil se žádný kalkulátor, nepohnul se žádný příznak špinavosti, Book zůstal nezměněn
end;

Kde cachované bajty doopravdy bydlí

U klasických souborů .xls je cache pole FormulaValue záznamu Formula, osm bajtů popsaných v [MS-XLS] §2.5.133. Když horní slovo se rovná $FFFF, náklad není IEEE 754 double, ale značkovaný variant a rozložení se dá jemně pokazit snadno: typ variantu sedí v val[0] a náklad boolean nebo BErr sedí v val[2], s val[1] nedefinovaným. HotXLS dřív četl náklad z val[1], což je taková odhyluka o jedničku, která vyleze jen na konkrétních souborech, které cachují boolean nebo chybu místo čísla. Čtenář i zapisovač sdílených vzorců teď souhlasí ve stejných posunech, takže cachované TRUE přežije načtení a uložení netknuté místo rozpadu v šum

Věrnost typů v balíčkových formátech je zvláštní problém s vlastní pastí. V OOXML se cachovaná hodnota věší na prvek c jako <v>, přičemž atribut t jmenuje typ podle ECMA-376 Part 1 §18.3.1.4. HotXLS čte t="e" rovnou do Variantu varError a při ukládání ho mapuje zpět na standardní text chyby, takže chyby se nikdy nepřevlékají za obyčejná celá čísla — ale Delphi RTL vám tady nepomůže, protože VarAsType(Integer, varError) hodí konverzní výjimku. Fungující konstrukce nastavuje TVarData.VType a TVarData.VError přímo. Data sledují tutéž disciplínu opačným směrem: t="d" a hodnotový typ data ODF jsou explicitní deklarace typu a stávají se varDate, zatímco číselný cache BIFF nenese žádný příznak data a zůstává tedy Double. HotXLS nikdy nehádá datum z číselného formátu buňky, protože číselný formát je prezentace a cache jsou data. ODF přidává jeden případ, který stojí za známost — office:value-type="void" vyjadřuje cache, která je přítomná, ale nenese žádnou hodnotu, a protože ODF nemá hodnotový typ chyby, text vypadající jako chyba se uchovává jako text místo povýšení na chybu

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;

Sdílejí sdílené vzorce své cachované hodnoty?

Ne, a domněnka opačná je způsob, jakým sken skončí s tomtéž číslem pro celý sloupec. Sdílený vzorec OOXML sdílí jen výraz vzorce a optimalizaci ukládání; každá členská buňka si stále vlastní své <v>. HotXLS proto nikdy nerozšiřuje cache kořenového člena na následovníka, který dorazil bez hodnoty, a následovník, který se načetl jako xlfcsMissing, i po uložení a znovuotevření hlásí xlfcsMissing. Kdybyste rozplétali, jak se skupina vůbec ukládá a rozvíjí, mechanika atributu si sdíleného vzorce a jeho rozvoje je pokrytá zvlášť; pro čtení cache se pravidlo redukuje na jeden řádek — zeptejte se každé buňky, nevěřte ničemu, o co jste se neptali

Čtení cachovaných hodnot, sjednocený mezienginový čtenář i výpočetní engine, který si můžete vybrat nezavolat, všechny dorazí ve standardní HotXLS Delphi Spreadsheet Component pro Delphi a C++Builder, bez závislosti na Excelu nebo na jakémkoli OLE automatizačním serveru; produktová stránka nese úplnou referenci API pro vstupní body sešitu a čtenáře ukázané tady