Excel formulių talpyklos reikšmės Delphi be perskaičiavimo

HotXLS, natyvi Delphi ir C++Builder Excel biblioteka, skaito reikšmę, kurią Excel jau saugoja šalia formulės, per TryGetCachedFormulaValue ir IXLSFormulaCacheReader. Nė vienas įėjimo taškas nekviečia skaičiuoklės, nedekompiliuoja formulės tokenų, neatnaujina dirty būsenos ir nerašo nieko atgal į modelį, tad darbo knyga, kurią tik skaitote, lieka tiksli taip, kaip ją atidarėte

Scenarijus, kuris visa tai motyvuoja, nuobodus ir itin dažnas. Naktinė užduotis atidaro kelis šimtus darbo knygų, pagamintų kažkieno kito, iš kiekvienos ištraukia vieną sumų stulpelį ir siunčia skaičius į duomenų sandėlį. Sumos jau sėdi failuose — Excel jas apskaičiavo ir išsaugojo. Vis dėlto akimirka, kai užduotis paprašo formulės langelio jo reikšmės, biblioteka, turinti tik vieną atsakymą į tą klausimą, sukonstruoja priklausomybių grafą ir įvertina visą lapą, o užduotis, kuri turėtų būti I/O ribojama, pavirsta skaičiavimo bandymu

Kodėl formulės langelio skaitymas kainuoja pilną perskaičiavimą?

Nes reikšmės getteris formulės langelyje yra prašymas pagaminti reikšmę, o vienintelis universaliai teisingas būdas ją pagaminti yra įvertinti formulę. Tai teisingas numatytasis elgesys programai, kuri redaguoja darbo knygas, ir neteisingas numatytasis elgesys procesui, kuris jas ištraukia. Dar blogiau, įvertinimas nėra be šalutinių poveikių: jis rašo rezultatus atgal į langelius, apverčia dirty flagus ir gali išspresti kitaip nei gaminanti programa, kai funkcija nepalaikoma arba išorinė nuoroda sugedusi. Užduotis, kurią operacijų komandai aprašėte kaip tik-skaitymui, tyliai pagamina darbo knygą, kuri nebematuoja diske esančios, o jei kas nors vėliau ją išsaugoja, keičiasi ir failas diske

Talpyklos reikšmės skaitymas yra kita kontrakto pusė. Jis atsako į siauresnį klausimą — ką gaminanti programa čia saugojo? — ir atsisako atsakyti į ką nors kita. Kai iš tikrųjų norite šviežių skaičių, HotXLS vis tiek duoda priklausomybių grafo valdomą inkrementinį perskaičiavimą; esmė ta, kad ištraukimas ir įvertinimas turėtų būti du skirtingi iškvietimai, o ne vienas iškvietimas su dviem nuotaikomis

Trys ortogonalūs faktai apie vieną langelį

Pirmiausia išvada: talpyklinė formulės reikšmė neša tris nepriklausomus faktus, ir jų suskleidimas į vieną Variantą praranda jums reikalingą informaciją. TXLSFormulaCacheInfo juos laiko atskirtus kaip State, Kind ir Value. TXLSFormulaCacheState užrašo kilmę per penkis atvejus — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated ir xlfcsInvalidated — o TXLSFormulaCacheValueKind klasifikuoja naštą kaip xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean arba xlfcvError. Būtent ta atskyra leidžia buvimą pranešti sąžiningai: talpyklinis tuščias, talpyklinė tuščia eilutė, talpyklinis False, talpyklinis nulis ir talpyklinė klaida yra visos tikros reikšmės, todėl buvimo niekada negalima išspresti iš VarIsEmpty ar VarIsNull. TryGetCachedFormulaValue grąžina True tik xlfcsLoaded ir xlfcsCalculated, o grąžindamas False vis tiek užpildo diagnozuojamą būseną

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row ir Col čia visi skaičiuojami nuo vieneto
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

Kodėl talpyklinė reikšmė trūksta?

Yra tikslios keturios priežastys, kodėl TryGetCachedFormulaValue grąžina False, o būsena pasako, kuri galioja. xlfcsNotFormula reiškia, kad langelis turi literalą arba nieko apskritai, ir koordinatės už ribų suskleidžia į tą patį atsakymą. xlfcsMissing reiškia, kad langelis tikrai yra formulė, bet gamintojas nesaugojo jokios reikšmės naštos — dažnas rezultatas, kai generatorius rašo formules ir leidžia Excel užpildyti rezultatus pirmame atidaryme. xlfcsInvalidated reiškia, kad formulės tekstas buvo pakeistas po įkėlimo, tad reikšmė, kuri ten buvo, apibūdina išraišką, kurios nebėra. xlfcsCalculated, priešingai, yra sėkmės atvejis: jis žymi reikšmę, kurią šio seanso metu pagamino jūsų paties kodas arba HotXLS vertintojas, skirtingai nei xlfcsLoaded, atėjusi iš failo

Sąžiningumas dėl trūkstamos talpyklos svarbesnis už jos užklijavimą. HotXLS atsisako išgalvoti reikšmę, o išsaugodamas yra vienodai griežtas — tik xlfcsLoaded ir xlfcsCalculated išduoda talpyklinę reikšmę, o xlfcsMissing ir xlfcsInvalidated rašo vien formulę, vietoj to, kad užšaldytų pasenęs skaičių į failą. Tai palieka tris protingus atsakymus procese: praleisti eilutę ir užfiksuoti tarlą, sąmoningai perskaičiuoti tą vieną darbo knygą ir priimti kainą, arba įvertinti ir sulyginti. Jei įvertintas skaičius nesutampa su tuo, ką būtų parašiusi gaminanti programa, formulės įvertinimo seklys yra įrankis, kuriuo sužinote, kur dvi skaičiavimo eigos išsiskiria, vietoj spėlionės iš rezultato

Vienas skaitytuvas per klasikinį, OOXML ir ODF variklius

Procesas neturėtų rūpintis, ar ką tik atidarytas failas buvo BIFF, OOXML ar ODF. IXLSFormulaCacheReader yra vienintelis tik-skaitymui skirtas įėjimo taškas visiems trims: ir TXLSWorkbook.CreateFormulaCacheReader, ir TXLSXWorkbook.CreateFormulaCacheReader grąžina lengvą adapterį ant retų langelių paieškų, kurias kiekvienas variklis jau naudoja, su identiškomis nuo vieneto skaičiuojamomis lapo, eilutės ir stulpelio koordinatėmis. Darbo knygų klasės tyčia patys neįgyvendina tos sąsajos — sąsajos nuoroda į darbo knygą pakeistų jos nuosavybės semantiką ir leistų kvietėjams patekti pro gyvavimo nuomos laiką. Vietoj to, sunaikinus darbo knygą, išvalomas žalias rodyklė tos nuomos viduje, ir bet koks skaitytuvas, vis dar laikomas jūsų kodo, kelia EXLSFormulaCacheReaderInvalidated kitoje užkloje, vietoj nuorodos į išlaisvintą atmintį. Tai fail-fast gyvavimo patikra, ne lygiagretumo garantija

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // Nesuveikė joks skaičiuoklė, nepajudėjo joks dirty flag, Book nepakito
end;

Kur talpyklos baitai iš tikrųjų gyvena

Klasikiniuose .xls failuose talpykla yra Formula įrašo laukas FormulaValue, aštuoni baitai, aprašyti [MS-XLS] §2.5.133. Kai aukštasis žodis lygus $FFFF, našta nėra IEEE 754 double, o pažymėtas variantas, ir maketą lengva subtiliai sugadinti: varianto tipas sėdi val[0], o boolean arba BErr našta sėdi val[2], su val[1] neapibrėžtu. HotXLS anksčiau skaitė naštą iš val[1], o tai tas off-by-one, kuris iškyla tik ant konkrečių failų, kurie talpina boolean arba klaidą, o ne skaičių. Skaitytuvas ir bendrų formulių rašytojas dabar sutaria dėl tų pačių poslinkių, tad talpyklinis TRUE išgyvena įkėlimą ir išsaugojimą nesugadintas, vietoj irimo į triukšmą

Tipų tikslumas paketo formatuose yra atskira problema su savo spąstais. OOXML talpyklinė reikšmė kabo ant c elemento kaip <v>, kur t atributas įvardija tipą pagal ECMA-376 Part 1 §18.3.1.4. HotXLS skaito t="e" tiesiai į varError Variantą ir išsaugodamas susieja jį atgal į standartinį klaidos tekstą, tad klaidos niekada neapsimetą paprastais sveikaisiais — bet Delphi RTL čia jums nepadės, nes VarAsType(Integer, varError) kelia konversijos išimtį. Veikianti konstrukcija nustato TVarData.VType ir TVarData.VError tiesiogiai. Datos laikosi tos pačios disciplinos priešinga kryptimi: t="d" ir ODF datos reikšmės tipas yra aiškios tipų deklaracijos ir tampa varDate, o BIFF skaitinė talpykla visai neneša datos flago ir todėl lieka Double. HotXLS niekada nesprendžia datos iš langelio skaičiaus formato, nes skaičiaus formatas yra pateiktis, o talpykla yra duomenys. ODF prideda dar vieną atvejį, vertą žinoti — office:value-type="void" reiškia talpyklą, kuri yra, bet neneša reikšmės, ir kadangi ODF neturi klaidos reikšmės tipo, klaidos pavidalo tekstas išsaugomas kaip tekstas, vietoj pakėlimo į klaidą

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

Ar bendros formulės dalijasi savo talpyklinėmis reikšmėmis?

Ne, ir prielaida, kad kitaip, yra būdas, kuriuo apžvalga prabėga pranešdama tą patį skaičių visam stulpeliui. OOXML bendra formulė dalijasi tik formulės išraiška ir saugojimo optimizacija; kiekvienas nario langelis vis tiek valdo savo paties <v>. HotXLS todėl niekada neskleidžia šakninio nario talpyklos sekėjui, atėjusiam be reikšmės, ir sekėjas, įkeltas kaip xlfcsMissing, vis tiek praneša xlfcsMissing po išsaugojimo ir pakartotinio atidarymo. Jei svarstote, kaip grupė apskritai saugoma ir išskleidžiama, bendros formulės si atributo ir jo išskleidimo mechanika aptarta atskirai; talpyklos skaitymui taisyklė susitraukia į vieną eilutę — klauskite kiekvieno langelio, nepasitikėkite niekuo, dėl ko neklausėte

Talpyklinės reikšmės skaitymas, suvienodintas tarpvariklių skaitytuvas ir perskaičiavimo variklis, kurio galite pasirinkti ne kviesti, visi atkeliavo 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 API nuoroda į čia parodytus darbo knygos ir skaitytuvo įėjimo taškus