Formelmotoren i HotXLS og egendefinerte funksjoner i Delphi

Et regnearkbibliotek som bare lagrer formelstrenger og et bibliotek med en fungerende formelmotor, er to forskjellige produkter som ser identiske ut helt til det øyeblikket du ber ett av dem om et tall. Det meste av Delphi-regnearkkode legger aldri merke til gapet, fordi Excel dekker over det: skriv SUM(B2:B501) inn i en celle, lagre, og Excel beregner totalen på nytt i det øyeblikket et menneske åpner filen. Ta mennesket ut av løkken, kjør samme arbeidsbok gjennom en serverpipeline som eksporterer rett til CSV, og forskjellen slutter å være akademisk. CSV-en bærer den bokstavelige teksten =SUM(B2:B501) der et tall hørte hjemme, fordi ingenting på noe tidspunkt faktisk evaluerte formelen

Det er den linjen HotXLS sitter på riktig side av. Det behandler en formel slik filformatene gjør, som lagret tekst pluss et valgfritt bufret resultat, så en bar CSV-eksport reproduserer oppskriften snarere enn retten. Men det bærer også en beregningsmotor du kan kalle direkte, samme motor i både XLS- og XLSX-fasadene, pluss en krok for å løse opp funksjonsnavn motoren aldri har hørt om. HotXLS er et nativt Object Pascal-bibliotek som leser og skriver XLS og XLSX fra Delphi og C++Builder uten Excel-automatisering, og beregningshalvdelen av det er det som gjør lagrede formler om til verdier på forespørsel

Formler lagres, de evalueres ikke grådig

Å skrive en formel inn i en celle beregner ingenting. Ved lagring registrerer arbeidsboken formelteksten. På XLS-siden registrerer den også flagg styrt av RecalcOnSave, som er True som standard og forteller Excel å beregne på nytt ved åpning. Den modellen er riktig for filer bestemt for Excel og feil for pipelines som konsumerer celleverdier direkte, enten det er CSV-eksport, HTML-eksport, eller din egen kode som leser celler tilbake. For de tilfellene, evaluer eksplisitt med Calculate. Den finnes på fire inngangspunkter: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook og TXLSXWorksheet eksponerer alle function Calculate(const Formula: WideString): Variant

// evaluer i prosessen, send så verdien i stedet for oppskriften
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV-en bærer nå tallet

Uttrykket som gis til Calculate, er vanlig Excel-formeltekst. Arkkryssende referanser, definerte navn og nøstede funksjoner løses alle opp mot den nåværende arbeidsboken i minnet, noe som gjør kallet nyttig langt utover å lappe CSV-eksporter. Behandle det som en påstandsmekanisme. En generator som nettopp skrev fem hundre detaljrader, kan spørre arbeidsboken om sin egen totalsum og sammenligne den mot tallet den beregnet uavhengig i Pascal, og fange en av-med-én-områdefeil før kundens revisor gjør det

Det rammer også inn den riktige teststrategien for formeltungt utdata. Excel forblir referanseimplementasjonen av formelspråket, så for den håndfullen formler som bærer forretningskonsekvenser, hold en godkjent fixture-fil hvis forventede verdier ble produsert av Excel selv, og la byggepipelinen evaluere den genererte arbeidsbokens formler med Calculate mot de fixturene. Forskjeller dukker da opp som feilende tester i Delphi i stedet for som avvik oppdaget av en kunde som sammenligner to rapporter

Å legge til forretningsfunksjoner med OnUserFunction

Når motoren møter et funksjonsnavn den ikke kjenner igjen, utløser den en hendelse i stedet for å feile rett ut. Tildel OnUserFunction på begge arbeidsbokklassene, og du kan løse kallet selv:

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 ankommer som en Variant-array
    Handled := True;
  end;
end;

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

Tre detaljer fortjener oppmerksomhet. Først, sett Handled := True bare når du faktisk kjente igjen navnet. Å la den stå False lar motoren fortsette sin vanlige håndtering av ukjente funksjoner, så én enkelt håndterer kan betjene flere arbeidsbøker uten å kreve alt som går gjennom. For det andre, sammenlign navn uten hensyn til store/små bokstaver med SameText, siden formelforfattere skriver discount( og DISCOUNT( om hverandre. For det tredje ankommer argumenter forhåndsevaluert: DISCOUNT(A1) gir deg verdien av A1, ikke referansen, så en funksjon kan ikke vite hvor inndataene kom fra. Det siste punktet setter opp begrensningen neste seksjon handler om

Behandle håndtererens kropp med samme defensivitet som ethvert eksternt inngangspunkt. Args-arrayen reflekterer hva enn formelforfatteren skrev, så valider argumentantallet og -typene før du indekserer inn i den, og bestem på forhånd hva et ugyldig kall returnerer: en Variant-feilverdi, eller et utløst unntak. Valget betyr noe fordi et unntak kastet inne i håndtereren forplanter seg ut gjennom Calculate-kallet som utløste evalueringen. Det er akseptabelt i en stramt kontrollert generator og uhøflig i en tjeneste som evaluerer brukerforfattede arbeidsbøker, der én dårlig formel ville felle hele forespørselen. I den settingen, fang inne i håndtereren og returner en sentinel den omkringliggende arbeidsflyten kan gjenkjenne og logge

Posisjonsbevisste funksjoner trenger Ex-varianten

Noen funksjoner er legitimt avhengige av hvor de blir evaluert. En sats som varierer per ark, et radrelativt oppslag, en per-region-multiplikator som bare gjelder på de regionale arkene: ingen av disse kan besvares av argumentverdier alene. Den vanlige hendelsen kan ikke uttrykke det, så motoren tilbyr OnUserFunctionEx, identisk bortsett fra én ekstra 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
    // samme formel gir en annen sats på hvert regionalt ark
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext bærer SheetIndex, Row og Col til den evaluerende cellen. Hvis en funksjons resultat er avhengig av plasseringen sin selv bare litt, koble opp Ex-hendelsen fra starten. Å ettermontere kontekst inn i en håndterer som tretti formler allerede kaller, er langt rotete enn å velge riktig signatur på dag én, og de to hendelsene er ellers så like at det er liten grunn til å starte med den smalere

Egendefinerte funksjoner reiser ikke til Excel

En egendefinert funksjon lever helt inne i prosessen din. Navnet DISCOUNT betyr noe bare mens Delphi-koden din og hendelseshåndtereren dens kjører. Åpne den lagrede filen i Excel, og DISCOUNT er bare et ukjent navn; cellen viser #NAME? med mindre en matchende VBA-funksjon eller et tillegg tilfeldigvis finnes på brukerens maskin. Dette er designfaktumet som skiller en demo fra et leveringsklart produkt, og det tvinger frem et valg du må ta bevisst i stedet for å oppdage senere

Bestem, per celle, hvilken av to kontrakter du leverer. Celler brukeren er ment å se beregnes på nytt inne i Excel, må bygges fra Excels egen funksjonsvokabular og ingenting annet. Celler hvis logikk er proprietær, bør evalueres i prosessen med Calculate og lagres som rene verdier, slik at den egendefinerte funksjonen oppfører seg som en intern beregningsregel snarere enn som filinnhold. Feilmodusen som pålitelig genererer supportsaker, er mellomgrunnen: å lagre en egendefinert-funksjon-formel og forvente at Excel skal respektere den

Det finnes en stille fordel med kun-verdier-kontrakten: den beskytter immaterielle rettigheter. En prisregel evaluert i Delphi-prosessen din og levert som et tall, kan ikke reverse-engineeres fra arbeidsboken slik en synlig formel kan, og en bruker kan ikke ødelegge den ved å redigere en mellomliggende celle. Fakturageneratorer, provisjonsoppgaver og prislister hører nesten alltid hjemme i denne leiren. Tilfellet som virkelig trenger levende formler, er den interaktive hva-hvis-modellen, der kunden forventes å endre inndata og se totaler bevege seg, og de må bygges fra Excels eget vokabular pluss definerte navn

Beregningsmoduser, iterasjon og R1C1: XLS-fasadens brytere

XLS-fasaden eksponerer beregningsinnstillingene på BIFF-nivå som Excel leser fra filen. CalculationMode godtar xlCalcManual, xlCalcAutomatic (standarden) eller xlCalcAutomaticExceptTables, og den avgjør hvordan Excel oppfører seg når filen er åpnet. En modellarbeidsbok med tusenvis av formler er ofte vennligere levert i manuell modus, slik at mottakeren bestemmer når omberegningsstormen skjer. EnableIteration (standard False), sammen med MaxIterations (standard 100) og MaxIterationChange (standard 0.001), låser opp de bevisste sirkulære referansene av den iterative-konvergens-typen som dukker opp i noen finansmodeller. ReferenceStyle bytter mellom A1- og R1C1-visning, og UseFullPrecision speiler Excels presisjon-som-vist-alternativ

Disse egenskapene bor på XLS-fasaden fordi de kartlegger til BIFF-poster; når du genererer .xlsx, planlegg formler slik at de ikke er avhengige av iterative innstillinger, eller beregn de konvergerte verdiene i Delphi og skriv resultater

Matriseformler: det offentlige inngangspunktet er XLSX

Gamle CSE-stil matriseformler opprettes gjennom TXLSXRange.SetArrayFormula:

// én matriseformel som spenner over A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Den tilsvarende metoden finnes i XLS-klassehierarkiet, men sitter i en privat seksjon, så det finnes ingen støttet måte å forfatte nye matriseformler inn i .xls-filer på. Eksisterende i åpnede filer gjennomgår en tur-retur intakt; det du ikke kan gjøre, er å opprette dem. Regelen som følger, er enkel nok: når matrisesemantikk er del av kravet, sikt mot .xlsx. Hvis en gammel .xls-leveranse virkelig trenger matriseoppførsel, er den pragmatiske veien å beregne matriseresultatet i Delphi og skrive de individuelle verdiene inn i cellene

To relaterte lesestykker på dette nettstedet: definerte navn og arkkryssende formler dekker navneoppløsningen motoren utfører, og artikkelen om CSV- og TSV-eksport detaljerer eksportoppførselen som gjør eksplisitt beregning nødvendig. Den fullstendige motorreferansen, inkludert det støttede funksjonssettet, følger med HotXLS Delphi Component