Stoppa tyst omräkning av XLS-formler vid sparning i Delphi

HotXLS, det nativa Excel-biblioteket för Delphi och C++Builder, sparar en klassisk BIFF8-arbetsbok i .xls cache-first: TXLSWorksheet.WriteFormula frågar TXLSWorkbook.TryGetCachedFormulaValue efter värdet som Excel lagrade bredvid varje formel och anropar bara utvärderaren när cachen saknas eller har ogiltigförklarats. En arbetsbok du öppnat och aldrig rört sparar tillbaka samma siffror, och färska resultat kräver ett uttryckligt anrop till Recalculate i stället för att vara en dold bieffekt av SaveAs

Buggen som tvingade fram det här kontraktet var pinsamt liten. En korpusfil som heter nested-subtotals.xls har en sluttotal i R2C4 vars cachade värde är 37. Öppna den med HotXLS, fråga TryGetCachedFormulaValue om cellen, få 37. Spara den utan att ändra en enda cell, öppna den sparade kopian, ställ samma fråga, få 67. Inget i API:et hade betts räkna ut något, ändå hade en siffra i filen flyttat sig med exakt 30 — och 30 råkar vara summan av de två gruppdeltotalerna, 10 och 20, som ligger inuti det område sluttotalen täcker

Varför ändrar en sparning av en XLS-fil ett formelvärde?

Två oberoende fel måste sammanfalla för att 37 skulle bli 67, och att fixa bara det ena skulle ha dolt det andra. Det första var strukturellt: den klassiska skrivaren räknade om varje formel vid varje sparning. Det andra var en typkontroll som aldrig kunde vara sann för en formel inläst från disk, vilket gjorde att utvärderaren räknade nästlade SUBTOTAL-celler två gånger. Korpusfilen var helt enkelt den första indata där en omräkning vid sparning gav ett annat svar än Excel och någon jämförde de två. Det strukturella felet är lätt att beskriva: före v2.382.3 hämtade TXLSWorksheet.WriteFormula och dess syskon för delade formler, WriteFormulaWithTExp, det åttabyte stora fältet FormulaValue i varje Formula-post genom att anropa TXLSWorkbook.GetFormulaValue, som är utvärderaren. Den cache som ParseFormula omsorgsfullt hade avkodat från källfilen vid inläsning konsulterades aldrig på vägen ut. I praktiken var varje sparning en full omräkning med arbetsbokens omräknings-API förbigånget, så inget du kunde ställa in på arbetsboken skulle ha stoppat den. Varje ställe där HotXLS utvärderare gick isär med Excel, vare sig det gällde en legitimt ostödd funktion eller en ren bugg, blev en tyst dataändring vid sparning

Det andra felet bodde i den återanropning för nästlade deltotaler som utvärderaren använder. Excel definierar varje SUBTOTAL-form som att den bortser från celler vars egen formel är en annan SUBTOTAL, så kalkylatorn i lxCalc.pas aktiverar FIgnoreSubtotalCells under aggregeringen och frågar arbetsboken, via TXLSWorkbook.GetClassicIsSubtotalCell, om varje cell i området är en sådan. Den återanropningen hämtade formeltexten som en Variant och testade den med VarType(f) = varOleStr. Texten kommer tillbaka från GetUnCompiledFormula som en Delphi-String, och en String som tilldelas en Variant är varUString, aldrig varOleStr. Predikatet var falskt för varje cell i varje inläst fil, gruppdeltotalerna rullades in i sluttotalen en andra gång, och vid en sparning som räknade om allt blev 10 + 20 + 7 lika med 67

// HotXLS 2.381 och tidigare: en formel-Variant byggd från en String
// är varUString, så den här jämförelsen lyckades aldrig
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr accepterar varString, varOleStr och varUString,
// och AGGREGATE utesluts från omslutande deltotaler precis 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 levererade VarIsStr-fixen och lärde, medan den var i samma funktion, återanropningen att AGGREGATE-celler också utesluts från omslutande deltotaler. Bara det gjorde att korpuspåståendet gick igenom, eftersom de omräknade 37 nu matchade de inlästa 37. Det gjorde inte biblioteket ärligt: sparningen räknade fortfarande om, och testet var bara grönt för att utvärderaren råkade hålla med Excel i just den filen. Reglerna för vilka celler SUBTOTAL och AGGREGATE hoppar över, inklusive dolda rader, täcks i artikeln om dolda rader med SUBTOTAL och AGGREGATE; det som spelar roll här är att ingen utvärderare ska få en röst om en fil du inte bett den räkna på

Vad garanterar Excel om cachade värden vid sparning?

Excel behandlar en sparning som en ögonblicksbild, inte som en beräkningshändelse. Värdet som skrivs i fältet FormulaValue i en Formula-post ([MS-XLS] §2.4.127, layout i §2.5.133) är vad cellen för närvarande visar, vilket i manuellt beräkningsläge kan vara år gammalt, och Excel skriver det ändå troget. Omräkning är en separat operation med sin egen trigger. HotXLS följer nu samma regel för klassiska sparningar: WriteFormula och WriteFormulaWithTExp anropar TryGetCachedFormulaValue först, tar CacheInfo.Value när tillståndet är xlfcsLoaded eller xlfcsCalculated, och faller vidare till GetFormulaValue bara för xlfcsMissing och xlfcsInvalidated. Läsidans halva av det här kontraktet, inklusive vad varje tillstånd betyder och varför en cachad tomhet eller False fortfarande räknas som ett värde, beskrivs i Läs Excels cachade formelvärden i Delphi utan omräkning

Fallbackvägen behålls med avsikt, den är inte borttagen. En formel du tilldelat i den här sessionen via Cells[Row, Col].Formula kommer utan cache, och en formel du ersatt på en inläst cell märks xlfcsInvalidated av _SetCompiledFormula; båda utvärderas vid sparning precis som förut, så en genererad arbetsbok öppnas fortfarande i Excel med siffror i sig. När inte ens utvärderaren kan producera ett värde ger skrivaren en nollpayload och sätter fAlwaysCalc (grbit bit 0 i §2.4.127) så att Excel räknar om cellen vid öppning i stället för att lita på platshållaren

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 1-baserat ark, rad och kolumn: R2C4 på första arket
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // ingen utvärderare inblandad för cachade celler
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 för nested-subtotals.xls
    // En sparning som räknade om skulle ha skrivit 67 här
  finally
    Book.Free;
  end;
end;

Var håller en BIFF-delad formels rot sitt cachade värde?

I sin egen Formula-post, som varje annan formelcell, och det är precis vad som gjorde rotcellen i en delad grupp till det enda stället där cache-first-sparningen fortfarande tappade. En delad formel i BIFF8 lagras som en ShrFmla-post ([MS-XLS] §2.4.260) som följer Formula-posten för cellen uppe till vänster, och varje medlemscell, roten inräknad, bär en rgce som består av en enda PtgExp-token (§2.5.198): första byten i det parsade uttrycket är $01, följd av rotcellens rad och kolumn. Följarcellerna är självständiga — HotXLS läser var och ens FormulaValue och löser uttrycket genom att slå upp rotens kompilerade formel. Rotcellen är annorlunda, för när dess Formula-post parsas finns uttrycket ännu inte; det kommer en post senare

Det en-post-gapet är där cachen tog vägen. TXLSReader.ParseFormula avkodar det cachade värdet och minns, när den ser en PtgExp vars koordinater är cellens egna, cellen i FSharedFormulaRow och FSharedFormulaCol och publicerar cachen till cellen. När ShrFmla-posten ($04BC) kommer kompilerar ParseSharedFormula uttrycket och installerar det med _SetCompiledFormula, och _SetCompiledFormula gör vad den måste göra för varje formeländring: den rensar FCachedFormulaValue och återställer tillståndet till xlfcsMissing. Rotens inlästa 37 kastades alltså bort innan någon kunde läsa det, TryGetCachedFormulaValue rapporterade roten som ocachad, och cache-first-skrivaren föll lydigt tillbaka på utvärderaren för precis den cell alla tittade på. Array-posten (§2.4.4) har samma ordning och hade samma hål

Fixen i v2.382.3 lägger till ett tredje fält, FSharedFormulaCachedValue, bredvid de väntande rotkoordinaterna. ParseFormula lägger det avkodade cachevärdet där när den känner igen en rot, och både ParseSharedFormula och ParseArrayFormula spelar upp det via _SetCellCachedFormulaValue direkt efter att ha installerat det kompilerade uttrycket och återställer sedan gömman till Unassigned. String-varianten av cachen påverkas inte av något av detta, eftersom dess payload kommer i en separat String-post och dirigeras efter cellkoordinater, inte efter postordning. Om du arbetar med OOXML-sidan av samma begrepp förklarar artikeln om si-expansion av delade formler i XLSX varför paketformatet inte har något motsvarande ordningsproblem men sina egna expansionsfällor

Varför behöver följare till delade formler en relativ förskjutning?

För att uttrycket som lagras i ShrFmla är skrivet relativt rotcellen, och en följare som återanvänder det ordagrant utvärderar rotens referenser i stället för sina egna. Den gamla läsaren installerade Value.GetCopy() på varje följare, en djupkopia utan förskjutning, så en grupp med roten i B1 och =A1*3 gav varje följare =A1*3 också. Cache-first-sparningen maskerade faktiskt detta för inlästa filer, eftersom följarna hade sitt eget FormulaValue och aldrig behövde uttrycket för att sparas korrekt; det kom upp i samma stund något räknades om. Läsaren installerar nu TXLSCompiledFormula.GetCopy(row - srow, col - scol), som går genom syntaxträdet och förskjuter varje relativ referens med följarens avstånd från roten, så följaren i B2 äger en äkta =A2*3

Regressionstestet som låser fast båda beteendena är värt att läsa, för det vägrar låta ett sammanträffande passera. Det bygger en arbetsbok med =A1*3 och =A2*3 över indata 2 och 4, och injicerar sedan de medvetet felaktiga cacherna 999 och 888 via _SetCellCachedFormulaValue, en gång med UseSharedFormulas på och en gång av. Efter en sparning och omladdning måste båda cellerna fortfarande rapportera 999 och 888 — bevis för att sparningen inte rörde vare sig rotens eller följarens cache. Först efter en uttrycklig Recalculate får de bli 6 och 12, bevis för att följarens förskjutna uttryck är korrekt. Ett test som sådde de sanna värdena skulle ha gått igenom även med den gamla skrivaren, vilket är hela poängen med att så felaktiga

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

    // Inlästa cacher för beroende formler ogiltigförklaras INTE av en
    // literalförändring, så en vanlig SaveAs skulle behålla de gamla siffrorna.
    // Begär en omräkning när du faktiskt vill ha färska resultat:
    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;

Vad cache-first-kontraktet inte gör åt dig

Cache-first-sparning bevarar det som lästes in; den håller inte reda på om det inlästa fortfarande är sant. Att ändra en literal som en formel beror på markerar beroendegrafen som smutsig för utvärderaren, men den lämnar den beroende cellens xlfcsLoaded-cache orörd, och den klassiska skrivaren skriver gladeligen det inaktuella värdet om du inte anropar Recalculate eller läser cellens Value först, vilket beräknar den och flyttar tillståndet till xlfcsCalculated. Det är samma avvägning Excel gör i manuellt beräkningsläge, och den är rätt för en pipeline som öppnar tredjepartsfiler, redigerar några etiketter och sparar — men det betyder att en arbetsbok som redigerar indata måste äga sitt omräkningssteg uttryckligen. XLSX-skrivarens policy RecalcBeforeSave påverkas inte av det här arbetet och har sitt eget manuella läge som bevarar cacher i samma anda. Två mindre gränser följer av detta: cache-first-vägen hjälper bara celler vars tillstånd är xlfcsLoaded eller xlfcsCalculated; en generator som skriver formler och aldrig utvärderar dem betalar fortfarande för en utvärdering per cell vid sparning, precis som förut. Och fixen för nästlade deltotaler rättar vilka celler utvärderaren hoppar över, inte varje funktion utvärderaren implementerar — en fil vars formler HotXLS inte kan beräkna identiskt med Excel är nu säker att rundresa orörd, men en avsiktlig Recalculate på den filen ger fortfarande bibliotekets svar i stället för Excels, och du bör jämföra de två innan du litar på en omräknad sparning

Cache-first-sparning av klassiska filer, de återställda rotcacherna för delade formler och arrayformler, den relativa referensförskjutningen för delade följare och de korrigerade nästlingsreglerna för SUBTOTAL och AGGREGATE levereras alla i standardupplagan av HotXLS Delphi Spreadsheet Component för Delphi och C++Builder, utan beroende av Excel eller någon OLE-automatiseringsserver; produktsidan bär hela API-referensen för de arbetsboks-, cacheläsar- och omräkningsingångar som används här