Перейти к содержимому
BXWiki

Выгрузка в Excel из кода - библиотека, объём, отдача файла

Собираем выгрузку в таблицу из кода: библиотека, запись порциями, большие объёмы, отдача файла и уборка за собой.

Механика

Штатной выгрузки в табличный формат в платформе нет. Есть экспорт в текстовый формат с разделителями у отдельных модулей, а всё остальное собирают сторонней библиотекой или берут готовым решением из каталога.

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

Автозагрузчик пакетного менеджера подключают один раз, в файле начальной настройки проекта. После этого классы библиотеки видны везде: в компонентах, в обработчиках событий и в консольных скриптах.

Библиотека держит весь лист в памяти целиком, вплоть до момента записи готового файла. Это главное ограничение: десять тысяч строк с формулами и стилями съедают сотни мегабайт, и скрипт падает не на записи файла, а где-то посередине выборки.

Отсюда правило: данные пишут по одной строке, а выбирают порциями. Массив на всю выгрузку не собирают никогда, даже если кажется, что данных немного - каталог растёт быстрее, чем правится код.

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

Готовый файл отдают с проверкой прав, а не ссылкой на каталог загрузок. Прайс с дилерскими ценами, лежащий по прямой ссылке, рано или поздно оказывается в поисковой выдаче.

Временные выгрузки убирают заданием по расписанию, а не руками при случае и по памяти. Без него каталог временных файлов растёт месяцами и однажды заканчивает место на диске в самый неудобный момент.

Шаги

  1. Поставить библиотеку пакетным менеджером и подключить её автозагрузчик.
  2. Написать выгрузку так, чтобы строки писались по одной, а данные шли порциями.
  3. Выбрать формат по объёму: таблица для отчётов, текст с разделителями для тысяч строк.
  4. Сохранить файл во временный каталог с именем, привязанным к пользователю.
  5. Отдать файл скриптом с проверкой прав вместо прямой ссылки на него.
  6. Завести задание, которое удаляет временные выгрузки старше суток.

Код

Ставим библиотеку пакетным менеджером:

Окно терминала
composer require phpoffice/phpspreadsheet
# файл описания зависимостей держат вне публичной части, путь к нему -
# в настройках платформы; тогда автозагрузчик подключается сам

Подключаем автозагрузчик один раз:

/local/php_interface/init.php
require_once __DIR__ . '/../../vendor/autoload.php';
// подключение один раз: дальше классы библиотеки видны во всём проекте

Пакетный менеджер решает и вторую задачу - зависимости библиотеки. Ручное копирование классов в свой модуль работает ровно до того момента, когда понадобится обновление или вторая библиотека с общей зависимостью.

Пишем строки по одной:

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->fromArray(['Название', 'Артикул', 'Цена'], null, 'A1');


$line = 2;
$rs = CIBlockElement::GetList(['ID' => 'ASC'], $filter, false,
    ['nPageSize' => 500], ['ID', 'NAME', 'PROPERTY_ARTICLE']);
while ($item = $rs->GetNext()) {
    $sheet->fromArray([$item['NAME'], $item['PROPERTY_ARTICLE_VALUE'], $prices[$item['ID']]],
        null, 'A' . $line++);
}
// массив на всю выгрузку не собирают: он и съедает всю память

Выборка порциями и запись по строке - это одна и та же мера, а не две разные. Данные не должны существовать в памяти дважды: ни в массиве выборки, ни в собственном накопителе.

Сохраняем файл во временный каталог:

$name = 'price-' . (int)$USER->GetID() . '-' . date('Ymd-His') . '.xlsx';
$path = $_SERVER['DOCUMENT_ROOT'] . '/upload/tmp/' . $name;
(new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet))->save($path);
$spreadsheet->disconnectWorksheets();   // освобождаем память сразу после записи

Имя файла привязывают к пользователю и времени. Общий файл на всех приводит к тому, что один дилер скачивает выгрузку другого, а разбирается это по жалобе и очень нескоро.

Отдаём большие объёмы текстом с разделителями:

$out = fopen($path, 'w');
fwrite($out, "\xEF\xBB\xBF");                 // метка кодировки для табличного редактора
fputcsv($out, ['Название', 'Артикул', 'Цена'], ';');
while ($item = $rs->GetNext()) {
    fputcsv($out, [$item['NAME'], $item['PROPERTY_ARTICLE_VALUE'], $item['PRICE']], ';');
}
fclose($out);

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

Отдаём файл с проверкой прав:

if (!in_array($dealerGroupId, $USER->GetUserGroupArray(), true)) {
    return;                                   // чужую выгрузку не отдаём
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="price.xlsx"');
readfile($path);

Прямая ссылка на файл в каталоге загрузок - это публичная ссылка. Отдача скриптом добавляет проверку прав и заодно позволяет записать в журнал, кто и когда скачивал выгрузку.

Чистим временные выгрузки заданием:

foreach (glob($_SERVER['DOCUMENT_ROOT'] . '/upload/tmp/price-*.xlsx') as $file) {
    if (filemtime($file) < time() - 86400) {
        unlink($file);                        // выгрузки старше суток не нужны никому
    }
}
return __METHOD__ . '();';                    // агент возвращает себя для следующего запуска

Проверяем настройки языка перед чтением чужих файлов:

Окно терминала
php -i | grep mbstring.func_overload    # значение 2 ломает чтение старого формата
php -i | grep memory_limit              # выгрузке нужен свой предел, а не общий

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

Ограничения

Библиотека рассчитана на отчёты, а не на выгрузку каталога целиком. Разумный предел - несколько тысяч строк на лист; всё, что больше, отдают текстом с разделителями или собирают по частям.

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

Ресайз картинок внутри цикла выгрузки - отдельная ловушка. Каждая уменьшенная копия занимает память, и скрипт падает с непонятным сообщением где-то в ядре, а не в вашем коде.

Формулы и оформление ячеек стоят дорого и по времени, и по памяти, особенно на больших листах. Ширина колонок, стили и объединённые ячейки увеличивают и время, и память в разы, поэтому в больших выгрузках их не используют вовсе.

Готовое решение из каталога закрывает типовой экспорт. Свой код оправдан там, где выгрузка нестандартная: свои поля, свои права, своё расписание и передача файла внешней системе.

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

Типичные проблемы

Класс библиотеки не найден, хотя файлы на месте.

Автозагрузчик пакетного менеджера не подключён или классы скопированы в модуль без регистрации. Автозагрузчик подключают один раз в файле начальной настройки.

Скрипт падает на нескольких тысячах строк.

Лист держится в памяти целиком, а данные ещё и собраны в массив. Строки пишут по одной, выборку ведут порциями, большие объёмы отдают текстом.

Вместо данных в файле мусор из символов.

Включена подмена строковых функций в настройках языка, и библиотека считает длины строк неверно. Настройку выключают, платформа её давно не требует.

Прайс с дилерскими ценами нашёлся в поиске.

Файл лежит в каталоге загрузок и доступен по прямой ссылке. Такие выгрузки отдают скриптом с проверкой прав, а не прямой ссылкой на файл.

Табличный редактор открывает текстовый файл кракозябрами.

В файле нет метки кодировки, и редактор читает его в системной кодировке. Метку кодировки пишут первой строкой файла, до заголовков колонок.

На диске закончилось место из-за папки выгрузок.

Временные файлы никто не удаляет, а каждая выгрузка создаёт новый. Чистку каталога временных файлов ставят отдельным заданием по расписанию, примерно раз в сутки.

Частые вопросы

Как подключить стороннюю библиотеку к проекту на платформе?

Пакетным менеджером, с подключением автозагрузчика в файле начальной настройки. Копирование классов в свой модуль тоже работает, но обновлять библиотеку и разрешать конфликты зависимостей придётся вручную.

Сколько строк выдержит выгрузка в таблицу?

На практике несколько тысяч строк без оформления; дальше растут и время, и память. Для десятков тысяч строк выбирают текстовый формат с разделителями - он не держит в памяти ничего.

Почему выгрузка падает без внятной ошибки?

Чаще всего это нехватка памяти: сообщение приходит из ядра платформы, а не из вашего кода. Замер пика памяти до и после цикла показывает виновника за одну попытку.

Как отдать готовый файл только своим покупателям?

Скриптом с проверкой прав, а файл держать вне открытых каталогов или с уникальным именем. Ссылка на каталог загрузок - это публичная ссылка, и правами она не защищена.

Стоит ли брать готовое решение вместо своего кода?

Для типового экспорта товаров и заказов - да, оно окупается сразу. Свой код оправдан, когда нужны свои поля, права, расписание или передача файла внешней системе.

Смежное

Первоисточник