Nume definite și formule între foi de calcul în Delphi cu HotXLS

Un nume definit este o etichetă care ține locul unei constante, al unui interval de celule sau al unei expresii de formulă, stocată o singură dată în registrul de lucru și referită simbolic oriunde este nevoie de ea. Scrii TaxRate într-o formulă, iar motorul îl rezolvă către ce ține definiția numelui, fie literalul 0.08, fie intervalul Data!$A$2:$D$100. O referință între foi este ideea ortogonală: Data!D2 ajunge la o celulă de pe altă foaie calificând adresa cu un nume de foaie. Pune-le pe cele două laolaltă și o foaie de sinteză poate însuma o foaie de detaliu printr-un nume care nu pomenește niciodată o adresă literală, adică exact ce vrei într-un registru de lucru asamblat de un generator și auditat mai târziu de un contabil

HotXLS, biblioteca nativă Delphi de la losLab pentru fișiere XLS și XLSX, expune tabela de nume a ambelor formate cu acces de creare, de căutare și de ștergere, plus un motor de formule care rezolvă în proces numele și referințele între foi. Cele două formate păstrează ierarhii de clase separate, iar diferențele dintre API-urile lor de nume sunt partea care încurcă un cod portat de la unul la celălalt

Două depozite de nume care nu împart o interfață

Pe partea XLS, TXLSWorkbook.GetNames întoarce o colecție IXLSNames, a cărei supraîncărcare Add(Name, RefersTo, Visible) scrie un nume în tabela de nume BIFF. Intrările individuale revin ca obiecte IXLSName, care poartă Name, RefersTo, un RefersToRange rezolvat și o metodă Delete. Pe partea XLSX, TXLSXWorkbook.DefinedNames este o colecție TXLSXDefinedNames cu Add, FindByName și DeleteByName

Convențiile de căutare se despart într-un fel care iese la iveală la portare, nu la compilare. Proprietatea implicită Item a colecției XLS acceptă un Variant, așa că și Names[0], și Names['TaxRate'] se rezolvă pe ea. Colecția XLSX nu are o asemenea proprietate implicită; apelezi FindByName('TaxRate'), care întoarce nil când numele lipsește. Un cod scris pentru o fațadă compilează pe cealaltă doar din întâmplare, iar eșecul tinde să apară ca un acces nil la execuție, nu ca o subliniere roșie în IDE

Domeniul este prima decizie, nu un indicator adăugat ulterior

Un nume definit are fie domeniu de registru de lucru, adică este vizibil formulelor de pe orice foaie, fie domeniu de foaie, adică este vizibil doar formulelor de pe foaia care îl deține. În API-ul XLSX, distincția este un singur parametru opțional. DefinedNames.Add(AName, AFormula) creează un nume la nivel de registru de lucru, în timp ce Add(AName, AFormula, ASheetIndex) îl leagă de o singură foaie. La citirea înapoi, TXLSXDefinedName.SheetIndex întoarce -1 pentru domeniul de registru de lucru și, în rest, indexul de foaie numerotat de la 0

Domeniul ține loc și de politică de coliziuni, iar acesta este motivul pentru care trebuie lămurit înainte să scrii primul nume. Excel permite câte un Total local pe fiecare foaie, plus un Total la nivel de registru de lucru, iar o formulă de pe o anumită foaie îl rezolvă întâi pe cel local. Registrele de lucru generate ar trebui să se sprijine pe asta în mod deliberat. Presupunerile de business pe care le consumă mai multe foi, precum cotele de taxare, cursurile valutare și perioada de raportare, își au locul la nivel de registru de lucru. Intervalele ajutătoare pe care le referă doar formulele unei singure foi stau mai în siguranță cu domeniu de foaie, unde nimic nu le poate umbri și nici ele nu pot umbri nimic

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... umple Data!A2:D100 cu rânduri de detaliu ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // domeniu de registru, o constantă
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // domeniu de registru, un interval
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // legat doar de foaia cu indexul 1

    // formulele XLSX nu primesc '=' în față
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Un nume definit nu trebuie neapărat să indice un interval. TaxRate de mai sus se referă la constanta simplă 0.08, iar aceasta este cea mai curată cale de a publica o presupunere de business. Apare o singură dată în Name Manager din Excel, fiecare formulă îl referă simbolic, iar schimbarea cotei de trimestrul viitor este o modificare de o linie în generator, nu o căutare prin paisprezece șiruri de formulă asamblate

Semnul egal care își are locul doar într-o parte

Canalul de introducere a formulelor este locul în care codul portat se rupe cel mai des, pentru că cele două fațade nu se înțeleg în privința semnului egal. Celulele XLS primesc formule prin Value, cu un = în față. Celulele XLSX au o proprietate dedicată, Formula, care primește expresia fără prefix. Scrie '=SUM(A1:A10)' în TXLSXCell.Formula și semnul egal devine parte din textul expresiei stocate, nu un marcaj, iar fișierul nu se va purta așa cum se purta același șir în partea XLS

var
  Book: IXLSWorkbook;   // numărat prin interfață: nu apela Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // presupunem că o foaie numită 'Data' ține deja rândurile de detaliu
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = ascuns din Name Manager

  // formulele XLS trec prin Value, cu prefixul '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Fragmentul acela arată încă două ciudățenii din partea XLS. Colecția de foi este numerotată de la 1, așa că Sheets[1] este prima foaie, față de Sheets[0] numerotat de la 0 în XLSX. Iar al treilea parametru al lui Add creează un nume ascuns: prezent în fișier și utilizabil de formule, dar invizibil în Name Manager din Excel. Numele ascunse sunt vehiculul potrivit pentru instalația internă a generatorului, pe care utilizatorii finali nu ar trebui să o editeze sau să o șteargă din greșeală

Referințe între foi și ce se întâmplă când se mută rândurile

Ambele motoare de formule acceptă sintaxa standard între foi. Numele simple de foaie califică direct, ca Data!A1; un nume cu spații sau cu semne de punctuație are nevoie de ghilimele simple, ca în 'Sheet With Space'!A1. În interiorul textului RefersTo al unui nume, apelează aproape de fiecare dată la referințe absolute, precum Data!$A$2:$D$100. O referință relativă din interiorul unui nume definit se rezolvă relativ la celula care îl folosește, ceea ce este o funcționalitate Excel deliberată și o sursă sigură de confuzie când se declanșează din greșeală

Editările structurale sunt momentul în care contabilitatea între foi își merită banii, iar partea XLSX ține numele consecvente de-a lungul lor. InsertRows și DeleteRows deplasează intervalele numelor definite odată cu celulele, îmbinările, hyperlinkurile și ancorele de grafic, așa că un nume care indică Data!$A$2:$D$100 tot acoperă blocul de date după ce generatorul deschide un gol deasupra lui. Formulele vin cu un avertisment documentat: inserarea de rânduri ajustează doar referințele care țintesc foaia editată. O formulă din Summary care referă Data!D2:D100 este rescrisă când intră rânduri în Data, adică exact cazul pe care de obicei îl vrei. Verifică-l, nu îl presupune, pentru că motorul ți-o spune ieftin:

// motorul de calcul rezolvă în proces numele și referințele între foi
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate evaluează o expresie arbitrară față de starea curentă a registrului de lucru, fără să salveze nimic, ceea ce îl face primitiva firească de aserțiune pentru testele de generator. Calculează în Pascal agregatul așteptat pornind de la datele sursă, evaluează formula proprie a registrului de lucru și compară-le. Articolul despre motorul de formule tratează ce evaluează motorul, când o face și cum îl extinzi cu funcții personalizate

Numele _xlnm pe care le deține stratul de proprietăți

Deschide tabela de nume a unui fișier generat într-un inspector de nivel jos și vei găsi intrări pe care nu le-ai scris niciodată: _xlnm.Print_Area, _xlnm.Print_Titles și rudele lor. Așa stochează OOXML (ECMA-376 / ISO 29500) zonele de tipărire și rândurile de titlu repetate, ca nume definite cu identificatori rezervați. HotXLS le administrează prin proprietăți dedicate ale foii de calcul, așa că setarea lui PrintArea sau PrintTitleRows scrie în locul tău intrarea _xlnm.* corespunzătoare

Capcana este să bagi mâna de unul singur în spațiul acela rezervat de nume. Adaugă o intrare _xlnm.Print_Area prin DefinedNames.Add în timp ce setezi și proprietatea PrintArea, iar registrul de lucru poartă două definiții contradictorii pentru un singur nume rezervat, o stare pe care Excel o rezolvă în feluri pe care niciun produs nu ar trebui să se bizuie. Tratează orice identificator care începe cu _xlnm. ca aparținând stratului de proprietăți. Ca să inspectezi configurarea tipăririi, citește proprietățile, nu tabela de nume. Articolul despre protecție și configurarea paginii tratează în context proprietățile zonei de tipărire

Două limite de știut înainte să te angajezi la un proiect

Numele definite nu călătoresc prin puntea de conveniență de la XLS la XLSX. SaveXLSWorkbookAsXLSX copiază conținutul celulelor și formatarea de bază, iar tabela de nume nu se află pe lista lui documentată de copiere, așa că un registru de lucru care se bizuia pe numele lui le pierde la trecere. Recreează numele prin DefinedNames.Add după conversie. Pasul acesta este o corvoadă mai mică decât pare, pentru că îți dă un moment în care să le normalizezi domeniile, în loc să duci mai departe ce s-a nimerit să aibă fișierul XLS

Cealaltă limită este abaterea dintre șirurile de formulă și numele de foi. Excel rescrie referințele de foaie din formule și din nume în timpul unei redenumiri interactive, așa că fișierele pe care un utilizator le editează în Excel rămân consecvente de la sine. Expunerea este pe partea de generator: când codul Pascal asamblează șiruri de formulă pornind de la un literal cu nume de foaie, redenumirea foii într-un loc și uitarea celuilalt produce o referință către o foaie care nu mai există. Ține numele foii într-o singură constantă Delphi și dă-l atât lui Sheets.Add, cât și asamblării tale de formule, iar cele două nu se pot contrazice niciodată. Este același instinct care pledează pentru a denumi celulele de rezultat ale unui raport în loc să le codifici adresele: un șablon a cărui celulă de total este denumită merge mai departe după ce un proiectant inserează trei rânduri deasupra ei, în timp ce un generator care scrie într-un B17 literal își pune tăcut numărul în locul greșit. Articolul despre generarea de rapoarte pe bază de șabloane se construiește exact pe tiparul acesta

API-ul complet de nume definite pentru ambele formate, împreună cu referința motorului de formule, vine la pachet cu HotXLS Delphi Component