Php excel объединить ячейки
Привет, Хабр!
Мне по работе часто приходится импортировать Excel-документы средствами PHP.
Для этого я использую библиотеку PHPExcel, которая на сегодняшний день является весьма удобным инструментом.
Но есть несколько «скользких» моментов, связанных с чтением данных из ячеек, о которых я хочу рассказать хабрачитателям, а также разобрать функцию, решающую эти проблемы.
1. Адресация ячеек
- колонка и ряд в виде строки: «A1»
- колонка буквой, ряд числом: («A», 1)
- колонка и ряд числом: (1, 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() не смогла отработать и вернула не значение, а саму формулу (первый символ " php">$val = $cell->getValue(); if((substr($val,0,1) === '=' ) && (strlen($val) > 1))< $val = $cell->getOldCalculatedValue(); >
Результат
В итоге мы получили функцию, которая позволяет универсально считывать значения ячеек:
Ложка дегтя
Важно отметить, что использование пунктов 2, 3 и 4 работает только в режиме ReadDataOnly = false. Это режим PHPExcel по умолчанию, когда он считывает всю мета-информацию о книге. Подходит для стандартных небольших документов, например счета, накладные и.т.д.
Включение ReadDataOnly = true может потребоваться для громоздких файлов, когда нужны только значения ячеек. По моей практике, такие файлы содержат отформатированные таблицы и там такой функционал не нужен.
Размеры ячеек
Вставленный в ячейку длинный текст будет выходить за ее пределы, переносы \r\n работать не будут.
Метод setWrapText(true) включает переносы строк и авто высоту.
Объединение ячеек
Стили текста
Также можно задать сразу несколько стилей для ячейки, массивом:
Установить стили шрифта для всего документа:
Выравнивание в ячейке
Фон ячейки
Стили фона устанавливаются массивом значений, type – определяет стиль заливки, далее в зависимости от выбранного стиля задаются его настройки в параметрах color, startcolor, endcolor, rotation.
Границы
Стили линий задаются костантами:
Внешняя рамка у ячеек
Внутриния рамка у ячеек
Таблица
Таблица с жирной рамкой
Отдельные линии
Изображения
Гиперссылки
Формулы
Русские названия функций не поддерживаются, придется использовать латинские аналоги.
Сохранение
Отдача на скачивание:
Если возникает ошибка – «Uncaught exception ‘PHPExcel_Writer_Exception’ with message ‘Could not close zip file php://output.’», то следует сделать скачивание через временный файл:
PHPExcel позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8.
Установка библиотеки
С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:
- Excel 2007;
- Excel 97 и поздние версии;
- PHPExcel Serialized Spreadshet;
- HTML;
- PDF;
- CSV.
Импорт данных из PHP в Excel
Рассмотрим пример по формированию таблицы умножения.
Рассмотрим еще на примере три полезные инструкции:
- $sheet->getColumnDimension('A')->setWidth(40) – устанавливает столбцу “A” ширину в 40 единиц;
- $sheet->getColumnDimension('B')->setAutoSize(true) – здесь у столбца “B” будет установлена автоматическая ширина;
- $sheet->getRowDimension(4)->setRowHeight(20) – устанавливает четвертой строке высоту равную 20 единицам.
Также обратите внимание на следующие необходимые для работы с отчетом методы:
- Методы для вставки данных в ячейку:
- setCellValue([$pCoordinate = 'A1' [, $pValue = null [, $returnCell = false]]]) — принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр эта одна из констант типа boolean: true или false (если передать значение true, то метод вернет объект ячейки, иначе объект рабочего листа);
- setCellValueByColumnAndRow([$pColumn = 0 [, $pRow = 1 [, $pValue = null [, $returnCell = false]]]]) — принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue().
- getCell([$pCoordinate = 'A1']) — принимает в качестве параметра координату ячейки;
- getCellByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) — принимает два параметра в виде номеров столбца и строки ячейки.
Как мы видим, вышеприведенные методы являются парными. Поэтому мы можем работать с ячейками используя строковое или числовое представление координат. Что конечно же является дополнительным преимуществом в работе.
Оформление отчета средствами PHP в Excel
Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.
Назначить стиль ячейке можно тремя способами:
- Использовать метод applyFromArray, класса PHPExcel_Style. В метод applyFromArray передается массив со следующими параметрами:
- fill — массив с параметрами заливки;
- font — массив с параметрами шрифта;
- borders — массив с параметрами рамки;
- alignment — массив с параметрами выравнивания;
- numberformat — массив с параметрами формата представления данных ячейки;
- protection — массив с параметрами защиты ячейки.
- pCellStyle – данный параметр является экземпляром класса PHPExcel_Style;
- pRange – диапазон ячеек.
Заливка
Значением параметра fill является массив со следующими необязательными параметрами:
- type — тип заливки;
- rotation — угол градиента;
- startcolor — значение в виде массива с параметром начального цвета в формате RGB;
- endcolor — значение в виде массива с параметром конечного цвета в формате ARGB;
- color — значение в виде массива с параметром начального цвета в формате RGB.
Или можно использовать следующие методы:
Вставка изображений
Довольно редко, но бывает полезным произвести вставку изображения в отчет. Это может быть логотип, схема и т.д. Для работы нам понадобятся следующие методы:
- setPath([$pValue = '', [$pVerifyFile = true]]) — данный метод принимает два параметра. В качестве первого параметра указывается путь к файлу с изображением. А второй параметр имеет смысл указывать, если необходимо осуществлять проверку существования файла (может принимать одно из значений true или false).
- setCoordinates([$pValue = 'A1'])) — принимает на вход один параметр в виде строки с координатой ячейки.
- setOffsetX([$pValue = 0]) — принимает один параметр со значением смещения по X от левого края ячейки.
- setOffsetY([$pValue = 0]) — принимает один параметр со значением смещения по Y от верхнего края ячейки.
- setWorksheet([$pValue = null, [$pOverrideOld = false]]) — этот метод принимает на вход два параметра. Первый является обязательным, а второй нет. В качестве первого параметра указывается экземпляр класса PHPExcel_Worksheet (текущий активный лист). Если в качестве значения второго параметра передать true, то если лист уже был назначен ранее – произойдет его перезапись и соответственно изображение удалится.
Код демонстрирующий алгоритм вставки изображения приведен ниже:
Вот так выглядит отчет со вставленным изображением:
Шрифт
В качестве значения параметра font указывается массив, который содержит следующие необязательные параметры:
- name — имя шрифта;
- size — размер шрифта;
- bold — выделять жирным;
- italic — выделять курсивом;
- underline — стиль подчеркивания;
- strike — перечеркнуть;
- superScript — надстрочный знак;
- subScript — подстрочный знак;
- color — значение в виде массива с параметром цвета в формате RGB.
Или воспользоваться следующими методами:
Рамка
В качестве значения параметра borders указывается массив, который содержит следующие необязательными параметры:
- тип рамки — (top|bootom|left|right|diagonal|diagonaldirection);
- style — стиль рамки;
- color — значение в виде массива с параметром цвета в формате RGB.
Так же можно прибегнуть к использованию следующих методов:
$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 является массив, который принимает на вход четыре необязательных параметра:
- horizontal — константа горизонтального выравнивания;
- vertical — константа вертикального выравнивания;
- rotation — угол поворота текста;
- wrap — разрешить перенос текста;
- shrinkToFit — изменять ли размер шрифта при выходе текста за область ячейки;
- indent — отступ от левого края.
Или использовать следующие методы:
Формат представления данных
Параметр numberformat представляет собой массив, который включает только один параметр: code — формат данных ячейки.
А можно и воспользоваться методом:
Защита ячеек
В качестве значения параметра protection выступает массив, который содержит два необязательных параметра:
- locked — защитить ячейку;
- hidden — скрыть формулы.
Или использовать следующие методы:
Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это тремя способами. Первый способ заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray, класса PHPExcel_Style.
Далее мы применим созданный нами стиль к ячейкам excel.
Сейчас применим тот же стиль, но используя другую методику.
Вот что у нас получилось:
Для получения данных о стиле конкретной ячейки необходимо использовать один из следующих методов, который вернет экземпляра класса PHPExcel_Style:
- getStyleByColumnAndRow([$pColumn = 0 [, $pRow = 1]]) – применяется если требуется обратиться к ячейке по числовым координатам. Методу необходимо передать два параметра в виде номеров столбца и строки ячейки;
- getStyle([pCellCoordinate = 'A1']) – используется для обращения по строковой координате ячейки. Методу требуется передать один параметр, это строковое представление координаты.
А теперь рассмотрим третий способ назначения стиля ячейкам путем дублирования стиля. Пример использования представлен ниже (предполагается, что к ячейке “B2” применен некий стиль и мы его хотим продублировать для диапазона ячеек “F2:F10”):
Добавление комментариев
Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:
Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:
- getFont() – возвращает объект класса для работы со шрифтами PHPExcel_Style_Font.
- setFont([$pFont = null]))]) – данному методу требуется передать в качестве параметра объект класса PHPExcel_Style_Font.
Вот какой комментарий мы должны получить:
Вставка ссылки
Вставка ссылок в ячейку тоже не вызывает каких-либо затруднений, что можно видеть из нижеописанного примера:
Чтение данных из Excel
Формировать отчеты и применять к ним стили это конечно отлично. Но на этом возможности библиотеки PHPExcel не заканчиваются. Ну что же, посмотрим на что она еще способна. А способна она еще и читать данные из файлов формата *.xls / *.xlsx.
С помощью библиотеки PHPExcel можно читать следующие форматы:
- Excel 2007;
- Excel 5.0/Excel 95;
- Excel 97 и поздние версии;
- PHPExcel Serialized Spreadshet;
- Symbolic Link;
- CSV.
Для работы нам понадобятся объекты двух классов:
- PHPExcel_Worksheet_RowIterator – используется для перебора строк;
- PHPExcel_Worksheet_CellIterator – используется для перебора ячеек.
Для демонстрации выведем данные из таблицы с информацией об автомобилях.
Пример чтения файла представлен ниже:
Первый вариант
Второй вариант
В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet:
- getHighestColumn() – возвращает символьное представление последнего занятого столбца в активном листе. Обратите внимание: не индекс столбца, а его символьное представление (A, F и т.д.);
- getHighestRow() – возвращает количество занятых строк в активном листе.
Другие полезные методы
Возможностей по работе с отчетами формата excel с использованием PHP как мы видим, достаточно много. Но мы рассмотрим еще несколько полезных методов, которые могут оказаться весьма полезны в работе:
- getMergeCells() – с помощью данного метода принадлежащего классу PHPExcel_Worksheet можно получить информацию обо всех объединенных ячейках в листе;
- setPreCalculateFormulas([$pCellStyle = true]) – данный метод необходимо использовать если требуется произвести расчет формул в листе (он имеется у двух классов: PHPExcel_Writer_Excel5 и PHPExcel_Writer_Excel2007). В рассматриваемый метод передается параметр типа boolean: true или false (если передать значение true, то расчет формул произойдет перед сохранением файла автоматически, иначе расчета формул не последует). Использование данного метода может оказаться полезным если созданный файл потребуется загрузить, к примеру на Google Drive. Ведь в таком случае расчет формул не будет произведен автоматически указанным сервисом и здесь вся ответственность ложиться на нас;
- stringFromColumnIndex([$pColumnIndex = 0]) – данный метод позволяет определить по номеру столбца его символьное представление, для этого в качестве параметра необходимо передать его номер;
- columnIndexFromString([$pString = 'A']) – с помощью данного метода можно определить номер столбца по его символьному представлению, для этого в качестве единственного параметра необходимо передать его обозначение.
Примечание: Методы stringFromColumnIndex и columnIndexFromString примечательны тем, что их можно использовать без создания объекта класса. Пример использования представлен ниже:
С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.
В данной статье я бы хотел рассмотреть библиотеку, которая позволяет создавать файлы формата xls и xlsx. Библиотека PhpSpreadsheet является по сути продолжением популярной библиотеки PHPExcel и разрабатывается теми же разработчиками, которые сделали PHPExcel , и, поскольку PHPExcel больше не поддерживается, использовать я буду именно PhpSpreadsheet.
Установка PhpSpreadsheet и требования
Прежде чем начать установку убедитесь, что у вас установлен composer (более подробно про установку composer можно прочесть здесь), а также установлена версия PHP не ниже 7.1. Затем для установки библиотеки выполняем следующую команду:
composer require phpoffice/phpspreadsheet
После того, как composer установит библиотеку и все ее зависимости можно приступать к формированию excel файлов.
Работа с библиотекой, запись данных и создание xlsx файлов
В рамках данной статьи мы познакомимся с некоторыми основными возможностями библиотеки, для более детального ее изучения можно ознакомиться с примерами, которые расположены в папке samples самой библиотеки.
Самый простой пример создания файла может выглядеть следующим образом:
Чтобы создать файл с расширением xls, а не xlsx, достаточно заменить класс Xlsx на Xls и изменить расширение в названии создаваемого файла.
Если при открытии файла с расширением xls возникают ошибки, необходимо проверить в настройках PHP параметр mbstring.func_overload. Для корректной работы библиотеки с расширением файлов xls необходимо, чтобы он был установлен в mbstring.func_overload = 0.
Установка стилей ячеек
Для установки стилей ячеек проще всего использовать метод applyFromArray, который принимает массив параметров. С его помощью можно задать шрифт, цвет текста, границы, выравнивание текста и другие параметры:
Исключение обработано просто для демонстрации в случае, если не удастся создать файл.
Установка размеров и объединение ячеек
Высота и ширина устанавливается на строку и столбец соответственно. Для установки высоты используется метод setRowHeight, а для установки ширины метод setWidth. Чтобы объединить несколько ячеек необходимо использовать метод mergeCells:
Как видно из примера для установки высоты и ширины предварительно необходимо вызвать вспомогательные методы getColumnDimension и getRowDimension.
Добавление картинок
Для добавления картинок используется класс Drawing:
Помимо тех возможностей, что используются в примере, также можно задавать тени у картинок, вращать их и т.д. Более подробно можно обратиться к примерам библиотеки. Также стоит отметить, что путь до файла с картинкой необходимо задавать относительно сервера на котором запускается скрипт создания ваших таблиц. Указать путь в виде ссылки на сторонний ресурс не удастся (на сколько мне известно), для этого сначала необходимо скачать данную картинку на ваш сервер и только потом указывать путь до картинки относительно текущего сервера.
Арифметические операции
В библиотеке доступно большое количество операций для выполнения расчетов, таких как: нахождение суммы чисел, нахождение среднего значения, взятие максимального и минимального числа и многие другие вычисления. В примере ниже показано, как вычислить сумму чисел, среднее значение и нахождение максимального из чисел:
Узнать более подробно о всех возможных операциях можно из примеров библиотеки.
Резюме
Создание файлов для excel на php не является такой сложной процедурой благодаря данной библиотеке. В ней доступно большое количество необходимых возможностей, которых должно быть достаточно для генерация различного рода отчетов и документов. Для более старых версий PHP можно использовать предыдущую версию данной библиотеки PHPExcel. Также хотел отметить, что при использовании PhpSpreadsheet были случаи, когда с помощью метода save класса Xlsx не удавалось сохранять файл в выходной буфер php://output, как правило эта ошибка связана с ограниченным доступом к временной папке. Для решения данной проблемы я сохранял файл в папку на сервере, а потом с помощью функции readfile отправлял его в буфер вывода.
В данной статье я бы хотел рассмотреть библиотеку, которая позволяет создавать файлы формата xls и xlsx. Библиотека PhpSpreadsheet является по сути продолжением популярной библиотеки PHPExcel и разрабатывается теми же разработчиками, которые сделали PHPExcel , и, поскольку PHPExcel больше не поддерживается, использовать я буду именно PhpSpreadsheet.
Установка PhpSpreadsheet и требования
Прежде чем начать установку убедитесь, что у вас установлен composer (более подробно про установку composer можно прочесть здесь), а также установлена версия PHP не ниже 7.1. Затем для установки библиотеки выполняем следующую команду:
composer require phpoffice/phpspreadsheet
После того, как composer установит библиотеку и все ее зависимости можно приступать к формированию excel файлов.
Работа с библиотекой, запись данных и создание xlsx файлов
В рамках данной статьи мы познакомимся с некоторыми основными возможностями библиотеки, для более детального ее изучения можно ознакомиться с примерами, которые расположены в папке samples самой библиотеки.
Самый простой пример создания файла может выглядеть следующим образом:
Чтобы создать файл с расширением xls, а не xlsx, достаточно заменить класс Xlsx на Xls и изменить расширение в названии создаваемого файла.
Если при открытии файла с расширением xls возникают ошибки, необходимо проверить в настройках PHP параметр mbstring.func_overload. Для корректной работы библиотеки с расширением файлов xls необходимо, чтобы он был установлен в mbstring.func_overload = 0.
Установка стилей ячеек
Для установки стилей ячеек проще всего использовать метод applyFromArray, который принимает массив параметров. С его помощью можно задать шрифт, цвет текста, границы, выравнивание текста и другие параметры:
Исключение обработано просто для демонстрации в случае, если не удастся создать файл.
Установка размеров и объединение ячеек
Высота и ширина устанавливается на строку и столбец соответственно. Для установки высоты используется метод setRowHeight, а для установки ширины метод setWidth. Чтобы объединить несколько ячеек необходимо использовать метод mergeCells:
Как видно из примера для установки высоты и ширины предварительно необходимо вызвать вспомогательные методы getColumnDimension и getRowDimension.
Добавление картинок
Для добавления картинок используется класс Drawing:
Помимо тех возможностей, что используются в примере, также можно задавать тени у картинок, вращать их и т.д. Более подробно можно обратиться к примерам библиотеки. Также стоит отметить, что путь до файла с картинкой необходимо задавать относительно сервера на котором запускается скрипт создания ваших таблиц. Указать путь в виде ссылки на сторонний ресурс не удастся (на сколько мне известно), для этого сначала необходимо скачать данную картинку на ваш сервер и только потом указывать путь до картинки относительно текущего сервера.
Арифметические операции
В библиотеке доступно большое количество операций для выполнения расчетов, таких как: нахождение суммы чисел, нахождение среднего значения, взятие максимального и минимального числа и многие другие вычисления. В примере ниже показано, как вычислить сумму чисел, среднее значение и нахождение максимального из чисел:
Узнать более подробно о всех возможных операциях можно из примеров библиотеки.
Резюме
Создание файлов для excel на php не является такой сложной процедурой благодаря данной библиотеке. В ней доступно большое количество необходимых возможностей, которых должно быть достаточно для генерация различного рода отчетов и документов. Для более старых версий PHP можно использовать предыдущую версию данной библиотеки PHPExcel. Также хотел отметить, что при использовании PhpSpreadsheet были случаи, когда с помощью метода save класса Xlsx не удавалось сохранять файл в выходной буфер php://output, как правило эта ошибка связана с ограниченным доступом к временной папке. Для решения данной проблемы я сохранял файл в папку на сервере, а потом с помощью функции readfile отправлял его в буфер вывода.
Читайте также: