Generarea rapoartelor Excel bazate pe șabloane în Delphi cu HotXLS

Modul fiabil de a produce un raport Excel stilizat din Delphi este să porniți de la un registru de lucru deja construit de un designer. Cineva din departamentul financiar aranjează factura în Excel: logo-ul, antetele de coloane, bordurile pe banda de detalii, rândul cu totaluri îngroșate, formatele monetare. Codul dvs. deschide acel fișier, injectează date live în celulele pe care designerul le-a rezervat pentru asta și salvează rezultatul. Aspectul este al lor; numerele sunt ale dvs. HotXLS, o bibliotecă nativă Delphi și C++Builder care citește și scrie registre de lucru XLS și XLSX fără să controleze Excel, vă oferă cele trei operații de care are nevoie această abordare: căutarea unei celule după textul ei, copierea unui interval cu stilurile și formulele intacte și inserarea de rânduri astfel încât tot ce este dedesubt se deplasează în jos odată cu datele

Singura regulă care separă un generator care supraviețuiește editărilor de șablon de unul care se rupe la prima editare este să nu adresați niciodată celulele prin numere literale de rând și coloană. Un șablon este un document pe care îl editează alți oameni. Echipa financiară adaugă o linie de taxă, mărește înălțimea rândului cu logo-ul, reordonează blocul de adresă, iar formatul de fișier nu vă ajută deloc: o salvare BIFF sau OOXML reușește indiferent dacă rândul 10 mai înseamnă sau nu ce însemna trimestrul trecut. Un generator care scrie prima linie de detaliu pe un rând 10 codificat direct va, prima dată când cineva inserează un bloc deasupra secțiunii de detalii, ștampila liniile de articole peste celulele greșite și va însuma un interval de totaluri care nu mai acoperă datele. Nimic nu ridică o excepție, fiecare salvare returnează succes, iar singurul semnal este un client care observă o factură greșită

Ancorați fiecare coordonată la un token de tip placeholder

Soluția este să faceți ca șablonul să își poarte propriile coordonate. Designerul scrie token-uri precum {{CUSTOMER}}, {{DATE}} și {{DETAIL_START}} în celulele pe care generatorul trebuie să le atingă, iar generatorul calculează fiecare poziție la momentul rulării, în funcție de unde găsește acele token-uri. Editările de aspect nu mai contează, pentru că token-ul se mișcă odată cu celula în care se află. A doua jumătate a contractului este regula de eșec: dacă un token necesar lipsește, job-ul se oprește înainte ca vreo dată de client să ajungă în fișier. Un șablon care a suferit derivă ar trebui să producă un tichet de job eșuat, nu un document livrat

Găsirea token-urilor: FindText și ReplaceText

Ambele familii de clase HotXLS expun căutare la nivel de foaie de lucru. FindText returnează rândul și coloana primei celule al cărei text corespunde, cu o supraîncărcare care adaugă sensibilitate la majuscule. ReplaceText înlocuiește fiecare apariție și returnează câte a schimbat. Cele două acoperă cele două tipuri de token pe care tindeți să le aveți. O ancoră unică precum numele clientului, pe care o localizați o dată și lângă care scrieți; un token care ar trebui să apară exact o dată, precum data raportului, pe care îl înlocuiți și verificați numărul. Pe partea XLSX, o umplere care se ancorează astfel arată așa:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // TXLSXSheets.Items este bazat pe 0

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // expansiunea detaliilor și salvarea urmează mai jos
  finally
    Book.Free;
  end;
end;

Contează două detalii. Primul: FindText și ReplaceText se potrivesc cu valoarea de text a unei celule; un token înglobat într-un șir de formulă le este invizibil, așa că token-urile placeholder aparțin celulelor simple, niciodată în interiorul formulelor. Al doilea: numărul de înlocuiri este detectorul dvs. de derivă. Un șablon care ar trebui să conțină exact un token {{DATE}}, dar raportează zero înlocuiri, a fost editat, iar ridicarea unei excepții în acel moment este exact ceea ce transformă deriva tăcută a aspectului într-un eșec vizibil

Clonarea rândului de detaliu fără a pierde stiluri sau formule

Secțiunea de detalii a unei facturi crește odată cu datele. Scrierea valorilor direct în rânduri goale sub linia de exemplu aruncă tot ce a pregătit designerul: bordurile, formatele numerice, formulele per rând. Modelul care păstrează toate acestea este să lăsați un rând de exemplu complet formatat în șablon și să îl clonați pentru fiecare articol. CopyRange duplică stilurile și formulele într-un singur apel, după care generatorul suprascrie doar celulele cu valori

const
  DetailRow = 10;            // rândul de exemplu formatat din șablon
var
  I: Integer;
begin
  // Deschideți întâi spațiu înainte de blocul de totaluri, astfel încât
  // intervalul SUM de sub banda de detalii se întinde odată cu datele.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // clonează stilurile + formulele din rândul de exemplu
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // fără prefix '='
  end;
end;

Urmăriți cu atenție atribuirea formulei. Proprietatea Formula din XLSX preia expresia fără semnul egal la început, în timp ce fațada XLS așteaptă '=B10*C10' atribuit prin Value. Amestecarea celor două convenții este cea mai frecventă greșeală de portare între familiile de clase, și eșuează fără nicio plângere: celula reține doar un șir literal pe care Excel îl afișează ca text. Dacă șablonul decorează banda de detalii cu rânduri de titlu îmbinate, țineți minte că doar celula din stânga sus a unei zone îmbinate poartă o valoare. Regulile de aspect din articolul complementar despre celulele îmbinate în șabloane de raport bazate pe aspect explică de ce zonele de îmbinare aparțin în întregime în afara benzii de date

Ce mută InsertRows, și ce lasă în urmă

Inserarea rândurilor înaintea blocului de totaluri este ceea ce menține un interval SUM întinzându-se pe măsură ce secțiunea de detalii crește. Pe partea XLSX, InsertRows poartă o listă lungă de structuri dependente odată cu celulele: intervale îmbinate, înălțimi de rânduri, hyperlink-uri, comentarii, panouri înghețate, intervale de autofiltru, formate condiționate, validări de date, tabele, nume definite, precum și ancore de imagini și diagrame. Există o limită în acea listă care merită reținută în memorie. Rescrierea formulelor ajunge doar la referințele din interiorul aceleiași foi. O formulă dintr-o foaie de sumar care indică spre regiunea mutată își păstrează coordonatele vechi și citește tacit celulele greșite, motiv pentru care totalurile extrase din alte foi sunt mai sigur exprimate prin nume la nivel de registru de lucru. Articolul complementar despre nume definite și formule între foi parcurge acel model

Formatul XLS vechi trasează linia într-un loc mai dur. HotXLS păstrează tabelele pivot, tabelele de interogare și conexiunile de date externe din fișierele BIFF ca blocuri brute de octeți. Ele supraviețuiesc deschiderii și salvării neschimbate, dar nu sunt modelate, așa că inserarea de rânduri nu le atinge niciodată. Un șablon care parchează un tabel pivot sub un bloc de detalii în expansiune se salvează fără niciun avertisment, în timp ce dreptunghiul sursă al pivotului se îndepărtează de date. Ieșirea este structurală, nu defensivă: păstrați conținutul pivot și de interogare pe foi în care generatorul nu inserează niciodată, și învechirea nu se poate întâmpla

Recalculați înainte de livrare, sau știți de ce ați omis-o

HotXLS nu evaluează formulele în timpul SaveAs. Când o persoană deschide fișierul, Excel recalculează totul (fațada XLS expune CalculationMode și RecalcOnSave dacă trebuie să direcționați asta), așa că un raport destinat unei căsuțe poștale umane nu mai are nevoie de nimic altceva de la dvs. Imaginea se schimbă în momentul în care registrul de lucru alimentează un alt program. Exportul CSV scrie formulele ca text literal și nu le calculează niciodată, iar orice parser din aval care are încredere în valorile din cache va citi numere învechite sau spații goale. Pentru acele căi, calculați pe server cu Calculate, care evaluează o expresie arbitrară față de registrul de lucru încărcat și returnează rezultatul:

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

Verificarea totalului calculat față de înregistrarea comenzii înainte de salvare este o asigurare ieftină cu o recompensă bună. Transformă o factură greșită într-un job eșuat. Un operator poate reîncerca un job eșuat în câteva secunde; o factură greșită ajunsă deja în căsuța poștală a unui client costă un account manager o scuză și o corecție

Două familii de clase, un singur algoritm

Aceeași logică se portează între formate, dar nu același cod. TXLSWorkbook pentru .xls vechi este bazat pe interfață și numărat prin referințe, cu indexare de foi bazată pe 1, și nu îl eliberați niciodată manual. TXLSXWorkbook pentru .xlsx este un obiect simplu pe care trebuie să îl eliberați într-un try..finally, cu indexare de foi bazată pe 0 și convenția de formule prezentată mai sus. FindText, ReplaceText, CopyRange și InsertRows trăiesc pe ambele părți, așa că forma ancorare-clonare-recalculare se transferă curat. Sfatul practic este să vă angajați la un singur format per pipeline, sau să ascundeți cele două cicluri de viață ale obiectelor în spatele unui adaptor propriu subțire, mai degrabă decât să răspândiți diferența prin tot generatorul

Dimensiunea rareori contează pentru tipul de raport pe care îl produce acest model. Clonarea unui rând stilizat de câteva mii de ori nu înseamnă nimic pentru hardware-ul actual. Calea de salvare devine blocajul doar atunci când o bandă de detalii ajunge la șase cifre de rânduri, iar în acel moment setarea StreamingWrite trimite XML-ul foii de lucru direct în pachetul de ieșire, în loc să îl pună în buffer; articolul despre scrierile în flux pentru job-uri de lot pe server acoperă când merită făcut acest compromis. Diagramele se comportă la fel ca restul aspectului: pe partea XLSX, atât ancora diagramei, cât și referințele seriei sale se mută atunci când InsertRows rulează deasupra lor, așa că o diagramă de sub rândul de totaluri rămâne legată de datele corecte, în timp ce pe partea XLS diagramele stau pe propriile foi de diagramă și, ca și tabelele pivot, nu se mută niciodată. Acesta este încă un argument pentru a păstra foile de prezentare departe de foaia pe care o extinde generatorul

Această abordare de tip ancorare-clonare-recalculare lasă un designer să dețină modul în care arată un registru de lucru, în timp ce codul dvs. deține ce spune, ceea ce în general face ca ieșirea Excel generată să merite întreținută. Apelurile de căutare, copiere și inserare prezentate aici, împreună cu motorul de formule folosit pentru verificarea totalului înainte de livrare, sunt livrate cu HotXLS Delphi Component pentru Delphi și C++Builder