php array to 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. Только расставляя метки наткнулся на Работа с большими файлами экселя. Хабрить надо было, а не гуглить — много бы времени сэкономил.
WebGuruz
Создание Excel документа XLSX на PHP с помощью PHPExcel
Часто на сайтах требуется выгрузка каких либо данных в формате excel (xslx). Естественно все хотят чтоб документ был уже сразу на выходе красиво оформлен. Недавно и мне потребовалось сделать такую выгрузку на php. Принято было решение использовать библиотеку PHPExcel – ее можно скачать тут http://phpexcel.codeplex.com
В скачанном вами архиве очень много документации и примеров, библиотека позволяет все от простого форматирования текста и формата ячеек до создания красивых графиков.
В моем случае, нужно было просто закрасить ячейку в нужный цвет в зависимости от значения в данной ячейке.
Подключаем библиотеку, указываем данные нашего документа:
Далее инициализируем возможные стили для ячеек, я указал border(границы) и background (закраску цветом):
Далее нам нужно внести данные в таблицу excel, делается это очень просто. Для того чтоб было быстрее я использовал метод fromArray:
А лишь потом делал закраску ячеек цветом ($data_arr – массив с данными)
Для 10 тысяч строк этот случай обрабатывался гораздо быстрее чем тот, где мы построчно заносим данные в xlsx документ:
Так же можно вручную указать ширину колонке, например самая первая колонка у меня должна была быть немного шире чем обычно:
Форматирование закончено, можем сохранить файл созданный с помощью php генератора excel файла в формате xlsx:
PHP библиотека PHPExcel для работы с Excel
Для работы с Excel использовал библиотеку PHPExcel. Установка простейшая – кладем папку Classes в нужную папку на сервере, указываем корректные пути в include/require.
Примеры кода по чтению/генерации файлов Excel можно посмотреть на github странице библиотеки.
Красивости
и этим не ограничивается функционал, это лишь то, что использовал:
ЧТЕНИЕ
Код для чтения (два столбца):
Редактирование
Открываем файл test.xlsx, на его основе создаем новый new.xlsx с измененными парой ячеек.
ГЕНЕРАЦИЯ
Пример генерации на основе результата MySQL (не тестил, использовал универсальную функцию ниже).
Если нужно протестить базовую работу генерации на основе двумерного массива
Пример генерации xls из двумерного массива с настройками
Пример вызова и код по генерации кнопки, добавлению к названию файла даты/времени (формат 20170123_003800_Название.xlsx) и переходу на страницу генерации xls.
Вызов функции (про функцию iconv_for_xls ниже):
Особенности
Мусор
Нужно очень внимательно смотреть, чтобы php не генерировал никакого вывода, в противном случае возможны разные ошибки и искажения файла.
Например, возможны кракозябры при генерации файла больше 20-40 строк, если оставить включенным debug типа print_r($_POST); (почему не воспроизводиться при генерации мелких файлов – это вопрос :)).
Аналогично, будет выдавать ошибку:
/XMLWriter.php on line 37” т.к. библиотека PHPExcel использует наследование класса от XMLWriter (“class PHPExcel_Shared_XMLWriter extends XMLWriter”), т.е. требует установленного модуля xmlwriter. Для “нормальных” дистрибутивов это делается простым sudo apt-get/yum install php-xmlwriter (или php5-xml) и перезагрузкой apache, для gentoo это делается через пересборку всего php с новым модулем.
Отправка array на другую страницу
Чтобы функция по генерации xlsx была универсальной, сделал отдельную страницу по генерации, но на эту страницу потребовалось передать двумерный массив. В интернете гуляет два варианта решения: сохранить массив в сессии/куках, передать его через json (лучше) или serialize (хуже).
Через сессии все передавалось, только значение почему то не апдейтилось корректно. Сходу не разобрался в причинах, использовал сначала serialize (полный пример см. в function create_xls), но потом мигрировал на json из-за периодических проблем со спец. символами serialize.
Кодировка
Если на сайте кодировка cp-1251, то при генерации обязательно нужно использовать iconv в utf-8. В противном случае вместо русских символов в ячейке будет бред (например, “ИСТИНА”).
searbe / parse_xlsx.php
This comment has been minimized.
Copy link Quote reply
lifecost commented Jun 20, 2013
This comment has been minimized.
Copy link Quote reply
robmcalister commented Aug 28, 2013
This works very well. Thanks for sharing it.
This comment has been minimized.
Copy link Quote reply
bcre8ve commented Nov 20, 2013
Thanks for sharing. I like this approach as it 1) is light and 2) allows better control.
In my particular case where I was implementing it, I had issues due to blank/null cells so my values didn’t always fall under the correct header. I was able to solve it with the following pieces of code:
Update it with this code as the last line of the same foreach loop:
This adds a column reference to be used as a key for each array member (for example: ‘A’, ‘B’, ‘C’, etc) The preg_replace simply removes the row reference (‘A1’ becomes ‘A’).
That should do it. Each row of the first array will have the row number as its key. And each column array will contain all header titles as keys even if the original cell was blank/null.
Also, to see your headers, use this code at the end of the function:
Or to see your finished array, use this:
Maybe this will help someone.
This comment has been minimized.
Copy link Quote reply
macinnir commented Nov 26, 2013
This is most helpful. Thank you.
This comment has been minimized.
Copy link Quote reply
fabianledefyl commented Jan 30, 2014
I use this script and it worked properly!, but I have a problem with dates, for example if I have a date 20/12/2012 in the xlsx document, worksheet 41263 write me, and I did not realize that conversion does.
anyone have any idea how I can fix this.
This comment has been minimized.
Copy link Quote reply
iliyakolev commented Sep 7, 2015
Hi.
Thanks for the great script.
Sometimes when there are empty cells they are missing in the xml which makes the script misplace the cell.
Example:
179 4568 180 181 5 6 7 8 4565 4566 4567 4569 1 1 13 104 2 0 182 104 104 104 104 104 104
This comment has been minimized.
Copy link Quote reply
darkworks commented Aug 25, 2016
what about comments and text strikes etc
This comment has been minimized.
Copy link Quote reply
lubosdz commented Nov 8, 2016
Here are two PHP functions to convert Excel dates into days, months, years:
This comment has been minimized.
Copy link Quote reply
makantayebi commented May 28, 2017
This comment has been minimized.
Copy link Quote reply
towony commented Jan 4, 2018
I want to be very grateful. I wanted a very simple one. You’ve already made it. I needed a little modification. thanks again
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Как считать 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