php библиотека для xlsx
Разбираем 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. Только расставляя метки наткнулся на Работа с большими файлами экселя. Хабрить надо было, а не гуглить — много бы времени сэкономил.
С помощью PHPExcel можно производить чтение и запись информации в файлы, форматировать их содержимое,
выполнять операции с формулами, стилями и т.д.
Возможности библиотеки PHPExcel:
1. PHPExcel может читать различные форматы файлов электронных таблиц:
2. Создание и обработка документов средствами PHP
Установка PHPExcel:
1) Скачать библиотеку с официального сайта http://phpexcel.codeplex.com/
2) В скачанном архиве, из папки classes скопировать содержимое в корень сайта
Состав Архива:
Пример чтения Excel файла при помощи класса PHPExcel
Для чтения данных с Excel файла необходимо подключить файл библиотеки PHPExcel и используется метод load() класса PHPExcel_IOFactory. Этот метод собирает данные всех со всех листов текущего документа:
Далее формируем массив из всех листов Excel файла с помощью цикла:
Вывод сформированного массива в виде HTML таблиц(ы) :
Основные методы и свойства класса PHPExcel
Класс PHPExcel – это ядро PHPExcel. Он содержит ссылки на содержащиеся листы, параметры безопасности документов и мета-данные документов. Помимо вышеизложенного, этот класс включает в себя множество других полезных методов для обработки получаемых данных. Некоторые из них будут описаны ниже.
getSheetCount() – возвратит количество листов в книге;
getSheetNames() – возвращает список всех листов в рабочей книге, индексируя их в порядке, в котором их «вкладки» появляются при открытии в MS Excel (или других программах для обработки электронных таблиц);
getSheet(N) – используется для доступа к листу по индексу. Индекс позиции (N) представляет собой порядок, в котором отображаются вкладки листов, когда книга открыта в MS Excel (или других программах для обработки электронных таблиц);
getSheetByName(list_name) – метод используется для доступа к листу по его имени (list_name);
getActiveSheet() – этот метод позволяет получить доступ к активному листу документа напрямую. Активным листом является тот, который будет активен, когда откроется книга в MS Excel (или другой соответствующей программе электронных таблиц);
setActiveSheetIndex() и setActiveSheetIndexByName() – с помощью этих методов можно назначить лист, который будет активным по его индексу или имени.
Получение значения ячейки по координатам
Для получения значения ячейки, сперва должна быть найдена ячейка путем использования метода getCell. Значение ячейки может быть получено с помощью следующей команды:
В случае если вам нужны подсчитанные значения ячейки, вместо getValue(), используйте метод getCalculatedValue():
Получение значения ячейки по строкам и столбцам
Для получения значения ячейки, в листе сначала должна быть найдена ячейка с использованием метода getCellByColumnAndRow. Значение ячейки может быть получено с помощью следующей команды:
Заключение
В этой статье были рассмотрены основные возможности библиотеки PHPExcel в области чтения excel файлов. Помимо всего выше изложенного, есть возможность более гибко использовать эту библиотеку для работы с различными параметрами таблицы. По этим вопросам рекомендуется обращаться к документации. Библиотека PHPExcel так же адаптирована для работы с другими типами файлов, таких как Excel (BIFF). XLS, Excel 2007 (OfficeOpenXML). XLSX, CSV, Libre / OpenOffice Calc. ODS, Gnumeric, PDF и HTML.
Лучший способ сказать автору «СПАСИБО» рассказать друзьям или поделиться ссылкой!
Другие статьи:
В этой статье Мы покажем Вам, как сделать так, чтобы шапка сайта выезжала при прокрутке страницы вверх, но при этом скрывалась при прокрутке страницы вниз, чтобы не загораживать нам просмотр самой страницы.
DateTimePicker – удобный и легкий в использовании плагин для быстрой подстановки даты и времени в поля ввода.
В этой статье будет рассмотрено решение этой задачи с использованием плагина Masked Input, который отличается простотой и удобством в использовании.
Метод, который мы рассмотрим ниже, позволяет размещать информацию на страницах любого пользователя или группы,к которому имеет доступ авторизованный пользователь.
Текстовый редактор TinyMCE в визуальном режиме при стандартной конфигурации обрезает некоторый теги и свойства.
Научимся вставлять теги в выделенный фрагмент текста, вставлять теги на место селектора в тексте и получать средствами JS HTML код отредактированного текста и текст без HTML сущностей.
Как вы уже поняли, редактор является платформенным Javascript редактором HTML кода, т.е. WYSIWYG редактором для сайтов и веб приложений. Разработчиком этого полезного продукта является компания Moxiecode Systems AB.
В статье мы: познакомимся с каруселью; научимся интегрировать ее на свой сайт; познакомимся с основными параметрами инициализации;рассмотрим самые популярные свойства и методы классов jCarousel
Любой backend программист php сталкивается с задачей автоматизации почтовой рассылки на веб сайтах и многие,кто сталкивался с данной задачей впервые, вставали перед ключевым вопросом: «Писать или не писать smtp клиент с нуля?».
PHPExcel и большие файлы
PHPExcel — отличная библиотека с огромным функционалом по работе с форматами xls, xlsx. Можно считывать, записывать, менять форматирование, задавать формулы, а из xlsx можно и картинки вытаскивать.
Чтение файла
25 000 строк) я использовал комплексное решение.
Во-первых, считываем файл не целиком, а по несколько строк. Это делается так:
Собственно класс chunkReadFilter — это то, что нам нужно. Устанавливаем его в качестве фильтра для чтения файла, и файл будет загружаться не целиком, а лишь определенное количество строк.
Во-вторых, кроме него еще используем такую полезную опцию как ReadDataOnly. Как следует из названия, она позволяет не загружать форматирование документа, высвобождая место для данных.
И, в-третьих, конечно же используем unset(). Это так же поможет высвободить память.
Но помимо нехватки памяти возникает другая проблема. На большинстве shared-хостингов у php-скриптов помимо ограничения на использование памяти, еще стоит ограничение на время выполнения. И крайне вероятно, что этого времени хватать не будет. Лично я обошел эту проблему при помощи сессий и повторяющихся ajax запросов. В коде представленном выше вы уже увидели использование сессий и завершающее «The End».
А вот код клиентской части.
Т.е. мы отправляем ajax-запрос нашему скрипту import_xls.php, ждем ответа, и, если ответ нас не устраивает, посылаем новый ajax-запрос. Встречал еще в сети решение без использования AJAX — при помощи редиректа в самом php. Обработка файла делится на малое количество строк и после этого вставляется код:
Но лично мне больше нравится решение с AJAX, потому что тут можно легко и просто добавить прогресс-бар и какие-нибудь другие рюшечки и плюшечки. Кстати, внимательный читатель заметил, что в моем коде простейший прогресс-бар уже реализован. Дополнительный немаловажный момент: в решение с AJAX не нужно заранее знать сколько строк скрипт сможет обработать за один проход.
Запись файла
Для записи файла в формате xls величиной так же около 25 000 строк крайне полезно использовать следующий код:
Можно еще поиграться с методами кеширования. Помимо кеширования во временной директории php еще поддерживается memcache:
А так же cache_to_discISAM.
UPDATE
Прошу у всех прощения за то, что код тут был представлен ужасно. Сразу не разобрался с подсветкой синтаксиса на Хабре (видимо, позднее время суток сказалось) и поторопился. Впредь не буду торопиться и буду умнее.
UPDATE2
Переделал JavaScript. Теперь он посылает запрос по таймауту в случае, если сервер не ответил.
Универсальное чтение ячеек в PHPExcel
1. Адресация ячеек
Но в PHPExcel нет универсальной функции получения ячейки любым из этих способов, есть только отдельные функции. Что ж, исправляем это упущение:
Сразу оговорюсь, что в приводимых мной примерах кода есть ссылки на $this, т.к. это методы моего класса-обертки над PHPExcel. В этом кусочке реализуются все три способа получения объекта ячейки.
2. Объединение ячеек
При чтении объединенных ячеек, PHPExcel возращает пустое значение для всех, кроме первой.
Т.е. для рисунка ниже значения B3 и C3 будут пустыми строками:
Мне всегда было неудобно такое поведение.
Гораздо удобнее (и логичнее!) для любой объединенной ячейки возвращать общее для них значение «mergedvalue».
Для этого при запросе значения нужно пройтись по всем объединенным диапазонам листа, и если заданная ячейка попадает в диапазон, то вернуть первую:
3. Даты
Как известно, Excel хранит даты как число дней с 1 января 1900 года. Поэтому при чтении ячейки B2 на скриншоте выше мы получим бесполезное 41044. Но есть и хорошая новость — в PHPExcel присутствует удобная функция PHPExcel_Shared_Date::ExcelToPHP(), которая превращает дату в формат php.
Осталось только применить эту функцию в нужный момент:
4. Формулы
В большинстве случаев стандартная функция $cell->getValue() корректно обрабатывает формулы и возвращает рассчитанное значение. Но бывают ситуации, когда формула ссылается на несуществующий лист или другой файл, который локально хранится у того, кто отправил вам excel-документ. Тогда getValue() возвратит ошибку, хотя визуально в экселе вы можете увидеть верное значение, если не было пересчета листа. Дело в том, что Excel сохраняет oldCalculatedValue, которое и используется, если не пересчитывать лист. На рисунке выше я показал это в ячейке B4 — она отображает старое значение, хотя ссылка в ней нерабочая.
PHPExcel, к счастью, тоже умеет хранить старое значение формулы. Это удобно использовать, когда getValue() не смогла отработать и вернула не значение, а саму формулу (первый символ «=»):
Результат
В итоге мы получили функцию, которая позволяет универсально считывать значения ячеек:
Для проверки прочитаем эксель со скриншота двумя способами: стандартной getValue (#1) и с использованием вышеописанной функции (#2):
Тест #1:
Тест #2:
Как видно, во втором случае все считалось корректно.
Ложка дегтя
Важно отметить, что использование пунктов 2, 3 и 4 работает только в режиме ReadDataOnly = false. Это режим PHPExcel по умолчанию, когда он считывает всю мета-информацию о книге. Подходит для стандартных небольших документов, например счета, накладные и.т.д.
Включение ReadDataOnly = true может потребоваться для громоздких файлов, когда нужны только значения ячеек. По моей практике, такие файлы содержат отформатированные таблицы и там такой функционал не нужен.
Установка режима чтения в PHPExcel делается так:
Работа с Excel средствами PHP
PHPExcel — библиотека для создания и чтения данных из файлов формата OpenXML (который используется в MS Excel 2007). С ее помощью можно считывать из файлов, записывать в файлы, форматировать содержимое, работать с формулами и т.д. Для работы PHPExcel требуется версия PHP 5.2 или выше, с установленными библиотеками Zip, XML и GD2.
Установка PHPExcel
Первым делом библиотеку необходимо скачать. Для этого переходим на официальный сайт библиотеки и скачиваем архив PHPExcel-1.7.8.zip. После распаковки мы получим несколько файлов и папок:
Файлы — это различные описания по предыдущим версиям, лицензионное соглашение и очень краткая инструкция по установке. Далее, в папке Classes, содержится непосредственно сама библиотека PHPExcel — эту папку необходимо скопировать в корень нашего скрипта.
В папке Documentation содержится документация по библиотеке на английском языке. В папке Tests — примеры по использованию библиотеки.
Создание Excel-файла
Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:
Настройки листа книги Excel
Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.
Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:
Если Вы захотите указать активным какой то другой лист, то вначале его необходимо создать, при помощи метода:
Затем, по аналогии, указываем индекс и получаем объект активного листа.
Далее при помощи метода setTitle(‘Прайс лист’) задаем название нашего листа.
Если нужно, можно при печати выводить шапку и подвал листа:
Обратите внимание на передаваемые параметры:
Затем указываем настройки шрифта по умолчанию:
Наполнение документа данными
Для начала давайте зададим ширину столбцов (в символьных единицах), которые нам понадобятся:
Теперь заполним несколько ячеек текстом:
Далее давайте в ячейку D4 запишем текущую дату:
Стилизация данных
Теперь, по аналогии, применим стили к остальным ячейкам:
Сохранение документа
Осталось только сохранить наш документ:
Если нужно вывести документ в браузер
Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.
Добавление формул
Формула Excel — это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке Excel может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.
Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы. Формулы Excel начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.
PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:
Чтение Excel-файла
Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив: