Un foaie de calcul cu un milion de rânduri și câteva zeci de coloane este un export perfect obișnuit dintr-un job de raportare a bazei de date. Deschizând-o în mod obișnuit, prin încărcarea întregului registru de lucru într-un TXLSWorkbook, procesul trebuie să materializeze fiecare dintre cele douăsprezece milioane de celule ca un obiect activ înainte ca prima linie de logică de business să ruleze. Fișierul de pe disc ar putea fi șaizeci de megaocteți de XML comprimat. Arborele de obiecte în care se extinde este de câteva ori mai mare, și toate trebuie să fie rezidente simultan deoarece modelul este proiectat pentru acces aleator. Pentru un raport pe care intenționați să-l citiți de sus în jos și să-l aruncați, aceasta este o cantitate mare de memorie cheltuită pe o structură de care nu ați avut niciodată nevoie
Există o a doua cale prin același fișier. În loc să construiți un model, scanați XML-ul foii de lucru doar înainte, câte o celulă pe rând, și lăsați fiecare celulă să treacă după ce ați analizat-o. Nimic nu se acumulează. Memoria rămâne aproape constantă indiferent dacă foaia are o mie de rânduri sau zece milioane, deoarece cititorul nu reține niciodată mai mult decât partea pe care o analizează în prezent plus câteva tabele de căutare mici. Acesta este ceea ce face cititorul direct HotXLS, iar restul acestui articol explică de ce rămâne mic și ce vă oferă în schimb
De ce modelul în memorie nu scalează
Un fișier XLSX este un pachet ZIP de părți XML descrise de ECMA-376. Fiecare foaie de lucru este propria sa parte, xl/worksheets/sheetN.xml, și în interiorul ei fiecare rând este un element <row> care conține elemente de celulă <c>. Calea de încărcare obișnuită citește acea parte și construiește un obiect adresabil pentru fiecare celulă, astfel încât mai târziu să puteți cere Cells[12345, 7] și să primiți un răspuns în timp constant. Accesul aleator este scopul principal al unui model de registru de lucru, și exact acesta este ceea ce face editarea, evaluarea formulelor și stilizarea convenabilă
Costul este că accesul aleator necesită ca totul să fie prezent simultan. Nu puteți indexa într-o structură pe care ați construit-o doar parțial. Deci memoria de vârf a unei încărcări complete este o funcție a numărului de celule, și pe o foaie cu milioane de celule populate, acea funcție aterizează undeva unde serviciul dvs. nu vrea să fie, mai ales dacă mai multe astfel de joburi rulează simultan pe o mașină partajată. Când tiparul de acces de care aveți nevoie este secvențial, plata pentru acces aleator înseamnă plata pentru o capacitate pe care nu o veți folosi
O scanare SAX numai-înainte care nu construiește niciun arbore
Cititorul direct deschide pachetul ZIP și parcurge fiecare parte a foii de lucru cu un parser de tip pull SAX. SAX înseamnă că parserul raportează evenimentele de analizare pe măsură ce le întâlnește: un element de start, un șir de text, un element de final, și apoi continuă. Nu păstrează niciun arbore de noduri în urma lui. Cititorul urmărește rândul și coloana curente din atributele r, colectează tipul celulei, indexul de stil, valoarea și textul formulei pe măsură ce evenimentele sosesc, și când se vede eticheta de închidere </c>, emite o celulă și o uită. Celula următoare reutilizează aceleași câteva variabile locale
Deoarece nimic nu este reținut între celule, amprenta de memorie nu crește cu numărul de celule. Aceasta este proprietatea care merită reținută. O foaie cu două sute de rânduri și una cu douăzeci de milioane de rânduri costă cititorul aceeași memorie rezidentă, iar diferența dintre ele este doar cât timp durează scanarea. Renunțați la accesul aleator, funcția principală a modelului, și în schimb obțineți un plafon al memoriei pe care numărul de celule nu îl poate depăși
Ce rămâne rezident și de ce acele două părți
Scanarea nu este complet fără stare, iar excepțiile sunt instructive. Două tabele mici trebuie reținute în memorie pe toată durata, deoarece o celulă singură nu poartă suficiente informații pentru a fi interpretată fără ele
Prima este tabela de șiruri partajate. În SpreadsheetML, o celulă text nu stochează propriul text. Poartă t="s" și un număr care este un index în xl/sharedStrings.xml, o singură listă deduplicată a fiecărui șir distinct din registrul de lucru. Acesta este un compromis bun de spațiu pentru fișierele unde aceleași etichete se repetă pe mii de rânduri, dar înseamnă că cititorul trebuie să încarce acea tabelă de șiruri în avans și să o mențină rezidentă, deoarece orice celulă oriunde în orice foaie poate face referire la orice intrare din ea. Tabela este dimensionată după numărul de șiruri distincte, nu după numărul de celule, deci rămâne modestă chiar și pe foile enorme
A doua este maparea formatelor numerice din partea de stiluri. O celulă numerică și una de dată sunt identice octet cu octet pe fir: ambele sunt un număr simplu, deoarece o dată în SpreadsheetML este doar un număr de serie pentru zile. Singurul lucru care le distinge este stilul celulei, care indică prin cellXfs în xl/styles.xml spre un id de format numeric. Pentru a raporta o dată ca dată, nu ca numărul de serie brut, cititorul încarcă acea tabelă de mapare stil-la-format și o menține rezidentă. Tot restul din fișier, datele reale ale celulelor care constituie cea mai mare parte a octeților, trece în flux fără a fi stocat
Fiecare celulă raportează un tip și o valoare
Fiecare celulă emisă sosește ca o înregistrare TXLSDirectCell. Poartă indexul și numele foii, rândul și coloana bazate pe 1, un Kind semantic, Value ca Variant, textul Formula fără semnul egal de start, și StyleIndex brut. Tipul este unul dintre xdkNumber, xdkString, xdkBoolean, xdkDate sau xdkError, astfel încât puteți ramifica pe baza semnificației celulei, nu pe baza re-derivării din atribute. O celulă formulă raportează tipul rezultatului său memorat în cache, cu textul formulei alăturat, astfel încât un total calculat apare ca un număr care vă spune și cum a fost produs
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Distingerea unei date de un număr
Întrebarea privind datele merită o privire mai atentă deoarece aici greșesc majoritatea scannerelor naive. Nu există niciun tip de dată pe o celulă numerică. O celulă care conține valoarea serială 46000 ar putea fi o cantitate, un preț sau 17 februarie 2025, și fișierul vă spune care dintre acestea doar prin id-ul de format numeric obținut prin stilul celulei. ECMA-376 rezervă un bloc de id-uri de format încorporate al căror sens este fix la fiecare producător conform, iar id-urile purtătoare de date se află în două intervale: de la 14 la 22 pentru formatele standard de dată și timp, și de la 45 la 47 pentru formatele de timp scurs, precum [h]:mm:ss. Când DetectDates este activat, ceea ce este cazul implicit, cititorul rezolvă stilul fiecărei celule numerice la id-ul său de format, iar o celulă al cărei id se încadrează în acele intervale rezervate este raportată ca xdkDate cu Value deja convertit la un Delphi TDateTime. Formatele personalizate sunt verificate de asemenea, prin inspecția codului de format pentru jetoane de dată și timp, dar intervalele rezervate sunt coloana vertebrală fiabilă. Dezactivați DetectDates și tabela de stiluri nu este nici măcar încărcată; fiecare celulă numerică apare ca xdkNumber, iar scanarea este ușor mai eficientă
Omiteți foi și opriți devreme
Scanarea secvențială are un avantaj discret pe care accesul aleator nu îl poate egala: puteți opri. Evenimentul OnSheet se declanșează înainte ca fiecare foaie de lucru să fie deschisă și vă oferă două comutatoare. Setați SkipSheet și acea parte întreagă nu este niciodată analizată, acesta este modul în care scanați doar foile care vă interesează dintr-un registru de lucru cu mai multe foi, fără a plăti pentru a citi restul. Setați Abort și întreaga scanare se termină imediat. Evenimentul OnCell are propriul Abort, astfel încât puteți opri în momentul în care ați găsit ceea ce căutați: un anumit rând, o valoare sentinel, sfârșitul unui bloc de antet, fără a citi milioanele de celule rămase. Pe o scanare numai-înainte, oprirea este cu adevărat gratuită, deoarece munca pe care o săriți este muncă care nu avusese loc încă
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Numărarea celulelor fără un handler
O rafinare recentă merită menționată deoarece transformă o întrebare frecventă într-un singur apel ieftin. Cititorul numără fiecare celulă populată pe care o trece, și face acest lucru indiferent dacă un handler OnCell este atașat sau nu. Anterior, fără handler setat, numărul de celule populate se întorcea ca zero, deoarece numărarea era un efect secundar al emiterii. Acum numărul este independent de emitere. Aceasta înseamnă că puteți pune o singură întrebare: câte celule populate conține de fapt acest registru de lucru, și primiți răspunsul la prețul unei scanări fără niciun callback. ReadFile și ReadStream returnează ambele acel total ca Int64, iar același număr este disponibil ulterior ca proprietatea CellCount. O returnare de -1 semnalează că fișierul nu a putut fi deschis sau nu este un pachet OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Pentru scanarea completă, atașați handler-ul și apelați ReadFile exact în același mod. Contrastul cu o încărcare completă este tocmai esența: în timp ce încărcarea quarterly_export.xlsx într-un registru de lucru ar extinde fiecare celulă într-un obiect rezident și le-ar reține pe toate, cititorul direct menține doar șirurile partajate și tabela de stiluri, în timp ce cele douăsprezece milioane de celule trec prin OnCell câte una pe rând. Calculul executat per celulă nu lasă nimic în urmă, deci memoria de vârf este determinată de numărul de șiruri distincte din registrul de lucru, nu de numărul de rânduri
Cititorul direct este instrumentul potrivit când jobul este să citiți o dată un registru de lucru mare și să extrageți sau să rezumați datele din el. Când aveți nevoie de accesul aleator al modelului complet, dar doriți să funcționeze bine cu fișiere mari, reglajele din notele noastre despre performanța cu registre de lucru mari în Delphi acoperă acea cale. Și când direcția este inversată, producând ieșiri mari în loc să consume, ghidul de scriere în flux pentru joburi de server și batch aplică aceeași disciplină de memorie constantă la scriere. Toate trei sunt livrate ca parte a componentei HotXLS Component pentru Delphi și C++Builder, alături de API-urile de citire, scriere, formulă și formatare acoperite în altă parte pe acest blog