Stopp at XLS-lagring stille regner om formler i Delphi

HotXLS, det innebygde Excel-biblioteket for Delphi og C++Builder, lagrer en klassisk BIFF8-.xls-arbeidsbok cache-first: TXLSWorksheet.WriteFormula spør TXLSWorkbook.TryGetCachedFormulaValue om verdien Excel lagret ved siden av hver formel, og kaller bare evaluatoren når den cachen mangler eller er ugyldiggjort. En arbeidsbok du åpnet og aldri rørte, lagrer de samme tallene tilbake, og ferske resultater krever ett eksplisitt Recalculate-kall i stedet for å være en skjult sideeffekt av SaveAs

Feilen som tvang denne kontrakten fram i lyset, var pinlig liten. En korpusfil som heter nested-subtotals.xls, har en totalsum i R2C4 med en cachet verdi på 37. Åpne den med HotXLS, spør TryGetCachedFormulaValue om cellen, få 37. Lagre den uten å endre en eneste celle, åpne den lagrede kopien, still det samme spørsmålet, få 67. Ingenting i API-en var blitt bedt om å regne ut noe, likevel hadde et tall i filen flyttet seg nøyaktig 30 — og 30 er tilfeldigvis summen av de to gruppedelsummene, 10 og 20, som ligger inne i området totalsummen dekker

Hvorfor endrer lagring av en XLS-fil en formelverdi?

To uavhengige feil måtte stille opp på rekke for at 37 skulle bli 67, og å rette bare én av dem ville ha skjult den andre. Den første var strukturell: den klassiske skriveren regnet om hver formel ved hver lagring. Den andre var en typesjekk som aldri kunne være sann for en formel lastet fra disk, og som gjorde at evaluatoren telte nestede SUBTOTAL-celler dobbelt. Korpusfilen var rett og slett det første inndataet der en omregning ved lagring ga et annet svar enn Excel, og noen sammenlignet de to. Den strukturelle feilen er lett å beskrive: før v2.382.3 hentet TXLSWorksheet.WriteFormula og søskenfunksjonen for delte formler, WriteFormulaWithTExp, det åtte byte store FormulaValue-feltet i hver Formula-record ved å kalle TXLSWorkbook.GetFormulaValue, som er evaluatoren. Cachen som ParseFormula omhyggelig hadde dekodet fra kildefilen ved innlasting, ble aldri konsultert på vei ut. I praksis var hver lagring en full omregning der API-et for omregning på arbeidsboknivå ble omgått, så ingenting du kunne stille inn på arbeidsboken, ville ha stoppet det. Ethvert sted der evaluatoren i HotXLS var uenig med Excel, enten en legitimt ustøttet funksjon eller en ren feil, ble en stille dataendring ved lagring

Den andre feilen bodde i tilbakekallet for nestede delsummer som evaluatoren bruker. Excel definerer hver SUBTOTAL-form som å ignorere celler der formelen selv er en annen SUBTOTAL, så kalkulatoren i lxCalc.pas armerer FIgnoreSubtotalCells under aggregeringen og spør arbeidsboken, gjennom TXLSWorkbook.GetClassicIsSubtotalCell, om hver celle i området er en slik. Det tilbakekallet hentet formelteksten som en Variant og testet den med VarType(f) = varOleStr. Teksten kommer tilbake fra GetUnCompiledFormula som en Delphi-String, og en String som tilordnes en Variant, er varUString, aldri varOleStr. Predikatet var usant for hver celle i hver innlastet fil, gruppedelsummene ble rullet inn i totalsummen en gang til, og ved en lagring som regnet om alt, ble 10 + 20 + 7 til 67

// HotXLS 2.381 og tidligere: en formel-Variant bygget fra en String
// er varUString, så denne sammenligningen lyktes aldri
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr godtar varString, varOleStr og varUString,
// og AGGREGATE utelukkes fra omsluttende delsummer slik Excel gjør
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(');

v2.382.0 leverte VarIsStr-rettelsen og lærte, mens den først var i den samme funksjonen, tilbakekallet at AGGREGATE-celler også utelukkes fra omsluttende delsummer. Det alene fikk korpuspåstanden til å passere, fordi den omregnede 37 nå matchet den innlastede 37. Det gjorde ikke biblioteket ærlig: lagringen regnet fortsatt om, og testen var bare grønn fordi evaluatoren tilfeldigvis var enig med Excel på akkurat den filen. Reglene for hvilke celler SUBTOTAL og AGGREGATE hopper over, inkludert skjulte rader, er dekket i artikkelen om SUBTOTAL, AGGREGATE og skjulte rader; det som betyr noe her, er at ingen evaluator bør få stemme på en fil du ikke ba den regne ut

Hva garanterer Excel om cachede verdier ved lagring?

Excel behandler en lagring som et øyeblikksbilde, ikke som en beregningshendelse. Verdien som skrives inn i FormulaValue-feltet i en Formula-record ([MS-XLS] §2.4.127, oppsett i §2.5.133), er det cellen viser akkurat nå, noe som i manuell beregningsmodus kan være årevis gammelt, og Excel skriver den likevel trofast. Omregning er en separat operasjon med sin egen utløser. HotXLS følger nå den samme regelen for klassiske lagringer: WriteFormula og WriteFormulaWithTExp kaller TryGetCachedFormulaValue først, tar CacheInfo.Value når tilstanden er xlfcsLoaded eller xlfcsCalculated, og faller bare videre til GetFormulaValue for xlfcsMissing og xlfcsInvalidated. Lesesiden av denne kontrakten, inkludert hva hver tilstand betyr og hvorfor en cachet tom verdi eller False fortsatt teller som en verdi, er beskrevet i Les cachede formelverdier fra Excel i Delphi uten omregning

Reserveveien beholdes med vilje og er ikke fjernet. En formel du tilordnet i denne økten gjennom Cells[Row, Col].Formula, kommer uten cache, og en formel du erstattet på en innlastet celle, merkes xlfcsInvalidated av _SetCompiledFormula; begge evalueres ved lagring nøyaktig som før, så en generert arbeidsbok åpnes fortsatt i Excel med tall i seg. Når ikke engang evaluatoren klarer å produsere en verdi, sender skriveren ut en null-payload og setter fAlwaysCalc (grbit bit 0 i §2.4.127), slik at Excel regner om cellen ved åpning i stedet for å stole på plassholderen

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 1-basert ark, rad og kolonne: R2C4 på det første arket
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // ingen evaluator innblandet for cachede celler
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 for nested-subtotals.xls
    // En lagring som regnet om, ville ha skrevet 67 her
  finally
    Book.Free;
  end;
end;

Hvor holder roten i en delt BIFF-formel den cachede verdien sin?

I sin egen Formula-record, som enhver annen formelcelle, og nettopp det gjorde rotcellen i en delt gruppe til det ene stedet der cache-first-lagring fortsatt tapte. En delt formel i BIFF8 lagres som en ShrFmla-record ([MS-XLS] §2.4.260) som følger Formula-recorden til cellen øverst til venstre, og hver medlemscelle, roten inkludert, bærer en rgce som består av et enkelt PtgExp-token (§2.5.198): første byte i det parserte uttrykket er $01, etterfulgt av rad og kolonne for rotcellen. Oppfølgercellene er selvstendige — HotXLS leser hver enkelts FormulaValue og løser uttrykket ved å slå opp rotens kompilerte formel. Rotcellen er annerledes, fordi uttrykket ikke finnes ennå når Formula-recorden dens parses; det kommer én record senere

Det gapet på én record er der cachen forsvant. TXLSReader.ParseFormula dekoder den cachede verdien og husker, når den ser en PtgExp der koordinatene er lik cellens egne, cellen i FSharedFormulaRow og FSharedFormulaCol og publiserer cachen til cellen. Når ShrFmla-recorden ($04BC) kommer, kompilerer ParseSharedFormula uttrykket og installerer det med _SetCompiledFormula, og _SetCompiledFormula gjør det den må gjøre ved enhver formelendring: den tømmer FCachedFormulaValue og nullstiller tilstanden til xlfcsMissing. Rotens innlastede 37 ble derfor kastet før noen kunne lese den, TryGetCachedFormulaValue rapporterte roten som ucachet, og den cache-first-skriveren falt lydig tilbake på evaluatoren for nøyaktig den cellen alle så på. Array-recorden (§2.4.4) har samme rekkefølge og hadde det samme hullet

Rettelsen i v2.382.3 legger til et tredje felt, FSharedFormulaCachedValue, ved siden av de ventende rotkoordinatene. ParseFormula gjemmer den dekodede cachen der når den gjenkjenner en rot, og både ParseSharedFormula og ParseArrayFormula spiller den av gjennom _SetCellCachedFormulaValue rett etter at det kompilerte uttrykket er installert, og nullstiller deretter mellomlageret til Unassigned. String-varianten av cachen påvirkes ikke av noe av dette, fordi nyttelasten kommer i en egen String-record og rutes etter cellekoordinater, ikke etter recordrekkefølge. Hvis du jobber med OOXML-siden av det samme konseptet, forklarer artikkelen om si-utvidelse av delte formler i XLSX hvorfor pakkeformatet ikke har det tilsvarende ordensproblemet, men sine egne utvidelsesfallgruver

Hvorfor trenger oppfølgerne i en delt formel en relativ forskyvning?

Fordi uttrykket som er lagret i ShrFmla, er skrevet relativt til rotcellen, og en oppfølger som gjenbruker det ordrett, evaluerer rotens referanser i stedet for sine egne. Den gamle leseren installerte Value.GetCopy() på hver oppfølger, en dyp kopi uten forskyvning, så en gruppe med rot i B1 og =A1*3 ga hver oppfølger =A1*3 også. Cache-first-lagring maskerte faktisk dette for innlastede filer, siden oppfølgerne hadde sin egen FormulaValue og aldri trengte uttrykket for å lagres riktig; det dukket opp i det øyeblikket noe ble regnet om. Leseren installerer nå TXLSCompiledFormula.GetCopy(row - srow, col - scol), som går gjennom syntakstreet og forskyver hver relativ referanse med oppfølgerens avstand fra roten, så oppfølgeren i B2 eier en ekte =A2*3

Regresjonstesten som fester begge oppførslene, er verdt å lese fordi den nekter å la en tilfeldighet passere. Den bygger en arbeidsbok med =A1*3 og =A2*3 over inndataene 2 og 4, og injiserer deretter de bevisst feilaktige cacheverdiene 999 og 888 gjennom _SetCellCachedFormulaValue, én gang med UseSharedFormulas på og én gang av. Etter en lagring og ny innlasting må begge cellene fortsatt rapportere 999 og 888 — bevis for at lagringen ikke rørte verken rotens eller oppfølgerens cache. Først etter et eksplisitt Recalculate må de bli 6 og 12, bevis for at oppfølgerens forskjøvede uttrykk er korrekt. En test som sådde de sanne verdiene, ville også ha passert under den gamle skriveren, og det er hele poenget med å så feilaktige

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // endre et inndata

    // Innlastede cacher for avhengige formler blir IKKE ugyldiggjort av en
    // bokstavelig redigering, så en vanlig SaveAs ville beholde de gamle tallene.
    // Be om en omregning når du faktisk vil ha ferske resultater:
    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;

Hva cache-first-kontrakten ikke gjør for deg

Cache-first-lagring bevarer det som ble lastet inn; den sporer ikke om det innlastede fortsatt er sant. Å endre en literal som en formel avhenger av, merker avhengighetsgrafen som skitten for evaluatoren, men den lar xlfcsLoaded-cachen til den avhengige cellen stå, og den klassiske skriveren skriver gjerne den utdaterte verdien med mindre du kaller Recalculate eller leser cellens Value først, noe som regner den ut og flytter tilstanden til xlfcsCalculated. Dette er samme avveining Excel gjør i manuell beregningsmodus, og det er den riktige for en pipeline som åpner tredjepartsfiler, redigerer noen etiketter og lagrer — men det betyr at en arbeidsbok som redigerer inndata, selv må eie omregningstrinnet sitt eksplisitt. RecalcBeforeSave-policyen til XLSX-skriveren er uendret av dette arbeidet og har sin egen manuelle modus som bevarer cacher i samme ånd. To mindre grenser følger av dette: cache-first-veien hjelper bare celler med tilstanden xlfcsLoaded eller xlfcsCalculated; en generator som skriver formler og aldri evaluerer dem, betaler fortsatt for én evaluering per celle ved lagring, nøyaktig som før. Og rettelsen av nestede delsummer retter hvilke celler evaluatoren hopper over, ikke hver funksjon evaluatoren implementerer — en fil der HotXLS ikke kan regne ut formlene identisk med Excel, er nå trygg å kjøre tur-retur urørt, men et bevisst Recalculate på den filen vil fortsatt gi bibliotekets svar og ikke Excels, og du bør sammenligne de to før du stoler på en omregnet lagring

Cache-first-lagring av klassiske filer, de gjenopprettede cacheverdiene for røttene i delte formler og array-formler, den relative referanseforskyvningen for oppfølgere av delte formler og de korrigerte nestingsreglene for SUBTOTAL og AGGREGATE leveres alle i den vanlige HotXLS Delphi Spreadsheet Component for Delphi og C++Builder, uten avhengighet til Excel eller noen OLE-automatiseringsserver; produktsiden har full API-referanse for arbeidsboken, cachelsereren og inngangspunktene for omregning som brukes her