Реализация быстрого импорта из Excel на PHP

    Мы продолжаем рассказывать о технологиях, используемых на нашем сервисе email-маркетинга Pechkin-mail.ru. Одной из ключевых задач любого сервиса, связанного с данными клиентов, является загрузка этих данных на сервис. Для Печкина очень важно быстро и без проблем для пользователя загружать адресные базы, содержащие email-адреса, имена, фамилии и другие дополнительные данные.

    Что использовать в качестве инструмента?


    В качестве базового стандарта, используемого при импорте адресных баз, мы взяли Microsoft Excel. Объясняется это просто:
    • это стандартный инструмент, которым на базовом уровне владеют 100% пользователей компьютеров. Более того, в бизнесе — это де-факто корпоративный стандарт и используется даже, если на рабочих компьютерах Mac или Linux.
    • Практически все CRM-, CMS-, облачные или десктопные системы имеют экспорт в Excel или CSV, который простым пересохранением приводится к формату XLS или XLSX.
    • Известно также, что “90% ошибок ПО сидит в полуметре от монитора”. Не в обиду будет сказано рядовым пользователям, но мы должны учитывать самый базовый уровень подготовки и тех. поддержке для объяснения достаточно сказать “Загрузите Excel-файл”, а не объяснять процедуру подготовки файла в нужном формате.


    Проблему пользователей при импорте адресных баз сняли. Но тут возникает уже проблема непосредственно разработки.


    Наша боль, как разработчиков


    Excel — это не open-source разработка, а проприетарное решение. Формат данных, особенно в новых версиях после 2007 года (xlsx), нетривиален. На Печкине используется PHP, поэтому мы начали поиск библиотек, которые позволят нам решить данную задачу. Но тут столкнулись с проблемой, что целый ряд библиотек, не позволяют читать xlsx:
    • php-spreadsheetreader reads a variety of formats (.xls, .ods AND .csv)
    • PHP-ExcelReader (xls only)
    • PHP_Excel_Reader (xls only)
    • PHP_Excel_Reader2 (xls only)
    • XLS File Reader Коммерческая и xls only
    • SimpleXLSX Из описания способен читать xlsx, однако, автор ссылается только на xls
    • PHP Excel Explorer Коммерческая и xls only


    Обратила на себя наше внимание библиотека PHPExcel. Ее мы использовали еще несколько лет назад в сервисе sms-рассылок SMS24X7.ru. Петя Соколов (Petr_Sokolov), наш талантливый разработчик, написал обертку для этой библиотеки, исправляющую ряд ее недостатков и багов.

    Библиотека, безусловно, интересная и развитая. Но для Печкина ее использовать стало невозможно уже через пару лет, когда выросли и мы и наши клиенты — ее катастрофическая требовательность к ресурсам и огромное время парсинга файлов. Например, нередки случаи загрузки на сервис адресных баз > 100 000 строк со сложной структурой. А если файл уже 500 000 строк и “весит” больше 30Мб?

    И тут нас отпустило...


    В процессе поисков мы наткнулись на коммерческую библиотеку libxl, увидев результаты “кустарного benchmark” на Stackoverflow.

    Библиотека написана на C++, а благодаря великолепному объектно-ориентированному расширению для PHP от Ilia Alshanetsky, легка в освоении и интеграции (например, переписать наше текущее решение с PHPExcel на LibXL заняло около 3 часов). Что очень классно, учитывая, что, к сожалению, документации от разработчика расширения нет и необходимо пользоваться расширением Reflection.

    Процесс установки очень прост.

    cd /usr/local/src/
    
    wget http://libxl.com/download/libxl.tar.gz
    tar zxfv libxl.tar.gz
    
    cd libxl-3.5.4.1/
    ln -s include_c include
    cd ../
    
    wget https://github.com/iliaal/php_excel/archive/master.zip
    unzip master.zip
    
    cd php_excel-master/
    ./configure --with-excel --with-libxl-libdir=../libxl-3.5.4.1/lib64 --with-libxl-incdir=../libxl-3.5.4.1/include_c
    make
    make test
    make install


    В результате компиляции вы получите файл excel.so в папке /usr/lib/php5/20090626/. Теперь достаточно создать файл /etc/php5/conf.d/excel.ini с содержимым.
    extension=excel.so


    Проверим установился ли модуль и перезагрузим веб-сервер.
    php -m | grep excel
    service lighttpd restart
    


    В коде все тоже очень просто. Подгружаете файл и читаете необходимые ячейки. Например, вот так:
    $doc = new ExcelBook(); 
    $doc->loadFile(‘example.xls’);
    for($r=$sheet->firstRow();$r<=$sheet->lastRow();$r++){
    	for($c=$sheet-> firstCol();$c<=$sheet-> lastCol();$c++){
    		echo ‘Строка: ’.$r.’ Столбец: ’.$c.’ Значение: ’.$sheet->read($r,$c).’<br />’;
    	}
    }
    


    Полученные результаты производительности


    Отсутствие потребности в оперативной памяти (в процессе загрузки файла и его чтения) приятно порадовало.


    А вот и прирост скорости загрузки excel-файла и его чтения на различных размерах адресных баз.


    Данные тесты проводились на xlsx-файлах с N подписчиками в один стоблец с email. Реальные же адресные базы еще больше и сложнее и преимущество в скорости и потреблении памяти выглядит еще значительнее.

    Стоимость библиотеки 199$ за девелоперскую лицензию, но, поверьте, это того стоит. Безусловно рекомендуем всем, кто сталкивается с проблемой импорта Excel-файлов на свой сервис.
    Pechkin.com 54,76
    Новый инструмент для e-mail маркетинга
    Поделиться публикацией
    Комментарии 23
    • +1
      Спасибо за хорошую наводку. Раньше я отказывался от проприетарных вещей, но сейчас понимаю что проще заплатить чем возиться самому, дешевле выходит.
      • +7
        xlsx — это банальный xml, пожатый zip-ом, ничего нетривиального для чтения данных там нет. Более того, если все данные в памяти одновременно не нужны, читать их можно потоковыми библиотеками так, что кол-во потребляемой памяти от объема документа не будет зависеть вообще.
        • +1
          Если у вас есть готовое решение, мы бы с радостью на это посмотрели. К сожалению, решений, которые одновременно бы качественно работали с xls и xlsx, мы не нашли.

          Данные PHPExcel читал, конечно же, не загружаются все в память. Грузится по 4072 строки. (Результаты видны на графиках).
          • +4
            Готовое решение для unzip + stream xml read? Можно, я вас в гугл перенаправлю?
            Обычный xls действительно нетривиален, тут мне предложить нечего кроме java apache poi. Мой комментарий касался только xlsx и того, что для него можно обойтись и без платных библиотек.
            • +1
              xls пора забывать, пользователю кнопкпу нажать, а разработчику времени на что-то другое не хватает. 2014 год. Хоть и многие сидят на 2003 ворде, но этот случай уже попахивает историей с поддержкой ie6
              • 0
                Просто так от xls отказаться не получится. Тот же OpenOffice в 2014 году по неясным для меня причинам сохранять в xlsx не умеет, кроме xls только ods. Который, в свою очередь, MS Excel читает и пишет, но с трудом.
                В общем, если поддерживать ровно один какой-то формат — остается только csv.
                • –1
                  Разве OpenOffice не умер, уступив место LibreOffice?
                  • 0
                    А с чего бы ему умирать? Крайнее обновление всего месяц назад вышло.
        • –8
          Возможно вопрос не по вашей теме, но раз вы уж тут, задам. Как можно организовать рассылку квитанций за ЖКУ? Есть база email, есть список файлов в формате PDF, который нужно прикрепить вложением к каждому email. Используя подобный сервис я смогу реализовать мою задачку?
          • +2
            Мне странным показалось только одно — заплатив 200$ нужно еще попрыгать вокруг с Reflection.
            • –1
              Возможно я чего-то не понимаю, но все что есть в примере поста — есть в документации.
              • 0
                С Reflection надо прыгать из-за отсутствия документации именно для PHP-обертки.
                • 0
                  Ей не нужна документация, она повторяет все классы и принципы работы библиотеки. А к ней документация есть. Для php расширения там есть пустые php классы с описанием классов и методов, причем хорошо докуметированные
                • 0
                  за 200$ уже можно и офис купить, и работать с любыми документами через com объекты на php. Правда… еще нужен сервак с виндой…
                  • 0
                    Мы так и сделали, на самом деле.
                • +4
                  Мы в нашем проекте сначала преобразуем XLS/XLSX в CSV, а потом уже построчно читаем CSV.
                  Для XLS используем консольную программу xls2csv (входит в пакет "catdoc" в репозитории Ubuntu), а для для XLSX — xlsx2csv.
                  Разумеется, все это бесплатно :)
                  • 0
                    Действительно, пользователям разных офисных программ достаточно экспортировать всё в csv и всё. Не надо забывать, что есть ещё другие форматы таблиц, OpenOffice, например. Хотя, в принципе, можно написать сайт для работы сразу со всеми, конечно. Но для csv в РНР есть встроенные решения для перегрузки их в MySQL.
                    • 0
                      kiaplayer как раз-таки говорит о том, что пользователю в их случае не обязательно менять привычные форматы XLS/XLSX на что-то другое — система переконвертирует сама.
                      • 0
                        Все верно. Правда, мы так до сих пор и не нашли конвертера OpenOffice -> CSV.
                        • 0
                          Из самого OpenOffice: Сохранить как… — .csv?
                          • 0
                            Необходимо без участия пользователя средствами доступными серверной ОС (например Ubuntu).
                            • 0
                              В прошлом проекте ребята наваяли что-то на базе то ли ЛО, то ли ОО для конвертации в пдф. Вроде через «консоль», но не вникал, может и иксы поднимали.
                  • 0
                    Спасибо за интересную библиотеку. Сам часто использовал phpExcel в своих проектах и меня жутко бесила скорость работы этой библиотеки. Еще один вопрос ( возможно не совсем в тему ) — вам не приходилось сталкиваться в миграцией данных из MS Access в Excel на php?

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

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