XLS įrašymas Delphi be tylaus perskaičiavimo

HotXLS — savo Delphi ir C++Builder Excel biblioteka — klasikinę BIFF8 .xls darbaknygę įrašo cache principu: TXLSWorksheet.WriteFormula klausia TXLSWorkbook.TryGetCachedFormulaValue reikšmės, kurią Excel saugojo šalia kiekvienos formulės, ir vertintuvą kviečia tik tada, kai tos podėlyje laikomos reikšmės nėra arba ji negalioja. Darbaknygė, kurią atidarėte ir niekada nelietėte, įrašoma su tais pačiais skaičiais atgal, o nauji rezultatai reikalauja vieno aiškaus Recalculate kvietimo, o ne būna paslėptas SaveAs šalutinis poveikis

Klaida, privertusi šią sutartį iškelti į viešumą, buvo gėdingai maža. Korpuso faile nested-subtotals.xls yra bendra suma R2C4, kurios podėlyje laikoma reikšmė yra 37. Atidarykite jį su HotXLS, paklauskite TryGetCachedFormulaValue tos ląstelės — gaunate 37. Įrašykite nepakeitę nė vienos ląstelės, atidarykite įrašytą kopiją, paklauskite to paties — gaunate 67. Niekas API nebuvo paprašytas ką nors apskaičiuoti, o skaičius faile pajudėjo lygiai 30 — ir 30 yra būtent dviejų grupinių tarpinių sumų, 10 ir 20, esančių bendros sumos apimamoje srityje, suma

Kodėl įrašant XLS failą pasikeičia formulės reikšmė?

Kad 37 virstų 67, turėjo sutapti du nepriklausomi defektai, ir sutvarkius bet kurį vieną atskirai antrasis būtų likęs paslėptas. Pirmasis buvo struktūrinis: klasikinis rašytojas įrašydamas perskaičiuodavo kiekvieną formulę. Antrasis buvo tipo patikra, kuri niekada negalėjo būti teisinga iš disko įkeltai formulei, ir todėl vertintuvas įdėtas SUBTOTAL ląsteles skaičiavo du kartus. Korpuso failas buvo tiesiog pirmoji įvestis, kurioje įrašymo metu perskaičiuota reikšmė skyrėsi nuo Excel, ir kas nors tas dvi palygino. Struktūrinį defektą lengva nusakyti: iki v2.382.3 TXLSWorksheet.WriteFormula ir jo shared formulės brolis WriteFormulaWithTExp aštuonių baitų FormulaValue lauką kiekvienam Formula įrašui gaudavo kviesdami TXLSWorkbook.GetFormulaValue — tai yra vertintuvą. Podėlyje laikoma reikšmė, kurią ParseFormula kraudamas taip kruopščiai dekodavo iš šaltinio failo, išeinant nebuvo pasiteiraujama nė karto. Faktiškai kiekvienas įrašymas buvo pilnas perskaičiavimas, apeinantis darbaknygės lygmens perskaičiavimo API, tad niekas, ką būtumėte nustatę darbaknygėje, nebūtų to sustabdę. Bet kuri vieta, kur HotXLS vertintuvas nesutapo su Excel — ar teisėtai nepalaikoma funkcija, ar paprasta klaida — tapdavo tyliu duomenų pakeitimu įrašymo metu

Antrasis defektas gyveno vertintuvo naudojamame įdėtų tarpinių sumų atgaliniame iškvietime. Excel kiekvieną SUBTOTAL formą apibrėžia taip, kad ji ignoruoja ląsteles, kurių pačių formulė yra kitas SUBTOTAL, tad skaičiuotuvas lxCalc.pas faile agregavimo metu įjungia FIgnoreSubtotalCells ir per TXLSWorkbook.GetClassicIsSubtotalCell klausia darbaknygės, ar kiekviena srities ląstelė yra tokia. Tas atgalinis iškvietimas formulės tekstą gaudavo kaip Variant ir tikrindavo su VarType(f) = varOleStr. Tekstas iš GetUnCompiledFormula grįžta kaip Delphi String, o Variant priskirtas String yra varUString, niekada varOleStr. Predikatas buvo klaidingas kiekvienai ląstelei kiekviename įkeltame faile, grupinės tarpinės sumos būdavo įtraukiamos į bendrą sumą antrą kartą, ir įrašant, kai viskas perskaičiuojama, 10 + 20 + 7 virsdavo 67

// HotXLS 2.381 ir ankstesnės: iš String sukurta formulės Variant
// yra varUString, tad šis palyginimas niekada nepasiteisindavo
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr priima varString, varOleStr ir varUString,
// o AGGREGATE, kaip ir Excel, neįtraukiamas į jį apimančias tarpines sumas
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 išleido VarIsStr pataisą ir, būdama toje pačioje funkcijoje, išmokė atgalinį iškvietimą, kad AGGREGATE ląstelės taip pat neįtraukiamos į jas apimančias tarpines sumas. Vien to pakako, kad korpuso teiginys praeitų, nes perskaičiuota 37 dabar sutapo su įkelta 37. Bet bibliotekos sąžiningumo tai nesuteikė: įrašymas vis tiek perskaičiuodavo, o testas buvo žalias tik todėl, kad vertintuvas tame konkrečiame faile atsitiktinai sutapo su Excel. Taisyklės, kurias ląsteles SUBTOTAL ir AGGREGATE praleidžia, įskaitant paslėptas eilutes, aptartos straipsnyje apie SUBTOTAL, AGGREGATE ir paslėptas eilutes; čia svarbu tai, kad joks vertintuvas neturi turėti balso faile, kurio skaičiuoti neprašėte

Ką Excel garantuoja dėl podėlyje laikomų reikšmių įrašant?

Excel įrašymą traktuoja kaip momentinę nuotrauką, o ne skaičiavimo įvykį. Reikšmė, įrašoma į Formula įrašo FormulaValue lauką ([MS-XLS] §2.4.127, išdėstymas §2.5.133), yra tai, ką ląstelė šiuo metu rodo — rankinio skaičiavimo režimu tai gali būti pasenę ne metais — ir Excel ją vis tiek įrašo ištikimai. Perskaičiavimas yra atskira operacija su savo paleidikliu. HotXLS dabar klasikiniam įrašymui taiko tą pačią taisyklę: WriteFormula ir WriteFormulaWithTExp pirmiausia kviečia TryGetCachedFormulaValue, ima CacheInfo.Value, kai būsena yra xlfcsLoaded arba xlfcsCalculated, ir nusileidžia į GetFormulaValue tik esant xlfcsMissing ir xlfcsInvalidated. Skaitymo pusės šios sutarties dalis, įskaitant tai, ką reiškia kiekviena būsena ir kodėl podėlyje laikoma tuščia reikšmė arba False vis tiek laikoma reikšme, aprašyta straipsnyje Excel podėlyje laikomų formulių reikšmių skaitymas Delphi be perskaičiavimo

Atsarginis kelias sąmoningai paliktas, o ne pašalintas. Formulė, kurią šioje sesijoje priskyrėte per Cells[Row, Col].Formula, ateina be podėlyje laikomos reikšmės, o formulė, kurią pakeitėte įkeltoje ląstelėje, _SetCompiledFormula pažymima kaip xlfcsInvalidated; abi įrašant vertinamos lygiai kaip anksčiau, tad sugeneruota darbaknygė Excel vis tiek atsidaro su skaičiais. Kai net vertintuvas negali gauti reikšmės, rašytojas išveda nulinį turinį ir nustato fAlwaysCalc (§2.4.127 grbit bitas 0), kad Excel atidarydamas ląstelę perskaičiuotų, o ne pasitikėtų rezervuota vieta

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // Lapas, eilutė ir stulpelis nuo 1: R2C4 pirmame lape
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // vertintuvas nenaudojamas ląstelėms su podėliu
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 faile nested-subtotals.xls
    // Įrašymas, kuris perskaičiuotų, čia būtų įrašęs 67
  finally
    Book.Free;
  end;
end;

Kur BIFF shared formulės šaknis laiko savo podėlyje esančią reikšmę?

Savo pačiame Formula įraše, kaip ir kiekviena kita formulės ląstelė, ir būtent tai bendros grupės šaknies ląstelę pavertė vienintele vieta, kur įrašymas cache principu vis dar prarasdavo. BIFF8 shared formulė saugoma kaip ShrFmla įrašas ([MS-XLS] §2.4.260), einantis po viršutinės kairiosios ląstelės Formula įrašo, ir kiekviena grupės ląstelė, įskaitant šaknį, neša rgce, sudarytą iš vieno PtgExp tokeno (§2.5.198): pirmas išanalizuotos išraiškos baitas yra $01, po jo eina šaknies ląstelės eilutė ir stulpelis. Sekančios ląstelės yra savarankiškos — HotXLS perskaito kiekvienos FormulaValue ir išraišką išsprendžia susirasdamas šaknies sukompiliuotą formulę. Šaknies ląstelė yra kitokia, nes tuo metu, kai analizuojamas jos Formula įrašas, išraiškos dar nėra; ji ateina vienu įrašu vėliau

Būtent tame vieno įrašo tarpelyje podėlyje laikoma reikšmė ir dingo. TXLSReader.ParseFormula dekoduoja podėlyje laikomą reikšmę ir, pamatęs PtgExp, kurio koordinatės sutampa su pačios ląstelės, įsimena ląstelę FSharedFormulaRow ir FSharedFormulaCol bei paskelbia reikšmę ląstelei. Kai ateina ShrFmla įrašas ($04BC), ParseSharedFormula sukompiliuoja išraišką ir įdiegia ją su _SetCompiledFormula, o _SetCompiledFormula daro tai, ką privalo daryti dėl bet kokio formulės pakeitimo: išvalo FCachedFormulaValue ir atstato būseną į xlfcsMissing. Tad įkeltąja šaknies 37 būdavo išmetama, kol niekas nespėdavo jos perskaityti, TryGetCachedFormulaValue pranešdavo, kad šaknis neturi podėlyje laikomos reikšmės, o rašytojas, veikiantis cache principu, klusniai nusileisdavo į vertintuvą būtent tai ląstelei, į kurią visi ir žiūrėjo. Array įrašas (§2.4.4) turi tą pačią eiliškumo ypatybę ir turėjo tą pačią skylę

v2.382.3 pataisa prideda trečią lauką, FSharedFormulaCachedValue, šalia laukiančių šaknies koordinačių. ParseFormula į jį nugula dekoduotą reikšmę, kai atpažįsta šaknį, o tiek ParseSharedFormula, tiek ParseArrayFormula iškart po sukompiliuotos išraiškos įdiegimo ją atkuria per _SetCellCachedFormulaValue, o paskui atstato nugultą reikšmę į Unassigned. Eilutinė podėlyje laikomos reikšmės atmaina visa to nepaveikia, nes jos turinys ateina atskirame String įraše ir keliauja pagal ląstelės koordinates, o ne pagal įrašų tvarką. Jei dirbate su OOXML puse to paties dalyko, straipsnis apie XLSX shared formulės si plėtimą paaiškina, kodėl paketo formatas tokios eiliškumo problemos neturi, bet turi savų plėtimo spąstų

Kodėl sekantiems shared formulės nariams reikia santykinio poslinkio?

Nes ShrFmla saugoma išraiška užrašyta šaknies ląstelės atžvilgiu, o ją pažodžiui panaudojęs sekantis narys įvertina šaknies nuorodas, o ne savąsias. Senasis skaitytuvas kiekvienam sekėjui įdiegdavo Value.GetCopy() — gilią kopiją be poslinkio, tad grupė, kurios šaknis B1 su =A1*3, kiekvienam sekėjui duodavo irgi =A1*3. Įrašymas cache principu įkeltiems failams tai iš tikrųjų maskuodavo, nes sekėjai turėjo savo FormulaValue ir išraiškos teisingam įrašymui nereikėdavo; tai išlindo tą akimirką, kai kas nors perskaičiavo. Dabar skaitytuvas įdiegia TXLSCompiledFormula.GetCopy(row - srow, col - scol), kuris pereina sintaksės medį ir kiekvieną santykinę nuorodą pastumia sekėjo atstumu nuo šaknies, tad sekėjas B2 gauna tikrą =A2*3

Regresinis testas, įtvirtinantis abu šiuos elgesius, vertas dėmesio, nes neleidžia atsitiktinumui praeiti. Jis sukuria darbaknygę su =A1*3 ir =A2*3 su įvestimis 2 ir 4, o paskui per _SetCellCachedFormulaValue įterpia tyčia neteisingas podėlyje laikomas reikšmes 999 ir 888 — kartą su įjungtu UseSharedFormulas, kartą su išjungtu. Po įrašymo ir pakartotinio įkėlimo abi ląstelės vis tiek turi pranešti 999 ir 888 — tai įrodymas, kad įrašymas nelietė nei šaknies, nei sekėjo reikšmės. Tik po aiškaus Recalculate jos turi virsti 6 ir 12 — įrodymas, kad sekėjo pastumta išraiška teisinga. Testas, kuris būtų įsėjęs tikrąsias reikšmes, būtų praėjęs ir su senuoju rašytoju, ir būtent todėl sėjamos neteisingos

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

    // Įkeltos priklausomų formulių reikšmės NEpažymimos negaliojančiomis
    // pakeitus literalą, tad paprastas SaveAs išlaikytų senus skaičius.
    // Prašykite perskaičiavimo, kai tikrai norite naujų rezultatų:
    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;

Ko cache principo sutartis už jus nepadaro

Įrašymas cache principu išsaugo tai, kas buvo įkelta; jis neseka, ar tai, kas įkelta, dar teisinga. Pakeitus literalą, nuo kurio priklauso formulė, vertintuvui priklausomybių grafas pažymimas nešvariu, bet priklausomos ląstelės xlfcsLoaded reikšmė lieka vietoje, ir klasikinis rašytojas mielai įrašys tą pasenusią reikšmę, nebent pirmiau pakviesite Recalculate arba perskaitysite ląstelės Value, kas ją apskaičiuoja ir perkelia būseną į xlfcsCalculated. Tai tie patys mainai, kuriuos Excel daro rankinio skaičiavimo režimu, ir jie teisingi grandinei, kuri atidaro trečiųjų šalių failus, paredaguoja keletą etikečių ir įrašo — bet tai reiškia, kad darbaknygė, keičianti įvestis, privalo pati aiškiai atlikti perskaičiavimo žingsnį. XLSX rašytojo RecalcBeforeSave politika šio darbo nepakeista ir turi savo rankinį režimą, išsaugantį podėlyje laikomas reikšmes ta pačia dvasia. Iš to plaukia dvi mažesnės ribos: cache principo kelias padeda tik toms ląstelėms, kurių būsena yra xlfcsLoaded arba xlfcsCalculated; generatorius, rašantis formules ir niekada jų nevertinantis, vis tiek įrašydamas moka po vieną vertinimą už ląstelę, lygiai kaip ir anksčiau. O įdėtų tarpinių sumų pataisa sutvarko tai, kurias ląsteles vertintuvas praleidžia, o ne kiekvieną vertintuvo įgyvendinamą funkciją — failas, kurio formulių HotXLS negali apskaičiuoti identiškai kaip Excel, dabar yra saugus kelionei pirmyn ir atgal neliestas, bet sąmoningas Recalculate tame faile vis tiek duos bibliotekos atsakymą, o ne Excel, ir prieš pasitikėdami perskaičiuotu įrašymu turėtumėte abu palyginti

Klasikinis įrašymas cache principu, atkurtos shared ir array formulių šaknų reikšmės, santykinis nuorodų poslinkis sekantiems shared nariams ir sutvarkytos SUBTOTAL bei AGGREGATE įdėjimo taisyklės — visa tai platinama standartiniame HotXLS Delphi Spreadsheet Component Delphi ir C++Builder, be jokios priklausomybės nuo Excel ar kokio nors OLE automatizavimo serverio; produkto puslapyje yra visa čia naudojamų darbaknygės, reikšmių skaitytuvo ir perskaičiavimo įėjimo taškų API informacija