Arkusz z milionem wierszy i kilkunastoma kolumnami to zupełnie zwyczajny eksport z zadania raportowania bazy danych. Otwórz go w normalny sposób - ładując cały skoroszyt do TXLSWorkbook - a proces musi zmaterializować każdą z tych dwunastu milionów komórek jako żywy obiekt, zanim Twoja pierwsza linia logiki biznesowej w ogóle się uruchomi. Plik na dysku może mieć sześćdziesiąt megabajtów skompresowanego XML. Drzewo obiektów, które z tego wynika, jest wielokrotnie większe i musi być w całości rezydować w pamięci jednocześnie, bo model zapewnia losowy dostęp z założenia. Dla raportu, który zamierzasz czytać od góry do dołu i wyrzucić, to bardzo dużo pamięci przeznaczonej na strukturę, której nigdy nie potrzebowałeś
Przez ten sam plik prowadzi druga droga. Zamiast budować model, skanujesz XML arkusza tylko do przodu, jedną komórkę na raz, i pozwalasz każdej komórce przepływać dalej po tym, jak ją obejrzałeś. Nic się nie akumuluje. Pamięć pozostaje niemal stała bez względu na to, czy arkusz ma tysiąc wierszy czy dziesięć milionów, bo czytnik nigdy nie trzyma więcej niż aktualnie parsowaną część plus kilka małych tablic wyszukiwania. Właśnie to robi bezpośredni czytnik HotXLS - i o tym traktuje reszta tego artykułu: dlaczego pozostaje mały i co daje w zamian
Dlaczego model w pamięci nie skaluje się
Plik XLSX to pakiet ZIP z częściami XML opisanymi przez ECMA-376. Każdy arkusz to własna część, xl/worksheets/sheetN.xml, a wewnątrz każdy wiersz to element <row> zawierający elementy <c> komórek. Standardowa ścieżka ładowania czyta tę część i konstruuje adresowalny obiekt dla każdej komórki, abyś mógł później zapytać o Cells[12345, 7] i dostać odpowiedź w stałym czasie. Losowy dostęp to sens całego modelu skoroszytu - i właśnie to sprawia, że edycja, obliczanie formuł i formatowanie są wygodne
Kosztem jest to, że losowy dostęp wymaga obecności wszystkiego jednocześnie. Nie możesz indeksować struktury, którą tylko częściowo zbudowałeś. Szczytowe zużycie pamięci pełnego ładowania jest więc funkcją liczby komórek, a przy arkuszach z milionami wypełnionych komórek ta funkcja ląduje w miejscu, w którym Twój serwis nie chce być - szczególnie jeśli kilka takich zadań działa jednocześnie na współdzielonym serwerze. Gdy wzorzec dostępu, jakiego faktycznie potrzebujesz, jest sekwencyjny, płacenie za losowy dostęp to płacenie za możliwość, z której nie skorzystasz
Jednokierunkowe skanowanie SAX bez budowania drzewa
Bezpośredni czytnik otwiera pakiet ZIP i przechodzi przez każdą część arkusza parserem pull w stylu SAX. SAX oznacza tu, że parser raportuje zdarzenia parsowania w miarę ich napotykania - element startowy, ciąg tekstu, element końcowy - i idzie dalej. Nie trzyma za sobą drzewa węzłów. Czytnik śledzi bieżący wiersz i kolumnę z atrybutów r, zbiera typ komórki, indeks stylu, wartość i tekst formuły w miarę nadchodzenia zdarzeń, a gdy zobaczy zamykający tag </c>, emituje jedną komórkę i zapomina o niej. Następna komórka ponownie używa tej samej garstki zmiennych lokalnych
Ponieważ nic nie jest zatrzymywane między komórkami, ślad pamięci nie rośnie wraz z liczbą komórek. To właśnie właściwość, którą warto zachować. Arkusz z dwustoma wierszami i arkusz z dwudziestoma milionami wierszy kosztują czytnik tyle samo pamięci rezydentnej - różni je tylko czas trwania skanowania. Rezygnujesz z losowego dostępu - głównej cechy modelu - a w zamian dostajesz sufit zużycia pamięci, przez który liczba komórek nie może przebić
Co pozostaje w pamięci i dlaczego właśnie te dwie części
Skanowanie nie jest w pełni bezstanowe, a wyjątki są pouczające. Dwie małe tablice muszą pozostawać w pamięci przez cały czas trwania skanowania, bo sama komórka nie zawiera wystarczających informacji do interpretacji bez nich
Pierwsza to tabela udostępnionych ciągów. W SpreadsheetML komórka tekstowa nie przechowuje własnego tekstu. Nosi t="s" i numeryczny ładunek będący indeksem do xl/sharedStrings.xml - pojedynczej zdeduplikowanej listy wszystkich odrębnych ciągów w skoroszycie. To dobra optymalizacja przestrzeni dla plików, gdzie te same etykiety powtarzają się w tysiącach wierszy, ale oznacza, że czytnik musi załadować tę tabelę ciągów z góry i trzymać ją w pamięci, bo dowolna komórka w dowolnym arkuszu może odwoływać się do dowolnego wpisu w niej. Rozmiar tabeli zależy od liczby odrębnych ciągów, nie od liczby komórek - więc pozostaje skromna nawet przy ogromnych arkuszach
Druga to odwzorowanie formatów liczb z części stylów. Komórka numeryczna i komórka z datą są identyczne w transmisji: obie to zwykła liczba, bo data w SpreadsheetML to po prostu seryjny licznik dni. Jedyną rzeczą, która je odróżnia, jest styl komórki, który wskazuje przez cellXfs w xl/styles.xml na identyfikator formatu liczb. Aby raportować datę jako datę, a nie jako surowy numer seryjny, czytnik ładuje tę tabelę styl-do-formatu i trzyma ją w pamięci. Wszystko inne w pliku - faktyczne dane komórek stanowiące większość bajtów - przepływa bez zatrzymywania
Każda komórka raportuje rodzaj i wartość
Każda emitowana komórka przybywa jako rekord TXLSDirectCell. Zawiera indeks i nazwę arkusza, wiersz i kolumnę (numerowane od 1), semantyczny Kind, wartość Value jako Variant, tekst Formula bez wiodącego znaku równości i surowy StyleIndex. Rodzaj to jeden z: xdkNumber, xdkString, xdkBoolean, xdkDate lub xdkError - możesz więc rozgałęziać kod na podstawie znaczenia komórki, a nie ponownie wywodzić go z atrybutów. Komórka z formułą raportuje rodzaj swojego buforowanego wyniku, wraz z towarzyszącym tekstem formuły - więc obliczona suma przybywa jako liczba, która też informuje, jak została wyliczona
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;Odróżnianie daty od liczby
Kwestia dat zasługuje na dokładniejsze omówienie, bo właśnie tu większość naiwnych skanerów popełnia błąd. W komórce numerycznej nie ma typu daty. Komórka przechowująca wartość seryjną 46000 mogłaby być ilością, ceną lub 17 lutego 2025 roku - a plik mówi, które z tych znaczeń ma, wyłącznie przez identyfikator formatu liczb uzyskany przez styl komórki. ECMA-376 rezerwuje blok wbudowanych identyfikatorów formatów o ustalonym znaczeniu dla każdego zgodnego producenta, a identyfikatory niosące daty siedzą w dwóch zakresach: 14-22 dla standardowych formatów daty i czasu oraz 45-47 dla formatów czasu uplywłego jak [h]:mm:ss. Gdy DetectDates jest włączone - co jest domyślne - czytnik rozwiązuje styl każdej komórki numerycznej do jej identyfikatora formatu, a komórka, której identyfikator należy do tych zarezerwowanych zakresów, jest raportowana jako xdkDate z wartością Value już skonwertowaną do Delphi TDateTime. Sprawdzane są też formaty niestandardowe przez analizę kodu formatu pod kątem tokenów daty i czasu, ale zarezerwowane zakresy są niezawodną podstawą. Wyłącz DetectDates, a tabela stylów w ogóle nie jest ładowana, każda komórka numeryczna przychodzi jako xdkNumber i skanowanie jest odrobinę lżejsze
Pomijanie arkuszy i wczesne przerywanie
Sekwencyjne skanowanie ma cichą zaletę, której losowy dostęp nie może dopasować: możesz zatrzymać się. Zdarzenie OnSheet odpala się przed otwarciem każdego arkusza i daje dwa przełączniki. Ustaw SkipSheet, a cała ta część nigdy nie jest parsowana - to sposób na skanowanie tylko interesujących Cię arkuszy ze skoroszytu wieloarkuszowego bez płacenia za odczyt reszty. Ustaw Abort, a całe skanowanie kończy się natychmiast. Zdarzenie OnCell ma własne Abort - możesz więc zatrzymać się w chwili, gdy znajdziesz to, czego szukałeś: konkretny wiersz, wartość wartowniczą, koniec bloku nagłówkowego - bez odczytywania pozostałych milionów komórek. Przy skanowaniu jednokierunkowym przerwanie jest naprawdę bezpłatne, bo praca, którą pomijasz, jeszcze nie nastąpiła
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Skanuj tylko arkusz "Data"; pozostałe pozostaw nieodczytane
SkipSheet := SheetName <> 'Data';
end;Liczenie komórek bez procedury obsługi
Jedno niedawne udoskonalenie warto wyróżnić, bo zamienia częste pytanie w jedno tanie wywołanie. Czytnik liczy każdą wypełnioną komórkę, przez którą przechodzi, i robi to bez względu na to, czy procedura obsługi OnCell jest podłączona. Wcześniej bez ustawionej procedury obsługi liczba wypełnionych komórek wracała jako zero, bo zliczanie było efektem ubocznym emitowania. Teraz liczba jest niezależna od emisji. Możesz więc zadać jedno pytanie - ile wypełnionych komórek faktycznie zawiera ten skoroszyt - i dostać odpowiedź za cenę skanowania bez żadnych wywołań zwrotnych. Zarówno ReadFile, jak i ReadStream zwracają tę sumę jako Int64, a ta sama liczba jest potem dostępna przez właściwość CellCount. Zwrócenie -1 sygnalizuje, że pliku nie można było otworzyć lub nie jest pakietem OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// Bez procedury obsługi OnCell: czyste zliczanie wypełnionych komórek, nadal przy niemal stałej pamięci
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;Dla pełnego skanowania podłączasz procedurę obsługi i wywołujesz ReadFile dokładnie w ten sam sposób. Kontrast z pełnym ładowaniem jest w tym właśnie sedno: podczas gdy załadowanie quarterly_export.xlsx do skoroszytu rozwinęłoby każdą komórkę w rezydujący obiekt i trzymało wszystko naraz, bezpośredni czytnik trzyma w pamięci tylko wspólne ciągi i tabelę stylów, a dwanaście milionów komórek przepływa przez Twoje OnCell jedna po drugiej. Arytmetyka wykonana na każdej komórce nie pozostawia śladu, więc szczytowa pamięć jest wyznaczona przez liczbę odrębnych ciągów w skoroszycie - nie przez liczbę wierszy
Bezpośredni czytnik to właściwe narzędzie, gdy zadanie polega na jednorazowym odczytaniu dużego skoroszytu i wyodrębnieniu lub podsumowaniu jego zawartości. Gdy zamiast tego potrzebujesz losowego dostępu pełnego modelu, ale chcesz, aby dobrze zachowywał się przy dużych plikach, notatki w artykule o wydajności dużych skoroszytów w Delphi omówią tę drogę. A gdy kierunek jest odwrócony - produkujesz duże wyniki zamiast je konsumować - przegląd strumieniowego zapisu dla zadań serwerowych i wsadowych stosuje tę samą dyscyplinę stałej pamięci do zapisu. Wszystkie trzy wchodzą w skład komponentu HotXLS dla Delphi i C++Builder, wraz z API do odczytu, zapisu, formuł i formatowania omówionymi w pozostałych artykułach tego bloga