Использование Spreadsheet Excel Writer на примерах

Разрабатывая программные комплексы на PHP, мы имеем в руках мощное средство для представления информации в различных форматах, основной из которых — это HTML.
Иногда функциональности чистого HTML может не хватить для реализации идеи разработчика. В таких случаях, PHP может предоставить информацию в практически любых других популярных форматах: в виде изображений с помощью библиотеки GD, в форматах PDF, XML, и даже как сгенерированный по запросу ролик SWF Flash или же просто (и сердито) в виде обычного текстового файла (text/plain).
А в некоторых случаях полезно научить наше приложение сохранять информацию в довольно экзотический для Всемирной паутины формат — в формат электронных таблиц Excel.
Этим мы и займёмся.

Введение

Для генерации таблиц воспользуемся Spreadsheet_Excel_Writer. Я начал свое знакомство с этой библиотекой со статьи «Создание таблиц Excel средствами PHP». Это удобная статья для первого ознакомления, но для дальнейшего использования этой библиотеки информации из неё может просто не хватить. Также автор не уделил должного внимания многим моментам: например, настройкам локализации. Поэтому мои первые таблицы содержали транслитизированные строки. В дальнейшем, изучая обсуждения на форумах и документацию на английском языке, я собрал полезную информацию и решил структурировать её в виде этой статьи.

Краткое описание библиотеки

Перевод описания со страницы модуля:
Spreadsheet_Excel_Writer появился как порт Spreadsheet::WriteExcel с Perl на PHP. Позволяет генерировать электронные таблицы Excel без необходимости подключения COM-объектов. Поддерживает формулы, изображения в формате BMP и все виды форматирования текста и ячеек.

Окружение: Операционная система с PHP 5 и PEAR. В моём архиве сборка PEAR для работы в автономном режиме.

Зависимости: PEAR и пакет OLE (Package for reading and writing OLE containers).

Версия и дата релиза: 0.9.1 (beta) was released on 2006-09-26</h
Это говорит нам, что библиотека хотя и не совсем закончена, но почти все нужные функции присутствуют, поэтому никто её дальше писать не собирается. Зато, код библиотеки устоялся и почти не изменялся, что позволило выявить многие ошибки (которые, впрочем не исправляются :( ). Последняя версия заточена на работу с Excel 8.0 (Office '98). Что нам это даёт? Из положительного — возможность открыть генерируемый файл таблицы практически в любых современных программах для работы с электронными таблицами (MS Office 2003, MS Office XP, MS Office 2007, OpenOffice с первой по третью версию). При этом, наши таблицы будут выглядеть практически одинаково.
Кроме того, эта библиотека отличается в хорошую сторону тем, что она не ограничивает нас в плане локализации (можно использовать unicode, но его надо настроить. более подробно — ниже).

Лицензия: LGPL. Код класса можно использовать в коммерческих проектах в качестве библиотеки (из wiki: GNU LGPL позволяет связывание с данной библиотекой или программой программы под любой лицензией, несовместимой с GNU GPL, при условии, что такая программа не является производной от объекта, распространяемого под (L)GPL, кроме как путём связывания).

Использование

Итак, приступим.
Нужно скачать, установить, настроить PEAR и нужные пакеты. Или просто использовать его из архива.
Далее приступим к программированию.

Внимание: Если Вы собираетесь сразу выводить данные пользователю, а не сохранять их на диск, то ваш сценарий должен не выводить никаких посторонних данных через стандартные функции вывода echo и print, иначе это разрушит структуру бинарного файла таблицы!

Примеры

Пример 0

Это простейший пример. Попробуем просто вывести данные из двумерного массива. Описание разбито на шаги. Соединив их вместе, вы получите нужную электронную таблицу.
Шаг 1. Подключаем библиотеку:
<?php
// адрес PEAR
$path = realpath(dirname(__FILE__).'/pear/');
// подключаем
set_include_path(dirname(__FILE__). PATH_SEPARATOR . $path);
require_once('PEAR.php');

// Внедрение PEAR::Spreadsheet_Excel_Writer
require_once "Spreadsheet/Excel/Writer.php";
?>

Шаг 2. Инициализация объекта
<?php
// Создание случая
$xls =& new Spreadsheet_Excel_Writer();

// Передаем номер версии Excel
$xls->setVersion(8);

// Отправка HTTP заголовков для сообщения обозревателю о типе вxодимыx данныx
$xls->send('excel_'.date("H_i_s").'.xls'); //название файла excel_17_16_18.xls
?>

Шаг 3. Создаём лист с именем "info" в переменной $sheet
<?php
// Добавление листа к файлу, возвращение объекта для добавления данныx
$sheet = $xls->addWorksheet('info'); // имя листа должно быть уникальным
?>

Шаг 4. Устанавливаем кодировку листа UTF-8
<?php
$sheet->setInputEncoding('UTF-8');
?>

Шаг 5. Наши данные: двумерный массив $table
<?php
$table = array(
'Header 1' => array(1, 2, 3),
'Header 2' => array(4, 5, 6),
'Header 3' => array(7, 8, 9)
);
?>

Шаг 6. Заполняем таблицу данными из массива $table. Используем цикл, инициализируем переменные $cell и $row, где
$cell — столбец
$row — строка.
Добавляем данные к листу. Пробегаем массив, данные записываем по столбцу с помощью функций:
$sheet->write($row, $cell, $header); — пишет в ячейку ($row;$cell) данные $header
$sheet->writeCol ($row, $cell, $row_data); — пишет столбец $row_data в ячейки, начиная с ($row;$cell).

<?php
$cell = 0;
foreach ($table AS $header => $row_data){
$row = 0;
$sheet->write($row, $cell, $header);
$row++;
$sheet->writeCol ($row, $cell, $row_data);

$cell++;
}?>

Шаг 7. Заканчиваем работу библиотеки, высылаем данные
<?php
$xls->close();
?>

Усложним форматирование и методы вывода информации, с помощью остальных примеров.

Пример 1

В этом примере введём понятие формата. Формат — это объект, описывающий оформление ячейки или группы ячеек.
<?php
// Создание объекта форматирования
$textFormat =& $xls->addFormat();
// Определение размера текста
$textFormat->setSize(10);
// Определение цвета текста
$textFormat->setColor('black');
$textFormat->setAlign('center');
$textFormat->setBorder(1);
?>
Методы говорят сами за себя.
Этот стиль можно применить при создании ячейки:
<?php
$sheet->write($row, $cell, $data, $textFormat); // Ячейки
?>

Пример 2

Решаем одну из проблем библиотеки «костылями». Как? В данном случае, определяем ширину столбца в цикле. Получаем массив с максимальной длиной строки в ячейках столбца и устанавливаем ширину столбца вручную с помощью функции $sheet->setColumn($cell, $cell, $celLenght), которая устанавливает ширину столбцов с $cell по $cell шириной $celLenght (в пикселях?).

Пример 3

Вывод таблицы умножения в электронные таблицы Excel. Игра слов ;)

Пример 4

Использование формул Excel на листе. Довольно бессмысленно, когда у нас есть мощь PHP, но иногда полезно.

Пример 5

Удобный скрипт, выводящий таблицу цветов, которые можно использовать при создании форматов стилей.

Архив с примерами

Файлы в архиве (формат zip, размер ~200kb, кодировка текстовых файлов UTF-8):
excel.php — простой лист (пример 0);
excel_1.php — лист с форматированием (пример 1);
excel_2.php — лист с ручным определением ширины столбца (пример 2);
excel_3.php — таблица умножения ;) (пример 3)
excel_4.php — лист с формулами (пример 4);
excel_5.php — лист с цветами (пример 5);
папочка pear с библиотекой.
папочка examples с xls-файлами примеров (для ознакомления).

Проблемы

Локализация:
решено функциями
$xls->setVersion(8); //Установка версии Excel, без этого - артефакты с кодировками, с отображением данных
$sheet->setInputEncoding('UTF-8'); //Для каждого листа - установка кодировки
Теперь можно писать данные на «чистом» UTF-8, и Excel вас поймёт ;)
Ошибка:
Создатели класса допустили ошибку. Но встречается она, только если пишешь на UTF-8 в версии Excel 8 и пишешь много (>200 строк, несколько листов). В архиве на файл наложен патч, найденный на багтрекере, решающий проблему.

Заключение

Подведём итоги нашей работы. Мы получили элементарные навыки создания электронных таблиц с помощью PHP. Изучая примеры из архива и на сайте разработчика Вы сможете усовершенствовать эти навыки.
Под конец напишу то, что надо было писать в начале. Итак, плюсы и минусы использования библиотеки Spreadsheet_Excel_Writer в своих проектах.
Плюсы:
+ Платформонезависимость
+ Возможность вводить формулы (из английской версии)
+ Нормально открывается в OpenOffice.org CALC
+ Можно добавлять картинки через void Worksheet::insertBitmap

Минусы:
— Ручное определение ширины столбца (если кто-нибудь найдет элегантные решения — буду искренне благодарен)
— Нельзя добавлять графики (особо и не надо)

Также рекомендуется для ознакомления:
Статья «Создание таблиц Excel средствами PHP» (рус)
Сайт груши (англ)
список функций класса Excel Writer (англ)
список функций Excel (англ)