Knjižnica za preglednice, ki le shranjuje nize formul, in knjižnica z delujočim pogonom za formule sta dva različna izdelka, ki sta videti enaka vse do trenutka, ko od enega izmed njiju zahtevate številko. Večina kode za preglednice v Delphiju te vrzeli nikoli ne opazi, saj jo Excel prikrije: vpišite SUM(B2:B501) v celico, shranite in Excel preračuna skupno vsoto takoj, ko človek odpre datoteko. Odstranite človeka iz te verige, poženite isti delovni zvezek skozi strežniški cevovod, ki izvaža neposredno v CSV, in razlika preneha biti le akademska. CSV bo vseboval dobesedno besedilo =SUM(B2:B501) tam, kjer bi morala biti številka, saj formule na nobeni točki ni nihče dejansko ovrednotil
To je tista meja, na katere pravi strani stoji HotXLS. Formulo obravnava tako, kot jo formati datotek — kot shranjeno besedilo in neobvezen predpomnjen rezultat, tako da goli izvoz v CSV poustvari recept in ne jedi same. Vsebuje pa tudi računski pogon, ki ga lahko pokličete neposredno, isti pogon na obeh straneh XLS in XLSX ter priključek (hook) za razreševanje imen funkcij, za katere pogon še nikoli ni slišal. HotXLS je izvorna knjižnica Object Pascal, ki bere in piše datoteke XLS in XLSX iz Delphija ter C++Builderja brez avtomatizacije Excela, računski del pa je tisti, ki shranjene formule na zahtevo pretvori nazaj v vrednosti
Formule so shranjene, se ne vrednotijo vnaprej
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 upravlja RecalcOnSave, ki je privzeto True in Excelu naroči ponovni izračun ob odpiranju. Ta model je pravilen za datoteke, namenjene Excelu, in napačen za cevovode, ki neposredno porabljajo vrednosti celic, naj bo to izvoz v CSV, izvoz v HTML ali vaša lastna koda, ki bere celice nazaj. V teh primerih vrednotenje izvedite eksplicitno z metodo Calculate. Ta obstoji na štirih vstopnih točkah: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook in TXLSXWorksheet vse izpostavljajo 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 numberIzraz, posredovan metodi Calculate, je običajno besedilo Excelove formule. Reference med listi, definirana imena in gnezdene funkcije se razrešijo glede na trenutni delovni zvezek v pomnilniku, zaradi česar je klic uporaben precej dlje od samega krpanja izvozov v CSV. Obravnavajte ga kot mehanizem trditev (assertions). Generator, ki je pravkar zapisal petsto vrstic s podrobnostmi, lahko vpraša delovni zvezek po lastni skupni vsoti in to primerja s številko, ki jo je neodvisno izračunal v Pascalu, ter tako ujame napako odmika za ena (off-by-one) v območju, preden to stori strankin revizor
Prav tako določa pravilno strategijo testiranja za izhod z veliko formulami. Excel ostaja referenčna implementacija jezika za formule, zato za tistih nekaj formul, ki imajo poslovne posledice, ohranite potrjeno testno datoteko, katere pričakovane vrednosti je ustvaril sam Excel, gradbeni cevovod (build pipeline) pa naj ovrednoti formule ustvarjenega delovnega zvezka z metodo Calculate glede na te testne vzorce. Razlike se nato pokažejo kot neuspeli testi v Delphiju in ne kot neskladja, ki jih odkrije stranka pri primerjavi dveh poročil
Dodajanje poslovnih funkcij z OnUserFunction
Ko pogon naleti na ime funkcije, ki je ne prepozna, sproži dogodek namesto takojšnje napake. 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 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)');Tri podrobnosti si zaslužijo pozornost. Prvič, nastavite Handled := True le, ko ste dejansko prepoznali ime. Če ga pustite na False, pogon nadaljuje z običajno obdelavo neznanih funkcij, tako da lahko en sam rokovnik (handler) služi več delovnim zvezkom, ne da bi prevzel vse, kar potuje skozenj. Drugič, primerjajte imena brez razlikovanja med velikimi in malimi črkami z uporabo SameText, saj avtorji formul izmenično pišejo discount( in DISCOUNT(. Tretjič, argumenti prispejo že ovrednoteni: DISCOUNT(A1) vam preda vrednost celice A1 in ne njene reference, zato funkcija ne more vedeti, od kod so prišli njeni vhodi. Ta zadnja točka postavlja omejitev, o kateri govori naslednji razdelek
Telo rokovnika obravnavajte z enako mero previdnosti kot katero koli zunanjo vstopno točko. Polje Args odraža tisto, kar je napisal avtor formule, zato pred indeksiranjem preverite število in tip argumentov ter se vnaprej odločite, kaj vrne neveljaven klic: vrednost napake Variant ali sproženo izjemo (exception). Odločitev je pomembna, saj se izjema, sprožena znotraj rokovnika, razširi navzven prek klica Calculate, ki je sprožil ovrednotenje. To je sprejemljivo v tesno nadzorovanem generatorju in neprimerno v storitvi, ki vrednoti delovne zvezke, ki jih je napisal uporabnik, kjer bi ena napačna formula lahko sesula celotno zahtevo. V tem okolju ulovite izjemo znotraj rokovnika in vrnite označevalec (sentinel), ki ga lahko okoliški potek dela prepozna in zabeleži
Funkcije, ki zaznavajo položaj, potrebujejo različico Ex
Nekatere funkcije so legitimno odvisne od tega, kje se vrednotijo. Stopnja, ki se razlikuje glede na list, iskanje glede na vrstico, množitelj za posamezno regijo, ki velja le na regionalnih listih: ničesar od tega ni mogoče ugotoviti le iz vrednosti argumentov. Navaden dogodek tega ne more izraziti, zato pogon ponuja OnUserFunctionEx, ki je enak, razen enega dodatnega 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
// ista formula prinaša različno stopnjo na vsakem regionalnem listu
Value := RateForSheet(Context.SheetIndex) * Args[0];
Handled := True;
end;
end;TXLSUserFunctionContext prenaša SheetIndex, Row in Col celice, ki se vrednoti. Če je rezultat funkcije le malce odvisen od njene lokacije, že od začetka uporabite dogodek Ex. Naknadno dodajanje konteksta v rokovnik, ki ga že kliče trideset formul, je veliko bolj zapleteno kot izbira pravega podpisa (signature) prvi dan, dogodka pa sta si sicer tako podobna, da ni pravega razloga za začetek z ožjim
Prilagojene funkcije ne potujejo v Excel
Prilagojena funkcija živi v celoti znotraj vašega procesa. Ime DISCOUNT pomeni nekaj le takrat, ko se izvajata vaša koda v Delphiju in njen rokovnik dogodkov. Odprite shranjeno datoteko v Excelu in DISCOUNT bo le neprepoznano ime; celica bo prikazala #NAME?, razen če na uporabnikovem računalniku slučajno obstaja ustrezna funkcija VBA ali dodatek (add-in). To je oblikovalsko dejstvo, ki ločuje predstavitev od končnega izdelka, in vas prisili v odločitev, ki jo morate sprejeti namerno in ne šele pozneje odkriti
Za vsako celico se odločite, katero od dveh pogodb pošiljate. Celice, za katere je predvideno, da jih uporabnik vidi preračunane znotraj Excela, morajo biti zgrajene iz Excelovega lastnega besednjaka funkcij in ničesar drugega. Celice, katerih logika je lastniška, je treba ovrednotiti znotraj procesa z metodo Calculate in jih shraniti kot navadne vrednosti, tako da se prilagojena funkcija obnaša kot interno 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
Obstaja tiha prednost pogodbe le-vrednosti (values-only): varuje intelektualno lastnino. Pravila določanja cen, ovrednotenega v vašem procesu Delphi in odpremljenega kot številka, ni mogoče obratno inženiriti iz delovnega zvezka na način, kot je to mogoče pri vidni formuli, uporabnik pa ga ne more pokvariti z urejanjem vmesne celice. Generatorji računov, provizijska poročila in ceniki skoraj vedno sodijo v ta tabor. Primer, ki resnično potrebuje žive formule, pa je interaktivni model "kaj-če" (what-if), kjer se od stranke pričakuje, da bo spreminjala vnose in opazovala premikanje skupnih zneskov, ti pa morajo biti zgrajeni iz Excelovega lastnega besednjaka in definiranih imen
Načini preračunavanja, iteracija in R1C1: gumbi na strani XLS
Stran XLS izpostavlja nastavitve preračunavanja na ravni BIFF, ki jih Excel prebere iz datoteke. Lastnost CalculationMode sprejema vrednosti xlCalcManual, xlCalcAutomatic (privzeto) ali xlCalcAutomaticExceptTables in določa, kako se Excel obnaša, ko je datoteka odprta. Delovni zvezek z modelom, ki vsebuje na tisoče formul, je pogosto prijazneje dostaviti v ročnem načinu, tako da prejemnik sam odloči, kdaj se zgodi val preračunavanja. Lastnost EnableIteration (privzeto False) skupaj z MaxIterations (privzeto 100) in MaxIterationChange (privzeto 0.001) odkleni namerne krožne reference iterativne konvergence, ki se pojavljajo v nekaterih finančnih modelih. ReferenceStyle preklaplja med prikazom A1 in R1C1, lastnost UseFullPrecision pa zrcali Excelovo možnost natančnosti, kot je prikazana
Te lastnosti živijo na strani XLS, ker se preslikajo v zapise BIFF; pri ustvarjanju datotek .xlsx načrtujte formule tako, da niso odvisne od iterativnih nastavitev, ali pa izračunajte konvergirane vrednosti v Delphiju in zapišite rezultate
Poljske (Array) formule: javna vstopna točka je XLSX
Enakovredna metoda obstaja v razredni hierarhiji XLS, vendar se nahaja v privatnem delu, zato ni podprtega načina za ustvarjanje novih poljskih formul v datotekah .xls. Obstoječe v odprtih datotekah nemoteno potujejo skozi povratno pot (round-trip); ne morete pa jih ustvarjati. Pravilo, ki sledi, je preprosto: ko so del semantike zahteve polja, ciljajte na .xlsx. Če zapuščeni izdelek .xls resnično potrebuje poljsko obnašanje, je pragmatična pot izračun poljskega rezultata v Delphiju in zapis posameznih vrednosti v celice
Dve sorodni branji na tej strani: definirana imena in formule med listi obravnava razreševanje imen, ki ga izvaja pogon, članek o izvozu v CSV in TSV pa opisuje obnašanje izvoza, zaradi katerega je eksplicitni izračun nujen. Celotna referenca pogona, vključno z naborom podprtih funkcij, se prinaša s komponento HotXLS Component