Pamięć komórek w blokach wierszy i strumieniowy zapis XLSX

W SpreadsheetML kolumna niosąca format walutowy potrzebuje dokładnie jednego elementu: <col min="2" max="2" style="3"/>. ECMA-376 Part 1 przypina styl komórki tylko w trzech miejscach: kolumnie (§18.3.1.13), wierszu (§18.3.1.73) i komórce (§18.3.1.4). Zwęż format do B2:D500000, a SpreadsheetML nie ma pojedynczego elementu dla tego kształtu: styl kolumny biegłby za wiersz 500000, a styl wiersza objąłby każdą kolumnę — zapisujący kończy więc z emitowaniem tego jako komórek tylko ze stylem, 1 499 997 takich

Model arkusza w pamięci trafia na tę samą ścianę o krok wcześniej. Jeśli formatowanie znaczy ustawienie właściwości na każdym obiekcie komórki, jedno kliknięcie w nagłówek kolumny alokuje milion obiektów, żeby ponieść jedną małą liczbę całkowitą, a proces Delphi, który chciał tylko format liczbowy, kończy przestrzeń adresową na skoroszycie wielkim na kilka kilobajtów na dysku. Prawdziwe pytanie projektowe brzmi: gdzie formatowanie mieszka w trakcie edycji i kiedy zamienia się w dane per komórka

Jak HotXLS formatuje zakres, nie tworząc komórek

HotXLS trzyma formatowanie zakresów jako interwały. Każdy setter stylu na TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — dopisuje jeden rekord do listy StyleOverlays arkusza: cztery współrzędne, części stylu, które wywołanie posiada, i indeksy pulowe dla tych części. Komórki, które już istnieją wewnątrz prostokąta, podchwytują nowe części od razu. Puste współrzędne zostają puste i nic dla nich nie jest alokowane

var
  Workbook: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Edge: TXLSXBorder;
  BorderIdx, Row: Integer;
  Stats: TXLSXCellStorageStatistics;
begin
  Workbook := TXLSXWorkbook.Create;
  try
    Sheet := Workbook.Sheets.Add('Ledger');
    Sheet.WriteStringRowValues(1, 2, ['Net', 'Tax', 'Gross']);
    for Row := 2 to 1001 do
      Sheet.WriteFloatRowValues(Row, 2, [Row * 1.5, Row * 0.3, Row * 1.8]);

    // Kolumny B..D na wszystkie 1 048 576 wierszy: jeden interwał na wywołanie
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Zbuduj krawędź raz, potem nakładaj przez indeks
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // numeracja od 1
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Oczekiwane 3 003: trzy komórki nagłówka plus 3 000 wartości
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

Te dwa wywołania formatujące objęły 3 145 728 współrzędnych i nie dołożyły do tamtego licznika niczego. Interwały startują od wiersza 1 celowo: interwał pełnej kolumny zapisuje się jako jeden element <col>, a nagłówki tekstowe w wierszu 1 i tak nie cierpią na formacie liczbowym. Zakresy zwracane przez RCRange, Range['A1:C5'], EntireColumn i EntireRow należą do arkusza, więc przykład nigdy ich nie zwalnia

TXLSXFormatParts to to, co trzyma kilka interwałów w ryzach. Jego składniki to xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection i xfpCellControl, a każdy setter TXLSXRange roszczy dokładnie jeden z nich. Gdy komórka jest rozwiązywana, interwały ją pokrywające są nakładane w kolejności dodania, każdy pisząc tylko we własne części, a jawne formatowanie na samej komórce wygrywa ze wszystkimi. Interwał kolumnowy ustawiający format liczbowy i interwał wierszowy ustawiający wypełnienie docierają do komórki B5 oba w całości

Gdy jeden rekord ma nieść kilka części, Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) biera TXLSXCellStyleState z wypełnionymi indeksami pulowymi. Add obcina współrzędne do siatki 1 048 576 × 16 384, zamienia odwrócone narożniki i zwraca nil bez zapisywania czegokolwiek, gdy Parts jest pusty. Edycje strukturalne trzymają rekordy w kroku: InsertRows wewnątrz sformatowanego bloku poszerza interwał o wstawioną liczbę, a wstawienie nad nim przesuwa cały rekord w dół

Dlaczego SetBorders tworzy komórki, skoro SetBorderIndex nie?

TXLSXRange.SetBorders(Kind, Style, Color) musi obejrzeć każdą komórkę osobno, bo odpowiedź zależy od pozycji. Przy xlsxEdgeOutline górny wiersz dostaje krawędź górną, lewa kolumna lewą, a wnętrze nic; przy xlsxEdgeInside jest w przybliżeniu odwrotnie. Wywołanie przy okazji scala nowe krawędzie z tą krawędzią, którą komórka już ma, więc Outline po Inside buduje pełną siatkę. Ta kompozycja per komórka idzie przez Cells[R, C], które tworzy komórkę, gdy żadnej nie ma. Na bloku raportu 10 × 40 to jest dokładnie to, co trzeba. Na EntireColumn(3) z xlsxEdgeAll to 1 048 576 nowych obiektów komórek

Podział jest więc prosty. Dla czegokolwiek wyższego niż ekran danych zbuduj krawędź raz i nakładaj przez indeks, jak robi pierwszy przykład: TXLSXBorder.SetAll, potem Workbook.Borders.LookupOrAdd dla indeksu liczonego od 1, potem SetBorderIndex. Zostaw SetBorders blokom, których obwódka i linie wewnętrzne się różnią — jak blok raportu 10 × 40 wyżej

Odczytywanie formatów bez cofania roboty

Ta sama pułapka łapie kod, który tylko czyta. Cells[R, C] to akcesor tworzący przy pudle: zapytaj go o format liczbowy B500000, a dostaniesz poprawną odpowiedź plus nową, sformatowaną, pustą komórkę na B500000, która od teraz będzie przechowywana i zapisana. Zapętlij to po kolumnie dla audytu formatowania, a przebudujesz milion obiektów, przed którymi interwał miał chronić — po jednym niewinnym w wyglądzie odczycie na raz

TXLSXWorksheet.GetCellVisibleFormat rozwiązuje efektywny styl współrzędnej bez dodawania komórki. Scala domyślny styl arkusza, interwały wierszowe i kolumnowe, interwały prostokątne, style tabel i ewentualne jawne formatowanie na komórce. Przeciążenie z parametrem out ABuiltinNumberFormat raportuje wbudowany format Excela, jak 14 (krótka data), osobno od NumberFormatIndex, który celuje w pulę własnych NumberFormats skoroszytu. Cells.HasCell(R, C) odpowiada, czy fizyczna komórka w ogóle istnieje

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets liczone od 1
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Styl efektywny, rozwiązany bez dodawania komórki do arkusza
    State := Sheet.GetCellVisibleFormat(Row, 2, Builtin);
    if (State.NumberFormatIndex = 0) and (Builtin = 0) then
      Inc(Unformatted);
    if Sheet.Cells.HasCell(Row, 2) then
      Inc(Physical);
    // Sheet.Cells[Row, 2].NumberFormatIndex odpowiada na to samo pytanie,
    // ale zostawia po jednym nowym obiekcie komórki za każdy odwiedzony wiersz
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Oba liczniki powinny być identyczne przed pętlą i po niej
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Uruchom taką kontrolę raz na swoim prawdziwym obciążeniu. StyledBlankCellCount liczy komórki, które mają styl i nie mają wartości, formuły ani rich text. Jeśli rośnie w przebiegu, który miał być tylko do odczytu, jakiś pomocnik tworzy komórki czysto po to, by trzymać styl — zwykle wołając Cells[R, C], żeby zdecydować, czy komórka wymaga formatowania

Co bloki po 256 wierszy znaczą dla twojego kodu

Fizyczne komórki mieszkają w blokach po 256 wierszy. Wewnątrz bloku każdy zaludniony wiersz trzyma swoje komórki na liście posortowanej po kolumnie; wiersz bez komórek to tylko puste gniazdo, a odcinek 256 wierszy bez żadnej komórki nie ma bloku wcale. Rozrzucony arkusz z danymi w wierszach 3, 7 i 512 trzyma więc dwa bloki. Zapis obchodzi tę samą strukturę w kolejności wiersz-główna — tej samej, której SpreadsheetML wymaga dla elementów <row> i ich dzieci <c>

Referencja TXLSXCell, którą trzymasz, pozostaje tym samym obiektem, gdy inne komórki są dodawane i usuwane dookoła niej; bloki reorganizują własną księgowość, nie twoje uchwyty. Masowi zapisujący, jak WriteFloatRowValues i WriteStringRowValues, biorą szybszą drogę na świeżym arkuszu i dopisują wartości wiersz-główna do upakowanego magazynu. Pierwszy per-komórkowy dostęp przez Cells[R, C] konwertuje ten magazyn na obiekty komórek raz. GetStorageStatistics pokazuje, w którym stanie jesteś, przez PackedStorageActive i PackedCellCount, obok RowBlockCount i PopulatedRowCount

Pule stylów są przycinane przy zapisie. Zanim styles.xml zostanie zapisany, HotXLS przebudowuje pule fontów, wypełnień, krawędzi, formatów liczbowych, wyrównań i ochrony tak, by trzymały tylko wpisy jeszcze używane — i skoroszyt, który użytkownik przestylizował przez godzinę, nie wysyła w świat każdego porzuconego wypełnienia. Komórki przypięte do bezwzględnego FormatIndex zachowują swoje gniazdo nietknięte

Zapisy strumieniowane per wiersz i liczby, które to pokazują

TXLSXWorkbook.StreamingWrite ma domyślnie True. Przy włączonym SaveAs generuje każdy <row> arkusza i pisze go prosto do strumienia deflate jego wpisu ZIP, więc największym kawałkiem XML arkusza w pamięci w dowolnej chwili jest pojedynczy wiersz. Przy wyłączonym XML każdego arkusza jest najpierw budowany jako jeden kompletny łańcuch, po czym kompresowany, a pamięć szczytowa podąża za największym arkuszem. Komórki tylko ze stylem, w które zamieniają się interwały prostokątne — przypadek B2:D500000 z otwarcia — są generowane wewnątrz tej pętli per wiersz też, więc doliczają się do pliku, ale nigdy nie siedzą w pamięci jako jeden blok XML

Współdzielone łańcuchy i rekordy cache tabel przestawnych idą tym samym wzorem. Każdy wpis <si> albo <r> jest generowany i zapisywany osobno, więc na szerokim skoroszycie analitycznym tabela współdzielonych łańcuchów — często większa niż jakikolwiek pojedynczy arkusz — nigdy nie musi istnieć jako jeden blok XML

Niczego z tego nie trzeba brać na wiarę. Po każdym zapisie skoroszyt wystawia cztery właściwości tylko do odczytu: LastSaveUsedStreaming, LastSavePeakRowXmlChars (najdłuższy pojedynczy wiersz, w znakach), LastSaveBufferedWorksheetXmlChars (całkowity XML arkuszy trzymany w pamięci, zero przy strumieniowaniu) i LastSavePeakAuxiliaryXmlChars (najdłuższy wpis współdzielonych łańcuchów albo tabeli przestawnej). LastSaveUsedStreaming jest True dla wyjścia .xlsx, .xltx i .xltm oraz dla binarnego zapisującego .xlsb; wyjście OpenDocument idzie własnym zapisującym i raportuje False

procedure ReportSave(Workbook: TXLSXWorkbook; const FileName: string;
  Streaming: Boolean);
begin
  Workbook.StreamingWrite := Streaming;
  if Workbook.SaveAs(FileName) <> 1 then
    raise Exception.CreateFmt('Save failed: %s', [FileName]);
  WriteLn(Format('streaming=%s peakRow=%d buffered=%d peakAux=%d',
    [BoolToStr(Workbook.LastSaveUsedStreaming, True),
     Workbook.LastSavePeakRowXmlChars,
     Workbook.LastSaveBufferedWorksheetXmlChars,
     Workbook.LastSavePeakAuxiliaryXmlChars]));
end;

// Ten sam skoroszyt, oba tryby; obserwuj pamięć procesu w profilerze
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

Na własnych danych linia buforowana pokaże liczbę buffered rzędu XML całego arkusza, a linia strumieniowana zero i peakRow nie większe niż twój najszerszy wiersz. Zostaw StreamingWrite włączone. Flaga rozstrzyga tylko, czy XML arkusza jest składany w całości przed kompresją, a LastSaveBufferedWorksheetXmlChars raportuje rozmiar tego składania w znakach

Gdzie idzie reszta pamięci

Gdy komórki i style są już ciasne, największymi konsumentami na dużym skoroszycie są tabela współdzielonych łańcuchów i satelickie części, które plik wozi: cache tabel przestawnych, rysunki i zachowane XML z części, których model obiektowy nie modeluje. Te mają własne dźwignie i żadne pojedyncze ustawienie nie pokrywa ich wszystkich

Jeśli wąskim gardłem jest otwieranie, a nie zapisywanie, zacznij od ładowania selektywnego: przejście przez artykuł ładowanie arkuszy tylko z metadanymi i selektywne pokrywa czytanie skoroszytu bez płacenia za arkusze, których nie dotkniesz. Dla przepustowości ścieżki odczytu na bardzo dużych plikach zobacz notki o artykule równoległe parsowanie XLSX i alokator pamięci, a dla zadań tylko-wyjściowych, które nie potrzebują modelu obiektowego, zapisy strumieniowane dla zadań wsadowych serwera pasują lepiej niż jakiekolwiek dostrajanie tutaj

HotXLS czyta i pisze XLS i XLSX z natywnego kodu Delphi i C++Builder bez Excela i automatyzacji OLE, więc każda liczba powyżej pochodzi z twojego własnego procesu i da się tam sprawdzić. Wspierane formaty i wersje RAD Studio są wypisane na stronie komponentu arkuszowego HotXLS Delphi