HotXLS formelmotor och egna funktioner i Delphi

Ett kalkylbladsbibliotek som bara lagrar formelsträngar och ett bibliotek med en fungerande formelmotor är två olika produkter som ser identiska ut ända till stunden då du ber det ena om ett tal. De flesta Delphi-kalkylbladskoder märker aldrig glappet, eftersom Excel tapetserar över det: skriv SUM(B2:B501) i en cell, spara, och Excel räknar om summan i samma ögonblick som en människa öppnar filen. Ta bort människan ur kedjan, kör samma arbetsbok genom en serverpipeline som exporterar rakt till CSV, och skillnaden slutar vara akademisk. CSV-filen bär den literala texten =SUM(B2:B501) där ett tal hörde hemma, eftersom ingenting någon gång faktiskt utvärderade formeln

Det är den linjen HotXLS ligger på rätt sida om. Den behandlar en formel som filformaten gör, som lagrad text plus ett valfritt cachelagrat resultat, så att en naken CSV-export återger receptet snarare än rätten. Men den bär också en beräkningsmotor du kan anropa direkt, samma motor i både XLS- och XLSX-fasaden, plus en krok för att lösa upp funktionsnamn motorn aldrig hört talas om. HotXLS är ett inbyggt Object Pascal-bibliotek som läser och skriver XLS och XLSX från Delphi och C++Builder utan Excel-automation, och beräkningshalvan är det som gör lagrade formler till värden på begäran

Formler lagras, de utvärderas inte ivrigt

Att skriva en formel i en cell beräknar ingenting. Vid sparande registrerar arbetsboken formeltexten. På XLS-sidan registrerar den också flaggor som styrs av RecalcOnSave, som har True som standard och säger åt Excel att räkna om vid öppning. Den modellen är korrekt för filer på väg till Excel och fel för pipelines som konsumerar cellvärden direkt, vare sig det är CSV-export, HTML-export eller din egen kod som läser tillbaka celler. För dessa, utvärdera uttryckligen med Calculate. Den finns på fyra ingångspunkter: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook och TXLSXWorksheet exponerar alla function Calculate(const Formula: WideString): Variant

// utvärdera i processen, leverera sedan värdet istället för receptet
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV-filen bär nu talet

Uttrycket du lämnar till Calculate är vanlig Excel-formeltext. Referenser över blad, definierade namn och nästlade funktioner löses alla upp mot den aktuella arbetsboken i minnet, vilket gör anropet användbart långt bortom att lappa CSV-exporter. Behandla det som en assertionsmekanism. En generator som just skrivit femhundra detaljrader kan be arbetsboken om sin egen slutsumma och jämföra den mot siffran den räknat fram oberoende i Pascal, och fånga ett plus-ett-fel i ett område innan en kunds revisor gör det

Det inramar också rätt teststrategi för formeltung utdata. Excel förblir referensimplementationen av formelspråket, så för den handfull formler som bär affärskonsekvenser, håll en godkänd fixturfil vars förväntade värden framställts av Excel självt, och låt byggpipelinen utvärdera den genererade arbetsbokens formler med Calculate mot de fixturerna. Skillnader dyker då upp som misslyckade tester i Delphi snarare än som avvikelser upptäckta av en kund som jämför två rapporter

Att lägga till affärsfunktioner med OnUserFunction

När motorn möter ett funktionsnamn den inte känner igen utlöser den en händelse istället för att fallera rakt av. Tilldela OnUserFunction på endera arbetsboksklassen så kan du lösa upp anropet själv:

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

// inkoppling och användning
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 förtjänar uppmärksamhet. För det första, sätt Handled := True bara när du faktiskt känt igen namnet. Att låta den vara False låter motorn fortsätta sin normala hantering av okända funktioner, så att en enda hanterare kan tjäna flera arbetsböcker utan att göra anspråk på allt som passerar. För det andra, jämför namn utan hänsyn till skiftläge med SameText, eftersom formelförfattare skriver discount( och DISCOUNT( om vartannat. För det tredje kommer argumenten färdigutvärderade: DISCOUNT(A1) ger dig värdet i A1, inte referensen, så en funktion kan inte veta varifrån dess indata kom. Den sista punkten lägger grunden för begränsningen nästa avsnitt handlar om

Behandla hanterarens kropp med samma försiktighet som varje annan extern ingångspunkt. Args-arrayen speglar vad formelförfattaren än skrev, så validera argumentantal och typer innan du indexerar in i den, och bestäm på förhand vad ett ogiltigt anrop returnerar: ett Variant-felvärde, eller ett utlöst undantag. Valet spelar roll eftersom ett undantag som kastas inuti hanteraren propagerar ut genom det Calculate-anrop som utlöste utvärderingen. Det är acceptabelt i en hårt kontrollerad generator och oförskämt i en tjänst som utvärderar användarförfattade arbetsböcker, där en enda dålig formel skulle fälla hela begäran. I den miljön, fånga inuti hanteraren och returnera ett vaktvärde som det omgivande arbetsflödet kan känna igen och logga

Positionsmedvetna funktioner behöver Ex-varianten

Vissa funktioner beror med rätta på var de utvärderas. En sats som skiljer sig per blad, en radrelativ uppslagning, en regionvis multiplikator som bara gäller på regionbladen: inget av detta kan besvaras av argumentvärden ensamma. Den enkla händelsen kan inte uttrycka det, så motorn erbjuder OnUserFunctionEx, identisk så när som på en extra 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
    // samma formel ger en annan sats på varje regionblad
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext bär SheetIndex, Row och Col för den utvärderande cellen. Om en funktions resultat beror på sin plats ens en aning, koppla in Ex-händelsen från början. Att i efterhand pressa in kontext i en hanterare som trettio formler redan anropar är långt stökigare än att välja rätt signatur dag ett, och de två händelserna är annars så lika att det finns lite skäl att börja med den smalare

Egna funktioner följer inte med till Excel

En egen funktion bor helt och hållet inuti din process. Namnet DISCOUNT betyder något bara medan din Delphi-kod och dess händelsehanterare kör. Öppna den sparade filen i Excel och DISCOUNT är bara ett okänt namn; cellen visar #NAME? om inte en matchande VBA-funktion eller ett tillägg råkar finnas på användarens maskin. Det här är designfaktumet som skiljer en demo från en leveransbar produkt, och det tvingar fram ett val du måste göra medvetet snarare än upptäcka senare

Bestäm, per cell, vilket av två kontrakt du levererar. Celler som användaren är tänkt att se räknas om inuti Excel måste byggas av Excels eget funktionsordförråd och inget annat. Celler vars logik är proprietär bör utvärderas i processen med Calculate och sparas som vanliga värden, så att den egna funktionen uppträder som en intern beräkningsregel snarare än som filinnehåll. Felbeteendet som pålitligt genererar supportärenden är mellanläget: att spara en formel med en egen funktion och förvänta sig att Excel respekterar den

Det finns en tyst fördel med värdekontraktet: det skyddar immateriella tillgångar. En prissättningsregel som utvärderas i din Delphi-process och levereras som ett tal kan inte bakåtkonstrueras ur arbetsboken på det sätt en synlig formel kan, och en användare kan inte förstöra den genom att redigera en mellancell. Fakturageneratorer, provisionsbesked och prislistor hör nästan alltid till det här lägret. Fallet som verkligen behöver levande formler är den interaktiva vad-om-modellen, där kunden förväntas ändra indata och se summorna röra sig, och de måste byggas av Excels eget ordförråd plus definierade namn

Beräkningslägen, iteration och R1C1: rattarna på XLS-fasaden

XLS-fasaden exponerar de beräkningsinställningar på BIFF-nivå som Excel läser ur filen. CalculationMode accepterar xlCalcManual, xlCalcAutomatic (standardvärdet) eller xlCalcAutomaticExceptTables, och den avgör hur Excel beter sig när filen väl är öppen. En modellarbetsbok med tusentals formler är ofta vänligare att leverera i manuellt läge, så att mottagaren avgör när omräkningsstormen sker. EnableIteration (standard False), tillsammans med MaxIterations (standard 100) och MaxIterationChange (standard 0,001), låser upp de avsiktliga cirkelreferenser av iterativ-konvergenstyp som dyker upp i vissa finansiella modeller. ReferenceStyle växlar mellan A1- och R1C1-visning, och UseFullPrecision speglar Excels alternativ för precision som visad

De här egenskaperna bor på XLS-fasaden eftersom de mappar mot BIFF-poster; när du genererar .xlsx, planera formler så att de inte beror på iterativa inställningar, eller beräkna de konvergerade värdena i Delphi och skriv resultaten

Matrisformler: den publika ingångspunkten är XLSX

Äldre matrisformler av CSE-typ skapas genom TXLSXRange.SetArrayFormula:

// en matrisformel som spänner över A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Den motsvarande metoden finns i XLS-klasshierarkin men sitter i en privat sektion, så det finns inget stött sätt att författa nya matrisformler in i .xls-filer. Befintliga i öppnade filer klarar rundturen intakta; vad du inte kan är att skapa dem. Regeln som följer är enkel nog: när matrissemantik är en del av kravet, sikta på .xlsx. Om en äldre .xls-leverans verkligen behöver matrisbeteende är den pragmatiska vägen att beräkna matrisresultatet i Delphi och skriva in de enskilda värdena i cellerna

Två besläktade texter på den här webbplatsen: definierade namn och formler över blad täcker den namnupplösning motorn utför, och artikeln om CSV- och TSV-export beskriver det exportbeteende som gör uttrycklig beräkning nödvändig. Den fullständiga motorreferensen, inklusive den funktionsuppsättning som stöds, levereras med HotXLS Delphi Component