HotXLS formulemotor en aangepaste functies in Delphi

Een spreadsheetbibliotheek die alleen formuletekenreeksen opslaat en een bibliotheek met een werkende formulemotor zijn twee verschillende producten die er identiek uitzien tot het moment waarop u er een om een getal vraagt. De meeste Delphi-spreadsheetcode merkt het gat nooit op, omdat Excel het toedekt: schrijf SUM(B2:B501) in een cel, sla op, en Excel herberekent het totaal op het moment dat een mens het bestand opent. Haal de mens uit de lus, laat dezelfde werkmap door een serverpijplijn lopen die rechtstreeks naar CSV exporteert, en het verschil is niet langer academisch. De CSV draagt de letterlijke tekst =SUM(B2:B501) waar een getal hoorde, omdat niets op enig moment de formule daadwerkelijk heeft geëvalueerd

Dat is de lijn waarvan HotXLS aan de goede kant staat. Het behandelt een formule zoals de bestandsformaten dat doen, als opgeslagen tekst plus een optioneel gecacht resultaat, dus een kale CSV-export reproduceert het recept in plaats van het gerecht. Maar het draagt ook een rekenmotor die u rechtstreeks kunt aanroepen, dezelfde motor in zowel de XLS- als de XLSX-facade, plus een haak om functienamen op te lossen waar de motor nog nooit van heeft gehoord. HotXLS is een native Object Pascal-bibliotheek die XLS en XLSX leest en schrijft vanuit Delphi en C++Builder zonder Excel-automatisering, en de rekenhelft daarvan is wat opgeslagen formules op verzoek weer in waarden verandert

Formules worden opgeslagen, niet gretig geëvalueerd

Een formule in een cel schrijven berekent niets. Bij het opslaan legt de werkmap de formuletekst vast. Aan de XLS-kant legt zij ook vlaggen vast die door RecalcOnSave worden bestuurd, die standaard True is en Excel opdraagt bij openen te herberekenen. Dat model klopt voor bestanden die voor Excel bestemd zijn en klopt niet voor pijplijnen die celwaarden rechtstreeks verbruiken, of dat nu CSV-export, HTML-export of uw eigen code is die cellen terugleest. Evalueer voor die gevallen expliciet met Calculate. Het bestaat op vier instappunten: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook en TXLSXWorksheet stellen alle vier function Calculate(const Formula: WideString): Variant beschikbaar

// evalueer in het proces zelf en verstuur dan de waarde in plaats van het recept
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // de CSV draagt nu het getal

De expressie die u aan Calculate meegeeft is gewone Excel-formuletekst. Cross-sheetverwijzingen, gedefinieerde namen en geneste functies lossen alle op tegen de huidige werkmap in het geheugen, wat de aanroep veel breder nuttig maakt dan het repareren van CSV-exports. Behandel hem als een assertiemechanisme. Een generator die zojuist vijfhonderd detailrijen heeft geschreven kan de werkmap om haar eigen eindtotaal vragen en dat vergelijken met het cijfer dat hij onafhankelijk in Pascal berekende, en zo een off-by-one in een bereik opvangen voordat de accountant van een klant dat doet

Het geeft ook de juiste teststrategie voor formulezware uitvoer vorm. Excel blijft de referentie-implementatie van de formuletaal, dus houd voor het handjevol formules met zakelijke gevolgen een goedgekeurd fixture-bestand aan waarvan de verwachte waarden door Excel zelf zijn geproduceerd, en laat de buildpijplijn de formules van de gegenereerde werkmap met Calculate tegen die fixtures evalueren. Verschillen komen dan boven als falende tests in Delphi en niet als afwijkingen die een klant ontdekt bij het vergelijken van twee rapporten

Zakelijke functies toevoegen met OnUserFunction

Wanneer de motor een functienaam tegenkomt die hij niet herkent, werpt hij een gebeurtenis op in plaats van ronduit te falen. Wijs OnUserFunction toe op een van beide werkmapklassen en u kunt de aanroep zelf oplossen:

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 komt binnen als een Variant-array
    Handled := True;
  end;
end;

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

Drie details verdienen aandacht. Ten eerste: zet Handled := True alleen wanneer u de naam werkelijk hebt herkend. Hem op False laten laat de motor zijn normale afhandeling van onbekende functies voortzetten, zodat één handler meerdere werkmappen kan bedienen zonder alles op te eisen wat erdoorheen komt. Ten tweede: vergelijk namen hoofdletterongevoelig met SameText, aangezien formuleauteurs discount( en DISCOUNT( door elkaar typen. Ten derde: argumenten komen vooraf geëvalueerd binnen: DISCOUNT(A1) geeft u de waarde van A1, niet de verwijzing, dus een functie kan niet zien waar haar invoer vandaan kwam. Dat laatste punt zet de beperking op waarover de volgende sectie gaat

Behandel de romp van de handler net zo defensief als elk ander extern instappunt. De Args-array weerspiegelt wat de formuleauteur ook maar typte, dus valideer het aantal en de typen van de argumenten voordat u erin indexeert, en beslis vooraf wat een ongeldige aanroep retourneert: een Variant-foutwaarde, of een opgeworpen exceptie. Die keuze doet ertoe omdat een exceptie die binnen de handler wordt geworpen naar buiten propageert door de Calculate-aanroep die de evaluatie startte. Dat is aanvaardbaar in een strak beheerste generator en onbeschoft in een dienst die door gebruikers geschreven werkmappen evalueert, waar één slechte formule het verzoek onderuit zou halen. In die omgeving vangt u binnen de handler en retourneert u een sentinel die de omringende workflow kan herkennen en loggen

Positiebewuste functies hebben de Ex-variant nodig

Sommige functies hangen terecht af van waar zij worden geëvalueerd. Een tarief dat per blad verschilt, een rij-relatieve opzoeking, een vermenigvuldiger per regio die alleen op de regioblad geldt: geen daarvan kan door argumentwaarden alleen worden beantwoord. De gewone gebeurtenis kan dat niet uitdrukken, dus biedt de motor OnUserFunctionEx, identiek op één extra parameter na:

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
    // dezelfde formule levert op elk regioblad een ander tarief op
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext draagt SheetIndex, Row en Col van de evaluerende cel. Als het resultaat van een functie ook maar enigszins van haar locatie afhangt, bedraad dan vanaf het begin de Ex-gebeurtenis. Context achteraf inbouwen in een handler die dertig formules al aanroepen is veel rommeliger dan op dag één de juiste signatuur kiezen, en de twee gebeurtenissen lijken verder zo sterk op elkaar dat er weinig reden is met de smallere te beginnen

Aangepaste functies reizen niet mee naar Excel

Een aangepaste functie leeft volledig binnen uw proces. De naam DISCOUNT betekent alleen iets zolang uw Delphi-code en haar gebeurtenishandler draaien. Open het opgeslagen bestand in Excel en DISCOUNT is slechts een onherkende naam; de cel toont #NAME? tenzij er toevallig een bijpassende VBA-functie of invoegtoepassing op de machine van de gebruiker bestaat. Dit is het ontwerpfeit dat een demo van een verzendbaar product scheidt, en het dwingt een keuze af die u bewust moet maken in plaats van later te ontdekken

Beslis per cel welk van twee contracten u levert. Cellen waarvan de gebruiker geacht wordt te zien dat ze binnen Excel herberekenen moeten uit de eigen functiewoordenschat van Excel worden gebouwd en uit niets anders. Cellen waarvan de logica bedrijfseigen is, moeten in het proces zelf met Calculate worden geëvalueerd en als gewone waarden bewaard, zodat de aangepaste functie zich als een interne rekenregel gedraagt en niet als bestandsinhoud. De faalwijze die betrouwbaar supporttickets genereert is de tussenweg: een formule met een aangepaste functie bewaren en verwachten dat Excel haar honoreert

Er zit een stil voordeel aan het contract met alleen waarden: het beschermt intellectueel eigendom. Een prijsregel die in uw Delphi-proces wordt geëvalueerd en als getal wordt verstuurd, kan niet uit de werkmap worden gereverse-engineerd zoals een zichtbare formule dat kan, en een gebruiker kan haar niet breken door een tussencel te bewerken. Factuurgeneratoren, commissieoverzichten en tariefkaarten horen vrijwel altijd in dit kamp. Het geval dat werkelijk levende formules nodig heeft, is het interactieve wat-als-model, waarbij van de klant wordt verwacht dat hij invoer verandert en de totalen ziet meebewegen, en die moeten uit de eigen woordenschat van Excel plus gedefinieerde namen worden gebouwd

Rekenmodi, iteratie en R1C1: de knoppen van de XLS-facade

De XLS-facade stelt de rekeninstellingen op BIFF-niveau beschikbaar die Excel uit het bestand leest. CalculationMode accepteert xlCalcManual, xlCalcAutomatic (de standaard) of xlCalcAutomaticExceptTables, en bepaalt hoe Excel zich gedraagt zodra het bestand open is. Een modelwerkmap met duizenden formules is vaak vriendelijker geleverd in handmatige modus, zodat de ontvanger beslist wanneer de herberekeningsstorm losbarst. EnableIteration (standaard False), samen met MaxIterations (standaard 100) en MaxIterationChange (standaard 0.001), ontgrendelt de bewuste kringverwijzingen van het iteratief-convergerende soort die in sommige financiële modellen opduiken. ReferenceStyle schakelt tussen A1- en R1C1-weergave, en UseFullPrecision spiegelt de optie precisie-zoals-weergegeven van Excel

Deze eigenschappen wonen op de XLS-facade omdat zij op BIFF-records afbeelden; plan bij het genereren van .xlsx uw formules zo dat ze niet van iteratieve instellingen afhangen, of bereken de geconvergeerde waarden in Delphi en schrijf de resultaten weg

Matrixformules: het publieke instappunt is XLSX

Klassieke matrixformules in CSE-stijl worden gemaakt via TXLSXRange.SetArrayFormula:

// één matrixformule die A2:A4 beslaat
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

De equivalente methode bestaat in de XLS-klassenhiërarchie maar zit in een private sectie, dus er is geen ondersteunde manier om nieuwe matrixformules in .xls-bestanden te schrijven. Bestaande formules in geopende bestanden maken de rondgang intact; wat u niet kunt doen is ze maken. De regel die daaruit volgt is eenvoudig genoeg: wanneer matrixsemantiek deel uitmaakt van de eis, richt u op .xlsx. Als een verouderd .xls-eindproduct werkelijk matrixgedrag nodig heeft, is de pragmatische route het matrixresultaat in Delphi te berekenen en de afzonderlijke waarden in de cellen te schrijven

Twee verwante artikelen op deze site: gedefinieerde namen en cross-sheetformules behandelt de naamresolutie die de motor uitvoert, en het artikel over CSV- en TSV-export beschrijft het exportgedrag dat expliciet rekenen noodzakelijk maakt. De volledige motorreferentie, inclusief de ondersteunde functieset, wordt geleverd bij het HotXLS Delphi Component