Обработка и оформление отчетов в 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 можно записывать данные в следующие форматы:
Excel 2007; Excel 97 и поздние версии; PHPExcel Serialized Spreadshet; HTML; PDF; CSV. Импорт данных из PHP в ExcelРассмотрим пример по формированию таблицы умножения. // Подключаем класс для работы с excel require_once ('PHPExcel.php'); // Подключаем класс для вывода данных в формате excel require_once ('PHPExcel/Writer/Excel5.php');
// Создаем объект класса PHPExcel $xls = new PHPExcel (); // Устанавливаем индекс активного листа $xls→setActiveSheetIndex (0); // Получаем активный лист $sheet = $xls→getActiveSheet (); // Подписываем лист $sheet→setTitle ('Таблица умножения');
// Вставляем текст в ячейку A1 $sheet→setCellValue («A1», 'Таблица умножения'); $sheet→getStyle ('A1')→getFill ()→setFillType ( PHPExcel_Style_Fill: FILL_SOLID); $sheet→getStyle ('A1')→getFill ()→getStartColor ()→setRGB ('EEEEEE');
// Объединяем ячейки $sheet→mergeCells ('A1: H1');
// Выравнивание текста $sheet→getStyle ('A1')→getAlignment ()→setHorizontal ( PHPExcel_Style_Alignment: HORIZONTAL_CENTER);
for ($i = 2; $i < 10; $i++) { for ($j = 2; $j < 10; $j++) { // Выводим таблицу умножения $sheet->setCellValueByColumnAndRow ( $i — 2, $j, $i. «x» .$j.»=» . ($i*$j)); // Применяем выравнивание $sheet→getStyleByColumnAndRow ($i — 2, $j)→getAlignment ()→ setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER); } } Далее нам необходимо получить наш *.xls файл. Здесь можно пойти двумя путями. Если предположим у вас интернет магазин, и клиент хочет скачать прайс лист, то будет лучше прибегнуть к такому выводу: // Выводим HTTP-заголовки header («Expires: Mon, 1 Apr 1974 05:00:00 GMT»); header («Last-Modified:» . gmdate («D, d M YH: i: s») .» GMT»); header («Cache-Control: no-cache, must-revalidate»); header («Pragma: no-cache»); header («Content-type: application/vnd.ms-excel»); header («Content-Disposition: attachment; filename=matrix.xls»);
// Выводим содержимое файла
$objWriter = new PHPExcel_Writer_Excel5($xls);
$objWriter→save ('php://output');
Здесь сформированные данные сразу «выплюнутся» в браузер. Однако, если вам нужно файл сохранить, а не «выбросить» его сразу, то не нужно выводить HTTP-заголовки и вместо «php://output» следует указать путь к вашему файлу. Помните что каталог, в котором предполагается создание файла, должен иметь права на запись. Это касается UNIX-подобных систем.Рассмотрим еще на примере три полезные инструкции:$sheet→getColumnDimension ('A')→setWidth (40) — устанавливает столбцу «A» ширину в 40 единиц;
$sheet→getColumnDimension ('B')→setAutoSize (true) — здесь у столбца «B» будет установлена автоматическая ширина;
$sheet→getRowDimension (4)→setRowHeight (20) — устанавливает четвертой строке высоту равную 20 единицам.
Также обратите внимание на эти вот методы: setCellValue и setCellValueByColumnAndRow.
setCellValue (pCoordinate, pValue, returnCell = false) принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр необязателен (если присвоить ему значение true, то метод вернет объект ячейки, иначе объект рабочего листа); setCellValueByColumnAndRow (pColumn, pRow, pValue = null, returnCell = false) принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue. То есть можно обращаться к ячейкам двумя разными способами. Что является очень удобным.
Оформление отчета средствами PHP в Excel Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой «минус» — нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.Назначить стиль ячейке можно двумя способами: Применить метод applyFromArray, класса PHPExcel_Style. В метод applyFromArray передается массив со следующими параметрами: fill — массив с параметрами заливки; font — массив с параметрами шрифта; borders — массив с параметрами рамки; alignment — массив с параметрами выравнивания; numberformat — массив с параметрами формата представления данных ячейки; protection — массив с параметрами защиты ячейки. Использовать метода класса PHPExcel_Style для каждого из стилей в отдельности. К примеру, назначить ячейке шрифт можно так: $sheet→getStyle ('A1')→getFont ()→setName ('Arial') . Заливка Значением параметра fill является массив со следующими необязательными параметрами: type — тип заливки; rotation — угол градиента; startcolor — значение в виде массива с параметром начального цвета в формате RGB; endcolor — значение в виде массива с параметром конечного цвета в формате ARGB; color — значение в виде массива с параметром начального цвета в формате RGB. Стили заливки
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 Пример указания настроек для заливки: array ( 'type' => PHPExcel_Style_Fill: FILL_GRADIENT_LINEAR, 'rotation' => 0, 'startcolor' => array ( 'rgb' => '000000' ), 'endcolor' => array ( 'argb' => 'FFFFFFFF' ), 'color' => array ( 'rgb' => '000000' ) ); Или можно использовать следующие методы:$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')).Вставка изображений Довольно редко, но бывает полезным произвести вставку изображения в отчет. Это может быть логотип, схема и т.д. Для работы нам понадобятся следующие методы: setPath ($pValue = '', $pVerifyFile = true) принимает один обязательный и второй не обязательный параметры: в качестве первого параметра указывается путь к файлу с изображением. Второй параметр имеет смысл указывать, если необходимо осуществлять проверку существования файла (может принимать одно из значений true или false). setCoordinates ($pValue = 'A1') принимает на вход один параметр в виде строки с координатой ячейки. setOffsetX ($pValue = 0) принимает один параметр со значением смещения по X от левого края ячейки. setOffsetY () принимает один параметр со значением смещения по Y от верхнего края ячейки. setWorksheet (PHPExcel_Worksheet $pValue = null, $pOverrideOld = false) этот метод принимает на вход два параметра. Первый является обязательным, а второй нет. В качестве первого параметра указывается экземпляр объекта активного листа. Если в качестве значения второго параметра передать true, то если лист уже был назначен ранее — произойдет его перезапись и соответственно изображение удалится. Код демонстрирующий алгоритм вставки изображения приведен ниже:
… $sheet→getColumnDimension ('B')→setWidth (40);
$imagePath = dirname (__FILE__) . '/excel.png'; if (file_exists ($imagePath)) { $logo = new PHPExcel_Worksheet_Drawing (); $logo→setPath ($imagePath); $logo→setCoordinates («B2»); $logo→setOffsetX (0); $logo→setOffsetY (0); $sheet→getRowDimension (2)→setRowHeight (190); $logo→setWorksheet ($sheet); }
… Вот так выглядит отчет со вставленным изображением:
Шрифт
В качестве значения параметра font указывается массив, который содержит следующие необязательные параметры: name — имя шрифта;
size — размер шрифта;
bold — выделять жирным;
italic — выделять курсивом;
underline — стиль подчеркивания;
strike — перечеркнуть;
superScript — надстрочный знак;
subScript — подстрочный знак;
color — значение в виде массива с параметром цвета в формате RGB.
Стили подчеркиванияUNDERLINE_NONE
нет
UNDERLINE_DOUBLE
двойное подчеркивание
UNDERLINE_SINGLE
одиночное подчеркивание
Пример указания параметров настроек для шрифта:
array (
'name' => 'Arial',
'size' => 12,
'bold' => true,
'italic' => false,
'underline' => PHPExcel_Style_Font: UNDERLINE_DOUBLE,
'strike' => false,
'superScript' => false,
'subScript' => false,
'color' => array (
'rgb' => '808080'
)
);
Или воспользоваться следующими методами:$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 указывается массив, который содержит следующие необязательными параметры: вид рамки (top|bootom|left|right|diagonal|diagonaldirection) — массив параметров: style — стиль рамки;
color — значение в виде массива с параметром цвета в формате RGB.
Стили линийBORDER_NONE
нет
BORDER_DASHDOT
пунктирная с точкой
BORDER_DASHDOTDOT
пунктирная с двумя точками
BORDER_DASHED
пунктирная
BORDER_DOTTED
точечная
BORDER_DOUBLE
двойная
BORDER_HAIR
волосная линия
BORDER_MEDIUM
средняя
BORDER_MEDIUMDASHDOT
пунктирная с точкой
BORDER_MEDIUMDASHDOTDOT
утолщенная пунктирная линия с двумя точками
BORDER_MEDIUMDASHED
утолщенная пунктирная
BORDER_SLANTDASHDOT
наклонная пунктирная с точкой
BORDER_THICK
утолщенная
BORDER_THIN
тонкая
Пример указания параметров настроек для рамки:
array (
'bottom' => array (
'style' => PHPExcel_Style_Border: BORDER_DASHDOT,
'color' => array (
' rgb' => '808080'
)
),
'top' => array (
'style' => PHPExcel_Style_Border: BORDER_DASHDOT,
'color' => array (
'rgb' => '808080'
)
)
);
Так же можно прибегнуть к использованию следующих методов:$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 — отступ от левого края.
Выравнивание по горизонталиHORIZONTAL_GENERAL
основное
HORIZONTAL_LEFT
по левому краю
HORIZONTAL_RIGHT
по правому краю
HORIZONTAL_CENTER
по центру
HORIZONTAL_CENTER_CONTINUOUS
по центру выделения
HORIZONTAL_JUSTIFY
по ширине
Выравнивание по вертикалиVERTICAL_BOTTOM
по нижнему краю
VERTICAL_TOP
по верхнему краю
VERTICAL_CENTER
по центру
VERTICAL_JUSTIFY
по высоте
Пример параметров настройки стилей выравнивания:
array (
'horizontal' => PHPExcel_Style_Alignment: HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment: VERTICAL_CENTER,
'rotation' => 0,
'wrap' => true,
'shrinkToFit' => false,
'indent' => 5
)
А можно и использовать следующие методы:$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_-
Пример настройки для формата данных ячейки:
array (
'code' => PHPExcel_Style_NumberFormat: FORMAT_CURRENCY_EUR_SIMPLE
);
А можно и воспользоваться методом:$PHPExcel_Style→getNumberFormat ()→setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_CURRENCY_EUR_SIMPLE); Защита ячеек
В качестве значения параметра protection выступает массив, который содержит два необязательных параметра: locked — защитить ячейку;
hidden — скрыть формулы.
Пример настройки параметров для защиты ячейки:
array (
'locked' => true,
'hidden' => false
);
Или использовать следующие методы:$PHPExcel_Style→getProtection ()→setLocked (true);$PHPExcel_Style→getProtection ()→setHidden (false); Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это двумя методами. Первый метод заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray, класса PHPExcel_Style.
$style = array (
'font' => array (
'name' => 'Arial',
),
'fill' => array (
'type' => PHPExcel_Style_Fill: FILL_SOLID,
'color' => array (
'rgb' => 'C2FABD'
)
),
'alignment' => array (
'horizontal' => PHPExcel_Style_Alignment: HORIZONTAL_CENTER
)
);
Далее мы применим созданный нами стиль к ячейкам excel.
$sheet→getStyleByColumnAndRow ($i — 2, $j)→applyFromArray ($style);
Сейчас применим тот же стиль, но используя другую методику.
//Устанавливаем выравнивание
$sheet→getStyleByColumnAndRow ($i — 2, $j)→getAlignment ()→setHorizontal (
PHPExcel_Style_Alignment: HORIZONTAL_CENTER);
// Устанавливаем шрифт
$sheet→getStyleByColumnAndRow ($i — 2, $j)→getFont ()→setName ('Arial');
// Применяем заливку
$sheet→getStyleByColumnAndRow ($i — 2, $j)→getFill ()→
setFillType (PHPExcel_Style_Fill: FILL_SOLID);
$sheet→getStyleByColumnAndRow ($i — 2, $j)→getFill ()→
getStartColor ()→applyFromArray (array ('rgb' => 'C2FABD'));
Вот что у нас получилось: 
Если требуется применять стиль многократно, то лучше подойдет первый метод, в другом же случае, лучше остановиться на втором. Для получения объекта (экземпляра класса PHPExcel_Style) ячейки отвечающего за стиль, необходимо использовать один из следующих методов: getStyleByColumnAndRow (pColumn, pRow) — для обращения к ячейке по индексу;
getStyle (pCellCoordinate) — для обращения по координате ячейки.
Чтение данных из 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 — используется для перебора ячеек.
Для демонстрации выведем данные из таблицы с информацией об автомобилях.
Пример чтения файла представлен ниже:
require_once ('PHPExcel/IOFactory.php');
// Открываем файл $xls = PHPExcel_IOFactory: load ('xls.xls'); // Устанавливаем индекс активного листа $xls→setActiveSheetIndex (0); // Получаем активный лист $sheet = $xls→getActiveSheet (); Первый вариант
…
echo »
| » . $cell→getCalculatedValue () .» | »; } echo »
…
echo »
| $value | »; } echo »
В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet: getHighestColumn () — возвращает символьное представление последнего занятого столбца в активном листе. Обратите внимание: не индекс столбца, а его символьное представление (A, F и т.д.);
getHighestRow () — возвращает количество занятых строк в активном листе.
А также нам не обойтись без помощи метода columnIndexFromString, который входит в состав класса PHPExcel_Cell. Данный метод позволяет определить индекс столбца по его символьному представлению.С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.
