Обработка и оформление отчетов в 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 5.0/Excel 95;
Excel 97 и поздние версии;
PHPExcel Serialized Spreadshet;
Symbolic Link;
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 97 и поздние версии;
PHPExcel Serialized Spreadshet;
HTML;
PDF;
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 "<table>";  // Получили строки и обойдем их в цикле $rowIterator = $sheet->getRowIterator(); foreach ($rowIterator as $row) { 	// Получили ячейки текущей строки и обойдем их в цикле 	$cellIterator = $row->getCellIterator();  	echo "<tr>"; 		 	foreach ($cellIterator as $cell) { 		echo "<td>" . $cell->getCalculatedValue() . "</td>"; 	} 	 	echo "</tr>"; } echo "</table>";

Второй вариант

...  echo "<table>";  for ($i = 0; $i < $sheet->getHighestRow(); $i++) {       echo "<tr>"; 	 	$nColumn = PHPExcel_Cell::columnIndexFromString( 		$sheet->getHighestColumn()); 	 	for ($j = 0; $j <= $nColumn; $j++) { 		$value = $sheet->getCellByColumnAndRow($j, $i)->getValue(); 		echo "<td>$value</td>"; 	}           echo "</tr>"; } echo "</table>";

В первом варианте мы производим чтение данных, из ячеек используя итераторы. А во втором, мы используем индексную адресацию для обращения и получения данных из ячеек листа. Получить данные о количестве строк и столбцов, можно воспользовавшись следующими методами класса PHPExcel_Worksheet:

getHighestColumn() – возвращает символьное представление последнего занятого столбца в активном листе. Обратите внимание: не индекс столбца, а его символьное представление (A, F и т.д.);
getHighestRow() – возвращает количество занятых строк в активном листе.

А также нам не обойтись без помощи метода columnIndexFromString, который входит в состав класса PHPExcel_Cell. Данный метод позволяет определить индекс столбца по его символьному представлению.
С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.

ссылка на оригинал статьи http://habrahabr.ru/post/245233/