HotXLS mehanizam za formule i prilagođene funkcije u Delphi-ju

Biblioteka za radne tabele koja samo čuva stringove formula i biblioteka sa funkcionalnim mehanizmom za formule 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 radne tabele nikada ne primeti ovu razliku jer je Excel prikriva: upišite SUM(B2:B501) u ćeliju, sačuvajte, i Excel ponovo izračunava ukupan iznos čim čovek otvori fajl. Izbacite čoveka iz tog procesa, provucite istu radnu svesku kroz serverski proces koji izvozi direktno u CSV, i ta razlika prestaje da bude akademska. CSV sadrži doslovni tekst =SUM(B2:B501) tamo gde bi trebalo da bude broj, jer ni u jednom 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 sačuvani tekst plus opcioni keširani rezultat, tako da čist CSV izvoz reprodukuje recept umesto gotovog jela. Ali on takođe sadrži mehanizam za proračun koji možete direktno pozvati, isti mehanizam u XLS i XLSX fasadama, kao i hook za rešavanje naziva funkcija za koje mehanizam nikada nije čuo. HotXLS je izvorna (native) Object Pascal biblioteka koja čita i upisuje XLS i XLSX iz Delphi-ja i C++Builder-a bez Excel automatizacije, a njegov računski deo je ono što sačuvane formule na zahtev pretvara nazad u vrednosti.

Formule se čuvaju, ne izračunavaju se odmah

Upisivanje formule u ćeliju ne izračunava ništa. Prilikom čuvanja, radna sveska snima tekst formule. Na strani XLS-a, ona takođe snima zastavice kojima upravlja RecalcOnSave, koji podrazumevano ima vrednost True i govori Excel-u da ponovo izračuna vrednosti prilikom otvaranja. Taj model je ispravan za datoteke namenjene Excel-u, a pogrešan za procese koji direktno troše vrednosti ćelija, bilo da je reč o izvozu u CSV, HTML ili vašem sopstvenom kodu koji ponovo čita ćelije. Za njih izvršite eksplicitno izračunavanje pomoću funkcije Calculate. Ona postoji na četiri ulazne tačke: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook i TXLSXWorksheet i sve 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 prosleđen funkciji Calculate je običan tekst Excel formule. Reference između listova, definisani nazivi i ugnežđene funkcije se razrešavaju u odnosu na trenutnu radnu svesku u memoriji, što ovaj poziv čini korisnim i van samog ispravljanja CSV izvoza. Tretirajte ga kao mehanizam asercije (potvrđivanja). Generator koji je upravo upisao pet stotina detaljnih redova može zatražiti od radne sveske sopstveni ukupni zbir i uporediti ga sa vrednošću koju je nezavisno izračunao u Pascal-u, hvatajući grešku opsega za jedan ("off-by-one") pre nego što to uradi revizor klijenta.

Takođe postavlja ispravnu strategiju testiranja za izlaz sa mnogo formula. Excel ostaje referentna implementacija jezika formula, pa za onih nekoliko formula koje imaju poslovne posledice, čuvajte odobrenu testnu datoteku (fixture) čije je očekivane vrednosti proizveo sam Excel, i podesite proces izgradnje (build pipeline) tako da izračunava formule generisane radne sveske pomoću Calculate u odnosu na te testne podatke. Razlike će se tada pojaviti kao neuspešni testovi u Delphi-ju, umesto kao neslaganja koja otkrije klijent poredeći dva izveštaja.

Dodavanje poslovnih funkcija pomoću OnUserFunction

Kada mehanizam naiđe na naziv funkcije koji ne prepoznaje, on pokreće događaj umesto da odmah prijavi grešku. Dodelite OnUserFunction na bilo kojoj klasi radne sveske i možete sami razreš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 pažnju. Prvo, postavite Handled := True samo kada ste zaista prepoznali naziv. Ostavljanje na False omogućava mehanizmu da nastavi sa uobičajenim rukovanjem nepoznatim funkcijama, tako da jedan handler može poslužiti za više radnih svezaka bez preuzimanja svega što prođe kroz njega. Drugo, uporedite nazive bez obzira na mala i velika slova pomoću SameText, pošto autori formula pišu discount( i DISCOUNT( naizmenično. Treće, argumenti stižu unapred izračunati: DISCOUNT(A1) vam predaje vrednost ćelije A1, a ne referencu, tako da funkcija ne može znati odakle su došli njeni ulazni podaci. Ova poslednja tačka postavlja ograničenje o kojem govori sledeći odeljak.

Tretirajte telo handlerske metode sa istom opreznošću kao i bilo koju spoljnu ulaznu tačku. Niz Args odražava ono što je autor formule otkucao, stoga proverite broj i tipove argumenata pre nego što im pristupite preko indeksa, i unapred odlučite šta nevažeći poziv vraća: Variant vrednost greške ili pokrenuti izuzetak (exception). Ovaj izbor je važan jer se izuzetak bačen unutar handlera propagira kroz poziv Calculate koji je pokrenuo izračunavanje. To je prihvatljivo u strogo kontrolisanom generatoru, ali grubo u servisu koji procenjuje radne sveske koje su kreirali korisnici, gde bi jedna loša formula mogla da obori ceo zahtev. U tom okruženju, uhvatite izuzetak unutar handlera i vratite kontrolnu vrednost (sentinel) koju okolni tok rada može da prepozna i zabeleži.

Funkcijama koje zavise od pozicije potrebna je Ex varijanta

Neke funkcije legitimno zavise od toga gde se izračunavaju. Stopa koja se razlikuje po listu, pretraga relativna u odnosu na red, množilac po regionu koji se primenjuje samo na regionalnim listovima: ništa od ovoga se ne može odrediti samo na osnovu vrednosti argumenata. Običan događaj to ne može izraziti, pa mehanizam nudi OnUserFunctionEx, koji je identičan osim jedne dodatne parametarske promenljive:

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 izračunava. Ako rezultat funkcije makar i malo zavisi od njene lokacije, povežite Ex događaj od samog početka. Naknadno uvođenje konteksta u handler koji već trideset formula poziva je mnogo komplikovanije nego izbor ispravnog potpisa prvog dana, a ova dva događaja su inače toliko slična da nema mnogo razloga da počnete sa užim.

Prilagođene funkcije se ne prenose u Excel

Prilagođena funkcija živi isključivo unutar vašeg procesa. Naziv DISCOUNT znači nešto samo dok se izvršavaju vaš Delphi kod i njegov handler događaja. Otvorite sačuvanu datoteku u Excel-u i DISCOUNT je samo nepoznato ime; ćelija prikazuje #NAME? osim ako na korisnikovom računaru ne postoji odgovarajuća VBA funkcija ili dodatak (add-in). Ovo je projektna činjenica koja razlikuje demo od gotovog proizvoda za isporuku, i ona nameće izbor koji morate doneti svesno, a ne da ga otkrijete kasnije.

Odlučite, po ćeliji, koji od dva pristupa isporučujete. Ćelije za koje je predviđeno da ih korisnik vidi kako se ponovo izračunavaju unutar Excel-a moraju biti izgrađene isključivo od Excel-ovog sopstvenog rečnika funkcija. Ćelije čija je logika vlasnička (proprietary) treba da se izračunaju u procesu pomoću funkcije Calculate i sačuvaju kao obične vrednosti, tako da se prilagođena funkcija ponaša kao interno pravilo izračunavanja, a ne kao sadržaj datoteke. Način neuspeha koji pouzdano stvara tikete podrške jeste srednji put: čuvanje formule sa prilagođenom funkcijom i očekivanje da je Excel podrži.

Postoji tiha prednost ugovora koji se odnosi samo na vrednosti: on štiti intelektualnu svojinu. Pravilo o određivanju cena koje se procenjuje u vašem Delphi procesu i isporučuje kao broj ne može se rekonstruisati (reverse-engineered) iz radne sveske na način na koji se to može uraditi sa vidljivom formulom, a korisnik ga ne može pokvariti izmenom neke međućelije. Generatori faktura, obračuni provizija i cenovnici skoro uvek pripadaju ovoj grupi. Slučaj kome su zaista potrebne žive formule je interaktivni "šta-ako" (what-if) model, gde se od kupca očekuje da menja ulazne podatke i posmatra kako se ukupni iznosi menjaju, a oni moraju biti izgrađeni od Excel-ovog sopstvenog vokabulara plus definisanih imena.

Režimi izračunavanja, iteracija i R1C1: podešavanja XLS fasade

XLS fasada izlaže podešavanja izračunavanja na nivou BIFF-a koja Excel čita iz datoteke. CalculationMode prihvata xlCalcManual, xlCalcAutomatic (podrazumevano) ili xlCalcAutomaticExceptTables, i određuje kako se Excel ponaša kada je datoteka otvorena. Radna sveska sa hiljadama formula često je lakša za korišćenje ako se isporuči u ručnom režimu (manual mode), tako da primalac odlučuje kada će se pokrenuti proces preračunavanja. 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 prebacuje između A1 i R1C1 prikaza, a UseFullPrecision preslikava Excel-ovu opciju preciznosti kako je prikazana.

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

Formule niza: javna ulazna tačka je XLSX

Nasleđene formule niza u CSE stilu se kreiraju preko 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 odeljku, tako da ne postoji podržani način za kreiranje novih formula niza u .xls datotekama. Postojeće formule u otvorenim datotekama ostaju netaknute tokom kružnog putovanja (round-trip); 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 nasleđeni .xls rezultat 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: definisani nazivi i formule između listova pokriva razrešavanje naziva koje mehanizam obavlja, a članak o CSV i TSV izvozu detaljno opisuje ponašanje izvoza zbog kojeg je eksplicitno izračunavanje neophodno. Kompletna referenca mehanizma, uključujući podržani skup funkcija, isporučuje se uz HotXLS komponentu.