XLS-mentés Delphiben: csendes képlet-újraszámítás nélkül

A HotXLS, a natív Delphi és C++Builder Excel-könyvtár, cache-first módon menti a klasszikus BIFF8 .xls munkafüzetet: a TXLSWorksheet.WriteFormula elkéri a TXLSWorkbook.TryGetCachedFormulaValue metódustól azt az értéket, amelyet az Excel minden képlet mellé eltárolt, és csak akkor hívja az értékelőt, ha ez a cache hiányzik vagy érvénytelenítve van. Egy munkafüzet, amelyet megnyitottunk és soha nem nyúltunk hozzá, ugyanazokat a számokat menti vissza, a friss eredményekhez pedig egyetlen explicit Recalculate hívás kell, nem pedig a SaveAs rejtett mellékhatása

A hiba, amely kikényszerítette ezt a szerződést, kínosan kicsi volt. A nested-subtotals.xls nevű korpuszfájl egy végösszeget tart az R2C4-ben, amelynek a cache-elt értéke 37. Nyissa meg a HotXLS-szel, kérdezze le a TryGetCachedFormulaValue hívással a cellát, és 37-et kap. Mentse el egyetlen cella megváltoztatása nélkül, nyissa meg a mentett másolatot, tegye fel ugyanezt a kérdést, és 67-et kap. Az API-tól senki nem kért semmilyen számítást, a fájlban lévő szám mégis pontosan 30-cal mozdult el — a 30 pedig épp a végösszeg által fedett tartományban lévő két csoport-részösszeg, a 10 és a 20 összege

Miért változik meg egy képlet értéke XLS-fájl mentésekor?

Két egymástól független hibának kellett összeállnia ahhoz, hogy a 37-ből 67 legyen, és bármelyik egyedüli javítása elrejtette volna a másikat. Az első szerkezeti volt: a klasszikus író minden mentéskor újraszámolt minden képletet. A második egy típusellenőrzés volt, amely soha nem lehetett igaz a lemezről betöltött képletre, és emiatt az értékelő kétszer számolta a beágyazott SUBTOTAL cellákat. A korpuszfájl egyszerűen az első olyan bemenet volt, ahol a mentéskori újraszámítás más eredményt adott, mint az Excel, és valaki össze is vetette a kettőt. A szerkezeti hiba könnyen elmondható: a v2.382.3 előtt a TXLSWorksheet.WriteFormula és a megosztott képletekhez tartozó testvére, a WriteFormulaWithTExp minden Formula-rekord nyolcbájtos FormulaValue mezőjét a TXLSWorkbook.GetFormulaValue hívással szerezte meg, ami maga az értékelő. Az a cache, amelyet a ParseFormula betöltéskor gondosan dekódolt a forrásfájlból, soha nem került szóba a kifelé vezető úton. Gyakorlatilag minden mentés teljes újraszámítás volt, amely megkerülte a munkafüzet szintű újraszámítási API-t, így semmi, amit a munkafüzeten be lehetett volna állítani, nem állította volna meg. Bármely pont, ahol a HotXLS értékelője eltért az Exceltől — akár egy jogosan nem támogatott függvény, akár egy egyszerű hiba —, csendes adatváltozássá vált mentéskor

A második hiba az értékelő által használt, beágyazott részösszeghez tartozó callbackben lakott. Az Excel úgy definiálja a SUBTOTAL minden formáját, hogy figyelmen kívül hagyja azokat a cellákat, amelyeknek a saját képlete szintén SUBTOTAL, így a lxCalc.pas-ban lévő számológép az aggregálás alatt felállítja az FIgnoreSubtotalCells jelzőt, és a TXLSWorkbook.GetClassicIsSubtotalCell útvonalon megkérdezi a munkafüzettől, hogy a tartomány minden cellája ilyen-e. Az a callback Variantként kérte le a képletszöveget, és a VarType(f) = varOleStr feltétellel vizsgálta. A szöveg a GetUnCompiledFormula hívásból Delphi String alakban jön vissza, a Variantba tett String pedig varUString, soha nem varOleStr. Az állítás minden betöltött fájl minden cellájára hamis volt, a csoport-részösszegek másodszor is bekerültek a végösszegbe, és egy mindent újraszámoló mentésnél a 10 + 20 + 7-ből 67 lett

// HotXLS 2.381 és korábbi: a Stringből épített képlet-Variant
// varUString lesz, így ez az összehasonlítás soha nem sikerült
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: a VarIsStr elfogadja a varString, varOleStr és varUString típusokat,
// az AGGREGATE pedig kimarad a körülvevő részösszegekből, ahogy az Excelben
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(');

A v2.382.0 szállította a VarIsStr javítását, és még ugyanabban a függvényben megtanította a callbacknek azt is, hogy az AGGREGATE cellák szintén kimaradnak a körülvevő részösszegekből. Ez önmagában átvitte a korpusz állítását, mert az újraszámolt 37 most már egyezett a betöltött 37-tel. A könyvtárat viszont nem tette becsületessé: a mentés továbbra is újraszámolt, a teszt pedig csak azért volt zöld, mert az értékelő épp egyetértett az Excellel abban a konkrét fájlban. Hogy a SUBTOTAL és az AGGREGATE mely cellákat hagyja ki, a rejtett sorokat is beleértve, azt a SUBTOTAL és AGGREGATE rejtett sorokról szóló cikk tárgyalja; itt az a lényeg, hogy egyetlen értékelőnek sem szabad szavaznia egy olyan fájlról, amelyet nem kértünk meg a kiszámítására

Mit garantál az Excel a cache-elt értékekről mentéskor?

Az Excel a mentést pillanatképnek tekinti, nem számítási eseménynek. A Formula-rekord FormulaValue mezőjébe írt érték ([MS-XLS] §2.4.127, az elrendezést a §2.5.133 adja) az, amit a cella épp megjelenít, ami kézi számítási módban akár évek óta elavult is lehet, az Excel pedig ezt is hűen kiírja. Az újraszámítás külön művelet, saját kiváltó okkal. A HotXLS mostantól ugyanezt a szabályt követi a klasszikus mentéseknél: a WriteFormula és a WriteFormulaWithTExp először a TryGetCachedFormulaValue metódust hívja, xlfcsLoaded vagy xlfcsCalculated állapotnál a CacheInfo.Value értéket veszi, és csak xlfcsMissing és xlfcsInvalidated esetén esik vissza a GetFormulaValue hívásra. A szerződés olvasási oldali fele, beleértve azt, hogy mit jelent az egyes állapotok, és hogy egy cache-elt üres érték vagy a False miért számít mégis értéknek, a Excel cache-elt képletértékeinek olvasása Delphiben újraszámítás nélkül című írásban van kifejtve

A tartalékút szándékosan megmaradt, nem távolítottuk el. Az a képlet, amelyet ebben a munkamenetben a Cells[Row, Col].Formula úton rendeltünk hozzá, cache nélkül érkezik, a betöltött cellán lecserélt képletet pedig a _SetCompiledFormula xlfcsInvalidated állapotúra jelöli; mindkettő pontosan úgy értékelődik mentéskor, mint korábban, így egy generált munkafüzet továbbra is számokkal nyílik meg az Excelben. Ha még az értékelő sem tud értéket előállítani, az író nulla tartalmat bocsát ki, és beállítja az fAlwaysCalc jelzőt (a §2.4.127 grbit 0. bitje), hogy az Excel megnyitáskor újraszámolja a cellát a helyőrzőben való bizakodás helyett

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 1-alapú munkalap, sor és oszlop: R2C4 az első munkalapon
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // cache-elt cellák esetén az értékelő nem kerül képbe
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // a nested-subtotals.xls esetén Before.Value = After.Value = 37
    // egy újraszámoló mentés 67-et írt volna ide
  finally
    Book.Free;
  end;
end;

Hol tartja a cache-elt értékét egy BIFF megosztott képlet gyökere?

A saját Formula-rekordjában, akárcsak minden más képletcella, és pontosan ez tette a megosztott csoport gyökércelláját az egyetlen hellyé, ahol a cache-first mentés még mindig veszített. Egy megosztott képletet a BIFF8 a bal felső cella Formula-rekordját követő ShrFmla rekordban tárol ([MS-XLS] §2.4.260), és minden tagcella, a gyökeret is beleértve, egyetlen PtgExp tokenből álló rgce elemet hordoz (§2.5.198): a feldolgozott kifejezés első bájtja $01, amelyet a gyökércella sora és oszlopa követ. A követő cellák önmagukban megállók — a HotXLS mindegyikük FormulaValue értékét beolvassa, a kifejezést pedig a gyökér lefordított képletének felkeresésével oldja fel. A gyökércella más, mert amikor a Formula-rekordját feldolgozzuk, a kifejezés még nem létezik; egy rekorddal később érkezik

Ebben az egyrekordos résben tűnt el a cache. A TXLSReader.ParseFormula dekódolja a cache-elt értéket, és amikor olyan PtgExp-et lát, amelynek a koordinátái megegyeznek a celláéval, megjegyzi a cellát a FSharedFormulaRow és FSharedFormulaCol mezőkben, a cache-t pedig közzéteszi a cellán. Amikor megérkezik a ShrFmla rekord ($04BC), a ParseSharedFormula lefordítja a kifejezést, és a _SetCompiledFormula hívással telepíti, a _SetCompiledFormula pedig azt teszi, amit minden képletváltozásnál tennie kell: törli a FCachedFormulaValue értékét, és az állapotot xlfcsMissing-re állítja vissza. A gyökér betöltött 37-e így elveszett, mielőtt bárki elolvashatta volna, a TryGetCachedFormulaValue pedig a gyökérről azt jelentette, hogy nincs cache-elve, a cache-first író pedig kötelességtudóan visszaesett az értékelőre, mégpedig pontosan arra a cellára, amelyre mindenki kíváncsi volt. Az Array rekord (§2.4.4) ugyanezt a sorrendet követi, és ugyanez a lyuk volt benne

A v2.382.3 javítása egy harmadik mezőt ad hozzá, a FSharedFormulaCachedValue elemet, a függőben lévő gyökérkoordináták mellé. A ParseFormula oda teszi el a dekódolt cache-t, amikor felismer egy gyökeret, a ParseSharedFormula és a ParseArrayFormula pedig azonnal a lefordított kifejezés telepítése után visszajátssza a _SetCellCachedFormulaValue útvonalon, majd a tárolót Unassigned értékre állítja vissza. A cache String-változatát mindez nem érinti, mert a tartalma külön String-rekordban érkezik, és a cellakoordináták, nem a rekordsorrend alapján irányítódik. Ha ugyanennek a fogalomnak az OOXML oldalával dolgozik, az XLSX megosztott képletek si-kiterjesztéséről szóló cikk kifejti, miért nincs a csomagformátumban ezzel egyenértékű sorrendi probléma, saját kiterjesztési buktatói viszont vannak

Miért kell relatív eltolás a megosztott képletek követő celláinak?

Mert a ShrFmla rekordban tárolt kifejezés a gyökércellához viszonyítva íródik, és az a követő, amely szó szerint újrahasználja, a gyökér hivatkozásait értékeli ki a sajátjai helyett. A régi olvasó a Value.GetCopy() másolatot telepítette minden követőre, egy eltolás nélküli mély másolatot, így a B1-ben gyökerező, =A1*3 képletű csoport minden követőnek szintén a =A1*3 képletet adta. A cache-first mentés valójában elfedte ezt a betöltött fájloknál, mivel a követőknek megvolt a saját FormulaValue értékük, és a helyes mentéshez soha nem kellett a kifejezés; abban a pillanatban került elő, amikor valami újraszámolt. Az olvasó mostantól a TXLSCompiledFormula.GetCopy(row - srow, col - scol) másolatot telepíti, amely bejárja a szintaxisfát, és minden relatív hivatkozást eltol a követő gyökértől mért távolságával, így a B2-ben lévő követő valódi =A2*3 képletet birtokol

Az a regressziós teszt, amely mindkét viselkedést rögzíti, érdemes az elolvasásra, mert nem engedi meg, hogy egy véletlen egybeesés átmenjen. Épít egy munkafüzetet =A1*3 és =A2*3 képlettel a 2 és 4 bemeneteken, majd szándékosan hibás, 999-es és 888-as cache-eket fecskendez be a _SetCellCachedFormulaValue útvonalon, egyszer bekapcsolt, egyszer kikapcsolt UseSharedFormulas mellett. Mentés és újratöltés után mindkét cellának továbbra is 999-et és 888-at kell jelentenie — bizonyíték arra, hogy a mentés sem a gyökér, sem a követő cache-éhez nem nyúlt. Csak egy explicit Recalculate után kell 6-nak és 12-nek lenniük, ami azt bizonyítja, hogy a követő eltolt kifejezése helyes. Egy olyan teszt, amely a valódi értékeket ültette volna el, a régi író alatt is átment volna, és pontosan ezért kell hibásakat elültetni

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // egy bemenet megváltoztatása

    // A függő képletek betöltött cache-eit NEM érvényteleníti egy
    // literál szerkesztése, így egy sima SaveAs megtartaná a régi számokat.
    // Újraszámítást akkor kérjen, amikor tényleg friss eredményt akar:
    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;

Amit a cache-first szerződés nem tesz meg Ön helyett

A cache-first mentés megőrzi azt, ami betöltődött; nem követi nyomon, hogy ami betöltődött, még igaz-e. Ha egy olyan literált változtatunk meg, amelytől egy képlet függ, az az értékelő számára piszkosra állítja a függőségi gráfot, a függő cella xlfcsLoaded cache-ét viszont a helyén hagyja, a klasszikus író pedig boldogan kiírja azt az elavult értéket, hacsak nem hív Recalculate-et, vagy nem olvassa ki előbb a cella Value tulajdonságát, ami kiszámítja, és az állapotot xlfcsCalculated-re mozdítja. Ugyanezt a kompromisszumot köti az Excel a kézi számítási módban, és ez a helyes választás egy olyan folyamathoz, amely harmadik féltől származó fájlokat nyit meg, átír néhány címkét és ment — azt viszont jelenti, hogy az olyan munkafüzetnek, amely bemeneteket szerkeszt, explicit módon magának kell vállalnia az újraszámítási lépést. Az XLSX-író RecalcBeforeSave szabályzata ezzel a munkával nem változik, és megvan a saját kézi módja, amely ugyanebben a szellemben őrzi meg a cache-eket. Ebből két kisebb határ következik: a cache-first út csak azoknak a celláknak segít, amelyek állapota xlfcsLoaded vagy xlfcsCalculated; egy olyan generátor, amely képleteket ír és soha nem értékeli ki őket, továbbra is fizet cellánként egy kiértékelésért mentéskor, pontosan ahogy korábban. A beágyazott részösszeg javítása pedig azt igazítja helyre, hogy az értékelő mely cellákat hagyja ki, nem pedig az értékelő minden függvényét — egy olyan fájl, amelynek a képleteit a HotXLS nem tudja az Excelével azonosan kiszámítani, mostantól biztonságosan körbejárható érintetlenül, egy szándékos Recalculate viszont továbbra is a könyvtár válaszát adja majd az Excelé helyett, és a kettőt össze kell vetnie, mielőtt megbízik egy újraszámolt mentésben

A cache-first klasszikus mentés, a helyreállított megosztott és tömbképlet-gyökér-cache-ek, a megosztott követők relatív hivatkozás-eltolása és a helyreigazított SUBTOTAL és AGGREGATE beágyazási szabályok mind a standard HotXLS Delphi Spreadsheet Componentben érkeznek Delphihez és C++Builderhez, az Exceltől és bármely OLE automatizálási szervertől függetlenül; a termékoldal tartalmazza az itt használt munkafüzet-, cache-olvasó és újraszámítási belépési pontok teljes API-referenciáját