Обработка и оформление отчетов в 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. А также были продемонстрированы почти все возможные методы для работы со стилями.

Автор: prishelec

Источник