Pogon formul HotXLS in prilagojene funkcije v Delphiju

Knjižnica za preglednice, ki hrani zgolj nize formul, in knjižnica z delujočim pogonom formul sta dva različna izdelka, ki sta videti enaka vse do trenutka, ko od enega od njiju zahtevate število. Večina kode za preglednice v Delphiju te vrzeli nikoli ne opazi, ker jo Excel prekrije: v celico zapišite SUM(B2:B501), shranite in Excel vsoto preračuna v trenutku, ko datoteko odpre človek. Človeka odstranite iz zanke, isti delovni zvezek poženite skozi strežniški cevovod, ki izvaža naravnost v CSV, in razlika neha biti akademska. CSV na mestu, kamor je sodilo število, nosi dobesedno besedilo =SUM(B2:B501), ker formule v resnici ni nikoli nihče ovrednotil

To je črta, na katere pravi strani stoji HotXLS. Formulo obravnava tako, kot to počnejo zapisi datotek, torej kot shranjeno besedilo in neobvezen predpomnjeni rezultat, zato goli izvoz CSV reproducira recept namesto jedi. Hkrati pa nosi tudi pogon za izračun, ki ga lahko pokličete neposredno, isti pogon v fasadi XLS in XLSX, poleg tega pa še zanko za razreševanje imen funkcij, za katera pogon še ni nikoli slišal. HotXLS je izvorna knjižnica v Object Pascalu, ki iz Delphija in C++Builderja bere ter zapisuje XLS in XLSX brez avtomatizacije Excela, njena računska polovica pa je tista, ki shranjene formule na zahtevo spet spremeni v vrednosti

Formule se shranijo, ne pa vnaprej ovrednotijo

Zapis formule v celico ne izračuna ničesar. Ob shranjevanju delovni zvezek zabeleži besedilo formule. Na strani XLS zabeleži tudi zastavice, ki jih ureja RecalcOnSave; ta je privzeto True in Excelu naroči, naj ob odpiranju preračuna. Ta model je pravilen za datoteke, namenjene Excelu, in napačen za cevovode, ki vrednosti celic uporabijo neposredno, pa naj gre za izvoz v CSV, izvoz v HTML ali vašo lastno kodo, ki celice bere nazaj. Za te ovrednotite izrecno z metodo Calculate. Ta obstaja na štirih vstopnih točkah: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook in TXLSXWorksheet vsi izpostavljajo function Calculate(const Formula: WideString): Variant

// ovrednotite v procesu, nato pošljite vrednost namesto recepta
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV zdaj nosi število

Izraz, ki ga izročite metodi Calculate, je običajno besedilo Excelove formule. Reference med listi, definirana imena in gnezdene funkcije se vsi razrešijo glede na trenutni delovni zvezek v pomnilniku, zaradi česar je klic uporaben precej dlje od popravljanja izvozov v CSV. Obravnavajte ga kot mehanizem za trditve. Generator, ki je pravkar zapisal petsto podrobnih vrstic, lahko delovni zvezek povpraša po njegovi lastni skupni vsoti in jo primerja s številko, ki jo je neodvisno izračunal v Pascalu, ter tako ujame napako območja za ena, preden to stori revizor pri stranki

Prav tako začrta pravo strategijo preizkušanja za izhode, bogate s formulami. Excel ostaja referenčna izvedba jezika formul, zato za peščico formul, ki nosijo poslovne posledice, hranite odobreno datoteko s pripravljenimi vrednostmi, katerih pričakovane vrednosti je ustvaril Excel sam, gradbeni cevovod pa naj formule ustvarjenega delovnega zvezka ovrednoti z metodo Calculate in jih primerja s temi vrednostmi. Razlike se tedaj pokažejo kot padli testi v Delphiju in ne kot neskladja, ki jih odkrije stranka ob primerjavi dveh poročil

Dodajanje poslovnih funkcij z OnUserFunction

Ko pogon naleti na ime funkcije, ki ga ne prepozna, namesto takojšnje odpovedi sproži dogodek. Dodelite OnUserFunction na katerem koli razredu delovnega zvezka in klic lahko razrešite sami:

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 prispe kot polje variant
    Handled := True;
  end;
end;

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

Trije podatki si zaslužijo pozornost. Prvič, Handled := True nastavite le takrat, ko ste ime resnično prepoznali. Če ostane False, lahko pogon nadaljuje svoje običajno ravnanje z neznanimi funkcijami, tako da lahko en sam upravljalnik streže več delovnim zvezkom, ne da bi prevzel vse, kar gre mimo. Drugič, imena primerjajte brez upoštevanja velikosti črk z SameText, saj avtorji formul izmenično tipkajo discount( in DISCOUNT(. Tretjič, argumenti prispejo vnaprej ovrednoteni: DISCOUNT(A1) vam izroči vrednost celice A1 in ne reference, zato funkcija ne more vedeti, od kod so prišli njeni vhodi. Zadnja točka napeljuje na omejitev, o kateri govori naslednji razdelek

Telo upravljalnika obravnavajte z enako obrambno držo kot katero koli zunanjo vstopno točko. Polje Args odraža vse, kar je natipkal avtor formule, zato pred indeksiranjem preverite število in vrste argumentov ter se vnaprej odločite, kaj vrne neveljaven klic: vrednost napake Variant ali sprožena izjema. Izbira je pomembna, ker se izjema, vržena znotraj upravljalnika, razširi navzven skozi klic Calculate, ki je sprožil ovrednotenje. To je sprejemljivo v tesno nadzorovanem generatorju in nesramno v storitvi, ki ovrednoti delovne zvezke, ki so jih napisali uporabniki, kjer bi ena sama slaba formula podrla celotno zahtevo. V takem okolju ujemite napako znotraj upravljalnika in vrnite stražno vrednost, ki jo okoliški potek dela lahko prepozna in zabeleži

Funkcije, ki se zavedajo položaja, potrebujejo različico Ex

Nekatere funkcije so upravičeno odvisne od tega, kje se ovrednotijo. Stopnja, ki se razlikuje po listih, iskanje glede na vrstico, množitelj za posamezno regijo, ki velja le na regijskih listih: nič od tega ni mogoče odgovoriti zgolj z vrednostmi argumentov. Navadni dogodek tega ne zna izraziti, zato pogon ponuja OnUserFunctionEx, ki je enak, le da ima en dodaten parameter:

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
    // ista formula da drugačno stopnjo na vsakem regijskem listu
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

Zapis TXLSUserFunctionContext nosi SheetIndex, Row in Col celice, ki se ovrednoti. Če je rezultat funkcije le količkaj odvisen od njenega položaja, dogodek Ex priklopite že od začetka. Naknadno vgrajevanje konteksta v upravljalnik, ki ga kliče že trideset formul, je precej bolj zamudno kot izbira pravega podpisa prvi dan, sicer pa sta si dogodka tako podobna, da ni prav veliko razloga za začetek pri ožjem

Prilagojene funkcije ne potujejo v Excel

Prilagojena funkcija živi v celoti znotraj vašega procesa. Ime DISCOUNT pomeni nekaj le, dokler tečeta vaša koda v Delphiju in njen upravljalnik dogodkov. Odprite shranjeno datoteko v Excelu in DISCOUNT je zgolj neprepoznano ime; celica pokaže #NAME?, razen če na uporabnikovem računalniku slučajno obstaja ujemajoča se funkcija VBA ali dodatek. To je oblikovalsko dejstvo, ki loči predstavitev od izdelka, ki ga je mogoče odpremiti, in vas prisili v odločitev, ki jo je bolje sprejeti načrtno kot odkriti pozneje

Za vsako celico posebej se odločite, katero od dveh pogodb odpremljate. Celice, ki naj bi jih uporabnik videl, kako se preračunajo znotraj Excela, morajo biti zgrajene iz Excelovega lastnega besedišča funkcij in nič drugega. Celice, katerih logika je lastniška, ovrednotite v procesu z metodo Calculate in jih shranite kot navadne vrednosti, tako da se prilagojena funkcija obnaša kot notranje računsko pravilo in ne kot vsebina datoteke. Način odpovedi, ki zanesljivo ustvarja zahtevke za podporo, je vmesna pot: shranjevanje formule s prilagojeno funkcijo in pričakovanje, da jo bo Excel upošteval

Pogodba, ki hrani samo vrednosti, ima tiho prednost: ščiti intelektualno lastnino. Cenovnega pravila, ovrednotenega v vašem procesu v Delphiju in odpremljenega kot število, iz delovnega zvezka ni mogoče obrniti nazaj tako kot vidne formule, uporabnik pa ga ne more pokvariti z urejanjem vmesne celice. Generatorji računov, obračuni provizij in cenovne kartice skoraj vedno sodijo v ta tabor. Primer, ki žive formule resnično potrebuje, je interaktivni model kaj-če, kjer naj bi stranka spreminjala vhode in gledala, kako se premikajo vsote; te je treba zgraditi iz Excelovega lastnega besedišča in definiranih imen

Načini izračuna, ponavljanje in R1C1: gumbi fasade XLS

Fasada XLS izpostavlja nastavitve izračuna na ravni BIFF, ki jih Excel bere iz datoteke. Lastnost CalculationMode sprejme xlCalcManual, xlCalcAutomatic (privzeto) ali xlCalcAutomaticExceptTables in določa, kako se Excel obnaša, ko je datoteka odprta. Model delovnega zvezka s tisoči formul je pogosto prijaznejši, če ga dostavite v ročnem načinu, tako da prejemnik sam določi, kdaj se zgodi vihar preračunavanja. Lastnost EnableIteration (privzeto False) skupaj z MaxIterations (privzeto 100) in MaxIterationChange (privzeto 0,001) odklene načrtne krožne reference tiste vrste s ponavljajočim se približevanjem, ki se pojavljajo v nekaterih finančnih modelih. Lastnost ReferenceStyle preklaplja med prikazom A1 in R1C1, UseFullPrecision pa zrcali Excelovo možnost natančnosti, kot je prikazana

Te lastnosti živijo na fasadi XLS, ker se preslikajo v zapise BIFF; ko ustvarjate .xlsx, formule načrtujte tako, da niso odvisne od nastavitev ponavljanja, ali pa približane vrednosti izračunajte v Delphiju in zapišite rezultate

Poljske formule: javna vstopna točka je XLSX

Klasične poljske formule v slogu CSE se ustvarijo prek metode TXLSXRange.SetArrayFormula:

// ena poljska formula, ki obsega A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Enakovredna metoda obstaja v hierarhiji razredov XLS, vendar leži v zasebnem razdelku, zato ni podprtega načina za pisanje novih poljskih formul v datoteke .xls. Obstoječe v odprtih datotekah preživijo obhod nedotaknjene; tisto, česar ne morete, je ustvariti jih. Pravilo, ki iz tega sledi, je dovolj preprosto: kadar je semantika polj del zahteve, ciljajte na .xlsx. Če izdelek v klasičnem .xls resnično potrebuje obnašanje polj, je pragmatična pot, da rezultat polja izračunate v Delphiju in posamezne vrednosti zapišete v celice

Dve sorodni branji na tem mestu: definirana imena in formule med listi pokrivata razreševanje imen, ki ga opravi pogon, članek o izvozu CSV in TSV pa podrobno opisuje obnašanje izvoza, zaradi katerega je izrecni izračun nujen. Popolna referenca pogona, vključno s podprto množico funkcij, je priložena paketu HotXLS Delphi Component