Stop met stil herberekenen bij XLS-opslag in Delphi

Een eindtotaal dat Excel als 37 toont, kan 67 terugkomen nadat een programma het .xls-bestand opent en opslaat zonder ook maar een cel te bewerken. De extra 30 is geen toeval. Het is precies de som van de twee groepssubtotalen, 10 en 20, die binnen het bereik liggen dat het eindtotaal bestrijkt. Twee dingen moeten tegelijk misgaan wil dat getal verschuiven: de opslag moet elke formule herberekenen op de weg naar buiten, en wat er herberekent moet vergeten dat een SUBTOTAL andere SUBTOTAL-cellen overslaat. Haal een van beide weg en het bestand overleeft het, en daarom kan het eerste probleem onopgemerkt achter het tweede zitten

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

Een opslag verandert een formulewaarde wanneer de writer het getal berekent dat hij naast elke formule opslaat in plaats van het geladen getal te kopiëren. Elk BIFF8 Formula-record draagt een FormulaValue-veld van acht bytes ([MS-XLS] §2.4.127, waardelay-out in §2.5.133) dat het resultaat bevat dat Excel het laatst voor die cel toonde. Een writer moet iets in die acht bytes zetten, en het kortste codepad is elke formule aan de rekenengine geven. Zodra dat gebeurt, is elke opslag een volledige herberekening die niemand vroeg, en wordt elk punt waar die engine en Excel van elkaar verschillen een veranderd getal in een bestand dat volgens de gebruiker onaangeraakt was

Het SUBTOTAL-verschil is een goed voorbeeld, want de spreadsheetregel is simpel en de valkuil zit in de Variant-typing van Delphi. Excel definieert elk SUBTOTAL-functienummer als het negeren van cellen waarvan de eigen formule een andere SUBTOTAL is. Voor een blad waarvan de detailrijen optellen tot 37 (de rijen onder twee groepen met subtotalen 10 en 20, plus 7 buiten elke groep), is het eindtotaal 37. Een engine die de geneste subtotalen mist, telt de 10 en de 20 een tweede keer mee en landt op 67. Ze herkennen betekent meestal een korte controle over de formuletekst, en de voor de hand liggende Delphi-versie compileert, draait en matcht nooit iets:

// Een hulpfunctie die je zelf schrijft, bijvoorbeeld bij het scannen van een blad
// naar formules die een omhullende SUBTOTAL moet overslaan
function IsNestedSubtotal(const F: Variant): Boolean;
begin
  // De voor de hand liggende test slaagt nooit op Unicode Delphi: een String
  // die aan een Variant wordt toegekend is varUString, niet varOleStr
  //   Result := (VarType(F) = varOleStr) and ...

  Result := False;
  if VarIsStr(F) then  // accepteert varString, varOleStr en varUString
    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(');
end;

// Gebruik met HotXLS (sheets, rijen en kolommen zijn 1-based):
//   if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...

VarIsStr is de test die de HotXLS-evaluator op dezelfde vraag toepast, en die behandelt ook AGGREGATE-cellen als uitgesloten van een omhullende subtotal, zoals Excel doet. Het herstellen van de predicate is echter niet het hele verhaal. Met een correcte controle schrijft de herberekende opslag 37 alleen omdat de engine op dit specifieke bestand toevallig met Excel meeleest. De volgende formule die de engine net iets anders behandelt, schuift weer op. De regels voor welke cellen SUBTOTAL en AGGREGATE overslaan, verborgen rijen inbegrepen, staan in het artikel over verborgen rijen bij SUBTOTAL en AGGREGATE. Met cache-first-opslag gaat een bestand waarvoor je nooit een berekening vroeg, terug naar schijf met 37 in de cel, wat de engine er ook van gemaakt zou hebben

Wat een klassieke XLS-opslag in elk Formula-record schrijft

HotXLS slaat een BIFF8-workbook cache-first op: per formulecel schrijft TXLSWorkbook.SaveAs de gecachte waarde zodra TryGetCachedFormulaValue er een zou melden, en draait hij de evaluator alleen wanneer dat niet zo is. De cachestatus bepaalt het. Een cel in status xlfcsLoaded (de waarde kwam uit het bronbestand) of xlfcsCalculated (HotXLS berekende hem deze sessie) krijgt zijn CacheInfo.Value byte voor byte weggeschreven. Een cel in status xlfcsMissing of xlfcsInvalidated wordt bij opslag geëvalueerd. Dat past bij hoe Excel een opslag behandelt: een momentopname van wat elke cel toont, wat in de handmatige rekenmodus flink verouderd kan zijn, toch getrouw weggeschreven

Het evaluatiepad blijft met goede reden. Een formule die je in code toekent via Cells[Row, Col].Formula heeft nog geen cache, en het toekennen van een nieuwe formule aan een geladen cel zet hem op xlfcsInvalidated, want het oude resultaat hoort bij de oude expressie. Beide worden tijdens de opslag berekend, dus een workbook dat van nul is opgebouwd, opent in Excel met berekende getallen in plaats van nullen. Kan zelfs de evaluator geen waarde produceren (een niet-ondersteunde functie, een kapotte externe link), dan slaat de writer een nul op en zet fAlwaysCalc, bit 0 van de recordvlaggen in §2.4.127, zodat Excel de cel bij het openen herberekent in plaats van de placeholder te vertrouwen

Een property lijkt erop en is het niet. TXLSWorkbook.RecalcOnSave spiegelt de Excel-optie "Werkmap opnieuw berekenen voordat opslaan" uit het CalcSaveRecalc-record ([MS-XLS] §2.4.37). Hij is alleen relevant voor Excel, alleen in de handmatige rekenmodus, en HotXLS schrijft hem simpelweg in het bestand. SaveAs raadpleegt hem niet bij de beslissing wat er in FormulaValue gaat. De round trip hieronder is de snelste manier om het contract op een eigen bestand te zien:

procedure CheckRoundTrip(const Source, Target: string;
  Row, Col: Integer);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    if Book.Open(Source) <> 1 then
      raise Exception.Create('Cannot open ' + Source);
    // Sheet, rij en kolom zijn 1-based
    if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
      raise Exception.Create('Cell carries no usable cached value');
    if Book.SaveAs(Target) <> 1 then   // gecachte cellen worden geschreven zoals ze staan
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, Row, Col, After);
    // Verwacht: After.Value is gelijk aan Before.Value, en After.State is
    // opnieuw xlfcsLoaded, want hij komt uit het opgeslagen bestand
    Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
  finally
    Book.Free;
  end;
end;

De leeskant van dit contract, inclusief wat elke status betekent en waarom een gecachte lege cel of False ook als waarde telt, staat beschreven in Read Excel Cached Formula Values in Delphi Without Recalc. Draai de controle hierboven op de eindtotaalcel van een workbook met geneste subtotalen en beide regels printen hetzelfde getal

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

In zijn eigen Formula-record, zoals elke andere formulecel, maar de expressie komt een record later, en in dat gat verliest een slordige lezer de cache. BIFF8 slaat een gedeelde formule op als een ShrFmla-record ([MS-XLS] §2.4.260) direct na het Formula-record van de cel linksboven in de groep. Elke lidcel, de wortel inbegrepen, draagt een rgce die uit een enkel PtgExp-token bestaat (§2.5.198): byte $01 gevolgd door rij en kolom van de wortel. Follower-cellen zijn makkelijk, want tegen de tijd dat ze verschijnen bestaat de expressie van de wortel al en staat hun eigen FormulaValue in hun eigen record. De wortel is niet makkelijk. Als zijn Formula-record wordt gelezen, heeft hij een gecachte waarde van 37 en nog geen expressie

Het installeren van een expressie op een cel wist zijn cache, en dat moet zo blijven, want een vervangen formule maakt het oude resultaat echt betekenisloos. Blind toegepast op het ShrFmla-record gooit dezelfde regel echter de 37 van de wortel weg op het moment dat de expressie arriveert, meldt TryGetCachedFormulaValue de wortel als xlfcsMissing, en valt de opslag terug op de evaluator voor precies de cel die een gebruiker het meest waarschijnlijk bekijkt. De HotXLS-lezer houdt de gedecodeerde waarde van de wortel vast terwijl hij op het ShrFmla-record wacht, installeert de expressie en zet daarna de geladen waarde terug met status xlfcsLoaded. Array-formules (het Array-record, §2.4.4) volgen dezelfde recordvolgorde en krijgen dezelfde behandeling. De String-vorm van de cache heeft dit probleem nooit gehad, want zijn tekst arriveert in een apart String-record en wordt op rij en kolom aan de cel gekoppeld, dus zijn positie in de stream doet er nooit toe

Als je ook de OOXML-kant behandelt, legt het artikel over XLSX shared formula si expansion uit waarom het pakketformaat geen recordvolgorde-gat kent maar wel zijn eigen expansievalkuilen heeft

Shared formula-followers bezitten een verschoven kopie van de expressie

De expressie in een ShrFmla-record staat relatief aan de wortelcel geschreven, dus elke follower heeft een eigen kopie nodig, verschoven over zijn afstand tot de wortel. Letterlijk kopiëren en een groep met wortel B1 en =A1*3 geeft dezelfde =A1*3 aan B2 en B3. HotXLS geeft elke follower een kopie waarvan de relatieve referenties zijn verschoven over de rij- en kolomafstand van de follower, dus B2 bezit een echte =A2*3 en B3 een =A3*3

Daarom kan een gewone round trip ook niet zeggen of follower-expressies kloppen. Followers houden hun geladen waarden door een opslag heen vast, dus een letterlijke kopie zou slagen. Een controle die iets bewijst, moet een input veranderen en herberekenen. De routine hieronder schrijft een gedeelde groep met UseSharedFormulas aan (hij defaultt op False, wat een Formula-record per cel schrijft), opent het bestand opnieuw, bewerkt A2 en kijkt naar B2:

procedure CheckSharedFollower(const FileName: string);
var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Info: TXLSFormulaCacheInfo;
  Row: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    for Row := 1 to 3 do
    begin
      Sheet.Cells[Row, 1].Value := Row * 2;                 // 2, 4, 6
      Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
    end;
    Book.UseSharedFormulas := True;   // B1:B3 worden een ShrFmla-groep
    Book.SaveAs(FileName);            // nog geen caches: geëvalueerd naar 6, 12, 18
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(FileName);
    Book.Sheets[1].Cells[2, 1].Value := 10;   // bewerk de input van de follower

    Book.TryGetCachedFormulaValue(1, 2, 2, Info);
    Writeln('B2 before: ', VarToStr(Info.Value));   // Verwacht: 12, nog steeds geladen

    Book.Recalculate;
    Book.TryGetCachedFormulaValue(1, 2, 2, Info);
    Writeln('B2 after: ', VarToStr(Info.Value));    // Verwacht: 30, uit =A2*3
  finally
    Book.Free;
  end;
end;

Een follower die =A1*3 had hergebruikt, zou op de tweede regel 6 printen. De eerste regel is net zo belangrijk: die laat zien dat het bewerken van een literal de geladen waarde van de afhankelijke formule op zijn plek laat totdat iets herberekent

Wat cache-first-opslag aan jou overlaat

Cache-first-opslag bewaart wat er geladen werd. Hij houdt niet bij of wat geladen werd nog klopt. Het veranderen van een cel waarvan een formule afhangt, markeert de afhankelijkheidsgraaf voor de volgende herberekening, maar de afhankelijke cel houdt zijn xlfcsLoaded-waarde vast, en SaveAs schrijft dat verouderde getal tenzij je eerst Recalculate aanroept of de Value van de cel leest, wat de formule berekent en hem naar xlfcsCalculated zet. Recalculate geeft het aantal formulecellen terug dat hij niet kon evalueren, dus een niet-nulresultaat is het loggen waard voor de opslag

In de gangbare pijplijn opent een programma een workbook dat iemand anders bouwde, vult een paar labels of een kop in, en slaat op. Niets in dat bestand zou van waarde mogen veranderen, en met cache-first-opslag verandert er niets. Een programma dat inputs bewerkt, bezit in plaats daarvan de herberekeningsstap, en die stap hoort in een zichtbare Recalculate-aanroep naast de bewerkingen, waar iedereen die de code leest hem kan zien. Excel maakt dezelfde afweging in de handmatige rekenmodus. De XLSX-writer heeft zijn eigen beleid, RecalcBeforeSave met de modi xrmManual, xrmIfDirty en xrmAlways, gezet op de XLSX-workbookklasse en onafhankelijk van alles hierboven

Uit het ontwerp volgen twee grenzen. Het cachepad helpt alleen cellen in status xlfcsLoaded of xlfcsCalculated, dus een generator die duizenden nieuwe formules schrijft en ze nooit evalueert, betaalt bij de opslag nog steeds een evaluatie per cel. En het contract gaat over wat een opslag schrijft, niet over wat een expliciete herberekening berekent: een onaangeraakt bestand houdt de getallen die Excel opslaat, terwijl een Recalculate de resultaten van de HotXLS-evaluator wegschrijft. Vergelijk beide op een paar representatieve workbooks voordat je een pijplijn omschakelt op herberekende opslagen

De HotXLS Delphi Spreadsheet Component leest en schrijft klassieke BIFF8- en XLSX-workbooks vanuit Delphi en C++Builder zonder Excel of OLE-automatisering. De productpagina documenteert de workbook-, cached-value- en herberekeningsaanroepen die in deze voorbeelden worden gebruikt