Создание Excel-файлов с помощью PHPExcel

Коммерция, а в особенности торговля никогда не обходится без отчетности. Отчеты нужны для эффективного анализа текущего положения дел в бизнесе. В таблицы можно вводить различные данные: числа, текст, даты и т.д. Excel позволяет не просто выстраивать таблицы с данными. Он позволяет делать вычисления над данными таблиц. Кроме того, можно сортировать и фильтровать данные. Как только бизнес начал занимать просторы Интернета, сюда начали переносить все решения для ведения бизнеса на персональных компьютерах. Excel не стал исключением. Сегодня я хочу рассказать про очень удобную объектно-ориентированную библиотеку для работы с Excel-файлами, которая имеет название PHPExcel.

Библиотека
Скачать библиотеку можно со странички Downloads официального сайта. Текущая доступная версия имеет номер 1.7.1. На сайте доступно несколько различных архивов для скачивания.

• PHPExcel 1.7.1 – вся библиотека со всей документацией (описанием и API);
• PEAR PHPExcel 1.7.1 – библиотека в пакетах PEAR;
• Documentation – документация (PHPExcel Developer Documentation);
• Calculation function reference – документация по функциям Excel.

Рекомендую скачать всю библиотеку со всей документацией. Размер распакованных данных внушительный (ок. 76 Мб), но большая часть – это документация. Сама библиотека «весит» всего 6 (!) Мб, что по моим меркам тоже кажется достаточно большим. Но с другой стороны, библиотека проводит много преобразований элементов без сторонних компонентов, поэтому размер должен быть не маленьким.
Распространяется PHPExcel под лицензией LGPL, поэтому проблем с лицензией быть не должно (если это кому-то важно ).
Системные требования библиотеки тоже не маленькие:

• PHP 5.2 или выше;
• php_zip – расширение для работы с Zip-архивами;
• php_xml – расширение для работы с XML;
• php_gd2 – расширение для работы с рисунками
• mb_string – расширения для работы с кодировками. Библиотека работает исключительно с UTF-8.

Быстрый старт
После распаковки архива получится папка PHPExcel, в которой находится папка Classes. Именно здесь располагаются файлы библиотеки. Чтобы не было проблем с путями библиотеки при внедрении в различные фреймворки, лучше всего установить путь к классам через set_include_path().
Вот пример создания небольшого Excel-файла.
excel.php

<?php
set_include_path(get_include_path() . PATH_SEPARATOR .
'PhpExcel/Classes/');
//подключаем и создаем класс PHPExcel
include_once 'PHPExcel.php';
$pExcel = new PHPExcel();
$pExcel->setActiveSheetIndex(0);
$aSheet = $pExcel->getActiveSheet();
$aSheet->setTitle('Первый лист');
//устанавливаем данные
//номера по порядку
$aSheet->setCellValue('A1','№');
$aSheet->setCellValue('A2','1');
$aSheet->setCellValue('A3','2');
$aSheet->setCellValue('A4','3');
$aSheet->setCellValue('A5','4');
//названия сайтов
$aSheet->setCellValue('B1','Названия');
$aSheet->setCellValue('B2','http://www.web-junior.net');
$aSheet->setCellValue('B3','http://www.google.com');
$aSheet->setCellValue('B4','http://www.yandex.ru');
$aSheet->setCellValue('B5','http://www.twitter.com');
//мой личный рейтинг
$aSheet->setCellValue('C1','Рейтинг');
$aSheet->setCellValue('C2','100');
$aSheet->setCellValue('C3','99');
$aSheet->setCellValue('C4','90');
$aSheet->setCellValue('C5','85');
//устанавливаем ширину
$aSheet->getColumnDimension('B')->setWidth(25);
//отдаем пользователю в браузер
include("PHPExcel/Writer/Excel5.php");
$objWriter = new PHPExcel_Writer_Excel5($pExcel);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="rate.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
?>

В результате получается вот такой чудо-рейтинг.

Самый правильный рейтинг всех времен и народов

Разъяснения
Теперь по порядку. Класс PHPExcel является ядром библиотеки. Он хранит в себе все листы (Sheet) книги Excel.
Для установки активного листа (тот с которым будем работать), нужно вызвать метод setActiveSheetIndex() и передать ему индекс листа по порядку. Индексация начинается с нуля, поэтому первый лист будет иметь индекс 0, второй – 1, третий – 2 и т.д.
Метод getActiveSheet() вернет активный лист. Это объект класса PHPExcel_Worksheet. Поэтому код

$aSheet = $pExcel->getActiveSheet();

Запишет в переменную $aSheet активный лист.
Класс PHPExcel_Worksheet имеет множество методов для работы с листом, ячейками, строками, столбцами и др.
Для установки значения ячейки нужно вызвать метод setCellValue() активного листа.

setCellValue( [string $pCoordinate = 'A1'],
[mixed $pValue = null])

В метод передаются два параметра:

• string $pCoordinate – номер ячейки – нумерация идет также как и в Excel, напр. A1, C5, D8 и т.д.
• mixed $pValue – значение, которое устанавливается в эту ячейку.

Тот столбец, в котором у нас вписаны сайты, получается слишком узким, поэтому нужно установить ширину для столбца B. Столбец мы можем получить с помощью метода getColumnDimension() активного листа ($aSheet)

PHPExcel_Worksheet_ColumnDimension getColumnDimension(
[string $pColumn = 'A'])

В качестве параметра передаем ему строку с именем нужного столбца (в нашем случае это B). Метод вернет объект столбца. Столбец представлен классом PHPExcel_Worksheet_ColumnDimension. Сейчас нас интересует только один из его методов. А именно setWidth().

PHPExcel_Worksheet_ColumnDimension setWidth(
[double $pValue = -1])

В качестве единственного параметра передаем устанавливаемую ширину столбца.

Ширина (и высота) в Excel может измеряться тремя способами:

• внутренняя ширина в символьных единицах (напр. 8,43. Этот вид чаще всего отображается в Excel);
• полная ширина в пикселях (напр. 64 pixels);
• полная ширина в символьных единицах (напр. 9,140625, -1 указывает на то, что ширина отключена).

Библиотека PHPExcel работает с третьим видом меры, поскольку этот вид хранится во всех версиях Excel.
Теперь нужно сохранить наше творение. Для сохранения доступно несколько классов. Каждый сохранит по-своему, в своем формате. Доступны форматы Excel5, Excel2007, HTML, PDF и др. Эти классы расположены в папке PHPExcel/Writer/ библиотеки. Они имеют название PHPExcel_Writer_*, где вместо * вставляем соответствующий формат.
Сохранение происходит следующим образом: в конструктор «классу-записывателю» передается объект класса PHPExcel, т.е. нашей книги. После чего нужно вызвать метод save(), которому передается имя файла.

void save($pFilename = null)

В этот файл будет записана Excel-книга в соответствующем формате. Если в качестве имени файла передать строку ‘php://output’, то файл не будет сохранен, а будет выведен в браузер.

Украшательства
Теперь рассмотрим пример работы со стилями. Прежде всего со шрифтами.
excel.php

…
//настройки для шрифтов
$baseFont = array(
	'font'=>array(
		'name'=>'Arial Cyr',
		'size'=>'10',
		'bold'=>false
	)
);
$boldFont = array(
	'font'=>array(
		'name'=>'Arial Cyr',
		'size'=>'10',
		'bold'=>true
	)
);
//и позиционирование
$center = array(
	'alignment'=>array(
		'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical'=>PHPExcel_Style_Alignment::VERTICAL_TOP
	)
);
//установим жирный шрифт для заголовков
//и заодно отцентрируем
$aSheet->getStyle('A1')->applyFromArray($boldFont)
->applyFromArray($center);
$aSheet->getStyle('B1')->applyFromArray($boldFont)
->applyFromArray($center);
$aSheet->getStyle('C1')->applyFromArray($boldFont)
->applyFromArray($center);
//и основной шрифт для всех остальных
for($i=2;$i<6;$i++){
	$aSheet->getStyle('A'.$i)->applyFromArray($baseFont);
	$aSheet->getStyle('B'.$i)->applyFromArray($baseFont);
	$aSheet->getStyle('C'.$i)->applyFromArray($baseFont);
}
…

Вот так выглядят изменения

Чудо-рейтинг меняет облик

Для настройки стилей ячейки есть класс PHPExcel_Style. Добраться до него можно из активного листа ($aSheet), вызвав метод getStyle()

PHPExcel_Style getStyle( [string $pCellCoordinate = 'A1'])

В качестве параметра передается строка с именем ячейки, для которой извлекается класс стилей. У класса стилей есть чудесный метод, который позволяет вынести все настройки в массив. Этот метод называется applyFromArray(). В качестве параметра ему передается массив с различными настройками. Для установки различных видов стилевых параметров нужно использовать различные секции в массиве.

• font – используется для настроек шрифтов ячейки;
• alignment – используется для настроек позиционирования;
• color – используется для настроек цвета ячейки;
• fill – используется для задания заливки ячейки
• и другие, подробнее см. документацию.

Формулы
Все Excel-файлы поддерживают формулы, для вычисления значений ячеек. PHPExcel тоже поддерживает добавление формул в ячейки. К сожалению в формулах содержится недостаток библиотеки PHPExcel. Все функции формул на английском языке. Как называются функции можно посмотреть в документации. Установить формулу можно так
excel.php

….
$aSheet->setCellValue('B6','Всего:')->getStyle('B6')
->applyFromArray($boldFont)->applyFromArray($center);
//формула для вычисления суммы балов
//наших сайтов
$formul = '=SUM(C2:C5)';
$aSheet->getCell('C6')->setDataType(
PHPExcel_Cell_DataType::TYPE_FORMULA)->setValue($formul);
…

Вместе мы набрали много баллов

Второй способ попроще

...
$aSheet->setCellValue('B6','Всего:')->getStyle('B6')
->applyFromArray($boldFont)->applyFromArray($center);
//формула для вычисления суммы балов
//наших сайтов
$formul = '=SUM(C2:C5)';
$aSheet->setCellValue('C6',$formul);
...

т.е. через все тот-же setCellValue()

На основе всего вышеизложенного можно уверенно заявить, что библиотека PHPExcel обладает достаточно мощными средствами для создания таблиц Excel и сохранения этих таблиц в различные форматы файлов.

Читайте все статьи цикла:

• Создание Excel-файлов с помощью PHPExcel
• Чтение Excel-файлов с помощью PHPExcel
• Часто задаваемые вопросы по PHPExcel
• Кириллические формулы в PHPExcel

Популярность: 100%

Интересное из других блогов: