PHP, YII2 и формирование больших excel-файлов

Начало


Одна поддерживаемая нашей компанией учетно-отчетная система начала очень быстро разрастаться в количестве хранимых данных. Система написана на PHP с использованием фреймворка Yii2. Изначально отчеты строились через библиотеку PhpSpreadsheet, которая пришла на смену, уже давно ставшему deprecated, PhpExcel.

Среди разного вида отчетности был один очень крупный – фактически полный набор всех хранящихся в БД данных должен выгружаться в одну excel-таблицу. На начальном этапе проблем не возникало, но когда объем стал превышать многие сотни тысяч записей, то скрипт формирования выгрузки стал отваливаться в timeout limit. Для начала повысили этот самый лимит и начали искать пути решения проблемы. Но врЕменного решения хватило ненадолго – проблема с лимитом времени превратилась в проблему с лимитом памяти. Серверу накинули «оперативки» и вообще сняли memory_limit для данной отдельно взятой операции. Очень скоро пользователи снова начали жаловаться на ошибки по времени выполнения. Пришлось убрать и временной лимит для полного отчета. Но сидеть и смотреть десяток минут на экран с индикатором загрузки – мало удовольствия. К тому же иногда отчет нужен был «здесь и сейчас», и каждая потраченная минута на его формирование оказывалась критичной. Эксперименты с настройками окружения прекратили, почесали затылок и приступили к оптимизации кода.

Поиск решения


Первое, что было сделано – скрипт отчетности вынесен в фоновый процесс, а пользователь наблюдает за ходом через «прогрессбар». Фоновое выполнение заданий реализовали через механизм очередей с использованием Redis для хранения. Работа в системе не останавливается, можно заниматься другими задачами и периодически возвращаться на страницу с отчетом – посмотреть, а не готов ли файл. Как только файл формируется, пользователю предлагается ссылка на скачивание. Но, как уже упоминалось выше, иногда файл требовался «немедленно», а повышение юзабилити никак не решало эту проблему. Тем временем количество данных продолжало расти и время построения файла дошло до 79 минут! Это совершенно не приемлемо, особенно учитывая, что отчетность — одна из основ функционала данной системы. Нет, все остальные части работали как часы, но эта ложка дегтя портила общее впечатление.

Первые результаты


Снова сели за анализ кода. Первое, что было протестировано – процесс выбора данных из БД. Но запросы уже были оптимизированы максимально возможным способом. Хоть самый долгий запрос и представлял собой страшную выборку с пятью-шестью обращениями к монструозному ФИАСу, но отрабатывал за 2-5 секунд. Слабым местом был не он, а формирование файла-«эксельника». Начались попытки оптимизации этого процесса. Начиная от кеширования в redis, до извращений вроде формирования отдельных небольших «эксельников» в параллельных потоках с последующим склеиванием в один файл. Но результат был всегда один: проблема со временем превращалась в проблему с памятью и наоборот. Золотой середины не было, только перетекание из крайности в крайность. После определенного количества данных потребление ресурсов библиотекой начинало расти экспоненциально и победить это не представлялось возможным. PhpSpreadsheet – не подходит для больших файлов. В итоге было принято решение сменить библиотеку. Как вариант – написание своего аналога для формирования эксель-файлов.

Анализ и выбор инструмента


Спешить с написанием велосипедов не стали, а для начала провели аналитику существующих решений. Из возможных вариантов заинтересовал только box/spout. Быстро переписали модуль с использованием этой библиотеки. В итоге, полный отчет получился за 145 секунд. Напомню, что последние тесты с PhpSpreadsheet — 79 минут, а тут 2,5 минуты! Провели тестирование: увеличили объем данных в 2 раза. Отчет сформировался за 172 секунды. Разница потрясающая. Конечно, библиотека не обладает всеми теми же функциями, что и PhpSpreadsheet, но в данном случае хватает и минимального набора инструментов, так как критичным является скорость работы.

Расширение для Yii2


Итоговое решение оформили в виде расширения для Yii2. Может быть, кому-то пригодится. Расширение позволяет выгрузить любой набор данных из GridView в excel с сохранением фильтрации и сортировки. В качестве зависимостей использует yii/queue и box/spout. Применять расширение имеет смысл для формирования действительно больших файлов, ну, хотя бы 50 000 строк =) В данный момент модуль, ставший основой для расширения, лихо справляется с нагрузкой почти в 600 000 строк.

Ссылка на github: Yii2 ExcelReport Extension

Спасибо за внимание!
Поделиться публикацией
Комментарии 37
    0
    Интересно, что потом пользователи делают с отчётом из 600К строк? Глазами, очевидно, все тысячи строк не смотрят…
      0
      Скорее, они могут использовать этот же эксель для импорта в другую софтину. Хотя, выгрузить тот же прайс с 100к позиций, которые разложены по вкладкам — тоже возможный сценарий :)
        0
        Действительно. Скорее всего анализируется какой-то срез данных из всех этих 600К строк. Может нужно было посмотреть в строну формирования не полностью отчета, а именно тех данных которые нужны.
          0
          Передают людям, которые не имеют доступа в систему. Система располагается в закрытой корпоративной сети, а часть пользователей — удаленно, и они могут не иметь доступа. Кто-то ходит через vipnet, а кто-то получает файлы в 600К строк
            0
            а экспортировать в csv а потом конвертнуть какойто консольной утилиткой в эксель файл?
              0
              Я думаю, если бы документ состоял чисто из таблицы с данными — то скорее всего просто использовали бы CSV формат.А так — данные в документе дополнительно можно форматировать (шрифты, цвета, фильтры, дропдауны), да и разбиение по листам — тоже нередко нужная вещь для тех, кто потом работает с excel.
                0
                Из личного опыта — найти утилиту правильно конвертирующую таблицу в соиню полей и пару сотен тысяч строк — не так просто, это ещё не учитывая того что в поле название организации вполне могут быть любые символы в т.ч. перенос строки во всех трёх известных вариантах.
                p.s. а у Oracle ещё number(38 цифр) — один из наиболее распоспостранённых типов, да и номер счёта (20 цифр) excel очень любит сохранить как число (с округлением ;)).
              0

              "Пучеглазят"
              Но по факту обычно фильтры/формулы/суммы/сводные таблицы/сравнения с другими данными/системы построенные на ссылках на другие файлы (это то что видел лично).
              Причём если вам покажется что эти процессы легко автоматизировать — это не совсем так, т.к. процесс может слишком часто меняться ввиду внешних причин/часто присутствует "экпертное мнение"/банально для многих ручных вещей может не существовать более удобного и гибкого инструмента чем эксель :)

              0

              Zodiak_smr, пришлось ли отказаться от функционала исходной библиотеки или все необходимое затащили в свою?

                0
                К сожалению, отказаться полностью от PhpSpreadsheet не удалось. Например, в некоторых отчетах требуется сохранить html-разметку (цвет текта, размер и т.д.) в формируемом эксельнике. В этих случаях и некоторых других оставили старую библиотеку. Свое решение применили точечно к крупным отчетам.
                  0

                  Форматирование корректно работает у вас? Текст, чтобы ведущие нули не терялись, не конвертировались в числа/деньги значения, количество нолей после запятой и др.

                    0
                    Форматирование — корректно с этим проблем нет. Это один из важных моментов, на который обращали внимание. Если бы можно было убрать форматирование, то время построения файла сократилось бы ~ 30%, проводили такие тесты.
                0
                А какого рода данные постоянно нужны сотрудникам в виде отчетов? Да еще и в экселе… Намекните хотя бы)
                  0
                  Увы. Заказчик — гос структура, и данные весьма… специфичны. А у нас соглашение о неразглашении
                    +1
                    По фразе доступ через VipNet я уже стал догадываться)
                  0
                  потом каждая операция в таком файле выпооняется несколько минут, или у юзеров машины хорошие
                    0
                    Во время тестирования объем данных искусственно увеличивали для оценки потенциала. Так при этих тестах несколько одновременно просто открытых файлов иногда подвешивали систему. Но заказчик пока не жалуется)
                    0

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

                      0
                      Держали в уме подобный вариант на случай, если не удастся достичь успеха внутри системы. Еще был резервный вариант с подключением к реплике Power BI и построение этого отчета оттуда. Но оказалось, что Power BI имеет ограничение: максимальное количество строк, которое можно экспортировать в XLSX-файл — 150 000
                      +1
                      А почему не банальный csv? который влет открывается тем же Excel?
                        0
                        видимо из-за раскраски разметки
                          0
                          Это было первое, что предлагали в качестве решения. Но заказчика вариант не устроил, хотели только xlsx на выходе. Переубедить не удалось. Пришлось «плакать, колоться, но продолжать жрать кактус»
                          0
                          А чем csv плох? Если конечно нет требований к заранее подготовленным формулам и не нужно цветами играть.

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

                          Данные можно лить в csv используя генераторы (yield) и поток с блокировкой напрямую в файл.

                          Проблема с совместимостью обычно решается двумя кнопками «скачать для windows (cp1251, ';')» и «скачать для unix (utf8, ',')

                          Csv насколько я знаю идеально открывается во всех существующих экселях, опендокументах, спредшитах и в чем угодно вообще.
                            0
                            В отдельно взятом проблемном отчете никаких требований к формулам и цветам не было, csv строился очень быстро и без дополнительных манипуляций, но заказчик хотел только xlsx на выходе, никаких других вариантов.
                              0

                              CSV надо импортировать, что требует лишних действий пользователей, а часто ещё необходимо типы данных вручную указывать (текст и др.).

                                0

                                Не открывается он идеально (из личного опыта) выше писал комментарий, если импортировать в эксель из csv, то нужно правильно задавать форматы полей, ну и самое главнре в csv нельзя залить поля текста с переводами строки.

                                  0

                                  Все там можно в csv с переводом строки, в двойные кавычки многострочный текст и экранирование внутри

                                    0

                                    Формально да на практике:
                                    "
                                    Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
                                    У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.

                                    И
                                    и ещё много чего, статья — https://m.habr.com/company/mailru/blog/129476/
                                    К сожалению приходилось ломать голову как экспортировать и импортировать, реально если подразумевать импорт в excel то универсального варианта не зависящего от настроек у пользователя просто нет.

                                0
                                У нас в системе тоже был подобный опыт, когда клиенту требовались большие отчеты со сложно-форматированной шапкой и разметкой. Мы тоже вынесли это в фоновые задачи, которые обрабатывались не php, а nodejs-скриптом с библиотекой exceljs.

                                Но это померкло в сравнении с задачей от другого клиента, которому потребовались фото-отчеты оформленные в формате PowerPoint'a. Вот тут пришлось подумать, но в итоге сделали по томуже принципу: nodejs с библиотекой officegen успешно смог осилить формирование pptx-файлов на 2-5 Gb.
                                  0
                                  Сталкивался с подобной задачей. Формирование больших экселей на PhpExcel приводило к кошмарному расходу RAM и занимало часы времени. Я изучил известные на то время альтернативы, и в итоге был вынужден создать свой велосипед.
                                  Для этого был изучен формат .xlsx. Оказалось что это обычный архив с кучей xml файлов (данные, стили, настройки...), картинок и прочих вспомогательных файлов. Используя эти файлы как примеры, был создан собственный велосипед с набором необходимых функций, которые работал в сотни (!) раз быстрее и практически не расходовал память. Объем потребляемой памяти не зависил от объема данных.
                                  Правильным выбором оказалось формирование xml файлов без всяких библиотек и готовых решений, а просто путем дозаписи строчек в файл. Именно это позволило не расходовать RAM как большинство других решений и не хранить сложные структуры данных в памяти. После формирования всех xml файлов и добавления вспомогательных статических файлов и изображений, они архивировались и получался готовый .xlsx файл.
                                    0
                                    Ничего удивительного. Вы создали частное решение которые быстрее универсального. Сам тоже так делал когда-то с docx, там тоже xml в архиве :)
                                    Совсем другое дело doc…
                                      0
                                      В принципе получилось не так уж и специализированно. Там было и форматирование и формулы. Но было одно важное требование — листы и ряды можно было создавать только последовательно. Собственно за счет этого и удалось добиться огромной скорости и экономии ресурсов.
                                      0

                                      Отличная идея, спасибо, тоже

                                        0
                                        Я думаю для целей автора надо было использовать именно такой подход. Причем писать в файл кусками (батчами) например по 1000 строк. 500к строк скрипт наполнит мгновенно, даже на слабом компьютере. Тут проблема уже будет со скоростью подготовки этих данных.
                                        0
                                        Помню, когда делали выгрузку XML для яндекс маркета на стандартном шаред хостинге (ограничение 30 секунд и 32 мегабайта памяти) каталога с 15к номенклатуры, просто делали кусками по 500 номенклатур, а потом склеивали. Учитывая, что xlsx — это зип архив с XML, тут можно было бы применить тот же подход.
                                          0
                                          Пару лет назад решали точно такую же проблему. Есть сайт с огромным кол-вом товаров и каждый пользователь (гость) должен иметь возможность выгрузить себе любую конфигурацию. Правда у нас на тот момент максимальное кол-во было около 100К, но в случае с PhpExcel этого более чем достаточно что бы понять, что если одновременно 2 и более гостя запустят формирование прайса — сервер ляжет. Пробовали различные варианты, в том числе csv с дальнейшим конвертированием в xls, но у клиента прайс разрисован всеми цветами радуги (цвет как индикатор кол-ва таваров: много/мало/достаточно и т.д.) и помимо этого строки должны быть ссылками на товарные позиции, а так же подкатегории красиво оформлены бордерами и фоновыми заливками. В результате недельных поисков обнаружили идеальное решение — библиотека LibXL. Устанавливается как расширение для php, помимо этого можно использовать в С/С++/С#/Delphi проектах. Она платная, но 199$ это не цена для такой библиотеки.
                                            0
                                            К сожалению не панацея. Кушает память эта библиотека поменьше, но всё равно немало. При попытке сгенерировать файл размером в 70 Мб уходит около 2,5 Гб оперативки. Написал в саппорт — разводят руками, говорят, что ничего поделать не могут, такова специфика работы.

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

                                          Самое читаемое