XLS-tallennusten hiljainen uudelleenlaskenta Delphissä

HotXLS, natiivi Delphi- ja C++Builder-Excel-kirjasto, tallentaa klassisen BIFF8-muotoisen .xls-työkirjan välimuisti edellä: TXLSWorksheet.WriteFormula kysyy TXLSWorkbook.TryGetCachedFormulaValue-funktiolta arvon, jonka Excel tallensi kunkin kaavan viereen, ja kutsuu evaluaattoria vain kun tuo välimuisti puuttuu tai on mitätöity. Työkirja, jonka avasit etkä koskenut siihen, tallentaa samat luvut takaisin, ja tuoreet tulokset vaativat yhden nimenomaisen Recalculate-kutsun sen sijaan, että ne olisivat SaveAs-kutsun piilovaikutus

Bugi, joka pakotti tämän sopimuksen päivänvaloon, oli kiusallisen pieni. Korpustiedostossa nimeltä nested-subtotals.xls on loppusumma solussa R2C4, jonka välimuistiarvo on 37. Avaa se HotXLS:llä, kysy solun arvoa TryGetCachedFormulaValue-funktiolla, saat 37. Tallenna se muuttamatta yhtäkään solua, avaa tallennettu kopio, kysy sama asia, saat 67. API:lta ei ollut pyydetty laskemaan mitään, silti tiedostossa oleva luku oli liikahtanut täsmälleen 30:llä — ja 30 sattuu olemaan niiden kahden ryhmävälisumman summa, 10 ja 20, jotka istuvat loppusumman kattaman alueen sisällä

Miksi XLS-tiedoston tallentaminen muuttaa kaavan arvoa?

Kahden riippumattoman vian piti osua kohdakkain, jotta 37:stä tuli 67, ja kumman tahansa korjaaminen yksin olisi kätkenyt toisen. Ensimmäinen oli rakenteellinen: klassinen kirjoittaja laski jokaisen kaavan uudelleen jokaisella tallennuksella. Toinen oli tyyppitarkistus, joka ei voinut koskaan olla totta levyltä ladatulle kaavalle, mikä sai evaluaattorin laskemaan sisäkkäiset SUBTOTAL-solut kahdesti. Korpustiedosto oli yksinkertaisesti ensimmäinen syöte, jossa tallennusaikainen uudelleenlaskenta tuotti eri vastauksen kuin Excel ja jossa joku vertasi kahta. Rakenteellinen vika on helppo sanoa ääneen: ennen versiota 2.382.3 TXLSWorksheet.WriteFormula ja sen jakokaavainen sisar WriteFormulaWithTExp hankkivat jokaisen Formula-tietueen kahdeksantavuisen FormulaValue-kentän kutsumalla TXLSWorkbook.GetFormulaValue-funktiota, joka on evaluaattori. Sitä välimuistia, jonka ParseFormula oli huolellisesti purkanut lähdetiedostosta lataushetkellä, ei koskaan kysytty ulospäin mentäessä. Käytännössä jokainen tallennus oli täysi uudelleenlaskenta, joka ohitti työkirjatason recalc-API:n, joten mikään työkirjasta asetettava ei olisi voinut estää sitä. Jokaisesta paikasta, jossa HotXLS:n evaluaattori oli eri mieltä Excelin kanssa — olipa kyse aidosti tukemattomasta funktiosta tai pelkästä bugista — tuli hiljainen datan muutos tallennuksessa

Toinen vika asui sisäkkäisen välisumman takaisinkutsussa, jota evaluaattori käyttää. Excel määrittelee jokaisen SUBTOTAL-muodon jättävän huomiotta solut, joiden oma kaava on toinen SUBTOTAL, joten laskin tiedostossa lxCalc.pas virittää FIgnoreSubtotalCells-lipun aggregoinnin ajaksi ja kysyy työkirjalta TXLSWorkbook.GetClassicIsSubtotalCell-funktion kautta, onko kukin alueen solu sellainen. Tuo takaisinkutsu haki kaavan tekstin Variantina ja testasi sitä vertailulla VarType(f) = varOleStr. Teksti palaa GetUnCompiledFormula-funktiosta Delphin String-tyyppisenä, ja Varianttiin sijoitettu String on varUString, ei koskaan varOleStr. Predikaatti oli epätosi jokaiselle solulle jokaisessa ladatussa tiedostossa, ryhmävälisummat laskettiin loppusummaan toiseen kertaan, ja tallennuksessa joka laski kaiken uudelleen 10 + 20 + 7 muuttui 67:ksi

// HotXLS 2.381 ja aiemmat: merkkijonosta rakennettu kaava-Variant
// on varUString, joten tämä vertailu ei koskaan onnistunut
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr hyväksyy tyypit varString, varOleStr ja varUString,
// ja AGGREGATE jätetään ulkopuolelle ympäröivistä välisummista kuten Excel tekee
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 toimitti VarIsStr-korjauksen ja opetti samalla samassa funktiossa takaisinkutsulle, että myös AGGREGATE-solut jätetään ympäröivien välisummien ulkopuolelle. Pelkästään se sai korpusväittämän menemään läpi, koska uudelleenlaskettu 37 täsmäsi nyt ladattuun 37:ään. Se ei tehnyt kirjastosta rehellistä: tallennus laski yhä uudelleen, ja testi oli vihreä vain koska evaluaattori sattui olemaan samaa mieltä Excelin kanssa juuri tuossa tiedostossa. Säännöt siitä, mitkä solut SUBTOTAL ja AGGREGATE ohittavat, piilotetut rivit mukaan lukien, käydään läpi SUBTOTAL- ja AGGREGATE-piilorivejä käsittelevässä artikkelissa; tässä oleellista on, ettei yhdelläkään evaluaattorilla pitäisi olla äänivaltaa tiedostossa, jota et pyytänyt sitä laskemaan

Mitä Excel takaa välimuistiin tallennetuista arvoista tallennuksessa?

Excel kohtelee tallennusta tilannekuvana, ei laskentatapahtumana. Formula-tietueen FormulaValue-kenttään ([MS-XLS] §2.4.127, asettelu §2.5.133) kirjoitetaan se, mitä solu kulloinkin näyttää, mikä manuaalisessa laskentatilassa voi olla vuosia vanhaa, ja Excel kirjoittaa sen silti uskollisesti. Uudelleenlaskenta on erillinen operaatio omalla liipaisimellaan. HotXLS noudattaa nyt samaa sääntöä klassisissa tallennuksissa: WriteFormula ja WriteFormulaWithTExp kutsuvat ensin TryGetCachedFormulaValue-funktiota, ottavat CacheInfo.Value-arvon kun tila on xlfcsLoaded tai xlfcsCalculated, ja putoavat GetFormulaValue-funktioon vain tiloilla xlfcsMissing ja xlfcsInvalidated. Tämän sopimuksen lukupuoli, mukaan lukien se mitä kukin tila tarkoittaa ja miksi välimuistiin tallennettu tyhjä tai False lasketaan silti arvoksi, kuvataan artikkelissa Excelin välimuistiin tallennettujen kaava-arvojen lukemisesta Delphissä ilman uudelleenlaskentaa

Varapolku pidetään tarkoituksella, sitä ei poisteta. Kaava, jonka annoit tässä istunnossa Cells[Row, Col].Formula-kautta, saapuu ilman välimuistia, ja kaava, jonka korvasit ladatussa solussa, merkitään xlfcsInvalidated-tilaan _SetCompiledFormula-funktiossa; molemmat evaluoidaan tallennushetkellä täsmälleen kuten ennenkin, joten generoitu työkirja avautuu yhä Excelissä lukujen kera. Kun edes evaluaattori ei pysty tuottamaan arvoa, kirjoittaja tuottaa nollahyötykuorman ja asettaa fAlwaysCalc-lipun (§2.4.127:n grbit-bitti 0), jotta Excel laskee solun uudelleen avatessaan sen sen sijaan, että luottaisi paikkamerkkiin

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 1-pohjainen sivu, rivi ja sarake: R2C4 ensimmäisellä sivulla
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // evaluaattoria ei käytetä välimuistillisille soluille
  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
    // Uudelleenlaskeva tallennus olisi kirjoittanut tähän 67
  finally
    Book.Free;
  end;
end;

Missä BIFF-jakokaavan juuri pitää välimuistiin tallennettua arvoaan?

Omassa Formula-tietueessaan, kuten jokainen muukin kaavasolu, ja juuri se teki jakoryhmän juurisolusta ainoan paikan, jossa välimuisti edellä -tallennus yhä hävisi. BIFF8:n jakokaava tallennetaan ShrFmla-tietueena ([MS-XLS] §2.4.260), joka seuraa vasemman yläkulman solun Formula-tietuetta, ja jokainen jäsensolu, juuri mukaan lukien, kantaa rgce-rakennetta, joka koostuu yhdestä PtgExp-tokenista (§2.5.198): jäsennetyn lausekkeen ensimmäinen tavu on $01, jota seuraavat juurisolun rivi ja sarake. Seuraajasolut ovat itsenäisiä — HotXLS lukee kunkin oman FormulaValue-arvon ja ratkaisee lausekkeen etsimällä juuren käännetyllä kaavalla. Juurisolu on erilainen, koska sen Formula-tietuetta jäsennettäessä lauseke ei ole vielä olemassa; se saapuu yhden tietueen myöhemmin

Tuossa yhden tietueen aukossa välimuisti meni. TXLSReader.ParseFormula purkaa välimuistiin tallennetun arvon ja, nähdessään PtgExpin jonka koordinaatit vastaavat solun omia, muistaa solun kentissä FSharedFormulaRow ja FSharedFormulaCol ja julkaisee välimuistin solulle. Kun ShrFmla-tietue ($04BC) saapuu, ParseSharedFormula kääntää lausekkeen ja asentaa sen _SetCompiledFormula-funktiolla, ja _SetCompiledFormula tekee sen minkä minkä tahansa kaavamuutoksen on pakko tehdä: se tyhjentää FCachedFormulaValue-kentän ja palauttaa tilan arvoon xlfcsMissing. Juuren ladattu 37 heitettiin siis pois ennen kuin kukaan ehti lukea sitä, TryGetCachedFormulaValue raportoi juuren välimuistittomaksi, ja välimuisti edellä -kirjoittaja putosi kiltisti evaluaattoriin juuri sen solun kohdalla, jota kaikki katsoivat. Array-tietueella (§2.4.4) on sama järjestys ja siinä oli sama aukko

v2.382.3:n korjaus lisää kolmannen kentän, FSharedFormulaCachedValue, odottavien juurikoordinaattien viereen. ParseFormula kätkee purkamansa välimuistin sinne tunnistaessaan juuren, ja sekä ParseSharedFormula että ParseArrayFormula toistavat sen _SetCellCachedFormulaValue-funktion kautta välittömästi käännätyn lausekkeen asentamisen jälkeen ja nollaavat sitten kätkön arvoon Unassigned. Välimuistin String-muotoon tämä kaikki ei vaikuta, koska sen hyötykuorma saapuu erillisessä String-tietueessa ja ohjataan solukoordinaattien eikä tietuejärjestyksen mukaan. Jos työskentelet saman käsitteen OOXML-puolen kanssa, XLSX:n jakokaavan si-laajennusta käsittelevä artikkeli selittää, miksi pakettiformaatissa ei ole vastaavaa järjestysongelmaa mutta siinä on omat laajennusansansa

Miksi jakokaavan seuraajat tarvitsevat suhteellisen siirron?

Koska ShrFmla-tietueeseen tallennettu lauseke on kirjoitettu juurisoluun nähden, ja seuraaja, joka käyttää sitä sellaisenaan, evaluoi juuren viittaukset omiensa sijaan. Vanha lukija asensi jokaiselle seuraajalle Value.GetCopy()-kopion, syväkopion ilman siirtoa, joten solmuun B1 juurtunut ryhmä kaavalla =A1*3 antoi jokaiselle seuraajalle myös kaavan =A1*3. Välimuisti edellä -tallennus itse asiassa peitti tämän ladatuissa tiedostoissa, koska seuraajilla oli omat FormulaValue-arvonsa eikä lauseketta koskaan tarvittu tallentamiseen oikein; se nousi esiin heti kun jokin laski uudelleen. Lukija asentaa nyt TXLSCompiledFormula.GetCopy(row - srow, col - scol) -kopion, joka käy syntaksipuun läpi ja siirtää jokaista suhteellista viittausta seuraajan etäisyydellä juuresta, joten solun B2 seuraaja omistaa aidon kaavan =A2*3

Molemmat käyttäytymiset kiinnittävä regressiotesti kannattaa lukea, koska se kieltäytyy päästämästä yhteensattumaa läpi. Se rakentaa työkirjan, jossa on =A1*3 ja =A2*3 syötteillä 2 ja 4, ja ruiskuttaa sitten tahallaan väärät välimuistit 999 ja 888 _SetCellCachedFormulaValue-funktion kautta, kerran UseSharedFormulas päällä ja kerran pois. Tallennuksen ja uudelleenlatauksen jälkeen molempien solujen on yhä raportoitava 999 ja 888 — todiste siitä, että tallennus ei koskenut juuren eikä seuraajan välimuistiin. Vasta nimenomaisen Recalculate-kutsun jälkeen niiden on muututtava arvoiksi 6 ja 12, mikä todistaa, että seuraajan siirretty lauseke on oikea. Testi, joka kylväisi oikeat arvot, olisi mennyt läpi myös vanhalla kirjoittajalla, ja juuri siksi siihen kylvetään vääriä

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

    // Riipuvaisten kaavojen ladattuja välimuisteja EI mitätöidä
    // literaalimuokkauksella, joten pelkkä SaveAs pitäisi vanhat luvut.
    // Pyydä uudelleenlaskenta, kun todella haluat tuoreet tulokset:
    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;

Mitä välimuisti edellä -sopimus ei tee puolestasi

Välimuisti edellä -tallennus säilyttää sen, mikä ladattiin; se ei seuraa, onko ladattu yhä totta. Literaalin muuttaminen, josta kaava riippuu, merkitsee riippuvuusgraafin likaiseksi evaluaattorille, mutta se jättää riippuvan solun xlfcsLoaded-välimuistin paikalleen, ja klassinen kirjoittaja kirjoittaa mielellään tuon vanhentuneen arvon ellet kutsu Recalculate-funktiota tai lue ensin solun Value-arvoa, mikä laskee sen ja siirtää tilan arvoon xlfcsCalculated. Tämä on sama vaihtokauppa, jonka Excel tekee manuaalisessa laskentatilassa, ja se on oikea valinta putkelle, joka avaa kolmannen osapuolen tiedostoja, muokkaa muutaman otsikon ja tallentaa — mutta se tarkoittaa, että työkirjan, joka muokkaa syötteitä, on omistettava uudelleenlaskentavaiheensa nimenomaisesti. XLSX-kirjoittajan RecalcBeforeSave-käytäntö ei muutu tämän työn myötä, ja siinä on oma manuaalinen tilansa, joka säilyttää välimuistit samassa hengessä. Tästä seuraa kaksi pienempää rajaa: välimuisti edellä -polku auttaa vain soluja, joiden tila on xlfcsLoaded tai xlfcsCalculated; generaattori, joka kirjoittaa kaavoja eikä koskaan evaluoi niitä, maksaa yhä yhden evaluoinnin per solu tallennushetkellä, täsmälleen kuten ennenkin. Ja sisäkkäisen välisumman korjaus korjaa sen, mitkä solut evaluaattori ohittaa, ei jokaista funktiota, jonka evaluaattori toteuttaa — tiedosto, jonka kaavoja HotXLS ei osaa laskea identtisesti Excelin kanssa, on nyt turvallinen kierrättää koskemattomana, mutta tahallinen Recalculate tuottaa siinä yhä kirjaston vastauksen eikä Excelin, ja ne kaksi kannattaa vertailla ennen kuin luotat uudelleenlaskettuun tallennukseen

Välimuisti edellä tehdyt klassiset tallennukset, palautetut jako- ja taulukkokaavojen juurivälimuistit, suhteellisten viittausten siirto jakokaavojen seuraajille sekä korjatut SUBTOTAL- ja AGGREGATE-sisäkkäisyyssäännöt toimitetaan kaikki vakiossa HotXLS Delphi Spreadsheet Componentissa Delphille ja C++Builderille, ilman riippuvuutta Excelistä tai mistään OLE-automaatiopalvelimesta; tuotesivulla on täysi API-referenssi tässä käytetyille työkirjan, välimuistinlukijan ja uudelleenlaskennan rajapinnoille