HotXLS mehanizam za formule i prilagođene funkcije u Delphiju

Biblioteka za proračunske tablice koja samo pohranjuje nizove formula i biblioteka s funkcionalnim mehanizmom za formule dva su različita proizvoda koji izgledaju identično sve do trenutka kada od jednog od njih zatražite broj. Većina Delphi koda za proračunske tablice nikada ne primijeti tu razliku jer je Excel prikriva: zapišite SUM(B2:B501) u ćeliju, spremite i Excel ponovno izračunava ukupni iznos čim čovjek otvori datoteku. Ako izbacite čovjeka iz tog procesa i pokrenete istu radnu knjigu kroz serverski cjevovod koji izvozi izravno u CSV, razlika prestaje biti samo akademska. CSV će sadržavati doslovan tekst =SUM(B2:B501) tamo gdje bi trebao biti broj, jer u nijednom trenutku ništa zapravo nije izračunalo formulu.

To je granica na čijoj se pravoj strani nalazi HotXLS. On tretira formulu onako kako to čine formati datoteka, kao pohranjeni tekst uz izborni predmemorirani rezultat, tako da čisti izvoz u CSV reproducira recept, a ne samo jelo. No, on također sadrži mehanizam za izračun koji možete pozvati izravno, isti mehanizam u XLS i XLSX sučeljima, kao i kuku (hook) za razrješavanje naziva funkcija za koje mehanizam nikada nije čuo. HotXLS je nativna Object Pascal biblioteka koja čita i piše XLS i XLSX iz Delphija i C++Buildera bez automatizacije Excela, a njegova polovica za izračun je ono što pretvara pohranjene formule natrag u vrijednosti na zahtjev.

Formule se pohranjuju, ne izračunavaju se odmah

Pisanje formule u ćeliju ne izračunava ništa. Prilikom spremanja, radna knjiga bilježi tekst formule. Na strani XLS-a također bilježi zastavice kojima upravlja RecalcOnSave, što je prema zadanim postavkama postavljeno na True i govori Excelu da ponovno izračuna vrijednosti pri otvaranju. Taj je model ispravan za datoteke namijenjene Excelu, ali pogrešan za cjevovode koji izravno troše vrijednosti ćelija, bilo da se radi o izvozu u CSV, izvozu u HTML ili vašem vlastitom kodu koji ponovno čita ćelije. Za njih eksplicitno izvršite procjenu pomoću Calculate. On postoji na četiri ulazne točke: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook i TXLSXWorksheet svi izlažu 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

Izraz predan funkciji Calculate je običan tekst Excel formule. Reference između listova, definirani nazivi i ugniježđene funkcije rješavaju se u odnosu na trenutnu radnu knjigu u memoriji, što čini ovaj poziv korisnim daleko izvan pukog krpanja izvoza u CSV. Tretirajte ga kao mehanizam tvrdnje (assertion). Generator koji je upravo zapisao petsto redaka pojedinosti može zatražiti od radne knjige njezin vlastiti ukupni iznos i usporediti ga s brojem koji je neovisno izračunao u Pascalu, čime će otkriti pogrešku raspona za jedan (off-by-one) prije nego što to učini klijentov revizor.

To također definira ispravnu strategiju testiranja za izlaze koji se uvelike oslanjaju na formule. Excel ostaje referentna implementacija jezika formula, pa za nekoliko formula koje imaju poslovne posljedice držite odobrenu testnu datoteku (fixture) čije je očekivane vrijednosti proizveo sam Excel, te neka cjevovod izgradnje procijeni formule generirane radne knjige pomoću Calculate u odnosu na te testne datoteke. Razlike se tada pojavljuju kao neuspjeli testovi u Delphiu, umjesto kao odstupanja koja otkrije klijent uspoređujući dva izvješća.

Dodavanje poslovnih funkcija pomoću OnUserFunction

Kada mehanizam naiđe na naziv funkcije koji ne prepoznaje, on pokreće događaj umjesto da odmah javi pogrešku. Dodijelite OnUserFunction na bilo kojoj klasi radne knjige i možete sami razriješiti poziv:

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 detalja zaslužuju pozornost. Prvo, postavite Handled := True samo kada ste stvarno prepoznali naziv. Ostavljanje vrijednosti na False omogućuje mehanizmu da nastavi sa svojim uobičajenim rukovanjem nepoznatim funkcijama, tako da jedan rukovatelj može služiti za nekoliko radnih knjiga bez prisvajanja svega što prođe kroz njega. Drugo, uspoređujte nazive bez obzira na velika i mala slova pomoću SameText, budući da autori formula upisuju discount( i DISCOUNT( naizmjenično. Treće, argumenti stižu već procijenjeni: DISCOUNT(A1) predaje vam vrijednost ćelije A1, a ne referencu, tako da funkcija ne može znati odakle su došli njezini ulazni podaci. Ova zadnja točka postavlja ograničenje o kojem govori sljedeći odjeljak.

Tretirajte tijelo rukovatelja s istom obrambenom pažnjom kao i bilo koju vanjsku ulaznu točku. Polje Args odražava ono što je autor formule upisao, stoga provjerite broj i tipove argumenata prije indeksiranja u njega i unaprijed odlučite što nevažeći poziv vraća: Variant vrijednost pogreške ili pokrenutu iznimku. Taj je izbor važan jer se iznimka bačena unutar rukovatelja širi van kroz poziv Calculate koji je pokrenuo procjenu. To je prihvatljivo u strogo kontroliranom generatoru, ali nepristojno u usluzi koja procjenjuje radne knjige koje su izradili korisnici, gdje bi jedna loša formula mogla srušiti cijeli zahtjev. U tom okruženju uhvatite iznimku unutar rukovatelja i vratite sentinel vrijednost koju okolni tijek rada može prepoznati i zabilježiti.

Funkcije svjesne položaja trebaju varijantu Ex

Neke funkcije legitimno ovise o tome gdje se procjenjuju. Stopa koja se razlikuje po listu, traženje u odnosu na redak, multiplikator po regiji koji se primjenjuje samo na regionalnim listovima: ništa od toga se ne može odgovoriti samo na temelju vrijednosti argumenata. Običan događaj to ne može izraziti, pa mehanizam nudi OnUserFunctionEx, koji je identičan osim jednog dodatnog 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 sadrži SheetIndex, Row i Col ćelije koja se procjenjuje. Ako rezultat funkcije makar i malo ovisi o njezinu položaju, povežite događaj Ex od samog početka. Naknadno dodavanje konteksta u rukovatelj koji trideset formula već poziva daleko je neurednije od odabira pravog potpisa prvog dana, a ta su dva događaja inače toliko slična da nema previše razloga započeti s onim užim.

Prilagođene funkcije se ne prenose u Excel

Prilagođena funkcija živi u potpunosti unutar vašeg procesa. Naziv DISCOUNT znači nešto samo dok se izvodi vaš Delphi kod i njegov rukovatelj događajima. Otvorite spremljenu datoteku u Excelu i DISCOUNT je samo neprepoznat naziv; ćelija prikazuje #NAME? osim ako na korisnikovom računalu slučajno ne postoji odgovarajuća VBA funkcija ili dodatak. To je dizajnerska činjenica koja odvaja demo verziju od gotovog proizvoda i tjera vas na izbor koji morate donijeti namjerno, a ne otkriti ga kasnije.

Odlučite, po ćeliji, koji od dva ugovora isporučujete. Ćelije za koje je predviđeno da korisnik vidi njihovo ponovno izračunavanje unutar Excela moraju biti izgrađene isključivo iz Excelovog vlastitog rječnika funkcija. Ćelije čija je logika vlasnička trebaju se procijeniti unutar procesa pomoću Calculate i pohraniti kao obične vrijednosti, tako da se prilagođena funkcija ponaša kao interno pravilo izračuna, a ne kao sadržaj datoteke. Način kvara koji pouzdano stvara tikete podrške je srednji put: spremanje formule s prilagođenom funkcijom i očekivanje da je Excel poštuje.

Postoji skrivena prednost ugovora koji se odnosi samo na vrijednosti: on štiti intelektualno vlasništvo. Pravilo određivanja cijena koje se procjenjuje u vašem Delphi procesu i isporučuje kao broj ne može se reverzno inženjerirati iz radne knjige na način na koji se to može učiniti s vidljivom formulom, a korisnik ga ne može pokvariti uređivanjem neke od međuvrijednosti. Generatori računa, izvještaji o provizijama i cjenici gotovo uvijek pripadaju ovoj skupini. Slučaj kojem su doista potrebne aktivne formule je interaktivni "što-ako" model, gdje se od kupca očekuje da mijenja ulaze i promatra kretanje ukupnih vrijednosti, a oni moraju biti izgrađeni iz Excelovog vlastitog rječnika i definiranih naziva.

Načini izračuna, iteracija i R1C1: kontrole XLS sučelja

XLS sučelje izlaže postavke izračuna na razini BIFF-a koje Excel čita iz datoteke. CalculationMode prihvaća xlCalcManual, xlCalcAutomatic (zadano) ili xlCalcAutomaticExceptTables i određuje kako se Excel ponaša nakon što se datoteka otvori. Radna knjiga modela s tisućama formula često se lakše isporučuje u ručnom načinu rada, tako da primatelj odlučuje kada će se dogoditi proces ponovnog izračuna. EnableIteration (zadano False), zajedno s MaxIterations (zadano 100) i MaxIterationChange (zadano 0.001), otključava namjerne kružne reference vrste iterativne konvergencije koje se pojavljuju u nekim financijskim modelima. ReferenceStyle prebacuje između prikaza A1 i R1C1, a UseFullPrecision odražava Excelovu opciju preciznosti-kako-je-prikazano.

Ova svojstva žive na XLS sučelju jer se mapiraju na BIFF zapise; kada generirate .xlsx, planirajte formule tako da ne ovise o postavkama iteracije ili izračunajte konvergirane vrijednosti u Delphiu i zapišite rezultate.

Matrične formule: javna ulazna točka je XLSX

Stare matrične formule u stilu CSE stvaraju se pomoću TXLSXRange.SetArrayFormula:

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

Ekvivalentna metoda postoji u XLS hijerarhiji klasa, ali se nalazi u privatnom odjeljku, tako da ne postoji podržani način za stvaranje novih matričnih formula u .xls datotekama. Postojeće formule u otvorenim datotekama prenose se netaknute u oba smjera; ono što ne možete učiniti je stvarati ih. Pravilo koje slijedi prilično je jednostavno: kada su matrična svojstva dio zahtjeva, ciljajte .xlsx. Ako naslijeđeni .xls izlaz doista treba matrično ponašanje, pragmatičan put je izračunati rezultat matrice u Delphiu i upisati pojedinačne vrijednosti u ćelije.

Dva srodna štiva na ovoj stranici: definirani nazivi i formule između listova pokrivaju razrješavanje naziva koje mehanizam izvodi, a članak o izvozu u CSV i TSV opisuje ponašanje izvoza zbog kojeg je eksplicitni izračun nužan. Cjelokupna referenca mehanizma, uključujući skup funkcija, dolazi s HotXLS komponentom.