Производительность больших книг Excel в Delphi с HotXLS

Когда экспорт на 300 000 строк вылетает за свой бюджет памяти, виноватым обычно назначают количество строк. Количество строк обычно невиновно. Дорогие части большой книги — это те, что созданы побочным эффектом: пул стилей, растущий на одну запись на каждую ячейку, потому что форматирование добавляли внутри цикла; XML листа, собираемый при сохранении в одну гигантскую строку; миллион одинаковых тел формул, сохранённых поодиночке. HotXLS, нативная библиотека losLab для Delphi, работающая с файлами XLS и XLSX, даёт конкретный рычаг для каждой из этих затрат. Ни один из них не включён по умолчанию, потому что каждый меняет некий компромисс, так что знание, какой рычаг какому симптому соответствует, и есть настоящее умение оптимизировать

На что большая книга тратит память

Рассуждать надо о двух разных режимах памяти. Во время генерации модель ячеек в памяти растёт с каждой затронутой вами ячейкой: значения, форматы и формулы все становятся объектами или записями пула. Во время сохранения путь XLSX по умолчанию дополнительно отрисовывает XML каждого листа в широкую строку, прежде чем сжать её в zip-контейнер, поэтому пик потребления — это модель плюс сериализованная форма самого большого листа. Задание, которое переживает цикл построения, а затем умирает внутри SaveAs, упирается во второй режим, а не в первый, и лекарство от одного не делает ничего для другого

Размер файла подчиняется смежному правилу: ячейки — лишь один вкладчик наряду со стилями, общими строками, формулами, изображениями и примечаниями. Проход аудита с ForEachCell и подсчётом коллекций по листам говорит, какой ресурс на самом деле доминирует в проблемном файле, до того как вы оптимизируете не то. Одна тонкость измерения: Sheet.Cells.Count на стороне XLSX сообщает число созданных ячеек в разреженном хранилище, а не площадь используемого диапазона. Лист, чьи данные занимают прямоугольник 1000 на 50 с половиной пустых ячеек, насчитает примерно 25 000, а не 50 000. Это различие важно, когда вы сравниваете «огромный» файл заказчика со своими эталонами, потому что площадь используемого диапазона и фактическая населённость ячеек в разреженных финансовых раскладках различаются на порядок

StreamingWrite лечит путь сохранения, а не путь построения

Установка TXLSXWorkbook.StreamingWrite := True переключает SaveAs на потоковый сериализатор, который пишет XML листа прямо в zip-поток, устраняя промежуточную строку на каждый лист. По умолчанию значение равно False ради совместимости поведения, а включение — это правка в одну строку:

Book := TXLSXWorkbook.Create;
try
  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;
  end;
  Book.StreamingWrite := True;   // XML листа потоком идёт прямо в zip-контейнер
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Будьте точны в том, что это даёт: модель ячеек, построенная циклом, занимает ровно столько же памяти, сколько и раньше. StreamingWrite сглаживает всплеск во время сохранения, а это разница между пакетным заданием, которое завершается, и заданием, которое падает на отметке 95 процентов. Если память исчерпывает сам цикл построения, нужны следующие два рычага

Пулы стилей: добавьте один раз, переиспользуйте индекс

Форматирование XLSX в HotXLS основано на пулах: Book.Fonts.Add(...), Fills.AddSolid(...) и Borders.Add(...) возвращают индекс пула, считаемый с нуля, на который ссылаются ячейки. Вызов Fonts.Add с одинаковыми параметрами внутри цикла дедуплицируется, поэтому он тратит время, а не память. Alignments.Add ведёт себя иначе: он возвращает новый объект при каждом вызове, поэтому создание выравнивания на каждую ячейку растит пул линейно от количества строк. Одна привычка покрывает оба случая. Разрешите каждый индекс пула один раз, вне цикла, и присваивайте индексы внутри него

// вынесите обращения к пулу за пределы горячего цикла
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // индекс пула, с нуля
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // ячейки хранят с единицы; 0 = по умолчанию

+ 1 — это не опечатка, и забыть его — классическая ошибка, порождающая симптомы: пулы выдают индексы с нуля, тогда как свойства со стороны ячейки трактуют 0 как «по умолчанию», поэтому каждый индекс пула при присваивании обязан быть сдвинут на единицу. Ошибитесь по недосмотру — и ваши заголовки молча отрисуются шрифтом книги по умолчанию, а этот дефект никто не заметит до проверки фирменного стиля

Замените поячеечный трафик Variant обратными вызовами по строкам

Каждое Sheet.Cells[R, C].Value := X влечёт поиск или создание ячейки плюс присваивание Variant. На нескольких сотнях тысяч ячеек эти накладные расходы на доступ становятся заметны в профилях. HotXLS предоставляет пакетные API с обратными вызовами на обоих фасадах (ForEachCell и ForEachRow для чтения, WriteCells и WriteRows для записи), которые переносят итерацию внутрь движка и отдают вашему коду целые строки за раз:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // остановить всю запись
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// один вызов движка вместо сотен тысяч обращений к свойствам
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Флаг Skip обратного вызова оставляет строку нетронутой, не прерывая работу, а Cancel завершает операцию досрочно, что полезно, когда источником служит читатель, чью длину вы узнаёте по ходу дела. Соедините WriteRows для построения с StreamingWrite для сохранения — и на пути генерации не останется ни одной поячеечной горячей точки

Рычаги на стороне чтения у фасада XLS

У больших устаревших файлов .xls свой набор инструментов. _DisableGraphics := True до Open полностью пропускает разбор слоя рисования, что ускоряет загрузку книг, несущих годами накопленные фигуры и встроенные картинки. Ограничение жёсткое: слоя рисования тогда нет в модели, поэтому сохранение такой книги пишет файл без её рисунков. Приберегите этот флаг для аналитических заданий только на чтение. SetTempDir перенаправляет временные файлы писателя BIFF, что имеет значение на серверах, где расположение temp по умолчанию имеет квоту или лежит на медленном хранилище. UseSharedFormulas группирует повторяющиеся тела формул в записи общих формул, уменьшая файлы, где столбец формул повторяется на шестьдесят тысяч строк вниз

У циклов чтения по данным XLS есть индексная ловушка, о которой стоит предупредить, потому что при осторожном обращении она удваивает работу, а при пропуске портит результаты: UsedRange сообщает свои границы FirstRow, LastRow, FirstCol и LastCol с нуля, тогда как Cells.Item[Row, Col] считает с единицы. Обход, идущий по используемому диапазону, обязан прибавлять единицу к каждой координате при доступе к ячейке, как в Cells.Item[Row + 1, Col + 1], иначе он читает сетку, сдвинутую по диагонали на одну ячейку, молча теряя последнюю строку и последний столбец и включая фантомные первые. Обратный вызов ForEachCell обходит это несоответствие целиком, и это ещё одна причина предпочитать его для сплошных обходов листа

Прощупывайте файлы до их загрузки

Самая дешёвая операция с большой книгой — та, которой вы избежали. GetSheetNames на обоих фасадах перечисляет листы файла, не загружая данные ячеек. Реализация для XLSX читает только манифест книги внутри zip и намеренно оставляет экземпляр книги незаполненным, а фасад XLS прекращает сканирование на первой границе подпотока. Это делает вызов правильной предварительной проверкой для вопроса «на какой лист должно быть нацелено это задание импорта», а CanReadEncrypted отвечает на вопрос «зашифрован ли этот контейнер» до обречённой попытки Open

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // при отказе список очищается
  // выберите целевой лист, затем решите, стоит ли полного Open
finally
  Book.Free;
  Names.Free;
end;

Обратите внимание на соглашение о кодах возврата: эти прощупывающие функции сигнализируют об отказе значениями ноль и ниже и опустошают выходной список, поэтому проверяйте <= 0, а не сравнивайте с одним конкретным значением успеха

Подбор подхода под задачу

Для автоматических конвейеров, которые генерируют много больших файлов подряд, картину довершают ещё две привычки. Объекты книги не потокобезопасны для совместного использования, но ничто не мешает завести по одной независимой книге на рабочий поток, что аккуратно распараллеливает пакетную конвертацию. А когда вывод идёт в HTTP, а не на диск, перегрузки сохранения в TStream сочетаются со StreamingWrite, так что крупный ответ никогда не материализуется временным файлом. Применима одна эксплуатационная сноска: сохранение в поток пишет с текущей позиции без перемотки, поэтому задайте Position := 0, прежде чем передавать поток фреймворку ответа. Статья про потоковую запись и пакетные задания развивает этот серверный приём, а статья про экспорт из базы данных показывает, куда эти рычаги встают в отчёте, управляемом набором данных

Наконец, держите по одному наихудшему эталонному файлу на каждое семейство отчётов и замеряйте его в CI. Регрессии производительности в генерации документов редко объявляют о себе. Стиль, добавленный внутри цикла, или прощупывание, заменённое полным Open, функционально не меняют ничего, а ночной пакет просто идёт на сорок минут дольше. Замеряемый по времени тест на представительном эталоне в полмиллиона ячеек превращает этот дрейф в красную сборку вместо эксплуатационного инцидента

Ознакомительные сборки, демонстрационные проекты с примером массовой генерации и полный справочник по API доступны на странице HotXLS Delphi Component