Реализация быстрого импорта из 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
    75.01
    Новый инструмент для e-mail маркетинга
    Share post

    Comments 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?

                    Only users with full accounts can post comments. Log in, please.