Motorul de formule HotXLS și funcții personalizate în Delphi

O bibliotecă de foi de calcul care doar stochează șiruri de formulă și una cu un motor de formule funcțional sunt două produse diferite care arată identic până în clipa în care îi ceri uneia dintre ele un număr. Cea mai mare parte a codului Delphi de foi de calcul nu observă niciodată golul, pentru că Excel îl acoperă: scrii SUM(B2:B501) într-o celulă, salvezi, iar Excel recalculează totalul în clipa în care un om deschide fișierul. Scoate omul din buclă, trece același registru de lucru printr-un pipeline de server care exportă direct în CSV, iar diferența încetează să mai fie academică. CSV-ul poartă textul literal =SUM(B2:B501) acolo unde îi era locul unui număr, pentru că în niciun moment nu a evaluat nimic formula cu adevărat

Aceasta este linia de partea bună a căreia se așază HotXLS. Tratează o formulă așa cum o tratează formatele de fișier, ca text stocat plus un rezultat opțional păstrat în cache, așa că un export CSV simplu reproduce rețeta, nu felul de mâncare. Dar poartă și un motor de calcul pe care îl poți apela direct, același motor în fațada XLS și în cea XLSX, plus un cârlig pentru rezolvarea numelor de funcții de care motorul nu a auzit niciodată. HotXLS este o bibliotecă nativă Object Pascal care citește și scrie XLS și XLSX din Delphi și C++Builder fără automatizare Excel, iar jumătatea ei de calcul este cea care preface la cerere formulele stocate înapoi în valori

Formulele sunt stocate, nu evaluate din start

Scrierea unei formule într-o celulă nu calculează nimic. La salvare, registrul de lucru consemnează textul formulei. Pe partea XLS consemnează și indicatori guvernați de RecalcOnSave, care are implicit valoarea True și îi spune lui Excel să recalculeze la deschidere. Modelul acela este corect pentru fișierele destinate lui Excel și greșit pentru pipeline-urile care consumă direct valori de celulă, fie că este vorba de export CSV, de export HTML sau de propriul tău cod care citește celulele înapoi. Pentru acelea, evaluează explicit cu Calculate. Există în patru puncte de intrare: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook și TXLSXWorksheet expun toate function Calculate(const Formula: WideString): Variant

// evaluează în proces, apoi livrează valoarea, nu rețeta
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV-ul poartă acum numărul

Expresia dată lui Calculate este text obișnuit de formulă Excel. Referințele între foi, numele definite și funcțiile imbricate se rezolvă toate față de registrul de lucru curent din memorie, ceea ce face apelul util cu mult dincolo de peticirea exporturilor CSV. Tratează-l ca pe un mecanism de aserțiune. Un generator care tocmai a scris cinci sute de rânduri de detaliu poate cere registrului de lucru propriul lui total general și îl poate compara cu cifra pe care a calculat-o independent în Pascal, prinzând o eroare de interval cu o unitate înainte să o prindă auditorul unui client

Tot el încadrează strategia potrivită de testare pentru rezultatele încărcate cu formule. Excel rămâne implementarea de referință a limbajului de formule, așa că, pentru cele câteva formule care poartă consecințe de business, ține un fișier de fixtură aprobat, ale cărui valori așteptate au fost produse chiar de Excel, și pune pipeline-ul de build să evalueze cu Calculate formulele registrului de lucru generat față de fixturile acelea. Diferențele ies atunci la suprafață ca teste picate în Delphi, nu ca neconcordanțe descoperite de un client care compară două rapoarte

Adăugarea de funcții de business cu OnUserFunction

Când motorul dă peste un nume de funcție pe care nu îl recunoaște, ridică un eveniment în loc să pice de tot. Atribuie OnUserFunction pe oricare dintre clasele de registru de lucru și poți rezolva singur apelul:

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 sosește ca tablou de Variant
    Handled := True;
  end;
end;

// cablare și utilizare
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Trei detalii merită atenție. Întâi, setează Handled := True doar atunci când chiar ai recunoscut numele. Lăsându-l pe False, motorul își continuă tratarea obișnuită a funcțiilor necunoscute, așa că un singur handler poate servi mai multe registre de lucru fără să revendice tot ce trece prin el. Al doilea, compară numele fără să ții cont de majuscule, cu SameText, de vreme ce autorii de formule tastează deopotrivă discount( și DISCOUNT(. Al treilea, argumentele sosesc deja evaluate: DISCOUNT(A1) îți dă valoarea lui A1, nu referința, așa că o funcție nu poate ști de unde i-au venit intrările. Ultimul punct pregătește limitarea despre care este secțiunea următoare

Tratează corpul handlerului cu aceeași prudență ca pe orice punct de intrare extern. Tabloul Args reflectă orice a tastat autorul formulei, așa că validează numărul și tipurile argumentelor înainte să indexezi în el și hotărăște din capul locului ce întoarce un apel invalid: o valoare Variant de eroare sau o excepție ridicată. Alegerea contează pentru că o excepție aruncată în interiorul handlerului se propagă în afară prin apelul Calculate care a declanșat evaluarea. Asta este acceptabil într-un generator strâns controlat și nepoliticos într-un serviciu care evaluează registre de lucru scrise de utilizatori, unde o singură formulă proastă ar doborî cererea. În cadrul acela, prinde excepția în interiorul handlerului și întoarce o santinelă pe care fluxul din jur o poate recunoaște și consemna

Funcțiile conștiente de poziție au nevoie de varianta Ex

Unele funcții depind pe bună dreptate de locul în care sunt evaluate. O cotă care diferă de la o foaie la alta, o căutare relativă la rând, un multiplicator regional care se aplică doar pe foile regionale: la niciuna dintre acestea nu se poate răspunde doar din valorile argumentelor. Evenimentul simplu nu poate exprima asta, așa că motorul oferă OnUserFunctionEx, identic în afară de un parametru în plus:

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
    // aceeași formulă dă o cotă diferită pe fiecare foaie regională
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext poartă SheetIndex, Row și Col ale celulei care se evaluează. Dacă rezultatul unei funcții depinde cât de puțin de locul ei, cablează evenimentul Ex de la bun început. Adăugarea ulterioară a contextului într-un handler pe care îl apelează deja treizeci de formule este mult mai anevoioasă decât alegerea semnăturii potrivite din prima zi, iar cele două evenimente sunt altfel atât de asemănătoare, încât nu prea există motiv să pornești de la cel mai îngust

Funcțiile personalizate nu ajung până în Excel

O funcție personalizată trăiește în întregime în interiorul procesului tău. Numele DISCOUNT înseamnă ceva doar cât timp rulează codul tău Delphi și handlerul lui de eveniment. Deschide fișierul salvat în Excel și DISCOUNT este doar un nume nerecunoscut; celula arată #NAME?, dacă nu cumva se nimerește să existe pe mașina utilizatorului o funcție VBA sau un add-in cu același nume. Acesta este faptul de proiectare care desparte o demonstrație de un produs livrabil și impune o alegere pe care trebuie să o faci deliberat, nu să o descoperi mai târziu

Hotărăște, pentru fiecare celulă, care dintre cele două contracte îl livrezi. Celulele pe care utilizatorul trebuie să le vadă recalculându-se în Excel trebuie construite din vocabularul de funcții al lui Excel și din nimic altceva. Celulele a căror logică este proprietară ar trebui evaluate în proces cu Calculate și păstrate ca valori simple, astfel încât funcția personalizată să se poarte ca o regulă internă de calcul, nu ca un conținut de fișier. Modul de eșec care produce cu regularitate tichete de suport este calea de mijloc: să păstrezi o formulă cu funcție personalizată și să te aștepți ca Excel să o onoreze

Contractul „doar valori” are un avantaj discret: protejează proprietatea intelectuală. O regulă de preț evaluată în procesul tău Delphi și livrată ca număr nu poate fi reconstituită din registrul de lucru așa cum poate una vizibilă, iar un utilizator nu o poate strica editând o celulă intermediară. Generatoarele de facturi, situațiile de comisioane și fișele de tarife țin aproape întotdeauna de tabăra aceasta. Cazul care are cu adevărat nevoie de formule vii este modelul interactiv de tip „ce-ar fi dacă”, unde clientul este așteptat să schimbe intrările și să vadă totalurile mișcându-se, iar acelea trebuie construite din vocabularul propriu al lui Excel plus nume definite

Moduri de calcul, iterație și R1C1: butoanele fațadei XLS

Fațada XLS expune setările de calcul de nivel BIFF pe care Excel le citește din fișier. CalculationMode acceptă xlCalcManual, xlCalcAutomatic (valoarea implicită) sau xlCalcAutomaticExceptTables și hotărăște cum se poartă Excel odată deschis fișierul. Un registru de lucru-model cu mii de formule este adesea mai prietenos livrat în mod manual, ca destinatarul să decidă el când se dezlănțuie furtuna de recalculare. EnableIteration (implicit False), împreună cu MaxIterations (implicit 100) și MaxIterationChange (implicit 0.001), deblochează referințele circulare deliberate, de tipul convergenței iterative, care apar în unele modele financiare. ReferenceStyle comută între afișarea A1 și cea R1C1, iar UseFullPrecision oglindește opțiunea Excel de precizie ca la afișare

Proprietățile acestea stau pe fațada XLS pentru că se mapează pe înregistrări BIFF; când generezi .xlsx, planifică formulele astfel încât să nu depindă de setările iterative sau calculează în Delphi valorile convergente și scrie rezultatele

Formule matriceale: punctul public de intrare este XLSX

Formulele matriceale vechi, în stil CSE, se creează prin TXLSXRange.SetArrayFormula:

// o singură formulă matriceală care se întinde pe A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Metoda echivalentă există în ierarhia de clase XLS, dar stă într-o secțiune privată, așa că nu există nicio cale suportată de a scrie formule matriceale noi în fișiere .xls. Cele existente în fișierele deschise fac dus-întorsul intacte; ce nu poți face este să le creezi. Regula care decurge de aici este destul de simplă: când semantica matriceală face parte din cerință, țintește .xlsx. Dacă un livrabil .xls vechi chiar are nevoie de comportament matriceal, drumul pragmatic este să calculezi rezultatul matriceal în Delphi și să scrii valorile individuale în celule

Două lecturi înrudite de pe acest site: numele definite și formulele între foi tratează rezolvarea de nume pe care o face motorul, iar articolul despre exportul CSV și TSV detaliază comportamentul de export care face necesar calculul explicit. Referința completă a motorului, inclusiv setul de funcții suportate, vine la pachet cu HotXLS Delphi Component