Экспорт миллиона строк в XLSX на чистом PHP: константная память вместо memory_limit = 2G

Есть два типа PHP-разработчиков: те, кто ещё не пробовал выгрузить большой отчёт в Excel, и те, кто уже видел это:

Fatal error: Allowed memory size of 536870912 bytes exhausted

Сценарий всегда один и тот же. Заказчик просит «кнопку выгрузки в Excel». На тестовых данных из 500 строк всё летает. Через полгода в таблице 800 тысяч строк, и экспорт валится с OOM. Это не редкий кейс: в трекере PhpSpreadsheet люди жалуются, что выгрузка 25 000 строк × 75 колонок съедает гигабайты памяти (issues #1011, #638), а в Laravel-Excel аналогичная тема (#1079) — одна из самых обсуждаемых.

Лечат это обычно так:

• ini_set('memory_limit', '-1') — до первого по-настоящему большого отчёта или второго параллельного запроса;
• разбить экспорт на чанки и склеивать несколько файлов — работает, но пользователь хотел один файл;
• унести экспорт в очередь на отдельный сервер с 16 ГБ памяти — дорого и не решает причину.

Всё это борьба с симптомом. В этой статье разберём причину и покажем, как выгружать миллион строк с постоянным потреблением памяти на чистом PHP с помощью avadim/fast-excel-writer. Всё ниже проверено на версии 6.16.

Почему универсальные библиотеки падают#

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

Для задачи «открыть файл, поменять три ячейки, сохранить» такая модель необходима. Для задачи «вылить результат SQL-запроса в XLSX» — это чистые накладные расходы: вы платите памятью за возможность редактирования, которой не пользуетесь.

Порядок цифр из бенчмарка в README FastExcelWriter (генерация без стилей):

Обратите внимание не на конкретные числа (они зависят от железа), а на форму зависимости: у PhpSpreadsheet память растёт линейно с количеством ячеек, у FastExcelWriter — не растёт вообще. Экстраполируйте первую колонку на миллион строк — и станет ясно, почему никакой memory_limit не спасает.

Потоковая архитектура: строка → temp-файл → ZIP#

XLSX — это ZIP-архив с XML-файлами внутри, и XML листа устроен строго последовательно: строка за строкой, сверху вниз. FastExcelWriter использует это буквально:

1. Вы пишете строку через writeRow() — она живёт в памяти как обычный массив.
2. При переходе к следующей строке предыдущая сериализуется в XML и сбрасывается во временный файл листа. Из памяти она исчезает.
3. При вызове save() библиотека дописывает служебные XML-части (стили, метаданные, связи) и упаковывает temp-файлы в ZIP.

В памяти в каждый момент времени находится только текущая строка плюс общие структуры (реестр стилей, ширины колонок). Поэтому пиковое потребление памяти не зависит от количества строк: файл на миллион строк требует столько же памяти, сколько файл на тысячу.

Цена этой архитектуры — запись только «вперёд»: к уже сброшенной строке вернуться нельзя. Для экспорта данных это ограничение почти незаметно, но о нём стоит знать заранее — подробнее в разделе про ограничения.

Минимальный пример#

composer require avadim/fast-excel-writer

use avadim\FastExcelWriter\Excel;

$excel = Excel::create(['Report']);
$sheet = $excel->sheet();

// Заголовок с форматами колонок: A — целое, B — строка, C — дата, D — деньги
$sheet->writeHeader([
    'ID'     => '@integer',
    'Name'   => '@string',
    'Date'   => '@date',
    'Amount' => '0.00',
]);

foreach ($rows as $row) {
    $sheet->writeRow($row); // строка ушла в temp-файл, память свободна
}

$excel->save('report.xlsx');
// или сразу отдать в браузер:
// $excel->download('report.xlsx');

Никаких flush(), «режимов экономии» и специальных врапперов — потоковая запись здесь не опция, а единственный способ работы.

Генератор из PDO: миллион строк без батчей#

Классический совет «читайте из БД чанками по 5000 строк» нужен только тогда, когда потребитель копит данные в памяти. Здесь потребитель ничего не копит, поэтому достаточно связать небуферизованный курсор БД и writeRow() через генератор:

use avadim\FastExcelWriter\Excel;

/**
 * Отдаёт строки результата по одной, не загружая весь результат в память
 *
 * @return \Generator
 */
function fetchOrders(PDO $pdo)
{
    // Для MySQL важно отключить буферизацию результата на стороне клиента,
    // иначе весь результат запроса окажется в памяти PHP
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

    $stmt = $pdo->query(
        'SELECT id, customer, created_at, status, amount FROM orders ORDER BY id'
    );
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        yield $row;
    }
}

$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'user', 'pass');

$excel = Excel::create(['Orders']);
$sheet = $excel->sheet();

$sheet->writeHeader(
    ['ID' => '@integer', 'Customer' => '@string', 'Created' => '@datetime', 'Status' => '@string', 'Amount' => '0.00'],
    ['font-style' => 'bold']
);

foreach (fetchOrders($pdo) as $row) {
    $sheet->writeRow($row);
}

$excel->save('orders.xlsx');

Цепочка получается сквозной: строка выходит из курсора БД, проходит через генератор, сериализуется в XML и уходит на диск. Ни на одном участке данные не накапливаются — память константна на любом количестве строк.

Проверяем цифры сами#

Не верьте бенчмаркам из чужих README (включая этот) — скрипт замера пишется за минуту. Это чуть упрощённый вариант demo/demo-99-199k-rows.php из репозитория библиотеки:

use avadim\FastExcelWriter\Excel;

$rowCount = 1000000;
$timer = microtime(true);

$excel = Excel::create(['1M']);
$sheet = $excel->getSheet();
$sheet->setColFormats(['@integer', '@string', '0.00', '@string']);

for ($i = 1; $i <= $rowCount; $i++) {
    $sheet->writeRow([$i, 'item-' . ($i % 1000), ($i % 100) / 100, date('Y-m-d')]);
}

$excel->save('million.xlsx');

printf(
    "Rows: %d\nTime: %.1f sec\nPeak memory: %.1f MB\n",
    $rowCount,
    microtime(true) - $timer,
    memory_get_peak_usage(true) / 1024 / 1024
);

Порядок цифр, который вы увидите: единицы мегабайт пиковой памяти (по сути, размер буферов и реестра стилей) и минуты времени — скорость на простых данных измеряется тысячами строк в секунду. Замер на PHP 8.4: миллион строк по четыре колонки — около восьми минут и 4 МБ пиковой памяти. Главный тест — поменяйте $rowCount с 25 тысяч на миллион: время вырастет в сорок с лишним раз, а строка «Peak memory» покажет те же 4 МБ. Именно это отличает потоковую запись от «оптимизированной» объектной модели.

Единственное, что растёт, — временные файлы на диске. Если системный temp-каталог мал или лежит не там, где нужно, задайте свой: Excel::setTempDir('/path/to/tmp') до Excel::create().

Практические приёмы#

Стилизуйте строками, а не ячейками. У writeRow() и writeHeader() есть второй аргумент — стиль всей строки. Один массив стилей на строку заметно дешевле, чем индивидуальные стили для отдельных ячеек:

// Хорошо: один стиль на всю строку
$sheet->writeRow($rowData, ['font-color' => '#900', 'fill-color' => '#fee']);

// Дороже: индивидуальные стили ячеек — третьим аргументом writeRow()
$sheet->writeRow($rowData, null, ['B' => ['fill-color' => '#fee']]);

Ещё дешевле — форматы колонок через setColFormats() или writeHeader(['Col' => '@date']): они задаются один раз на весь лист, и в цикле записи про стили можно вообще не вспоминать.

Inline strings против shared strings. По умолчанию библиотека пишет строки прямо в XML листа (inline) — это быстрее и не требует держать словарь строк в памяти. Классический для XLSX словарь общих строк включается опцией Excel::create([], ['shared_string' => true]): файл получится компактнее, если значения часто повторяются (статусы, названия городов), но словарь уникальных строк живёт в памяти до самого save(). Для максимально предсказуемого потребления памяти оставьте режим по умолчанию.

Чего избегать. Не собирайте данные в массив перед записью («сначала выгружу всё из БД, потом запишу») — это возвращает линейный рост памяти, только теперь на вашей стороне. И не используйте области beginArea()/makeArea() для основного массива данных: они буферизуются в памяти целиком, их место — в шапках.

Честные ограничения#

Чтобы не было сюрпризов на второй день использования:

• Только запись, только XLSX. Библиотека не читает и не редактирует существующие файлы. Для чтения есть парный fast-excel-reader, для шаблонов — fast-excel-templator.
• Только вперёд. После writeRow() предыдущая строка уже в temp-файле — запись в неё выбросит исключение. Итоговую строку «Всего: N» нельзя дописать в начало листа постфактум: либо посчитайте агрегаты заранее (отдельным SQL-запросом — это дёшево), либо пишите итоги в конце листа, либо используйте формулы вроде =SUM(...), которые Excel посчитает сам.
• Не всё в книге потоковое. Строки уходят в temp-файл сразу, но часть сущностей живёт в памяти до самого save(): ячейки областей, объединения, гиперссылки, заметки, правила проверки ввода и условного форматирования, диаграммы, а при shared_string ещё и словарь строк. Порядок величины — доли килобайта на элемент, так что плоский экспорт миллиона строк не стоит почти ничего, а вот миллион строк, где каждая получает заметку, гиперссылку или собственное объединение, снова даёт линейный рост. Лечится тем же приёмом, что и в Excel: одно правило на диапазон вместо тысяч правил на ячейку.
• Шапки со сложной разметкой — через области. Если шапку отчёта нужно заполнять в произвольном порядке, объявите буферизуемую область: $area = $sheet->beginArea('A1'); или $sheet->makeArea('A1:F5');, пишите в неё в любом порядке через $area->setValue(), затем сбросьте её в файл вызовом $sheet->writeAreas() — и дальше стройте построчно. Область держится в памяти целиком, поэтому годится для шапки на десятки строк, но не для данных на миллион.

Если ваша задача — «отредактировать существующий файл» или «посчитать формулы на стороне PHP», берите PhpSpreadsheet и не мучайтесь. Но экспорт данных — а это большинство реальных задач с Excel в вебе — потоковый писатель делает в несколько раз быстрее, а по памяти разрыв тем больше, чем крупнее файл: в таблице выше на 1 000 × 25 это семикратная разница, а на 10 000 × 50 — уже стократная.

Итог#

• OOM при экспорте в Excel — не повод поднимать memory_limit и городить очереди: это архитектурная проблема объектной модели, а не нехватка железа.
• Потоковая запись «строка → temp-файл → ZIP» даёт константную память: миллион строк стоит столько же мегабайт, сколько тысяча.
• Генератор поверх небуферизованного PDO-курсора + writeRow() — и батчи не нужны в принципе.
• Стили строками и форматы колонок — дёшево; области и накопление массивов — дорого.
• Ограничение «только вперёд» — цена скорости; для шапок есть beginArea().

composer require avadim/fast-excel-writer

Библиотека работает на PHP 7.4+ и из расширений требует только стандартные zip, json и mbstring (плюс небольшой пакет avadim/fast-excel-helper, общий у писателя с ридером). Документация и примеры — в репозитории на GitHub и на Read the Docs.