Hodnoty vzorcov Excelu z cache v Delphi bez prepočtu

HotXLS, natívna Excel knižnica pre Delphi a C++Builder, číta hodnotu, ktorú Excel už uložil vedľa vzorca, cez TryGetCachedFormulaValue a IXLSFormulaCacheReader. Žiadny vstupný bod nezavolá kalkulačku, nedešifruje tokeny vzorca, neaktualizuje dirty stav ani nič nezapisuje späť do modelu, takže zošit, ktorý len čítate, ostáva presne taký, aký ste ho otvorili

Scenár, ktorý to riadi, je nudný a extrémne bežný. Nočná úloha otvorí pár stoviek zošitov vyrobených niekým iným, vytiahne jeden stĺpec súčtov z každého a tlačí čísla do skladu dát. Súčty už v súboroch sedia — Excel ich spočítal a uložil. No v momente, keď úloha požiada formulovú bunku o jej hodnotu, knižnica, ktorá má na tú otázku len jednu odpoveď, stavia graf závislostí a vyhodnocuje celý list, a úloha, ktorá mala byť viazaná na I/O, sa zmení na benchmark výpočtu

Prečo čítanie formulovej bunky stojí celý prepočet?

Pretože value getter na formulovej bunke je požiadavka vyprodukovať hodnotu a jediný univerzálne správny spôsob, ako ju vyprodukovať, je vyhodnotiť vzorec. To je správna predvoľba pre aplikáciu, ktorá zošity upravuje, a zlá predvoľba pre pipeline, ktorá ich extrahuje. Horšie je, že vyhodnotenie nie je bez vedľajších účinkov: zapisuje výsledky späť do buniek, preklápa dirty vlajky a môže sa rozlíšiť inak ako produkujúca aplikácia, keď funkcia nie je podporovaná alebo externá referencia je rozbitá. Úloha, ktorú ste operačnému tímu popísali ako read-only, mlčky produkuje zošit, ktorý sa už nezhoduje s tým na disku, a ak ho niečo neskôr uloží, súbor na disku sa zmení tiež

Čítanie z cache je druhá polovica kontraktu. Odpovedá na užšiu otázku — čo tu produkujúca aplikácia uložila? — a odmieta odpovedať na čokoľvek iné. Keď skutočne chcete čísla ako svieže, HotXLS vám aj tak dáva inkrementálny prepočet riadený grafom závislostí; pointa je, že extrakcia a vyhodnotenie majú byť dve rôzne volania, nie jedno volanie s dvoma náladami

Tri ortogonálne skutočnosti o jednej bunke

Najprv záver: uložená hodnota vzorca nesie tri nezávislé skutočnosti a ich zrútenie do jediného Variantu stráca informácie, ktoré potrebujete. TXLSFormulaCacheInfo ich drží oddelené ako State, Kind a Value. TXLSFormulaCacheState zaznamenáva pôvod naprieč piatimi prípadmi — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated a xlfcsInvalidated — zatiaľ čo TXLSFormulaCacheValueKind klasifikuje payload ako xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean alebo xlfcvError. Toto oddelenie je to, čo umožňuje prítomnosť hlásiť úprimne: uložená prázdna bunka, uložený prázdny reťazec, uložené False, uložená nula a uložená chyba sú všetko skutočné hodnoty, takže prítomnosť sa nikdy nedá odvodiť z VarIsEmpty ani VarIsNull. TryGetCachedFormulaValue vracia True len pre xlfcsLoaded a xlfcsCalculated a aj pri vrátení False naplní diagnostikovateľný stav

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row a Col sú tu všetky 1-based
    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;

Prečo uložená hodnota chýba?

Existujú presne štyri dôvody, prečo TryGetCachedFormulaValue vráti False, a stav vám povie, ktorý platí. xlfcsNotFormula znamená, že bunka drží literál alebo vôbec nič, a súradnice mimo rozsahu sa zrútia do tej istej odpovede. xlfcsMissing znamená, že bunka skutočne je vzorec, ale producent preň neuložil žiadny value payload — bežný výsledok, keď generátor zapisuje vzorce a nechá Excel doplniť výsledky pri prvom otvorení. xlfcsInvalidated znamená, že text vzorca bol po načítaní vymenený, takže hodnota, ktorá tam bývala, popisuje výraz, ktorý už neexistuje. xlfcsCalculated je naproti tomu úspešný prípad: označuje hodnotu, ktorú váš vlastný kód alebo evaluator HotXLS vyprodukoval počas tejto sedenia, na rozdiel od xlfcsLoaded, ktorá prišla zo súboru

Úprimnosť o chýbajúcej cache záleží viac než jej prelepenie. HotXLS odmieta vymyslieť hodnotu a pri uložení je rovnako prísny — len xlfcsLoaded a xlfcsCalculated emitujú uloženú hodnotu, zatiaľ čo xlfcsMissing a xlfcsInvalidated zapisujú samotný vzorec namiesto zamrznutia zastaraného čísla do súboru. To vám necháva tri zdravé odpovede v pipeline: preskočiť riadok a zaznamenať medzeru, zámerne prepočítať ten jeden zošit a prijať náklady, alebo vyhodnotiť a zosúladiť. Ak vyhodnotené číslo nesúhlasí s tým, čo by produkujúca aplikácia napísala, tracer vyhodnocovania vzorcov je nástroj na zistenie, kde sa tie dva výpočty rozídu, namiesto hádania z výsledku

Jeden čítačka naprieč klasickým, OOXML a ODF engine

Pipeline by nemala záležať na tom, či súbor, ktorý práve otvorila, bol BIFF, OOXML alebo ODF. IXLSFormulaCacheReader je jediný read-only vstupný bod pre všetky tri: TXLSWorkbook.CreateFormulaCacheReader aj TXLSXWorkbook.CreateFormulaCacheReader vracajú ľahký adaptér nad riedkym vyhľadávaním buniek, ktorý každý engine už používa, s identickými súradnicami listu, riadku a stĺpca 1-based. Triedy zošitov zámerne samy neimplementujú toto rozhranie — referencia rozhrania na zošit by zmenila jeho sémantiku vlastníctva a dovolila by volajúcim preklznúť okolo lifetime lease. Namiesto toho zničenie zošita vymaže surový ukazovateľ vo vnútri tohto lease a každý čítačka, ktorú váš kód ešte drží, vyvolá EXLSFormulaCacheReaderInvalidated pri ďalšom dopyte namiesto dereferencovania uvoľnenej pamäte. Je to fail-fast kontrola životnosti, nie záruka konkurencie

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);
  // Žiadna kalkulačka nebežala, žiadna dirty vlajka sa nepohnula, Book je nezmenený
end;

Kde uložené bajty naozaj žijú

Pre klasické súbory .xls je cache pole FormulaValue záznamu Formula, osem bajtov popísaných v [MS-XLS] §2.5.133. Keď horné slovo rovná sa $FFFF, payload nie je IEEE 754 double, ale tagovaný variant a rozloženie sa dá ľahko pokaziť jemne: typ variantu sedí v val[0] a payload booleanu alebo BErr sedí v val[2], s val[1] nedefinovaným. HotXLS predtým čítal payload z val[1], čo je ten druh off-by-one, ktorý sa vynorí len na špecifických súboroch, ktoré cachujú boolean alebo chybu namiesto čísla. Čítačka a zdieľaný formulový zápisovač sa teraz zhodujú na rovnakých ofsetoch, takže uložené TRUE prežije načítanie a uloženie nedotknuté namiesto rozpadu do šumu

Typová vernosť v balíkových formátoch je samostatný problém s vlastnou pascou. V OOXML visí uložená hodnota na elemente c ako <v> s atribútom t pomenuvajúcim typ podľa ECMA-376 Part 1 §18.3.1.4. HotXLS číta t="e" rovno do Variantu varError a mapuje ho späť na štandardný text chyby pri uložení, takže chyby sa nikdy neprezúvajú za obyčajné celé čísla — ale Delphi RTL vám tu nepomôže, pretože VarAsType(Integer, varError) vyvolá konverznú výnimku. Fungujúca konštrukcia nastavuje TVarData.VType a TVarData.VError priamo. Dátumy nasledujú rovnakú disciplínu opačným smerom: t="d" a ODF dátový hodnotový typ sú explicitné deklarácie typu a stanú sa varDate, zatiaľ čo BIFF numerická cache nenesie žiadnu dátovú vlajku a preto ostáva Double. HotXLS nikdy neháda dátum z číselného formátu bunky, pretože číselný formát je prezentácia a cache sú dáta. ODF pridáva ešte jeden prípad, ktorý stojí za poznanie — office:value-type="void" vyjadruje cache, ktorá je prítomná, ale nenesie žiadnu hodnotu, a keďže ODF nemá chybový hodnotový typ, text vyzerajúci ako chyba sa zachováva ako text namiesto povýšenia 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;

Zdieľajú zdieľané vzorce svoje uložené hodnoty?

Nie a predpoklad opaku je spôsob, ako sweep skončí hlásiť to isté číslo pre celý stĺpec. Zdieľaný vzorec OOXML zdieľa výraz vzorca a optimalizáciu ukladania; každá členská bunka stále vlastní vlastné <v>. HotXLS preto nikdy nepropaguje cache koreňového člena na followera, ktorý prišiel bez hodnoty, a follower, ktorý sa načítal ako xlfcsMissing, aj po uložení a opätovnom otvorení hlási xlfcsMissing. Ak pracujete cez to, ako sa skupina najprv ukladá a rozširuje, mechanika atribútu si zdieľaného vzorca a jeho expanzie je pokrytá samostatne; pre čítanie cache sa pravidlo redukuje na jeden riadok — pýtajte každú bunku, neverte ničomu, o čo ste si nepýtali

Čítanie uložených hodnôt, zjednotený cross-engine čítačka a engine prepočtu, ktorý sa rozhodnete nezavolať, sa všetky dodávajú v štandardnom HotXLS Delphi Spreadsheet Component pre Delphi a C++Builder, bez závislosti na Exceli alebo akomkoľvek OLE automatizačnom serveri; produktová stránka nesie kompletnú API referenciu vstupných bodov zošita a čítačky zobrazených tu