Блочное хранение ячеек по строкам и потоковое сохранение

В SpreadsheetML столбцу с валютным форматом нужен ровно один элемент: <col min="2" max="2" style="3"/>. ECMA-376 Part 1 прикрепляет стиль ячейки лишь в трёх местах: столбец (§18.3.1.13), строка (§18.3.1.73) и ячейка (§18.3.1.4). Сузьте формат до B2:D500000 — и единого элемента для такой формы у SpreadsheetML нет: стиль столбца заехал бы за строку 500000, а стиль строки накрыл бы все столбцы, так что писатель вынужден выдавать это ячейками-только-стилями — все 1 499 997

Ту же стену объектная модель таблиц в памяти встречает на шаг раньше. Если форматирование означает установку свойства на каждом объекте ячейки, один клик по заголовку столбца выделяет миллион объектов ради одного маленького целого числа, и процесс Delphi, которому был нужен всего лишь числовой формат, кончает адресное пространство на книге, занимающей на диске несколько килобайт. Настоящий вопрос дизайна: где живёт форматирование, пока вы правите, и когда оно превращается в данные на ячейку

Как HotXLS форматирует диапазон, не создавая ячеек

HotXLS хранит форматирование диапазонов интервалами. Каждый сеттер стиля на TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — добавляет одну запись в список StyleOverlays листа: четыре координаты, части стиля, которыми владеет вызов, и пуловые индексы этих частей. Ячейки, уже существующие внутри прямоугольника, подхватывают новые части сразу. Пустые координаты остаются пустыми, и для них не выделяется ничего

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]);

    // Столбцы B..D на всех 1 048 576 строк: один интервал за вызов
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Соберите границу один раз, затем примените по индексу
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // индексация с единицы
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Ожидайте 3 003: три ячейки заголовка плюс 3 000 значений
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

Два вызова форматирования накрыли 3 145 728 координат и не добавили к этому счётчику ничего. Интервалы начинаются со строки 1 нарочно: полностолбцовый интервал сохраняется одним элементом <col>, а текстовые заголовки в строке 1 числовой формат и так не трогает. Диапазоны, возвращённые RCRange, Range['A1:C5'], EntireColumn и EntireRow, принадлежат листу, поэтому пример их никогда не освобождает

TXLSXFormatParts — то, что удерживает несколько интервалов от драки. Его члены — xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection и xfpCellControl, и каждый сеттер TXLSXRange заявляет ровно один из них. Когда ячейка разрешается, покрывающие её интервалы применяются в порядке добавления, каждый пишет только свои части, а явное форматирование самой ячейки побеждает их всех. Столбцовый интервал с числовым форматом и строчный интервал с заливкой оба добираются до ячейки B5 целыми

Когда одна запись должна нести несколько частей, Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) принимает TXLSXCellStyleState с заполненными пуловыми индексами. Add прижимает координаты к сетке 1 048 576 × 16 384, меняет местами перепутанные углы и возвращает nil, ничего не записывая, когда Parts пуст. Структурные правки держат записи в тонусе: InsertRows внутри форматированного блока расширяет интервал на вставленное число, а вставка выше него сдвигает всю запись вниз

Почему SetBorders создаёт ячейки, а SetBorderIndex — нет?

TXLSXRange.SetBorders(Kind, Style, Color) обязан смотреть на каждую ячейку отдельно, потому что ответ зависит от позиции. С xlsxEdgeOutline верхняя строка получает верхнюю грань, левый столбец — левую, а середина — ничего; с xlsxEdgeInside всё примерно наоборот. Вызов к тому же вливает новые грани в ту границу, что у ячейки уже есть, поэтому Outline с последующим Inside строит полную сетку. Эта почекеечная сборка идёт через Cells[R, C], создающий ячейку, если её нет. На блоке отчёта 10 × 40 это ровно то, что нужно. А на EntireColumn(3) с xlsxEdgeAll это 1 048 576 новых объектов ячеек

Разделение потому простое. Для всего, что выше экрана данных, соберите границу один раз и примените по индексу, как делает первый пример: TXLSXBorder.SetAll, затем Workbook.Borders.LookupOrAdd ради индекса с единицы, затем SetBorderIndex. SetBorders придержите для блоков, у которых контур и внутренние линии различаются — как у блока отчёта 10 × 40 выше

Чтение форматов обратно без отмены сделанной работы

Та же ловушка накрывает код, который только читает. Cells[R, C] — аксессор «создай при промахе»: спросите у него числовой формат B500000, и вы получите правильный ответ плюс новую стилизованную пустую ячейку на B500000, которую теперь будут хранить и сохранять. Прогоните это циклом по столбцу ради аудита форматирования — и вы пересобрали тот миллион объектов, от которого интервал должен был уберечь, по одному невинному с виду чтению за раз

TXLSXWorksheet.GetCellVisibleFormat разрешает эффективный стиль координаты, не добавляя ячейку. Он смешивает значение листа по умолчанию, строчные и столбцовые интервалы, прямоугольные интервалы, стили таблиц и любое явное форматирование на ячейке. Перегрузка с параметром out ABuiltinNumberFormat сообщает встроенный формат Excel вроде 14 (короткая дата) отдельно от NumberFormatIndex, указывающего в пользовательский пул NumberFormats книги. Cells.HasCell(R, C) отвечает, существует ли физическая ячейка вообще

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets нумеруется с единицы
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Эффективный стиль, разрешённый без добавления ячейки на лист
    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 отвечает на тот же вопрос,
    // но оставляет по одному новому объекту ячейки на каждую посещённую строку
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Оба счётчика должны совпадать до и после цикла
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Прогоните такую проверку однажды на вашей реальной нагрузке. StyledBlankCellCount считает ячейки, у которых есть стиль и нет ни значения, ни формулы, ни rich text. Если он растёт во время прохода, который должен был быть read-only, какой-то помощник создаёт ячейки чисто ради стиля — обычно вызывая Cells[R, C], чтобы решить, нужно ли ячейке форматирование

Что блоки в 256 строк значат для вашего кода

Физические ячейки живут блоками по 256 строк. Внутри блока каждая населённая строка держит свои ячейки в списке, отсортированном по столбцу; строка без ячеек — просто пустой слот, а пролёт в 256 строк без ячеек не имеет блока вовсе. Разреженный лист с данными в строках 3, 7 и 512 держит поэтому два блока. Сохранение обходит ту же структуру в построчном порядке — том, который SpreadsheetML требует для элементов <row> и их детей <c>

Ссылка TXLSXCell, которую вы держите, остаётся тем же объектом, пока вокруг добавляются и удаляются другие ячейки; блоки реорганизуют собственную бухгалтерию, а не ваши дескрипторы. Групповые писатели вроде WriteFloatRowValues и WriteStringRowValues на свежем листе идут быстрым путём и дописывают построчные значения в упакованное хранилище. Первый почекеечный доступ через Cells[R, C] один раз конвертирует это хранилище в объекты ячеек. GetStorageStatistics показывает, в каком вы состоянии, через PackedStorageActive и PackedCellCount рядом с RowBlockCount и PopulatedRowCount

Стилевые пулы подрезаются при сохранении. Прежде чем будет записан styles.xml, HotXLS пересобирает пулы шрифтов, заливок, границ, числовых форматов, выравниваний и защиты так, чтобы в них остались только используемые записи, и книга, которую пользователь час переоформлял, не тащит в файл каждую брошенную заливку. Ячейки, приколотые к абсолютному FormatIndex, сохраняют свой слот нетронутым

Построчно-стриминговое сохранение и числа, которые это показывают

TXLSXWorkbook.StreamingWrite по умолчанию True. С ним SaveAs генерирует каждую строку листа <row> и пишет её прямо в deflate-поток соответствующей записи ZIP, так что крупнейший кусок XML листа в памяти в любой момент — одна строка. Без него XML каждого листа сперва собирается одной целой строкой и лишь затем сжимается, и пиковая память следует за крупнейшим листом. Ячейки-только-стили, в которые превращаются прямоугольные интервалы — случай B2:D500000 из начала статьи, — генерируются внутри того же построчного цикла, так что они добавляют файлу веса, но никогда не сидят в памяти одним куском XML

Общие строки и записи кеша сводных таблиц следуют тому же образцу. Каждая запись <si> или <r> генерируется и пишется сама по себе, поэтому на широкой аналитической книге таблица общих строк — частенько больше любого отдельного листа — не обязана существовать одним куском XML

Ничто из этого не нужно принимать на веру. После каждого сохранения книга выставляет четыре свойства только для чтения: LastSaveUsedStreaming, LastSavePeakRowXmlChars (самая длинная одиночная строка, в символах), LastSaveBufferedWorksheetXmlChars (весь XML листа, удержанный в памяти; при стриминге ноль) и LastSavePeakAuxiliaryXmlChars (самая длинная запись общей строки или сводной таблицы). LastSaveUsedStreaming равен True для вывода .xlsx, .xltx и .xltm и для бинарного писателя .xlsb; вывод OpenDocument идёт через собственного писателя и сообщает 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;

// Та же книга, оба режима; следите за памятью процесса в профилировщике
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

На ваших собственных данных буферизованная строка покажет buffered порядка XML всего листа, а стриминговая — ноль и peakRow не шире вашей самой широкой строки. Оставьте StreamingWrite включённым. Флаг решает лишь, собирается ли XML листа целиком до сжатия, а LastSaveBufferedWorksheetXmlChars сообщает размер этой сборки в символах

Куда уходит оставшаяся память

Когда ячейки и стили ужаты, следующие крупнейшие потребители большой книги — таблица общих строк и какие ни на есть спутниковые части, что несёт файл: кеши сводных таблиц, рисунки и сохранённый XML из частей, которые объектная модель не моделирует. У тех свои рычаги, и ни одна настройка не накрывает их все

Если узкое место — открытие, а не сохранение, начинайте с выборочной загрузки: разбор статьи о загрузке только по метаданным и с выбором листов описывает чтение книги без платы за листы, которых вы не коснётесь. Для пропускной способности чтения на очень больших файлах см. заметки о параллельном разборе XLSX и распределителе памяти, а для заданий, работающих только на вывод и не нуждающихся в объектной модели вовсе, потоковая запись для серверных пакетных заданий подойдёт лучше любых настроек здесь

HotXLS читает и пишет XLS и XLSX из нативного кода Delphi и C++Builder без Excel и OLE-автоматизации, поэтому каждое число выше приходит из вашего собственного процесса и может быть проверено там. Поддерживаемые форматы и версии RAD Studio перечислены на странице компонента электронных таблиц HotXLS для Delphi