Объектом. Но дела обстоят иначе, если необходимо сформировать отчет в формате excel на PHP . И чтобы это творение функционировало на UNIX -подобных системах. Но, к счастью, не так все плохо. И библиотек для этого хватает. Я свой выбор остановил на PHPExcel . Я уже пару лет работаю с этой библиотекой, и остаюсь доволен. Поскольку она является кроссплатформенной, то не возникает проблем с переносимостью.
PHPExcel позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8
.
Установка библиотеки
Для работы необходима версия PHP 5.2.0 или выше. А также необходимы следующие расширения: php_zip, php_xml и php_gd2. Скачать библиотеку можно отсюда .
С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:
Рассмотрим пример по формированию таблицы умножения.
Здесь сформированные данные сразу “выплюнутся” в браузер. Однако, если вам нужно файл сохранить, а не “выбросить” его сразу, то не нужно выводить HTTP-заголовки и вместо “php://output” следует указать путь к вашему файлу. Помните что каталог, в котором предполагается создание файла, должен иметь права на запись. Это касается UNIX-подобных систем.
Рассмотрим еще на примере три полезные инструкции:
Также обратите внимание на следующие необходимые для работы с отчетом методы:
Как мы видим, вышеприведенные методы являются парными. Поэтому мы можем работать с ячейками используя строковое или числовое представление координат. Что конечно же является дополнительным преимуществом в работе.
Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.
Назначить стиль ячейке можно двумя способами:
Значением параметра fill является массив со следующими необязательными параметрами:
FILL_NONE | none |
FILL_SOLID | solid |
FILL_GRADIENT_LINEAR | linear |
FILL_GRADIENT_PATH | path |
FILL_PATTERN_DARKDOWN | darkDown |
FILL_PATTERN_DARKGRAY | darkGray |
FILL_PATTERN_DARKGRID | darkGrid |
FILL_PATTERN_DARKHORIZONTAL | darkHorizontal |
FILL_PATTERN_DARKTRELLIS | darkTrellis |
FILL_PATTERN_DARKUP | darkUp |
FILL_PATTERN_DARKVERTICAL | darkVertical |
FILL_PATTERN_GRAY0625 | gray0625 |
FILL_PATTERN_GRAY125 | gray125 |
FILL_PATTERN_LIGHTDOWN | lightDown |
FILL_PATTERN_LIGHTGRAY | lightGray |
FILL_PATTERN_LIGHTGRID | lightGrid |
FILL_PATTERN_LIGHTHORIZONTAL | lightHorizontal |
FILL_PATTERN_LIGHTTRELLIS | lightTrellis |
FILL_PATTERN_LIGHTUP | lightUp |
FILL_PATTERN_LIGHTVERTICAL | lightVertical |
FILL_PATTERN_MEDIUMGRAY | mediumGray |
Или можно использовать следующие методы:
$PHPExcel_Style->getFill()->setFillType(PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR);
$PHPExcel_Style->getFill()->setRotation(0);
$PHPExcel_Style->getFill()->getStartColor()->applyFromArray(array("rgb" => "C2FABD"));
$PHPExcel_Style->getFill()->getEndColor()->applyFromArray(array("argb" => "FFFFFFFF")).
Довольно редко, но бывает полезным произвести вставку изображения в отчет. Это может быть логотип, схема и т.д. Для работы нам понадобятся следующие методы:
Код демонстрирующий алгоритм вставки изображения приведен ниже:
Вот так выглядит отчет со вставленным изображением:
font указывается массив, который содержит следующие необязательные параметры:
UNDERLINE_NONE | нет |
UNDERLINE_DOUBLE | двойное подчеркивание |
UNDERLINE_SINGLE | одиночное подчеркивание |
Или воспользоваться следующими методами:
$PHPExcel_Style->getFont()->setName(‘Arial’);
$PHPExcel_Style->getFont()->setBold(true);
$PHPExcel_Style->getFont()->setItalic(false);
$PHPExcel_Style->getFont()->setSuperScript(false);
$PHPExcel_Style->getFont()->setSubScript(false);
$PHPExcel_Style->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_DOUBLE);
$PHPExcel_Style->getFont()->setStrikethrough(false);
$PHPExcel_Style->getFont()->getColor()->applyFromArray(array("rgb" => "808080"));
$PHPExcel_Style->getFont()->setSize(12).
В качестве значения параметра 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 является массив, который принимает на вход четыре необязательных параметра:
VERTICAL_BOTTOM | по нижнему краю |
VERTICAL_TOP | по верхнему краю |
VERTICAL_CENTER | по центру |
VERTICAL_JUSTIFY | по высоте |
$PHPExcel_Style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$PHPExcel_Style->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_JUSTIFY);
$PHPExcel_Style->getAlignment()->setTextRotation(10);
$PHPExcel_Style->getAlignment()->setWrapText(true);
$PHPExcel_Style->getAlignment()->setShrinkToFit(false);
$PHPExcel_Style->getAlignment()->setIndent(5).
Параметр numberformat представляет собой массив, который включает только один параметр: code - формат данных ячейки.
FORMAT_GENERAL | General |
FORMAT_TEXT | @ |
FORMAT_NUMBER | 0 |
FORMAT_NUMBER_00 | 0.00 |
FORMAT_NUMBER_COMMA_SEPARATED1 | #,##0.00 |
FORMAT_NUMBER_COMMA_SEPARATED2 | #,##0.00_- |
FORMAT_PERCENTAGE | 0% |
FORMAT_PERCENTAGE_00 | 0.00% |
FORMAT_DATE_YYYYMMDD2 | yyyy-mm-dd |
FORMAT_DATE_YYYYMMDD | yy-mm-dd |
FORMAT_DATE_DDMMYYYY | dd/mm/yy |
FORMAT_DATE_DMYSLASH | d/m/y |
FORMAT_DATE_DMYMINUS | d-m-y |
FORMAT_DATE_DMMINUS | d-m |
FORMAT_DATE_MYMINUS | m-y |
FORMAT_DATE_XLSX14 | mm-dd-yy |
FORMAT_DATE_XLSX15 | d-mmm-yy |
FORMAT_DATE_XLSX16 | d-mmm |
FORMAT_DATE_XLSX17 | mmm-yy |
FORMAT_DATE_XLSX22 | m/d/yy h:mm |
FORMAT_DATE_DATETIME | d/m/y h:mm |
FORMAT_DATE_TIME1 | h:mm AM/PM |
FORMAT_DATE_TIME2 | h:mm:ss AM/PM |
FORMAT_DATE_TIME3 | h:mm |
FORMAT_DATE_TIME4 | h:mm:ss |
FORMAT_DATE_TIME5 | mm:ss |
FORMAT_DATE_TIME6 | h:mm:ss |
FORMAT_DATE_TIME7 | i:s.S |
FORMAT_DATE_TIME8 | h:mm:ss |
FORMAT_DATE_YYYYMMDDSLASH | yy/mm/dd; @ |
FORMAT_CURRENCY_USD_SIMPLE | "$"#,##0.00_-;@ |
FORMAT_CURRENCY_USD | $#,##0_- |
FORMAT_CURRENCY_EUR_SIMPLE | [$EUR ]#,##0.00_- |
А можно и воспользоваться методом:
$PHPExcel_Style->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
В качестве значения параметра protection выступает массив, который содержит два необязательных параметра:
Или использовать следующие методы:
$PHPExcel_Style->getProtection()->setLocked(true);
$PHPExcel_Style->getProtection()->setHidden(false);
Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это двумя методами. Первый метод заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray , класса PHPExcel_Style .
$sheet->getStyleByColumnAndRow($i - 2, $j)->applyFromArray($style);
Сейчас применим тот же стиль, но используя другую методику.
Вот что у нас получилось:
Если требуется применять стиль многократно, то лучше подойдет первый метод, в другом же случае, лучше остановиться на втором. Для получения объекта (экземпляр класса PHPExcel_Style ) ячейки отвечающего за стиль, необходимо использовать один из следующих методов:
Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:
Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:
Вот какой комментарий мы должны получить.
.
Способ, представленный в той заметке действительно очень прост, но не всегда может быть удобен.
Есть множество других способов передать табличные данные из PHP в Excel, я опишу тот, который показался мне наиболее простым и функциональным. Нужно особенно отметить, что что я не говорю о генерации xls файла, а лишь предлагаю пользователю открыть полученные данные при помощи Excel так, что пользователи не искушённые в программировании не заметят подлога.
Итак, первое что необходимо сделать - разместить на нашей страничке ссылку на скрипт генерирующий следующие заголовки:
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/x-msexcel");
header("Content-Disposition: attachment; filename=\"" . iconv("UTF-8", "CP1251", $object->getFileName()) . "\";");
header("Content-Transfer-Encoding: binary");
header("Content-Length: " . $object->getFileSize());
$object - сферический объект в вакууме, который каждый читатель реализует так, как ему больше нравится. Назначение геттеров getFileName() и getFileSize() понятно из названий. Здесь стоит выделить один неочевидный нюанс (спасибо savostin за то что напомнил об этом) - getFileName() конечно может возвращать любое имя файла, но если вы хотите что бы браузер предложил открыть полученный контент в Excel, то расширение файла должно быть xls.
Ничего нового я пока не рассказал, все это придумано до меня, впрочем как и то что будет описано ниже.
Как верно было отмечено в комментариях к заметке о генерации xls в PHP , Excel гораздо быстрее работает с XML. Но самое главное преимущество, пожалуй, все же не в скорости, а в гораздо более широких возможностях. Углубляться в дебри я не стану, а лишь приведу простой пример, и ссылку на подробное описание всех тегов.
Итак, после того как заголовки сгенерированны, нам нужно отдать пользователю собственно данные. Я обычно заворачиваю генерацию таблицы в отдельный метод:
echo $object->getContent();
А таблицу генерирую при помощи Smarty:
Как видно из кода, в шаблон передается массив $data содержащий два массива - строку заголовка таблицы и собственно данные.
Стоит отметить что использовать шаблонизатор только для генерации XML несколько накладно, и получить XML можно множеством других способов. В моем конкретном случае генерация XML это лишь маленькая плюшка в большом проекте, где без шаблонизатора не обойтись.
В качестве примера я привел простейшую таблицу, при желании можно манипулировать гораздо большим числом атрибутов. Это особенно приятно, если учесть, что для реализации не требуются никакие сторонние библиотеки.
Описанный метод несколько лет работает на одном проекте и ни один пользователь до сих пор не заподозрил, что открываемые им данные не являются документом MS Office.
Подробнее о структуре XML используемой в MS Excel можно почитать в
.
Способ, представленный в той заметке действительно очень прост, но не всегда может быть удобен.
Есть множество других способов передать табличные данные из PHP в Excel, я опишу тот, который показался мне наиболее простым и функциональным. Нужно особенно отметить, что что я не говорю о генерации xls файла, а лишь предлагаю пользователю открыть полученные данные при помощи Excel так, что пользователи не искушённые в программировании не заметят подлога.
Итак, первое что необходимо сделать - разместить на нашей страничке ссылку на скрипт генерирующий следующие заголовки:
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/x-msexcel");
header("Content-Disposition: attachment; filename=\"" . iconv("UTF-8", "CP1251", $object->getFileName()) . "\";");
header("Content-Transfer-Encoding: binary");
header("Content-Length: " . $object->getFileSize());
$object - сферический объект в вакууме, который каждый читатель реализует так, как ему больше нравится. Назначение геттеров getFileName() и getFileSize() понятно из названий. Здесь стоит выделить один неочевидный нюанс (спасибо за то что напомнил об этом) - getFileName() конечно может возвращать любое имя файла, но если вы хотите что бы браузер предложил открыть полученный контент в Excel, то расширение файла должно быть xls.
Ничего нового я пока не рассказал, все это придумано до меня, впрочем как и то что будет описано ниже.
Как верно было отмечено в комментариях к заметке , Excel гораздо быстрее работает с XML. Но самое главное преимущество, пожалуй, все же не в скорости, а в гораздо более широких возможностях. Углубляться в дебри я не стану, а лишь приведу простой пример, и ссылку на подробное описание всех тегов.
Итак, после того как заголовки сгенерированны, нам нужно отдать пользователю собственно данные. Я обычно заворачиваю генерацию таблицы в отдельный метод:
echo $object->getContent();
А таблицу генерирую при помощи Smarty:
Как видно из кода, в шаблон передается массив $data содержащий два массива - строку заголовка таблицы и собственно данные.
Стоит отметить что использовать шаблонизатор только для генерации XML несколько накладно, и получить XML можно множеством других способов. В моем конкретном случае генерация XML это лишь маленькая плюшка в большом проекте, где без шаблонизатора не обойтись.
В качестве примера я привел простейшую таблицу, при желании можно манипулировать гораздо большим числом атрибутов. Это особенно приятно, если учесть, что для реализации не требуются никакие сторонние библиотеки.
Описанный метод несколько лет работает на одном проекте и ни один пользователь до сих пор не заподозрил, что открываемые им данные не являются документом MS Office.
Подробнее о структуре XML используемой в MS Excel можно почитать в