Zapis strumieniowy HotXLS w zadaniach wsadowych Delphi

Powiedzmy, że nocna usługa w Delphi generuje jeden XLSX na klienta, kilkaset plików, część z nich szerokich na 400000 wierszy. Sprofiluj to, a niespodzianką rzadko jest pętla wypełniająca komórki. To wywołanie SaveAs. Przy domyślnym zapisującym każdy arkusz jest serializowany do jednego ciągu XML w pamięci, zanim ten ciąg zostanie skompresowany do zipa OOXML, a dla szerokiego arkusza ten tymczasowy ciąg potrafi przyćmić model komórek, z którego powstał. Więc zadanie, które komfortowo buduje swoje dane i siedzi na 800 MB, przy zapisie skoczy powyżej limitu 2 GB kontenera, a zabójca OOM składa raport o błędzie o 3:00, gdy nikt nie patrzy. HotXLS, natywna biblioteka arkuszowa losLab dla Delphi i C++Buildera, ma właściwość wycelowaną wprost w ten skok: StreamingWrite. Wokół niej siedzą dwie dalsze dźwignie decydujące o tym, czy proces wsadowy zostanie w swoim budżecie pamięci i czasu, mianowicie wywołania zwrotne zapisu na poziomie wiersza i to, jak pula stylów zachowuje się wewnątrz ciasnej pętli

Co buforuje domyślna ścieżka zapisu i co zmienia StreamingWrite

Domyślny zapisujący XLSX stawia na prostotę. Renderuje XML arkusza w całości, a potem podaje gotowy ciąg kompresorowi zip. To właściwy kompromis dla przytłaczającej większości skoroszytów, gdzie XML całego arkusza mieści się w kilku megabajtach. Przestaje być właściwy, gdy zserializowana postać jednego arkusza sięga setek megabajtów. XML arkuszowy jest gadatliwy: każda komórka liczbowa kosztuje dziesiątki znaków znaczników, a ciąg trzymający to wszystko musi być ciągły. Na wykresie pamięci sygnatura jest nie do przeoczenia. Długie płaskie plateau, gdy wiersze się wypełniają, potem ostry trójkątny skok podczas SaveAs, potem zapaść, gdy zip zostanie wypchnięty

Ustawienie Book.StreamingWrite := True przełącza SaveAs na zapisujący arkusze, który emituje XML arkusza wprost do strumienia zip w miarę jego generowania. Pośredni ciąg nigdy nie zostaje zaalokowany, a trójkątny skok spłaszcza się do poziomu szumu

Bądź precyzyjny co do tego, co to naprawdę daje, bo przesadzanie z obietnicami prowadzi do błędnych planów pojemności. Flaga zmienia tylko ścieżkę zapisu. Budowanie skoroszytu wciąż alokuje pełny model komórek w pamięci, więc plateau w fazie wypełniania jest dokładnie tak samo wysokie jak wcześniej. Znika skok serializacji, który dawniej piętrzył się na tym plateau w chwili zapisu, a dla zadania wypełniającego 400 tysięcy wierszy ten skok rutynowo stanowi całą różnicę między zmieszczeniem się w budżecie pamięci a jego wysadzeniem. Właściwość ma domyślnie False, by zachować historyczne zachowanie, więc wejście w nią to jedna jawna linia, którą piszesz świadomie

Eksport masowy z włączoną flagą

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // indeks puli, od zera
  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;
    if (R mod 1000) = 0 then
      Sheet.Cells[R, 2].FontIndex := BoldIdx + 1;        // od jedynki po stronie komórki
  end;
  Book.StreamingWrite := True;   // strumieniuj XML arkusza prosto do zipa
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Cells[R, C] tworzy komórki na żądanie, co utrzymuje ciało pętli czystym. Dwa limity siatki warto zapamiętać: 1048576 wierszy i 16384 kolumny, wystawione jako XlsxMaxRow i XlsxMaxCol. Strumień danych przekraczający limit wierszy musi zostać podzielony na arkusze przez twój własny kod. Nic niżej w łańcuchu nie zauważa przekroczenia ani nie naprawia go za ciebie, a plik po prostu kończy się ucięty na limicie

Wypełnianie wierszy bez narzutu Variantów na każdej komórce

Każde przypisanie Cells[R, C].Value płaci za wyszukanie komórki i konwersję Variantu. Przy dziesięciu tysiącach wierszy nikt tego nie zauważa. Przy milionie wierszy po dwadzieścia kolumn każdy ten narzut na wywołanie staje się dominującym kosztem fazy wypełniania, a profiler wskaże wprost na niego. Interfejsy wsadowe pozwalają podać zapisującemu cały wiersz naraz. WriteRows napędza wywołanie zwrotne dostarczające jeden wiersz na wywołanie:

procedure TBulkExporter.FillRow(Sender: TObject; SheetIndex, Row, FirstCol,
  LastCol: Integer; var Values: Variant; var Skip: Boolean;
  var Cancel: Boolean);
begin
  if not FReader.Next then
  begin
    Cancel := True;              // źródło danych wyczerpane: zatrzymaj czysto
    Exit;
  end;
  Values := VarArrayCreate([FirstCol, LastCol], varVariant);
  Values[FirstCol]     := FReader.RecordId;
  Values[FirstCol + 1] := FReader.CustomerName;
  Values[FirstCol + 2] := FReader.Amount;
end;

// wypełnij wiersze 2..100001, kolumny A..C, pobierając z czytnika
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

Flaga Cancel jest tym, co zamienia stały zakres wierszy w "do N wierszy", czyli w naturalny kształt, gdy liczba wierszy pochodzi z zapytania, którego nie skończyłeś wykonywać. Skip to lżejsze dotknięcie: zostawia pojedynczy wiersz pustym, nie zatrzymując przebiegu. Poza wypełnianiem komórek wywołanie zwrotne okazuje się dobrym domem dla spraw operacyjnych, które inaczej doczepia się do pętli wypełniającej w niezręczny sposób. Licznik postępu tykający co tysiąc wierszy, żeton anulowania odpytywany z harmonogramu zadań, ogranicznik tempa odczytów ze źródłowej bazy danych: wszystko to mieszka w jednym miejscu, zamiast być przewlekane przez kod zapisujący komórki. Po stronie odczytu ForEachRow i ForEachCell odzwierciedlają ten sam wzorzec, co ma znaczenie, gdy zadanie wsadowe zarówno konsumuje, jak i produkuje duże pliki

Pule stylów nagradzają wynoszenie poza pętlę

Model stylowania XLSX to zestaw współdzielonych pul. Fonts.Add, Fills.AddSolid i Borders.Add zwracają indeks puli liczony od zera, a komórka odwołuje się do czcionki, przechowując ten indeks plus jeden w FontIndex, gdzie zero jest zarezerwowane dla domyślnej wartości skoroszytu. To +1 stoi wprost w powyższym przykładzie masowym. Zapomnij o nim, a komórka po cichu bierze niewłaściwy styl, bo błąd o jeden w indeksie puli stylów nadal jest poprawnym indeksem i nic się nie podnosi

Wynikającą z tego dyscypliną jest tworzenie każdego obiektu stylu przed pętlą wierszy i odwoływanie się do jego indeksu wewnątrz pętli. Fonts.Add deduplikuje identyczne definicje, więc wołanie go raz na wiersz marnuje tylko procesor. Alignments.Add jest pułapką, bo zwraca świeży wpis przy każdym wywołaniu. Wewnątrz pętli o 100 tysiącach wierszy to zasypuje styles.xml setką tysięcy duplikatów rekordów wyrównania, co rozdyma plik na dysku i spowalnia każde późniejsze otwarcie w Excelu, gdy duplikaty są parsowane od nowa. Zbuduj każdy styl raz poza pętlą, a potem odwołuj się do jego indeksu tyle razy, ile trzeba

Strumienie, katalogi tymczasowe i pętla wsadowa wokół tego wszystkiego

Nic z tego nie wymaga systemu plików. Obie fasady niosą przeciążenia z TStream w całej swojej powierzchni wejścia-wyjścia, między innymi Open, SaveAs, SaveAsCSV, SaveAsHTML i SaveAsODS, więc proces wsadowy może renderować prosto do TMemoryStream przeznaczonego do magazynu obiektów albo do odpowiedzi HTTP, nigdy nie dotykając dysku. Jest tu jedna ostra krawędź do zapamiętania. SaveAs(Stream) zapisuje od bieżącej pozycji strumienia i potem go nie przewija, więc ustaw Position := 0 sam, zanim podasz strumień temu, co go dostarcza, albo konsument odczyta zero bajtów. Fasada XLS dokłada dwa własne pokrętła. SetTempDir kieruje pliki tymczasowe zapisującego BIFF na wolumen, który ma miejsce i zapas wydajności wejścia-wyjścia, by je wchłonąć, co ma znaczenie na serwerach, gdzie domyślna ścieżka tymczasowa siedzi na ciasnym dysku systemowym. UseSharedFormulas składa powtarzające się ciała formuł w grupy współdzielone, co realnie zmniejsza rozmiar w klasycznym kształcie raportu, gdzie jedna formuła jest kopiowana przez całą kolumnę

Sama pętla wsadowa celowo pozostaje nudna:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // świeża instancja: żadnych wycieków stanu
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // jedno złe wejście nie może zabić partii
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

Świeża instancja skoroszytu na plik kosztuje mikrosekundy i usuwa całą kategorię błędów skażenia między plikami: style, nazwy zdefiniowane i właściwości dokumentu z pliku 17 nie mają jak przeciec do pliku 18. Pominięcie i kontynuowanie po nieudanym Open zarabia na siebie w tym samym stopniu, bo jedna obcięta wysyłka w sześćsetplikowej partii powinna kosztować cię jedną linię logu, a nie resztę przebiegu. Warto zaznaczyć też to, czego odcinek CSV celowo nie robi. SaveAsCSV wypisuje formuły jako dosłowny tekst i nigdy ich nie wylicza, więc partia konwersji, której konsumenci oczekują policzonych liczb, musi najpierw uruchomić Calculate na odpowiednich komórkach albo wystartować ze skoroszytów, które już niosą buforowane wyniki z wcześniejszego obliczenia

Model współbieżności: jeden skoroszyt na wątek

Obiekty żadnej z fasad nie są bezpieczne wątkowo i projekt nigdy nie udawał inaczej. Ponieważ między instancjami nie ma współdzielonego stanu globalnego, reguła skalowania brzmi po prostu: jeden skoroszyt na wątek roboczy, bez dzielenia skoroszytu między wątkami. Pula N procesów roboczych, z których każdy ma własny TXLSXWorkbook, skaluje się blisko liniowo, dopóki sufitem nie stanie się pamięć, a ten sufit można ująć liczbą: największy równoczesny model komórek pomnożony przez liczbę procesów roboczych plus ten narzut z chwili zapisu, który spłaszczył StreamingWrite. Kiedy kolejka robi się głęboka, nakładaj przeciwciśnienie na kolejce zadań, a nie wewnątrz zapisującego. Zagłodzony wątek, który zapisał skoroszyt do połowy, nie wyprodukował nic użytecznego, podczas gdy zadanie, które poczekało kilka sekund na wolny proces roboczy, kończy się w całości

Szerszy obraz strojenia, wraz z formułami współdzielonymi, pomijaniem grafiki po stronie odczytu i dźwigniami specyficznymi dla XLS, znajdziesz w przewodniku po wydajności dużych skoroszytów. Zadania wsadowe, których wiersze pochodzą wprost z zapytania, omawiają osobno wzorce eksportu z bazy danych do raportów w Delphi

HotXLS kompiluje się do twojej usługi w Delphi albo C++Builderze jako natywny Object Pascal bez zewnętrznych zależności; edycje i licencjonowanie są na stronie produktu HotXLS Delphi Component