Stop XLS-gemninger i lydløst at genberegne formler i Delphi

HotXLS, det native Delphi- og C++Builder-Excel-bibliotek, gemmer en klassisk BIFF8 .xls-arbejdsbog cache først: TXLSWorksheet.WriteFormula spørger TXLSWorkbook.TryGetCachedFormulaValue om den værdi, Excel gemte ved siden af hver formel, og kalder kun evaluatoren, når den cache mangler eller er invalideret. En arbejdsbog, du åbnede og aldrig rørte, gemmer de samme tal tilbage, og friske resultater kræver ét eksplicit Recalculate-kald frem for at være en skjult sideeffekt af SaveAs

Bugen, der tvang denne kontrakt frem i lyset, var pinagtigt lille. En corpus-fil ved navn nested-subtotals.xls holder en grand total i R2C4, hvis cachede værdi er 37. Åbn den med HotXLS, spørg TryGetCachedFormulaValue om cellen, få 37. Gem den uden at ændre en eneste celle, åbn den gemte kopi, spørg det samme, få 67. Intet i API'en var blevet bedt om at beregne noget, og alligevel var et tal i filen flyttet sig med præcis 30 — og 30 er tilfældigvis summen af de to gruppe-subtotaler, 10 og 20, som ligger inden for det range, grand totalen dækker

Hvorfor ændrer en XLS-gemning en formelværdi?

To uafhængige defekter skulle falde på plads, før de 37 blev 67, og at rette kun én af dem ville have skjult den anden. Den første var strukturel: Den klassiske writer genberegnede hver formel ved hvert gem. Den anden var et type-tjek, der aldrig kunne være sandt for en formel indlæst fra disk, hvilket fik evaluatoren til at tælle indlejrede SUBTOTAL-celler dobbelt. Corpus-filen var simpelthen det første input, hvor en gemnings-genberegning gav et andet svar end Excel, og nogen sammenlignede de to. Den strukturelle defekt er let at sige: Før v2.382.3 hentede TXLSWorksheet.WriteFormula og dens shared-formula-søster WriteFormulaWithTExp det otte-byte FormulaValue-felt i hver Formula-record ved at kalde TXLSWorkbook.GetFormulaValue, som er evaluatoren. Cachen, som ParseFormula omhyggeligt havde dekodet fra kildefilen ved indlæsning, blev aldrig konsulteret på vejen ud. Reelt set var hvert gem en fuld genberegning med arbejdsbogens recalc-API omgået, så intet, du kunne sætte på arbejdsbogen, ville have standset det. Ethvert sted, hvor HotXLS-evaluatoren var uenig med Excel — en legitimt ikke-understøttet funktion eller en almindelig bug — blev til en lydløs dataændring ved gem

Den anden defekt boede i nested-subtotal-callbacken, evaluatoren bruger. Excel definerer hver SUBTOTAL-form som at ignorere celler, hvis egen formel er en anden SUBTOTAL, så kalkulatoren i lxCalc.pas aktiverer FIgnoreSubtotalCells under aggregering og spørger arbejdsbogen gennem TXLSWorkbook.GetClassicIsSubtotalCell, om hver celle i rangeet er én. Den callback hentede formelteksten som en Variant og testede den med VarType(f) = varOleStr. Teksten kommer tilbage fra GetUnCompiledFormula som en Delphi String, og en String tildelt en Variant er varUString, aldrig varOleStr. Prædikatet var falsk for hver celle i hver indlæst fil, gruppe-subtotaler blev rullet ind i grand totalen en anden gang, og ved et gem, der genberegnede alt, blev 10 + 20 + 7 til 67

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

// HotXLS 2.382.0: VarIsStr accepterer varString, varOleStr og varUString,
// og AGGREGATE udelukkes fra omsluttende subtotaler, som Excel gø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 shippede VarIsStr-fixet og lærte, mens den var i gang med samme funktion, callbacken, at AGGREGATE-celler også udelukkes fra omsluttende subtotaler. Det alene fik corpus-assertionen til at bestå, fordi de genberegnede 37 nu matchede de indlæste 37. Det gjorde ikke biblioteket ærligt: Gemningen genberegnede stadig, og testen var kun grøn, fordi evaluatoren tilfældigvis var enig med Excel i netop den fil. Reglerne for, hvilke celler SUBTOTAL og AGGREGATE springer over, inklusive skjulte rækker, er dækket i artiklen om SUBTOTAL og AGGREGATE med skjulte rækker; det, der tæller her, er, at ingen evaluator skal have en stemme over en fil, du ikke bad den om at beregne

Hvad garanterer Excel om cachede værdier ved gem?

Excel behandler et gem som et snapshot, ikke en beregningsevent. Værdien, der skrives ind i FormulaValue-feltet i en Formula-record ([MS-XLS] §2.4.127, layout i §2.5.133), er hvad end cellen aktuelt viser, hvilket i manuel beregningstilstand kan være år gammelt, og Excel skriver det alligevel trofast. Genberegning er en separat operation med sin egen trigger. HotXLS følger nu samme regel for klassiske gem: WriteFormula og WriteFormulaWithTExp kalder TryGetCachedFormulaValue først, tager CacheInfo.Value, når tilstanden er xlfcsLoaded eller xlfcsCalculated, og falder kun igennem til GetFormulaValue for xlfcsMissing og xlfcsInvalidated. Læseside-halvdelen af denne kontrakt, inklusive hvad hver tilstand betyder og hvorfor en cached blank eller False stadig tæller som en værdi, er beskrevet i at læse Excels cachede formelværdier i Delphi uden genberegning

Fallback-vejen er bevidst beholdt, ikke fjernet. En formel, du tildelte i denne session gennem Cells[Row, Col].Formula, ankommer uden cache, og en formel, du udskiftede på en indlæst celle, markeres xlfcsInvalidated af _SetCompiledFormula; begge evalueres ved gem-tid præcis som før, så en genereret arbejdsbog stadig åbnes i Excel med tal i. Når selv evaluatoren ikke kan producere en værdi, emitterer writeren en nul-payload og sætter fAlwaysCalc (grbit bit 0 i §2.4.127), så Excel genberegn cellen ved åbning i stedet for at stole på pladsholderen

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 1-baseret ark, række og kolonne: R2C4 på det første ark
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // ingen evaluator involveret 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
    // Et gem, der genberegnede, ville have skrevet 67 her
  finally
    Book.Free;
  end;
end;

Hvor opbevarer en BIFF shared formula-rod sin cachede værdi?

I sin egen Formula-record som enhver anden formelcelle, og det er præcis det, der gjorde en delt gruppes rodcelle til det eneste sted, cache-først-gemning stadig tabte. En delt formel i BIFF8 gemmes som en ShrFmla-record ([MS-XLS] §2.4.260), der følger top-venstre cellens Formula-record, og hver medlemscelle, roden inkluderet, bærer en rgce bestående af en enkelt PtgExp-token (§2.5.198): Den første byte af det parsede udtryk er $01, efterfulgt af rodcellens række og kolonne. Follower-cellerne er self-contained — HotXLS læser hver af deres FormulaValue og opløser udtrykket ved at slå rodens kompilerede formel op. Rodcellen er anderledes, for når dens Formula-record parses, findes udtrykket ikke endnu; det ankommer én record senere

Det én-record-hul er der, cachen gik tabt. TXLSReader.ParseFormula dekoder den cachede værdi og, ved synet af en PtgExp, hvis koordinater er lig cellens egne, husker cellen i FSharedFormulaRow og FSharedFormulaCol og udgiver cachen til cellen. Når ShrFmla-recorden ($04BC) ankommer, kompilerer ParseSharedFormula udtrykket og installerer det med _SetCompiledFormula, og _SetCompiledFormula gør, hvad den må gøre ved enhver formelændring: Den rydder FCachedFormulaValue og nulstiller tilstanden til xlfcsMissing. Rodens indlæste 37 blev altså smidt ud, før nogen kunne læse den, TryGetCachedFormulaValue rapporterede roden som uncached, og cache-først-writeren faldt pligttro tilbage til evaluatoren for præcis den celle, alle så på. Array-recorden (§2.4.4) deler samme orden og havde samme hul

Fixet i v2.382.3 tilføjer et tredje felt, FSharedFormulaCachedValue, ved siden af de ventende rod-koordinater. ParseFormula lægger den dekodede cache dér, når den genkender en rod, og både ParseSharedFormula og ParseArrayFormula afspiller den gennem _SetCellCachedFormulaValue umiddelbart efter installationen af det kompilerede udtryk og nulstiller derefter lageret til Unassigned. Cachens String-variant er upåvirket af alt dette, fordi dens payload ankommer i en separat String-record og routes efter cellekoordinater, ikke efter record-orden. Arbejder du med samme koncepts OOXML-side, forklarer artiklen om XLSX shared formula si-ekspansion, hvorfor pakkeformatet ikke har noget tilsvarende ordenproblem, men har sine egne ekspansionsfælder

Hvorfor behøver shared formula-followers en relativ forskydning?

Fordi udtrykket, der gemmes i ShrFmla, er skrevet relativt til rodcellen, og en follower, der genbruger det ordret, evaluerer rodens referencer i stedet for sine egne. Den gamle reader installerede Value.GetCopy() på hver follower, en dyb kopi uden forskydning, så en gruppe med rod i B1 med =A1*3 også gav hver follower =A1*3. Cache-først-gemning maskerede faktisk dette for indlæste filer, eftersom followers havde deres egen FormulaValue og aldrig behøvede udtrykket for at gemme korrekt; det kom frem i det øjeblik, noget genberegnedes. Readeren installerer nu TXLSCompiledFormula.GetCopy(row - srow, col - scol), som gennemløber syntakstræet og forskyder hver relativ reference med followerens afstand fra roden, så followeren ved B2 ejer en ægte =A2*3

Regressionstesten, der fastlåser begge adfærd, er det værd at læse, fordi den nægter at lade et tilfælde passere. Den bygger en arbejdsbog med =A1*3 og =A2*3 over inputs 2 og 4 og injicerer derefter de bevidst forkerte caches 999 og 888 gennem _SetCellCachedFormulaValue, én gang med UseSharedFormulas slået til og én gang fra. Efter et gem og genindlæsning skal begge celler stadig rapportere 999 og 888 — bevis på, at gemmet ikke rørte hverken rod- eller follower-cachen. Først efter en eksplicit Recalculate skal de blive 6 og 12, bevis på, at followerens forskudte udtryk er korrekt. En test, der såede de rigtige værdier, ville også være bestået under den gamle writer, hvilket er hele pointen med at såe forkerte

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

    // Indlæste caches af afhængige formler invalideres IKKE af en
    // bogstavelig redigering, så en almindelig SaveAs ville beholde de gamle tal.
    // Bed om en genberegning, når du faktisk vil have friske 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;

Hvad cache-først-kontrakten ikke gør for dig

Cache-først-gemning bevarer det, der blev indlæst; den sporer ikke, om det indlæste stadig er sandt. At ændre en literal, en formel afhænger af, markerer dependency-grafen som urene for evaluatoren, men det efterlader den afhængige celles xlfcsLoaded-cache på plads, og den klassiske writer skriver gerne den forældede værdi, medmindre du kalder Recalculate eller læser cellens Value først, hvilket beregner den og flytter tilstanden til xlfcsCalculated. Det er samme afvejning, Excel foretager i manuel beregningstilstand, og det er den rigtige for en pipeline, der åbner tredjepartsfiler, redigerer nogle få labels og gemmer — men det betyder, at en arbejdsbog, der redigerer inputs, må eje sit genberegningstrin eksplicit. XLSX-writerens RecalcBeforeSave-politik er uændret af dette arbejde og har sin egen manuelle tilstand, der bevarer caches i samme ånd. To mindre grænser følger heraf: Cache-først-vejen hjælper kun celler, hvis tilstand er xlfcsLoaded eller xlfcsCalculated; en generator, der skriver formler og aldrig evaluerer dem, betaler stadig én evaluering pr. celle ved gem-tid, præcis som før. Og nested-subtotal-fixet retter, hvilke celler evaluatoren springer over, ikke hver funktion evaluatoren implementerer — en fil, hvis formler HotXLS ikke kan beregne identisk med Excel, er nu sikker at round-trippe urørt, men en bevidst Recalculate på den fil vil stadig producere bibliotekets svar frem for Excels, og du bør sammenligne de to, før du stoler på en genberegnet gemning

Cache-først klassiske gem, de genoprettede shared- og array-formel-rod-caches, den relative reference-forskydning for delte followers og de rettede SUBTOTAL- og AGGREGATE-nesting-regler ships alle i standard-HotXLS Delphi Spreadsheet Component til Delphi og C++Builder uden afhængighed af Excel eller nogen OLE automation-server; productsiden bærer den fulde API-reference for arbejdsbogs-, cache-reader- og genberegningsindgangspunkterne brugt her