HotXLS formuliu variklis ir pasirinktines funkcijos Delphi

Skaiciuokliniu lenteliu biblioteka, kuri tik issaugo formules kaip teksta, ir biblioteka su veikiancio formuliu varikliu yra du skirtingi produktai, kurie atrodo identiski tol, kol nepaklausite vieno is ju skaiciaus. Dauguma Delphi skaiciuokliu kodo siojo skirtumo nepastebi, nes Excel ji uzdangstoatlieka: irasytas SUM(B2:B501) i lastele, issaugota ir Excel pats perskaiciuoja suma, kai zmogus atidaro faila. Pasalinkite zmogui is proceso, paleiskite ta pati darbaknygiu per serverio konvejeriu, kuris eksportuoja tiesiai i CSV, ir skirtumas nustoja buti teorinis. CSV faile yra literalinis tekstas =SUM(B2:B501) ten, kur turejo buti skaicius, nes joks procesas formuliu neivykde.

Tai yra linija, kurioje HotXLS atsiduria tinkamoje puseje. Jis formule traktuoja taip, kaip tai daro failų formatai: kaip issaugota teksta su pasirinktiniu saugyklos rezultatu, todel paprastas CSV eksportas atgamina receptu, o ne patiekala. Taciau jis taip pat turi apskaiciavimo varikliu, kuri galima istatyti tiesiai, ta pati varikliu tiek XLS, tiek XLSX fasaduose, plius kabliuka nezinomu funkciju pavadinimu sprendimui. HotXLS yra gimtojo Object Pascal biblioteka, skaitanti ir rasyanti XLS ir XLSX is Delphi ir C++Builder be Excel automatizavimo, o jo apskaiciavimo dalis pavercia issaugotas formules reiksmemis pagal poreiki.

Formuliai yra issaugomos, o ne skubomai ivykdomos

Formules irasas i lastele nieko neskaiciuoja. Issaugojimo metu darbaknygiu registruoja formules teksta. XLS puseje taip pat registruojami pozymiai, valdomu RecalcOnSave, kurio numatytoji reikme yra True ir nurodo Excel perskaiciuoti atidarius. Sitas modelis yra tinkamas failams, skirtiems Excel, ir netinkamas konvejeriams, kurie tiesiogiai naudoja lasteles reiksmes, nesvarbu, ar tai CSV eksportas, HTML eksportas, ar jusu paties kodas, skaitantis lastes atgal. Tam atlikite eksplicitu ivykdyma su Calculate. Ji galima istatyti keturiose vietose: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook ir TXLSXWorksheet visi atskleidzia function Calculate(const Formula: WideString): Variant.

// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // the CSV now carries the number

Israiska, perduodama Calculate, yra paprastas Excel formules tekstas. Kryztines lapu nuorodos, apibrezi pavadinimai ir isdestytos funkcijos visi raskomi dabartines atminties darbaknygiu, kas daro kvietimu naudinga ne tik CSV eksporto pataisymui. Traktuokite ji kaip patvirtinimo mechanizma. Generatorius, kuris tik iruse penkis simus detaliu eiluciu, gali paklausti darbaknygiu savo bendros sumos ir palyginti ja su skaiciumi, kurį savarankiskai apskaiciavo Pascal, pagaudamas vienos diapazono klaida pries kliento auditoriaus.

Tai taip pat apibrezii tinkama testavimo strategija formule sunkiai apkrautai isvestiai. Excel lieka referentine formuliu kalbos implementacija, todel del keliu formuliu, nesiancio verslo pasekmes, issaugokite patvirtinta fiksuoto faila, kurio laukiamos reiksmes buvo sukurtos paties Excel, ir leiskite kurybos konvejeriui ivertinti sugenruotos darbaknygiu formules su Calculate toms fiksacijoms. Skirtumai tada atranda kaip nepasiseke testai Delphi, o ne neatitikimai, kuriuos klientas aptinka lygindamas du ataskaitas.

Verslo funkciju pridejimas su OnUserFunction

Kai variklis sutinka funkcijos pavadinima, kurio nepazista, jis kelia renginI, o ne isgyvena. Priskirkite OnUserFunction bet kuriai darbaknygiu klasei ir galite pats issprusti kvietimu:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args arrives as a Variant array
    Handled := True;
  end;
end;

// wiring and use
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Trys detaliai vertes demesio. Pirma, nustatykite Handled := True tik tada, kai is tikruju atpazinote pavadinima. Paliekant False leidzia varikliui testi normaliu nezinomu funkciju tvarkyma, todel vienas tvarkytuvas gali tarnauti kelioms darbaknygems, nepretendamas i viska, kas praeina. Antra, lyginkite pavadinimus nejautriai didziuju raidziu naudojimui su SameText, nes formuliu autoriai raso discount( ir DISCOUNT( pakaitomis. Trecia, argumentai ateina is anksto ivykdyti: DISCOUNT(A1) perduoda jums A1 reiksmen, o ne nuoroda, todel funkcija negali zinoti, is kur atejo jos ivestys. Sia paskutine pastaba sudaro apribojima, apie kuri kalbama kitame skyriuje.

Tvarkytuvo kuna traktuokite su tokiu paciu atsargumu, kaip bet kuri isore punto prieiga. Args masyvas atspindi viska, ka formules autorius pasalino, todel patvirtinkite argumentu skaiciu ir tipus pries indeksavima i ji, ir is anksto nuspreskte, ka sugadinto kvietimas grazins: Variant klaidos reiksmen arba iskelta isimti. Pasirinkimas yra svarbus, nes isimtis, ismesta tvarkytuve, sklinda per Calculate kvietimu, kuris inicijavo ivykdyma. Tai priimtina griztai kontroliuojamame generatoriuje ir netinkama servise, ivykdanciame naudotojo sukurtas darbaknygias, kur viena bloga formule numusttu uzklausima. Tokiu atveju gaukite tvarkytuve ir grazinkite sentinela, kuri aplinkybes gali atpazinti ir uzregistruoti.

Pozicijoms jautrios funkcijos reikia Ex varianto

Kai kurios funkcijos teiseti priklauso nuo to, kur jos yra ivertinamos. Norma, skiriasi pagal lapu, eilute santykine paieska, regioninis koeficientas, kuris taikomas tik regioniniuose lapuose: nera imanoma atsakyti tik pagal argumentu reiksmes. Paprastas renginys to negali isreiksti, todel variklis siulo OnUserFunctionEx, identisku, iskyrus viena papildomu parametra:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // the same formula yields a different rate on each regional sheet
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext nesha SheetIndex, Row ir Col ivykdomos lasteles. Jei funkcijos rezultatas net nezymiai priklauso nuo jos vietos, is karto sujunkite Ex renginI. Konteksto integravimas i tvarkytuva, kuri jau kviecia trisdesimt formuliu, yra daug chaotiskesnis negu tinkamo paraso pasirinkimas pirma diena, o abu renginiai yra kitaip tokie panasus, kad mazo priezasciu pradeti nuo siauresniojo.

Pasirinktines funkcijos nekeliauja i Excel

Pasirinktine funkcija gyvena visiskai jusu procese. Pavadinimas DISCOUNT kuo nors reiskia tik tada, kai veikia jusu Delphi kodas ir jo renginio tvarkytuvas. Atidaryti issaugota faila Excel ir DISCOUNT yra tik neatpazintas pavadinimas; lastele rodo #NAME?, nebent atitinkama VBA funkcija ar priedo priedas atitinkamai egzistuoja naudotojo masinos. Tai yra projektavimo faktas, skiriantis demonstracija nuo pristatymo produkto, ir jis veriga pasirinkima, kuri turite priimti apgalvotai, o ne atraskit veliau.

Nuspreskte, pagal lastele, kuria is dvieju sutarciu siuncziate. Lasteles, kurias vartotojas yra skirtas matyti perskaiciuojamoms Excel viduje, turi buti sukurtos is paties Excel funkciju zodyno ir nieko kito. Lasteles, kuriy logika yra nuosava, turi buti ivykdomos procese su Calculate ir issaugotos kaip paprastos reiksmes, todel pasirinktine funkcija elgiasi kaip vidinis apskaiciavimo taisykliu, o ne failo turinio. Nesekmes rezimas, patikimai generuojantis palaikomo bilietus, yra vidutinis kelias: issaugant pasirinktines-funkcijos formule ir tikintis, kad Excel ja gerbs.

Yra tylus privalumas reiksmes-tik sutartyje: ji saugo intelektualiaja nuosavybe. Kainu taisykle, ivertinta jusu Delphi procese ir isspesta kaip skaicius, negali buti atkurta is darbaknyges taip, kaip matoma formule, o vartotojas negali jos sugadinti redaguodamas tarpine lastele. Saskaitu fakturos generatoriai, komisiniu ataskaitos ir kainu zinyynai beveik visada priklauso siam stovyklai. Atvejis, kuris is tikruju reikalauja gyvuju formuliu, yra interaktyvi kas-jei modelis, kur klientas tikisi keisti ivestis ir stebet, kaip juda sumos, ir tie turi buti sukurti is paties Excel zodyno bei apibrezt pavadinimu.

Apskaiciavimo rezimai, iteracija ir R1C1: XLS-fasado valdikliai

XLS fasadas atskleidzia BIFF lygio apskaiciavimo nustatymus, kuriuos Excel skaito is failo. CalculationMode priima xlCalcManual, xlCalcAutomatic (numatytasis) arba xlCalcAutomaticExceptTables, ir tai nulemia, kaip Excel elgiasi atidarius faila. Modelio darbaknygiu su tukstanciu formuliu daznai yra maloniau perduodama rankiniu rezimu, todel gavejo nusprendzia, kada ivyksta perskaiciavimo audra. EnableIteration (numatytasis False), kartu su MaxIterations (numatytasis 100) ir MaxIterationChange (numatytasis 0.001), atrakina tyciniu apvaliu nuorodu iteracinio-konvergencijos tipo, kuris pasirodo kai kuriuose finansiniuose modeliuose. ReferenceStyle perjungia tarp A1 ir R1C1 rodymo, o UseFullPrecision atspindi Excel tikslumas-kaip-rodyto parinktis.

Sios savybes yra XLS fasade, nes jos susiejamos su BIFF irasais; generuojant .xlsx, planuokite formules taip, kad jos nepriklausytu nuo iteraciniu nustatymu, arba apskaiciuokite konverguotas reiksmes Delphi ir rasykite rezultatus.

Masyvo formuliai: viesoji prieigos taskas yra XLSX

Sensaipas CSE stiliaus masyvo formuliai kuriamos per TXLSXRange.SetArrayFormula:

// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Atitikmens metodas egzistuoja XLS klases hierarchijoje, bet yra privaciai sekcijoje, todel nera palaikymo budo kurti nauju masyvo formuliu i .xls failus. Esamos atidarytose failuose apkeiciamos sveikomis; ko negalima padaryti, tai jas sukurti. Taisykle, kuri seka, yra pakankamai paprasta: kai masyvo semantika yra dalis reikalavimo, nusitaikykite i .xlsx. Jei sensas .xls pristatymas tikrai reikalauja masyvo elgesio, pragmatiskas kelias yra apskaiciuoti masyvo rezultata Delphi ir irasyti atskiras reiksmes i lasteles.

Du susije skaitymai siame svetainelyje: apibrezi pavadinimai ir kryztines lapu formuliai apima pavadinimo sprendima, kurį variklis atlieka, o CSV ir TSV eksporto straipsnis isleis eksporto elgesi, kuris daro ekspliciti apskaiciavima butinu. Pilnas varikliu nuoroda, iskaitant palaikomo funkciju rinkinI, pridedamas prie HotXLS Component.