HotXLS-formelmotoren og brugerdefinerede funktioner i Delphi

Et regnearksbibliotek, der kun gemmer formelstrenge, og et bibliotek med en fungerende formelmotor er to forskellige produkter, der ser identiske ud, indtil det øjeblik man beder et af dem om et tal. Det meste Delphi-regnearkskode lægger aldrig mærke til kløften, fordi Excel dækker over den: skriv SUM(B2:B501) ind i en celle, gem, og Excel genberegner totalen, i det øjeblik et menneske åbner filen. Tag mennesket ud af løkken, kør den samme projektmappe gennem en server-pipeline, der eksporterer direkte til CSV, og forskellen holder op med at være akademisk. CSV'en bærer den bogstavelige tekst =SUM(B2:B501), hvor et tal skulle have været, fordi intet nogensinde reelt evaluerede formlen

Det er den linje, HotXLS står på den rigtige side af. Det behandler en formel, som filformaterne selv gør, som gemt tekst plus et valgfrit cachet resultat, så en bar CSV-eksport gengiver opskriften frem for retten. Men det bærer også en beregningsmotor, du kan kalde direkte, den samme motor i både XLS- og XLSX-facaderne, plus en krog til at opløse funktionsnavne, motoren aldrig har hørt om. HotXLS er et nativt Object Pascal-bibliotek, der læser og skriver XLS og XLSX fra Delphi og C++Builder uden Excel-automatisering, og beregningshalvdelen af det er det, der forvandler gemte formler tilbage til værdier på forlangende

Formler gemmes, de evalueres ikke straks

At skrive en formel ind i en celle beregner ikke noget. Ved gemmetidspunktet registrerer projektmappen formelteksten. På XLS-siden registrerer den også flag styret af RecalcOnSave, som som standard er True og fortæller Excel at genberegne ved åbning. Den model er korrekt for filer bestemt til Excel og forkert for pipelines, der forbruger celleværdier direkte, hvad enten det er CSV-eksport, HTML-eksport eller din egen kode, der læser celler tilbage. Til de tilfælde skal man evaluere eksplicit med Calculate. Den findes ved fire indgangspunkter: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook og TXLSXWorksheet eksponerer alle function Calculate(const Formula: WideString): Variant

// evaluér in-process, og send så værdien frem for opskriften
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV'en bærer nu tallet

Udtrykket, der gives til Calculate, er almindelig Excel-formeltekst. Krydsark-referencer, definerede navne og indlejrede funktioner opløses alle mod den nuværende projektmappe i hukommelsen, hvilket gør kaldet nyttigt langt ud over at lappe CSV-eksporter. Behandl det som en assertion-mekanisme. En generator, der lige har skrevet fem hundrede detaljerækker, kan bede projektmappen om sin egen hovedtotal og sammenligne den med det tal, den beregnede uafhængigt i Pascal, og dermed fange en off-by-one-områdefejl, før en kundes revisor gør det

Det rammer også den rigtige teststrategi for formeltungt output. Excel forbliver referenceimplementeringen af formelsproget, så for den håndfuld formler, der bærer forretningsmæssige konsekvenser, skal man beholde en godkendt fixture-fil, hvis forventede værdier blev produceret af Excel selv, og lade build-pipelinen evaluere den genererede projektmappes formler med Calculate mod de fixtures. Forskelle dukker så op som fejlende tests i Delphi frem for som uoverensstemmelser opdaget af en kunde, der sammenligner to rapporter

At tilføje forretningsfunktioner med OnUserFunction

Når motoren møder et funktionsnavn, den ikke genkender, rejser den en hændelse i stedet for at fejle direkte. Tildel OnUserFunction på en af de to projektmappeklasser, og du kan opløse kaldet 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 et Variant-array
    Handled := True;
  end;
end;

// tilslutning og brug
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 opmærksomhed. For det første skal man kun sætte Handled := True, når man faktisk genkendte navnet. At lade det stå på False lader motoren fortsætte sin normale håndtering af ukendte funktioner, så én enkelt handler kan betjene flere projektmapper uden at kræve alt, der passerer igennem. For det andet skal man sammenligne navne uden hensyn til store/små bogstaver med SameText, da formelforfattere skriver discount( og DISCOUNT( i flæng. For det tredje ankommer argumenter pre-evalueret: DISCOUNT(A1) giver dig værdien af A1, ikke referencen, så en funktion kan ikke se, hvor dens input kom fra. Det sidste punkt sætter scenen for den begrænsning, næste afsnit handler om

Behandl handler-kroppen med samme forsvarsagtighed som ethvert eksternt indgangspunkt. Args-arrayet afspejler, hvad formelforfatteren nu skrev, så valider argumentantal og -typer, før du indekserer ind i det, og beslut på forhånd, hvad et ugyldigt kald returnerer: en Variant-fejlværdi eller en kastet undtagelse. Valget betyder noget, fordi en undtagelse kastet inde i handleren forplanter sig ud gennem det Calculate-kald, der udløste evalueringen. Det er acceptabelt i en stramt kontrolleret generator og uhøfligt i en tjeneste, der evaluerer brugerforfattede projektmapper, hvor én dårlig formel ville vælte hele forespørgslen. I den sammenhæng skal man fange inde i handleren og returnere en sentinel-værdi, den omgivende workflow kan genkende og logge

Positionsbevidste funktioner har brug for Ex-varianten

Nogle funktioner afhænger legitimt af, hvor de bliver evalueret. En sats, der varierer pr. ark, et rækkerelativt opslag, en per-region-multiplikator, der kun gælder på de regionale ark: ingen af disse kan besvares af argumentværdier alene. Den almindelige hændelse kan ikke udtrykke det, så motoren tilbyder OnUserFunctionEx, identisk bortset 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 giver en anden sats på hvert regionalt ark
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext bærer SheetIndex, Row og Col for den celle, der evalueres. Hvis en funktions resultat afhænger af sin placering selv en smule, skal man tilslutte Ex-hændelsen fra starten. At eftermontere kontekst i en handler, som tredive formler allerede kalder, er langt mere rodet end at vælge den rigtige signatur fra dag ét, og de to hændelser er ellers så ens, at der er ringe grund til at starte med den snævrere

Brugerdefinerede funktioner rejser ikke med til Excel

En brugerdefineret funktion lever udelukkende inde i din proces. Navnet DISCOUNT betyder kun noget, mens din Delphi-kode og dens hændelseshandler kører. Åbn den gemte fil i Excel, og DISCOUNT er bare et ukendt navn; cellen viser #NAME?, medmindre en matchende VBA-funktion eller et add-in tilfældigvis findes på brugerens maskine. Det er den designmæssige kendsgerning, der adskiller en demo fra et leveringsklart produkt, og den tvinger et valg frem, man må træffe bevidst frem for at opdage senere

Beslut, pr. celle, hvilken af to kontrakter du leverer. Celler, brugeren skal se genberegne inde i Excel, skal bygges udelukkende af Excels eget funktionsvokabular. Celler, hvis logik er proprietær, bør evalueres in-process med Calculate og gemmes som almindelige værdier, så den brugerdefinerede funktion opfører sig som en intern beregningsregel frem for som filindhold. Den fejltilstand, der pålideligt genererer supportsager, er mellemvejen: at gemme en brugerdefineret-funktion-formel og forvente, at Excel respekterer den

Der er en stille fordel ved den kun-værdier-kontrakt: den beskytter intellektuel ejendom. En prisregel evalueret i din Delphi-proces og leveret som et tal kan ikke reverse-engineeres fra projektmappen, som en synlig formel kan, og en bruger kan ikke ødelægge den ved at redigere en mellemliggende celle. Fakturagenerator, provisionsopgørelser og prislister hører næsten altid til i denne lejr. Det tilfælde, der reelt har brug for levende formler, er den interaktive what-if-model, hvor kunden forventes at ændre input og se totaler bevæge sig, og de skal bygges af Excels eget vokabular plus definerede navne

Beregningstilstande, iteration og R1C1: XLS-facadens skiver

XLS-facaden eksponerer BIFF-niveau-beregningsindstillingerne, som Excel læser fra filen. CalculationMode accepterer xlCalcManual, xlCalcAutomatic (standarden) eller xlCalcAutomaticExceptTables, og den afgør, hvordan Excel opfører sig, når filen er åben. En modelprojektmappe med tusindvis af formler leveres ofte mere venligt i manuel tilstand, så modtageren afgør, hvornår genberegningsstormen sker. EnableIteration (standard False), sammen med MaxIterations (standard 100) og MaxIterationChange (standard 0.001), låser op for de bevidste cirkulære referencer af den iterativ-konvergens-type, der dukker op i nogle finansielle modeller. ReferenceStyle skifter mellem A1- og R1C1-visning, og UseFullPrecision spejler Excels indstilling for precision-as-displayed

Disse egenskaber bor på XLS-facaden, fordi de kortlægger til BIFF-poster; når man genererer .xlsx, skal man planlægge formler, så de ikke afhænger af iterative indstillinger, eller beregne de konvergerede værdier i Delphi og skrive resultaterne

Array-formler: det offentlige indgangspunkt er XLSX

Gamle CSE-stil array-formler oprettes via TXLSXRange.SetArrayFormula:

// én array-formel, der spænder over A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Den tilsvarende metode findes i XLS-klassehierarkiet, men sidder i en privat sektion, så der findes ingen understøttet måde at forfatte nye array-formler ind i .xls-filer på. Eksisterende i åbnede filer overlever en rundtur intakt; det, man ikke kan, er at oprette dem. Reglen, der følger, er simpel nok: når array-semantik er en del af kravet, skal man sigte mod .xlsx. Hvis en gammel .xls-leverance reelt har brug for array-adfærd, er den pragmatiske vej at beregne array-resultatet i Delphi og skrive de individuelle værdier ind i cellerne

To relaterede læsninger på dette site: definerede navne og krydsark-formler dækker den navneopløsning, motoren udfører, og artiklen om CSV- og TSV-eksport beskriver den eksportadfærd, der gør eksplicit beregning nødvendig. Den fulde motorreference, inklusive det understøttede funktionssæt, følger med HotXLS-Delphi-komponenten