Потоковая запись HotXLS для пакетных задач Delphi

Допустим, ночной сервис на Delphi формирует по одному XLSX на клиента — несколько сотен файлов, часть из них шириной в 400 000 строк. Профилируете его — и неожиданностью оказывается редко цикл заполнения ячеек. Ею оказывается вызов SaveAs. При писателе по умолчанию каждый лист сериализуется в единую XML-строку в памяти, и только потом эта строка сжимается в zip-пакет OOXML, а для широкого листа временная строка способна затмить ту модель ячеек, из которой она построена. Так что задание, спокойно собирающее свои данные и сидящее на 800 МБ, во время сохранения выстреливает за лимит контейнера в 2 ГБ, и OOM killer подаёт отчёт об ошибке в 03:00, когда никто не смотрит. У HotXLS, нативной библиотеки электронных таблиц losLab для Delphi и C++Builder, есть свойство, нацеленное ровно в этот всплеск: StreamingWrite. Рядом с ним стоят ещё два рычага, решающих, останется ли пакетный обработчик в своём бюджете памяти и времени, — построчные колбэки записи и поведение пула стилей внутри плотного цикла

Что буферизует путь сохранения по умолчанию и что меняет StreamingWrite

Писатель XLSX по умолчанию выбирает простоту. Он полностью формирует XML листа, а затем передаёт готовую строку компрессору zip. Для подавляющего большинства книг, где XML всего листа умещается в несколько мегабайт, это верный размен. Он перестаёт быть верным, когда сериализованная форма одного листа доходит до сотен мегабайт. Табличный XML многословен: каждая числовая ячейка стоит десятков символов разметки, а строка, вмещающая всё это, обязана быть непрерывной. На графике памяти подпись этого не пропустить. Долгое ровное плато, пока заполняются строки, затем резкий треугольный всплеск во время SaveAs, затем обвал, как только zip сброшен

Присвоение Book.StreamingWrite := True переключает SaveAs на писатель листов, который выдаёт XML листа прямо в zip-поток по мере его формирования. Промежуточная строка вообще не выделяется, а треугольный всплеск утопает в шуме

Будьте точны в том, что это реально даёт, потому что преувеличение ведёт к неверным планам мощностей. Флаг меняет только путь сохранения. Построение книги по-прежнему выделяет полную модель ячеек в памяти, поэтому плато на фазе заполнения ровно такое же высокое, как раньше. Исчезает именно всплеск сериализации, который раньше надстраивался над этим плато в момент сохранения, а для задания, заполняющего 400 тысяч строк, этот всплеск сплошь и рядом и есть вся разница между попаданием в бюджет памяти и его срывом. По умолчанию свойство равно False, чтобы сохранить историческое поведение, поэтому включение — одна явная строка, которую вы пишете намеренно

Массовая выгрузка с включённым флагом

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // индекс в пуле, с нуля
  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;        // у ячейки с единицы
  end;
  Book.StreamingWrite := True;   // XML листа потоком прямо в zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Cells[R, C] создаёт ячейки по требованию, что оставляет тело цикла чистым. Два предела сетки стоит заучить наизусть: 1 048 576 строк и 16 384 колонки, доступные как XlsxMaxRow и XlsxMaxCol. Поток данных, перебирающий предел строк, придётся разбивать по листам в вашем собственном коде. Ниже по течению никто перебора не заметит и за вас его не исправит, а файл просто окажется обрезанным на пределе

Заполнение строк без накладных расходов на Variant в каждой ячейке

Каждое присваивание Cells[R, C].Value оплачивает поиск ячейки и преобразование Variant. На десяти тысячах строк этого никто не замечает. На миллионе строк по двадцать колонок эти накладные расходы на вызов становятся главной статьёй затрат фазы заполнения, и профилировщик укажет прямо на них. Пакетные интерфейсы позволяют передавать писателю целую строку за раз. WriteRows ведёт колбэк, поставляющий по одной строке на вызов:

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;              // источник данных исчерпан: останавливаемся чисто
    Exit;
  end;
  Values := VarArrayCreate([FirstCol, LastCol], varVariant);
  Values[FirstCol]     := FReader.RecordId;
  Values[FirstCol + 1] := FReader.CustomerName;
  Values[FirstCol + 2] := FReader.Amount;
end;

// заполняем строки 2..100001, колонки A..C, беря данные из читателя
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

Флаг Cancel и превращает фиксированный диапазон строк в «не более N строк» — естественную форму, когда число строк приходит из запроса, выполнение которого вы ещё не завершили. Skip действует мягче: он оставляет отдельную строку пустой, не останавливая прогон. Помимо заполнения ячеек, колбэк оказывается удобным домом для эксплуатационных забот, которые иначе неуклюже прикручиваются к циклу заполнения. Счётчик прогресса, тикающий каждую тысячу строк, токен отмены, опрашиваемый у планировщика заданий, ограничитель скорости чтения из исходной базы данных — всё это живёт в одном месте, а не протягивается сквозь код записи ячеек. На стороне чтения тот же шаблон повторяют ForEachRow и ForEachCell, и это важно, когда пакетное задание и потребляет, и производит большие файлы

Пулы стилей вознаграждают вынос за цикл

Модель оформления XLSX — это набор общих пулов. Fonts.Add, Fills.AddSolid и Borders.Add возвращают индекс в пуле, считая с нуля, а ячейка ссылается на шрифт, храня этот индекс плюс единицу в FontIndex, где ноль зарезервирован под значение книги по умолчанию. Эта +1 прямо видна в примере массовой выгрузки выше. Забудьте про неё — и ячейка тихо подхватит не тот стиль, потому что ошибка на единицу в индексе пула стилей остаётся допустимым индексом и ничего не возбуждает

Отсюда и дисциплина: создавайте все объекты стилей до цикла по строкам, а внутри цикла ссылайтесь на их индексы. Fonts.Add убирает дубли одинаковых определений, поэтому вызов его на каждой строке лишь впустую жжёт процессор. Ловушка — Alignments.Add, потому что он возвращает новую запись при каждом вызове. Внутри цикла на 100 тысяч строк это погребает styles.xml под сотней тысяч дублирующихся записей выравнивания, что раздувает файл на диске и замедляет каждое последующее открытие в Excel, поскольку дубли приходится разбирать заново. Стройте каждый стиль один раз вне цикла, а затем ссылайтесь на его индекс столько раз, сколько нужно

Потоки, временные каталоги и пакетный цикл вокруг всего этого

Ничего из этого не требует файловой системы. Оба фасада несут перегрузки с TStream по всей своей поверхности ввода-вывода — среди них Open, SaveAs, SaveAsCSV, SaveAsHTML и SaveAsODS, — поэтому пакетный обработчик может формировать результат прямо в TMemoryStream, идущий в объектное хранилище или в HTTP-ответ, ни разу не тронув диск. Об одном остром крае надо помнить. SaveAs(Stream) пишет с текущей позиции потока и после этого не перематывает его, поэтому выставляйте Position := 0 сами перед передачей потока тому, кто его доставляет, иначе потребитель прочтёт ноль байтов. Фасад XLS добавляет две собственные ручки. SetTempDir направляет временные файлы писателя BIFF на том, у которого есть место и запас по вводу-выводу, чтобы их принять, а это важно на серверах, где путь temp по умолчанию лежит на тесном системном диске. UseSharedFormulas сворачивает повторяющиеся тела формул в общие группы — реальное уменьшение размера для классической формы отчёта, где одна формула скопирована на всю колонку

Сам пакетный цикл нарочно остаётся скучным:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // свежий экземпляр: состояние не протекает
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // один плохой вход не должен убить пакет
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

Свежий экземпляр книги на файл стоит микросекунды и снимает целый класс ошибок перекрёстного заражения между файлами: у стилей, определённых имён и свойств документа из файла 17 нет пути протечь в файл 18. Пропуск с продолжением при неудачном Open окупается ничуть не хуже, потому что одна усечённая загрузка в пакете из 600 файлов должна стоить вам одной строки журнала, а не остатка прогона. Стоит отметить и то, чего ветка CSV намеренно не делает. SaveAsCSV выводит формулы буквальным текстом и никогда их не вычисляет, поэтому пакет конвертации, чьи потребители ждут посчитанных чисел, обязан сначала выполнить Calculate на нужных ячейках или начинать с книг, уже несущих кэшированные результаты прошлого вычисления

Модель параллелизма: одна книга на поток

Объекты ни одного из фасадов не потокобезопасны, и замысел никогда не притворялся иным. Поскольку общего глобального состояния между экземплярами нет, правило масштабирования простое: одна книга на рабочий поток, без разделения книги между потоками. Пул из N обработчиков, каждый со своим TXLSXWorkbook, масштабируется почти линейно, пока потолком не станет память, и этот потолок можно выразить числом: крупнейшая одновременная модель ячеек, умноженная на число обработчиков, плюс те накладные расходы времени сохранения, которые сгладил StreamingWrite. Когда очередь уходит вглубь, применяйте обратное давление на очереди заданий, а не внутри писателя. Голодающий поток, наполовину записавший книгу, не произвёл ничего полезного, тогда как задание, подождавшее несколько секунд свободного обработчика, завершается целым

Более широкую картину настройки, включая общие формулы, пропуск графики на стороне чтения и рычаги, специфичные для XLS, смотрите в руководстве по производительности на больших книгах. Пакетные задания, чьи строки идут прямо из запроса, разобраны отдельно в шаблонах экспорта из базы данных для отчётов на Delphi

HotXLS компилируется в ваш сервис на Delphi или C++Builder как нативный Object Pascal без внешних зависимостей; редакции и лицензирование описаны на странице продукта HotXLS Delphi Component