Performanța registrelor Excel mari în Delphi cu HotXLS

Când un export de 300.000 de rânduri își sparge bugetul de memorie, vina cade de obicei pe numărul de rânduri. Numărul de rânduri este de obicei nevinovat. Părțile scumpe ale unui registru de lucru mare sunt cele create ca efect secundar: un fond de stiluri care crește cu câte o intrare pentru fiecare celulă, pentru că formatarea a fost adăugată în interiorul buclei, XML de foaie de calcul asamblat ca un singur șir uriaș la salvare, un milion de corpuri de formulă identice stocate unul câte unul. HotXLS, biblioteca nativă Delphi de la losLab pentru fișiere XLS și XLSX, îți dă câte o pârghie anume pentru fiecare dintre aceste costuri. Niciuna nu este activată implicit, pentru că fiecare schimbă un compromis, așa că a ști ce pârghie se potrivește cu ce simptom este adevărata pricepere în materie de performanță

Unde își cheltuiește memoria un registru de lucru mare

Sunt două regimuri distincte de memorie la care merită să te gândești. În timpul generării, modelul de celule din memorie crește cu fiecare celulă pe care o atingi: valorile, formatele și formulele devin toate obiecte sau intrări în fond. În timpul salvării, calea XLSX implicită randează în plus XML-ul fiecărei foi de calcul într-un șir lat înainte să îl comprime în containerul zip, așa că vârful de consum este modelul plus forma serializată a celei mai mari foi. Un job care supraviețuiește buclei de construcție și apoi moare în SaveAs lovește în al doilea regim, nu în primul, iar remediul pentru unul nu face nimic pentru celălalt

Dimensiunea fișierului urmează o regulă înrudită: celulele sunt doar unul dintre contributori, alături de stiluri, șiruri partajate, formule, imagini și comentarii. O trecere de audit cu ForEachCell și cu numărătorile colecțiilor de pe fiecare foaie îți spune ce resursă domină de fapt un fișier problematic, înainte să o optimizezi pe cea greșită. O subtilitate de măsurare: Sheet.Cells.Count de pe partea XLSX raportează numărul de celule instanțiate din depozitul rar, nu aria intervalului folosit. O foaie ale cărei date ocupă un dreptunghi de 1000 pe 50, cu jumătate dintre celule goale, numără vreo 25.000, nu 50.000. Distincția aceasta contează când compari fișierul „uriaș” al unui client cu fixturile tale, pentru că aria intervalului folosit și populația reală de celule pot diferi cu un ordin de mărime în machetele financiare rare

StreamingWrite repară calea de salvare, nu calea de construcție

Setarea lui TXLSXWorkbook.StreamingWrite := True comută SaveAs pe un serializator în flux, care scrie XML-ul foii de calcul direct în fluxul zip, eliminând intermediarul de tip șir de pe fiecare foaie. Valoarea implicită este False, pentru compatibilitate de comportament, iar pornirea lui este o schimbare de o singură linie:

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // XML-ul foii curge în containerul zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Fii precis în privința a ceea ce îți cumpără asta: modelul de celule construit de buclă ocupă exact la fel de multă memorie ca înainte. StreamingWrite aplatizează vârful din momentul salvării, adică diferența dintre un job în lot care se termină și unul care cade la 95%. Dacă însăși bucla de construcție epuizează memoria, pârghiile de care ai nevoie sunt următoarele două

Fondurile de stiluri: adaugă o dată, refolosește indexul

Formatarea XLSX în HotXLS se sprijină pe fonduri: Book.Fonts.Add(...), Fills.AddSolid(...) și Borders.Add(...) întorc un index de fond numerotat de la 0, pe care celulele îl referă. Apelarea lui Fonts.Add cu parametri identici în interiorul unei bucle este deduplicată, așa că irosește timp, nu spațiu. Alignments.Add se poartă altfel: întoarce un obiect nou la fiecare apel, așa că crearea unei alinieri pentru fiecare celulă crește fondul liniar cu numărul de rânduri. Un singur obicei acoperă ambele cazuri. Rezolvă fiecare index de fond o singură dată, în afara buclei, și atribuie indecșii înăuntru

// scoate căutările în fond în afara buclei fierbinți
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // index de fond, de la 0
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // celulele îl țin de la 1; 0 = implicit

+ 1 nu este o greșeală de tipar, iar uitarea lui este eroarea clasică generatoare de simptome de aici: fondurile dau indecși numerotați de la 0, în timp ce proprietățile din partea celulei tratează 0 drept „implicit”, așa că fiecare index de fond trebuie deplasat cu unu la atribuire. Greșește-l prin omisiune și antetele tale se afișează tăcut cu fontul implicit al registrului de lucru, un defect pe care nu îl observă nimeni până la analiza de brand

Înlocuiește traficul de Variant per celulă cu apeluri inverse pe rând

Fiecare Sheet.Cells[R, C].Value := X implică o căutare-sau-creare de celulă plus o atribuire de Variant. La câteva sute de mii de celule, costul acela per acces devine măsurabil în profiluri. HotXLS oferă pe ambele fațade API-uri de callback în masă (ForEachCell și ForEachRow pentru citire, WriteCells și WriteRows pentru scriere), care mută iterația în interiorul motorului și îi predau codului tău rânduri întregi deodată:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // oprește toată scrierea
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// un singur apel de motor în loc de sute de mii de atingeri de proprietăți
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Indicatorul Skip al callbackului lasă un rând neatins fără să întrerupă totul, iar Cancel încheie operația mai devreme, ceea ce este util când sursa este un cititor a cărui lungime o afli pe parcurs. Împerechează WriteRows pentru construcție cu StreamingWrite pentru salvare și calea de generare nu mai are niciun punct fierbinte per celulă

Pârghii de citire pe fațada XLS

Fișierele .xls vechi și mari au trusa lor de unelte. _DisableGraphics := True înainte de Open sare complet peste analizarea stratului de desenare, ceea ce grăbește încărcarea registrelor de lucru care poartă ani întregi de forme adunate și de imagini încorporate. Restricția este dură: stratul de desenare lipsește atunci din model, așa că salvarea unui asemenea registru de lucru scrie un fișier fără desenele lui. Rezervă indicatorul acesta pentru joburile de analiză exclusiv în citire. SetTempDir redirecționează fișierele temporare ale scriitorului BIFF, ceea ce contează pe serverele unde locația temporară implicită are o cotă sau stă pe stocare lentă. UseSharedFormulas grupează corpurile de formulă repetate în înregistrări de formulă partajată, micșorând fișierele în care o coloană de formule se repetă pe șaizeci de mii de rânduri

Buclele de citire peste date XLS au o capcană de indexare care merită semnalată, pentru că dublează munca atunci când este tratată defensiv și strică rezultatele când este ratată: UsedRange își raportează limitele FirstRow, LastRow, FirstCol și LastCol numerotate de la 0, în timp ce Cells.Item[Row, Col] este numerotat de la 1. O scanare care parcurge intervalul folosit trebuie să adune unu la fiecare coordonată în momentul accesului la celulă, ca în Cells.Item[Row + 1, Col + 1], altfel citește o grilă deplasată pe diagonală cu o celulă, pierzând tăcut ultimul rând și ultima coloană și incluzând un prim rând fantomă. Callbackul ForEachCell ocolește nepotrivirea cu totul, ceea ce este încă un motiv să îl preferi pentru scanările de foaie întreagă

Sondează fișierele înainte să le încarci

Cea mai ieftină operație pe un registru de lucru mare este cea pe care o eviți. GetSheetNames de pe ambele fațade listează foile de calcul ale unui fișier fără să încarce datele din celule. Implementarea XLSX citește doar manifestul registrului de lucru din arhiva zip și lasă explicit instanța de registru nepopulată, iar fațada XLS se oprește din scanat la prima graniță de subflux. Asta o face verificarea potrivită de dinaintea zborului pentru „ce foaie ar trebui să țintească acest job de import”, iar CanReadEncrypted răspunde la „este acesta un container criptat” înainte de o încercare de Open sortită eșecului

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // eșecul golește lista
  // alege foaia țintă, apoi hotărăște dacă merită un Open complet
finally
  Book.Free;
  Names.Free;
end;

Ia aminte la convenția codurilor de returnare: aceste funcții de sondare semnalează eșecul cu valori la zero sau sub zero și golesc lista de ieșire, așa că testează <= 0 în loc să compari cu o singură valoare anume de succes

Potrivirea abordării cu dimensiunea sarcinii

Pentru pipeline-urile nesupravegheate care generează în serie multe fișiere mari, încă două obiceiuri rotunjesc tabloul. Obiectele registru de lucru nu sunt sigure pentru partajare, dar nimic nu împiedică un registru de lucru independent pentru fiecare fir lucrător, ceea ce paralelizează curat conversia în lot. Iar când ieșirea merge spre HTTP, nu spre disc, supraîncărcările de salvare pe TStream se combină cu StreamingWrite, astfel încât un răspuns mare să nu se materializeze niciodată ca fișier temporar. Se aplică o notă operațională: salvarea pe flux scrie de la poziția curentă, fără să deruleze înapoi, așa că setează Position := 0 înainte să predai fluxul cadrului de răspuns. Articolul despre scrierea în flux și joburile în lot dezvoltă tiparul acela de pe server, iar articolul despre exportul din baza de date arată unde se așază aceste pârghii într-un raport condus de un set de date

În fine, ține câte o fixtură cu cel mai rău caz pentru fiecare familie de rapoarte și cronometreaz-o în CI. Regresiile de performanță din generarea de documente rareori se anunță singure. Un stil adăugat în interiorul unei bucle sau o sondare înlocuită cu un Open complet nu schimbă nimic funcțional, iar lotul de noapte pur și simplu durează cu patruzeci de minute mai mult. Un test cronometrat pe o fixtură reprezentativă de jumătate de milion de celule preface abaterea aceea într-un build roșu, în loc de un incident de operațiuni

Versiunile de evaluare, proiectele demonstrative cu un exemplu de generare în masă și referința completă de API sunt disponibile pe pagina HotXLS Delphi Component