HotXLS mehanizam formula i prilagođene funkcije u Delphi-ju

Biblioteka za tabele koja samo čuva stringove formula i biblioteka sa funkcionalnim mehanizmom za proračun formula su dva različita proizvoda koja izgledaju identično sve do trenutka kada od jednog od njih zatražite broj. Većina Delphi koda za rad sa tabelama nikada ne primeti ovu razliku, jer je Excel maskira: upišite SUM(B2:B501) u ćeliju, sačuvajte i Excel ponovo izračunava ukupnu vrednost čim čovek otvori datoteku. Izbacite čoveka iz te petlje, propustite istu radnu svesku kroz serverski cevovod koji izvozi direktno u CSV, i razlika prestaje da bude akademska. CSV će nositi doslovan tekst =SUM(B2:B501) tamo gde bi trebao biti broj, jer u čitavom tom procesu ništa zapravo nije evaluiralo formulu

To je linija na čijoj ispravnoj strani stoji HotXLS. On tretira formulu onako kako to čine sami formati datoteka — kao sačuvani tekst plus opcioni keširani rezultat, tako da čist CSV izvoz reprodukuje recept umesto gotovog jela. Međutim, on takođe nosi mehanizam za proračun koji možete pozvati direktno (isti mehanizam na XLS i XLSX fasadama), uz mogućnost presretanja i razrešavanja naziva funkcija za koje mehanizam nikada ranije nije čuo. HotXLS je izvorna Object Pascal biblioteka koja čita i piše XLS i XLSX iz Delphi-ja i C++Builder-a bez automatizacije Excel-a, a proračunski deo je ono što sačuvane formule po potrebi pretvara nazad u vrednosti

Formule se čuvaju, a ne evaluiraju se odmah (eagerly)

Upisivanje formule u ćeliju ne izračunava ništa. U vreme čuvanja, radna sveska beleži tekst formule. Na XLS strani, ona takođe beleži zastavice kojima upravlja RecalcOnSave, koje su podrazumevano postavljene na True i govore Excel-u da izvrši ponovni proračun prilikom otvaranja. Taj model je ispravan za datoteke namenjene Excel-u, a pogrešan za cevovode koji direktno konzumiraju vrednosti ćelija, bilo da je reč o izvozu u CSV, HTML ili vašem sopstvenom kodu koji čita ćelije nazad. Za te slučajeve vršite eksplicitnu evaluaciju pomoću metode Calculate. Ona postoji na četiri ulazne tačke: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook i TXLSXWorksheet — sve one izlažu funkciju 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 prosleđen metodi Calculate je običan tekst Excel formule. Reference na druge listove, definisana imena i ugnježđene funkcije se razrešavaju u odnosu na trenutnu radnu svesku u memoriji, što ovaj poziv čini korisnim daleko izvan pukog krpljenja CSV izvoza. Tretirajte ga kao mehanizam potvrde (assertion). Generator koji je upravo upisao pet stotina redova detalja može zatražiti od radne sveske sopstveni ukupni zbir i uporediti ga sa cifrom koju je nezavisno izračunao u Pascal-u, hvatajući grešku opsega "off-by-one" pre nego što to uradi klijentov revizor

To takođe uokviruje ispravnu strategiju testiranja za izlaz bogat formulama. Excel ostaje referentna implementacija jezika formula, pa za onih nekoliko formula koje nose poslovne posledice držite odobrenu datoteku sa test podacima (fixture) čije je očekivane vrednosti proizveo sam Excel, i podesite da razvojni cevovod evaluira formule generisane radne sveske pomoću Calculate u odnosu na te test podatke. Razlike se tada pojavljuju kao neuspešni testovi u Delphi-ju, umesto kao neslaganja koja otkriva klijent upoređujući dva izveštaja

Dodavanje poslovnih funkcija preko OnUserFunction

Kada mehanizam naiđe na naziv funkcije koju ne prepoznaje, on podiže događaj (event) umesto da odmah prijavi grešku. Dodelite proceduru događaju OnUserFunction na bilo kojoj klasi radne sveske i možete sami razrešiti taj 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 pažnju. Prvo, postavite Handled := True samo kada ste zaista prepoznali naziv funkcije. Ostavljanje na False omogućava mehanizmu da nastavi svoje uobičajeno rukovanje nepoznatim funkcijama, tako da jedan hendler može služiti za više radnih svezaka a da ne prisvaja sve što prođe kroz njega. Drugo, poredite nazive bez obzira na mala i velika slova koristeći SameText, jer autori formula pišu discount( i DISCOUNT( naizmenično. Treće, argumenti stižu već evaluirani: poziv DISCOUNT(A1) predaje vam vrednost ćelije A1, a ne referencu, tako da funkcija ne može znati odakle su njeni ulazi došli. Ta poslednja stavka postavlja ograničenje o kojem govori sledeći odeljak

Tretirajte telo hendlera sa istom defanzivnošću kao i bilo koju spoljnu ulaznu tačku. Niz Args odražava šta god da je autor formule napisao, pa validirajte broj i tipove argumenata pre nego što im pristupite preko indeksa, i odlučite unapred šta nevažeći poziv vraća: a Variant vrednost greške ili podignut izuzetak. Taj izbor je važan jer se izuzetak bačen unutar hendlera propagira napolje kroz poziv Calculate koji je pokrenuo evaluaciju. To je prihvatljivo u strogo kontrolisanom generatoru, ali je grubo u servisu koji evaluira radne sveske koje pišu korisnici, gde bi jedna loša formula srušila čitav zahtev. U tom okruženju, uhvatite izuzetak unutar hendlera i vratite sentinel vrednost koju okolni radni tok može prepoznati i zabeležiti u logu

Funkcijama koje zavise od pozicije potrebna je Ex varijanta

Neke funkcije opravdano zavise od mesta na kojem se evaluiraju. Stopa koja se razlikuje po listovima, pretraga relativna u odnosu na red, množilac po regionu koji se primenjuje samo na regionalnim listovima: na ništa od ovoga se ne može odgovoriti samo na osnovu vrednosti argumenata. Običan događaj to ne može izraziti, pa mehanizam nudi OnUserFunctionEx, koji je identičan osim što ima jedan dodatni parametar:

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;

Prilagođene funkcije ne putuju u Excel

Prilagođena funkcija živi u potpunosti unutar vašeg procesa. Naziv DISCOUNT znači nešto samo dok se vaš Delphi kod i njegov hendler događaja izvršavaju. Otvorite sačuvani fajl u Excel-u i DISCOUNT je samo neprepoznat naziv; ćelija prikazuje grešku #NAME? osim ako na korisničkoj mašini slučajno ne postoji odgovarajuća VBA funkcija ili dodatak (add-in). To je činjenica u dizajnu koja razlikuje demo primer od proizvoda spremnog za isporuku, i ona nameće izbor koji morate doneti svesno, a ne otkriti ga naknadno

Odlučite, po ćeliji, koji od dva ugovora isporučujete. Ćelije za koje je predviđeno da ih korisnik vidi kako se preračunavaju unutar Excel-a moraju biti izgrađene isključivo iz Excel-ovog sopstvenog rečnika funkcija. Ćelije čija je logika vlasnička trebalo bi evaluirati unutar procesa pomoću metode Calculate i sačuvati ih kao obične vrednosti, tako da se prilagođena funkcija ponaša kao interno pravilo proračuna a ne kao sadržaj fajla. Način otkazivanja koji pouzdano stvara tikete podrške jeste srednji put: čuvanje formule sa prilagođenom funkcijom i očekivanje da je Excel ispoštuje

Postoji tiha prednost ugovora koji čuva samo vrednosti: on štiti intelektualnu svojinu. Pravilo formiranja cena koje se evaluira u vašem Delphi procesu i isporučuje kao običan broj ne može se rekonstruisati (reverse-engineer) iz radne sveske na način na koji se može vidljiva formula, i korisnik ga ne može pokvariti izmenom neke međućelije. Generatorima faktura, izveštajima o provizijama i tarifnim karticama je skoro uvek mesto u ovom taboru. Slučaj kojem su zaista potrebne žive formule je interaktivni "šta-ako" (what-if) model, gde se od kupca očekuje da menja ulaze i posmatra kako se ukupne vrednosti pomeraju, a takvi modeli moraju biti izgrađeni iz Excel-ovog sopstvenog rečnika plus definisanih imena

Režimi proračuna, iteracije i R1C1: točkići na XLS fasadi

XLS fasada izlaže podešavanja proračuna na nivou BIFF-a koja Excel čita iz datoteke. Svojstvo CalculationMode prihvata vrednosti xlCalcManual, xlCalcAutomatic (podrazumevano) ili xlCalcAutomaticExceptTables, i određuje kako se Excel ponaša kada je fajl otvoren. Radnu svesku modela sa hiljadama formula je često prijatnije isporučiti u manuelnom režimu, tako da primalac odlučuje kada će se desiti proračunska oluja. Svojstvo EnableIteration (podrazumevano False), zajedno sa MaxIterations (podrazumevano 100) i MaxIterationChange (podrazumevano 0.001), otključava namerne kružne reference tipa iterativne konvergencije koje se pojavljuju u nekim finansijskim modelima. ReferenceStyle vrši prebacivanje između prikaza A1 i R1C1, a UseFullPrecision odražava Excel-ovu opciju preciznosti kako je prikazano (precision-as-displayed)

Ova svojstva žive na XLS fasadi jer se mapiraju na BIFF zapise; kada generišete .xlsx, planirajte formule tako da ne zavise od iterativnih podešavanja, ili izračunajte konvergirane vrednosti u Delphi-ju i upišite rezultate

Formule niza: javna ulazna tačka je XLSX

Tradicionalne CSE formule niza se kreiraju preko metode TXLSXRange.SetArrayFormula:

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

Ekvivalentna metoda postoji u hijerarhiji XLS klasa, ali se nalazi u privatnom odeljku, tako da ne postoji podržani način za upisivanje novih formula niza u .xls datoteke. Postojeće formule niza u otvorenim fajlovima prolaze kroz kružni tok netaknute; ono što ne možete jeste da ih kreirate. Pravilo koje sledi je prilično jednostavno: kada su semantike niza deo zahteva, ciljajte .xlsx. Ako isporučeni stariji .xls fajl zaista zahteva ponašanje niza, pragmatičan put je da izračunate rezultat niza u Delphi-ju i upišete pojedinačne vrednosti u ćelije

Dva srodna teksta na ovom sajtu: članak o definisanim imenima i međulistnim formulama pokriva razrešavanje imena koje vrši mehanizam, a članak o izvozu u CSV i TSV detaljno opisuje ponašanje pri izvozu koje čini eksplicitan proračun neophodnim. Kompletna referenca mehanizma, uključujući skup podržanih funkcija, isporučuje se sa HotXLS komponentom