excel в php массив

Разбираем xlsx в PHP без готовых библиотек

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

Все бы ничего, но хостер заказчика дает 64мб памяти под выполнение и плевать он хотел на то, что у клиента Exсel файлы вообще без форматирования весят по 10-15мб, что при загрузке его PHPExel съедает (на тест-сервере) что-то около 500мб памяти.
Решение под катом с трудом дотянуло до 5мб.

Предусловия:
1. Имеется Exсel документ листов так в 10-20 с данными о товарах в интернет-каталоге. В каждом листе шапка — «название», «цена» и т.п. + воз доп. характеристик в 40 столбцов — и собственно данные в количестве «у-экселя-сантиметровый-скроллер»;
2. никакого CSV использовать нельзя. Все данные у заказчика уже в Exel и пересохранять их он не собирается… пообещали тут и все;
3. Spreadsheet_Excel_Writer откинут по причине неуниверсальности, хотя написано про него много хорошего. Жду комментариев по memory tests;
4. что удивительно, универсальных решений гугль не предложил. Неужели никто не сталкивался с такой проблемой на PHP *nix, удивился я.

Решение:
После перебора различных способов, вежливо предоставленных гуглом, решили почитать спецификации (эхх, учил меня отец. ). Увидев там ключевые слова основан на Open XML и используется сжатие ZIP быстро позвонили заказчику и перевели разговор в русло xlsx и только: «Ну вы же понимаете! 21 век все-таки! Зачем нам хвататься за старое! Нужно одной ногой стоять в будущем!»

Далее алгоритм таков: принимаем файл, распаковываем его и внимательно смотрим на получившееся.
Полную инвентаризацию надо будет на досуге провести, а сейчас же нам наиболее интересно содержимое директории [xl], конкретно — /xl/worksheets/ и файл /xl/sharedStrings.xml.
В файле /xl/workbook.xml лежит описание листов, но т.к. задачи собрать названия листов не стояло, этот пункт я пропущу. При необходимости разобраться в нем не составит труда.

и так далее в том же духе. Представляет собой текстовые данные в ячейках исходного документа. Со всех листов! Пока просто соберем эти данные в массив.

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

Напоследок скажу, что толком в спецификации xlsx не разбирался, а только выполнил поставленную задачу с конкретными xlsx документами. Куда-то ведь должны писаться формулы и изображения (t=«i»?). Когда столкнусь с такой задачей — непременно опишу, а пока представляю нетребовательный к системе алгоритм для сбора текстовых данных из xslx. Надеюсь, будет востребован, т.к. в поисках подобного не встречал.

P.S. Только расставляя метки наткнулся на Работа с большими файлами экселя. Хабрить надо было, а не гуглить — много бы времени сэкономил.

Источник

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

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

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

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

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

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

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

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

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

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

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

Проверим установился ли модуль и перезагрузим веб-сервер.

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

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

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

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

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

Стоимость библиотеки 199$ за девелоперскую лицензию, но, поверьте, это того стоит. Безусловно рекомендуем всем, кто сталкивается с проблемой импорта Excel-файлов на свой сервис.

Источник

Обработка и оформление отчетов в Excel на PHP

Не редко при разработке некоего проекта, возникает необходимость в формировании отчетной статистики. Если проект разрабатывается на Delphi, C# или к примеру, на С++ и под Windows, то тут проблем нет. Всего лишь необходимо воспользоваться COM объектом. Но дела обстоят иначе, если необходимо сформировать отчет в формате excel на PHP. И чтобы это творение функционировало на UNIX-подобных системах. Но, к счастью, не так все плохо. И библиотек для этого хватает. Я свой выбор остановил на PHPExcel. Я уже пару лет работаю с этой библиотекой, и остаюсь доволен. Поскольку она является кроссплатформенной, то не возникает проблем с переносимостью.

PHPExcel позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8.

Установка библиотеки

Для работы необходима версия PHP 5.2.0 или выше. А также необходимы следующие расширения: php_zip, php_xml и php_gd2. Скачать библиотеку можно отсюда.

С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:

Импорт данных из PHP в Excel

Рассмотрим пример по формированию таблицы умножения.

Далее нам необходимо получить наш *.xls файл. Здесь можно пойти двумя путями. Если предположим у вас интернет магазин, и клиент хочет скачать прайс лист, то будет лучше прибегнуть к такому выводу:

Здесь сформированные данные сразу “выплюнутся” в браузер. Однако, если вам нужно файл сохранить, а не “выбросить” его сразу, то не нужно выводить HTTP-заголовки и вместо “php://output” следует указать путь к вашему файлу. Помните что каталог, в котором предполагается создание файла, должен иметь права на запись. Это касается UNIX-подобных систем.

Рассмотрим еще на примере три полезные инструкции:

Также обратите внимание на следующие необходимые для работы с отчетом методы:

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

Оформление отчета средствами PHP в Excel

Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.

Назначить стиль ячейке можно тремя способами:

Заливка

Значением параметра fill является массив со следующими необязательными параметрами:

Или можно использовать следующие методы:

Вставка изображений

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

Код демонстрирующий алгоритм вставки изображения приведен ниже:

Вот так выглядит отчет со вставленным изображением:

Шрифт

В качестве значения параметра font указывается массив, который содержит следующие необязательные параметры:

Или воспользоваться следующими методами:

Рамка

В качестве значения параметра borders указывается массив, который содержит следующие необязательными параметры:

Так же можно прибегнуть к использованию следующих методов:

$PHPExcel_Style->getBorders()->getLeft()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getRight()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getTop()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getBottom()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getDiagonal()->applyFromArray(array(‘style’ => PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->setDiagonalDirection(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′))).

Выравнивание

Значением параметра alignment является массив, который принимает на вход четыре необязательных параметра:

Или использовать следующие методы:

Формат представления данных

Параметр numberformat представляет собой массив, который включает только один параметр: code — формат данных ячейки.

Список возможных форматов

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

А можно и воспользоваться методом:

Защита ячеек

В качестве значения параметра protection выступает массив, который содержит два необязательных параметра:

Или использовать следующие методы:

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

Далее мы применим созданный нами стиль к ячейкам excel.

Сейчас применим тот же стиль, но используя другую методику.

Вот что у нас получилось:

Для получения данных о стиле конкретной ячейки необходимо использовать один из следующих методов, который вернет экземпляра класса PHPExcel_Style:

А теперь рассмотрим третий способ назначения стиля ячейкам путем дублирования стиля. Пример использования представлен ниже (предполагается, что к ячейке “B2” применен некий стиль и мы его хотим продублировать для диапазона ячеек “F2:F10”):

Добавление комментариев

Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:

Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:

Вот какой комментарий мы должны получить:

Вставка ссылки

Вставка ссылок в ячейку тоже не вызывает каких-либо затруднений, что можно видеть из нижеописанного примера:

Так же в виде ссылки может быть использован, к примеру, email адрес: mailto:example@mail.com.

Чтение данных из Excel

Формировать отчеты и применять к ним стили это конечно отлично. Но на этом возможности библиотеки PHPExcel не заканчиваются. Ну что же, посмотрим на что она еще способна. А способна она еще и читать данные из файлов формата *.xls / *.xlsx.

С помощью библиотеки PHPExcel можно читать следующие форматы:

Для работы нам понадобятся объекты двух классов:

Для демонстрации выведем данные из таблицы с информацией об автомобилях.

Пример чтения файла представлен ниже:

Первый вариант

Второй вариант

В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet:

Другие полезные методы

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

Примечание: Методы stringFromColumnIndex и columnIndexFromString примечательны тем, что их можно использовать без создания объекта класса. Пример использования представлен ниже:

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

Источник

Как считать Excel файл PHP скриптом?

Задача у меня не сложная, но что-то не получается реализовать. Может сталкивался кто, поможет?

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

Нашел фреймворк PHPExcel, но не получается в нем разобраться, что-то не так делаю.

Как-то сталкивался с таким заданием. Считать файл excel гораздо проще чем создать и делается это так.

Я пользовался упомянутой вами библиотекой PHPExcel. Скачиваем последнюю версию библиотеки по этой ссылке.

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

Пример использования:

Предположим, наш файл file.xlsx имеет путь www/site.ru/module/file.xlsx и содержит 3 столбца данных: дата, ключ, значение и 5 строк с заполненными данными. Тогда считывая файл нашей созданной функцией:

Мы получим такой массив:

Тут каждый элемент массива это строка и заполненные колонки в ней.

П.С. Для работы библиотеки ваш хостинг должен удовлетворять следующим требованиям:

версия PHP 5.2 или выше
Включенное расширение PHP php_zip
Включенное расширение PHP php_xml
Включенное расширение PHP php_gd2

Источник

Как получить массив групп товаров при обработке файла excel

Подскажите как можно получить массив сгруппированных товаров из файла excel.

Я обрабатываю файл, но разбиения на группы нет, выдает просто по порядку все строки.

1 ответ 1

Нашел решение, как получить уровень вложенности или многоуровневую группировку строк. Использовал PHPExcel т.к. подходит для старых версий PHP. PhpSpreadsheet идет от версии PHP 5.6.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками php excel phpexcel xls xlsx или задайте свой вопрос.

Похожие

Подписаться на ленту

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

дизайн сайта / логотип © 2021 Stack Exchange Inc; материалы пользователей предоставляются на условиях лицензии cc by-sa. rev 2021.9.16.40232

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Источник