Stop met stil herberekenen bij XLS-opslag in Delphi

HotXLS, de native Excel-library voor Delphi en C++Builder, slaat een klassieke BIFF8-.xls-workbook cache-first op: TXLSWorksheet.WriteFormula vraagt TXLSWorkbook.TryGetCachedFormulaValue om de waarde die Excel naast elke formule heeft opgeslagen en roept de evaluator alleen aan wanneer die cache ontbreekt of ongeldig is. Een workbook die je hebt geopend en nooit hebt aangeraakt slaat dezelfde getallen terug op, en verse resultaten kosten één expliciete Recalculate-aanroep in plaats van een verborgen neveneffect van SaveAs te zijn

De bug die dit contract aan het licht dwong was beschamend klein. Een corpusbestand met de naam nested-subtotals.xls bevat een eindtotaal in R2C4 waarvan de gecachte waarde 37 is. Open het met HotXLS, vraag TryGetCachedFormulaValue voor die cel, krijg 37. Sla het op zonder één cel te wijzigen, open de opgeslagen kopie, stel dezelfde vraag, krijg 67. Er was via de API nergens om een berekening gevraagd, en toch was een getal in het bestand met precies 30 verschoven — en 30 is toevallig de som van de twee groepssubtotalen, 10 en 20, die binnen het bereik van het eindtotaal liggen

Waarom verandert het opslaan van een XLS-bestand een formulewaarde?

Er moesten twee onafhankelijke defecten samenvallen voordat die 37 een 67 werd, en er één alleen fixen zou de andere verborgen hebben. Het eerste was structureel: de klassieke schrijver herberekende elke formule bij elke opslag. Het tweede was een typecontrole die nooit waar kon zijn voor een formule die van schijf was geladen, waardoor de evaluator geneste SUBTOTAL-cellen dubbel telde. Het corpusbestand was simpelweg de eerste invoer waarbij een herberekening bij het opslaan een ander antwoord gaf dan Excel en iemand de twee vergeleek. Het structurele defect is makkelijk te benoemen: vóór v2.382.3 haalden TXLSWorksheet.WriteFormula en zijn gedeelde-formulebroertje WriteFormulaWithTExp het acht-byte veld FormulaValue van elk Formula-record op via TXLSWorkbook.GetFormulaValue, en dat is de evaluator. De cache die ParseFormula bij het laden zorgvuldig uit het bronbestand had gedecodeerd, werd op weg naar buiten nooit geraadpleegd. In de praktijk was elke opslag dus een volledige herberekening met de workbook-brede herbereken-API omzeild, dus niets wat je op de workbook kon instellen zou het hebben gestopt. Elke plek waar de HotXLS-evaluator het oneens was met Excel, of het nu een legitiem niet-ondersteunde functie was of een gewone bug, werd een stille datawijziging bij het opslaan

Het tweede defect zat in de callback voor geneste subtotalen die de evaluator gebruikt. Excel definieert elke SUBTOTAL-vorm als het negeren van cellen waarvan de eigen formule weer een SUBTOTAL is, dus zet de calculator in lxCalc.pas tijdens de aggregatie FIgnoreSubtotalCells aan en vraagt hij de workbook, via TXLSWorkbook.GetClassicIsSubtotalCell, of elke cel in het bereik er een is. Die callback haalde de formuletekst als Variant op en testte hem met VarType(f) = varOleStr. De tekst komt uit GetUnCompiledFormula terug als een Delphi-String, en een String die aan een Variant wordt toegewezen is varUString, nooit varOleStr. Het predicaat was onwaar voor elke cel in elk geladen bestand, groepssubtotalen werden een tweede keer in het eindtotaal gerold, en bij een opslag die alles herberekende werd 10 + 20 + 7 gelijk aan 67

// HotXLS 2.381 en eerder: een formule-Variant opgebouwd uit een String
// is varUString, dus deze vergelijking slaagde nooit
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr accepteert varString, varOleStr en varUString,
// en AGGREGATE wordt net als bij Excel uitgesloten van omvattende subtotalen
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 bracht de VarIsStr-fix uit en leerde de callback, terwijl hij toch in die functie zat, dat AGGREGATE-cellen ook van omvattende subtotalen worden uitgesloten. Dat alleen al liet de corpusassertie slagen, want de herberekende 37 kwam nu overeen met de geladen 37. Eerlijk maakte het de library niet: de opslag herberekende nog steeds, en de test was alleen groen omdat de evaluator het bij dat specifieke bestand toevallig met Excel eens was. De regels voor welke cellen SUBTOTAL en AGGREGATE overslaan, inclusief verborgen rijen, staan in het artikel over SUBTOTAL, AGGREGATE en verborgen rijen; waar het hier om gaat is dat geen enkele evaluator een stem hoort te krijgen over een bestand dat je hem niet hebt gevraagd te berekenen

Wat garandeert Excel over gecachte waarden bij het opslaan?

Excel behandelt een opslag als een snapshot, niet als een berekeningsmoment. De waarde die in het veld FormulaValue van een Formula-record wordt geschreven ([MS-XLS] §2.4.127, lay-out in §2.5.133) is wat de cel op dat moment toont, wat in handmatige berekeningsmodus jaren oud kan zijn, en Excel schrijft hem toch trouw weg. Herberekenen is een aparte bewerking met zijn eigen trigger. HotXLS volgt nu dezelfde regel voor klassieke opslag: WriteFormula en WriteFormulaWithTExp roepen eerst TryGetCachedFormulaValue aan, nemen CacheInfo.Value wanneer de staat xlfcsLoaded of xlfcsCalculated is, en vallen alleen voor xlfcsMissing en xlfcsInvalidated door naar GetFormulaValue. De leeskant van dit contract, inclusief wat elke staat betekent en waarom een gecachte leegte of False toch als waarde telt, staat in Gecachte Excel-formulewaarden lezen in Delphi zonder herberekening

Het terugvalpad blijft bewust bestaan, het is niet weggehaald. Een formule die je in deze sessie hebt toegewezen via Cells[Row, Col].Formula komt binnen zonder cache, en een formule die je op een geladen cel hebt vervangen wordt door _SetCompiledFormula als xlfcsInvalidated gemarkeerd; beide worden bij het opslaan geëvalueerd precies zoals voorheen, dus een gegenereerde workbook opent nog steeds in Excel met getallen erin. Wanneer zelfs de evaluator geen waarde kan produceren, schrijft de writer een zero-payload en zet fAlwaysCalc (grbit bit 0 van §2.4.127), zodat Excel de cel bij het openen herberekent in plaats van de placeholder te vertrouwen

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // blad, rij en kolom 1-based: R2C4 op het eerste blad
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // geen evaluator betrokken bij gecachte cellen
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 voor nested-subtotals.xls
    // een opslag die herberekende zou hier 67 hebben geschreven
  finally
    Book.Free;
  end;
end;

Waar bewaart de hoofdcel van een gedeelde BIFF-formule zijn gecachte waarde?

In zijn eigen Formula-record, net als elke andere formulecel, en precies dat maakte de hoofdcel van een gedeelde groep de enige plek waar cache-first opslaan nog verloor. Een gedeelde formule in BIFF8 wordt opgeslagen als een ShrFmla-record ([MS-XLS] §2.4.260) dat volgt op het Formula-record van de cel linksboven, en elke lidcel, de hoofdcel inbegrepen, draagt een rgce die uit één PtgExp-token bestaat (§2.5.198): de eerste byte van de geparseerde expressie is $01, gevolgd door de rij en kolom van de hoofdcel. De volgcellen staan op zichzelf — HotXLS leest de FormulaValue van elk en lost de expressie op door de gecompileerde formule van de hoofdcel op te zoeken. De hoofdcel is anders, want wanneer zijn Formula-record wordt geparseerd bestaat de expressie nog niet; die komt één record later

Die leemte van één record is waar de cache heen ging. TXLSReader.ParseFormula decodeert de gecachte waarde en onthoudt de cel, bij het zien van een PtgExp waarvan de coördinaten gelijk zijn aan die van de cel zelf, in FSharedFormulaRow en FSharedFormulaCol, en publiceert de cache naar de cel. Wanneer het ShrFmla-record ($04BC) binnenkomt, compileert ParseSharedFormula de expressie en installeert hem met _SetCompiledFormula, en _SetCompiledFormula doet wat het voor elke formulewijziging moet doen: het wist FCachedFormulaValue en zet de staat terug op xlfcsMissing. De geladen 37 van de hoofdcel werd dus weggegooid voordat iemand hem kon lezen, TryGetCachedFormulaValue meldde de hoofdcel als ongecacht, en de cache-first writer viel netjes terug op de evaluator voor precies de cel waar iedereen naar keek. Het Array-record (§2.4.4) heeft dezelfde ordening en had hetzelfde gat

De fix in v2.382.3 voegt een derde veld toe, FSharedFormulaCachedValue, naast de wachtende coördinaten van de hoofdcel. ParseFormula legt de gedecodeerde cache daar neer wanneer hij een hoofdcel herkent, en zowel ParseSharedFormula als ParseArrayFormula speelt hem terug via _SetCellCachedFormulaValue, direct nadat de gecompileerde expressie is geïnstalleerd, en zet de bewaarplaats daarna op Unassigned. De String-variant van de cache heeft hier allemaal geen last van, omdat zijn payload in een apart String-record aankomt en op celcoördinaten wordt gerouteerd, niet op recordvolgorde. Werk je met de OOXML-kant van hetzelfde concept, dan legt het artikel over het uitklappen van gedeelde XLSX-formules via si uit waarom het pakketformaat geen equivalent ordeningsprobleem heeft, maar wel zijn eigen valkuilen bij het uitklappen

Waarom hebben volgcellen van een gedeelde formule een relatieve verschuiving nodig?

Omdat de expressie in ShrFmla relatief ten opzichte van de hoofdcel is geschreven, en een volgcel die hem woordelijk hergebruikt de verwijzingen van de hoofdcel evalueert in plaats van zijn eigen. De oude reader installeerde Value.GetCopy() op elke volgcel, een diepe kopie zonder verplaatsing, dus een groep met hoofdcel B1 en =A1*3 gaf elke volgcel ook =A1*3. Cache-first opslaan maskeerde dit voor geladen bestanden, want volgcellen hadden hun eigen FormulaValue en hadden de expressie nooit nodig om correct op te slaan; het kwam boven water zodra er iets herberekende. De reader installeert nu TXLSCompiledFormula.GetCopy(row - srow, col - scol), dat de syntaxisboom doorloopt en elke relatieve verwijzing verschuift met de afstand van de volgcel tot de hoofdcel, zodat de volgcel op B2 een echte =A2*3 bezit

De regressietest die beide gedragingen vastpint is het lezen waard, omdat hij een toeval niet laat passeren. Hij bouwt een workbook met =A1*3 en =A2*3 over de invoeren 2 en 4, en injecteert daarna de bewust foute caches 999 en 888 via _SetCellCachedFormulaValue, één keer met UseSharedFormulas aan en één keer uit. Na een opslag en herladen moeten beide cellen nog steeds 999 en 888 melden — het bewijs dat de opslag noch de cache van de hoofdcel noch die van de volgcel heeft aangeraakt. Pas na een expliciete Recalculate moeten het 6 en 12 worden, het bewijs dat de verschoven expressie van de volgcel correct is. Een test die de juiste waarden had gezaaid zou ook onder de oude writer zijn geslaagd, en daarom worden er bewust foute gezaaid

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

    // Geladen caches van afhankelijke formules worden NIET ongeldig door een
    // letterlijke bewerking, dus een gewone SaveAs zou de oude getallen houden.
    // Vraag om een herberekening wanneer je echt verse resultaten wilt:
    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;

Wat het cache-first contract niet voor je doet

Cache-first opslaan behoudt wat er geladen is; het houdt niet bij of wat geladen is nog waar is. Het wijzigen van een letterlijke waarde waar een formule van afhangt markeert de afhankelijkheidsgraaf vuil voor de evaluator, maar laat de xlfcsLoaded-cache van de afhankelijke cel staan, en de klassieke writer schrijft die verouderde waarde met plezier weg tenzij je Recalculate aanroept of eerst de Value van de cel leest, wat hem berekent en de staat naar xlfcsCalculated verzet. Dat is dezelfde afweging die Excel in handmatige berekeningsmodus maakt, en het is de juiste voor een pipeline die bestanden van derden opent, een paar labels bewerkt en opslaat — maar het betekent wel dat een workbook die invoeren bewerkt zijn herberekeningsstap expliciet moet bezitten. Het RecalcBeforeSave-beleid van de XLSX-writer is door dit werk niet veranderd en heeft zijn eigen handmatige modus die caches in dezelfde geest behoudt. Twee kleinere grenzen volgen hieruit: het cache-first pad helpt alleen cellen waarvan de staat xlfcsLoaded of xlfcsCalculated is; een generator die formules schrijft en ze nooit evalueert betaalt bij het opslaan nog steeds één evaluatie per cel, precies zoals voorheen. En de fix voor geneste subtotalen corrigeert welke cellen de evaluator overslaat, niet elke functie die de evaluator implementeert — een bestand waarvan de formules HotXLS niet identiek aan Excel kan berekenen is nu veilig om onaangeroerd rond te zetten, maar een bewuste Recalculate op dat bestand geeft nog steeds het antwoord van de library in plaats van dat van Excel, en die twee kun je beter vergelijken voordat je een herberekende opslag vertrouwt

Cache-first klassieke opslag, de herstelde caches van hoofdcellen van gedeelde en array-formules, de relatieve-verwijzingverschuiving voor volgcellen en de gecorrigeerde nestregels voor SUBTOTAL en AGGREGATE zitten allemaal in de standaard HotXLS Delphi Spreadsheet Component voor Delphi en C++Builder, zonder afhankelijkheid van Excel of welke OLE-automatiseringsserver dan ook; de productpagina bevat de volledige API-referentie voor de workbook-, cachelezer- en herberekeningsingangen die hier gebruikt worden