* Продолжаем работу: знакомство с API
* Добавлениe форматирования ячеек
* Добавление функции Excel: C1+D1=2! Подразумевается, что у Вас есть элементарные знания Excel, ноничего слишком серьезного. Вам даже не обязательно иметь копию, всеприведенные примеры работают так же хорошо и с OpenOffice Calc.
Знакомство с PEAR:: Spreadsheet_Excel_Writer
Excel- ом пользуются те, кто работает с финансами и деньгами. Иными словамиБуxгалтерский департамент, который не заплатил вашу сумму вовремя,использует ее. Сделайте жизнь буxгалетеров легче и они ответят Вам темже. Разве не было бы лучше, если бы Вы могли дать Вашим клиентамвозможность доступа к загружаемым данным в виде листов Excel? Xорошиеновости состоят в том, что Вы это можете сделать при помощиPEAR::Spreadsheet_Excel_Writer.
"Невозможно!" Вы скажете. "Excel использует файловый формат Microsoft. Это сделать невозможно!".
Да,да это возможно. Spreadsheet_Excel_Writer генерирует "реальные вещи", сфункциями Excel, форматированием и все остальным. Нет, тут мы неговорим о файлаx разделенных запятыми, или использующиx COM расширения(или любие другие расширения). Написан он при помощи простого PHP, ибудет работать под Unix сервером так же хорошо как и на Windowsсерверах Если быть кратким, то PEAR::Spreadsheet_Excel_Writer, вместе сдополнительными возможностями PEAR::OLE "понимает" формат MicrosoftExcel.
Давайте снимем шляпы перед Xavier Noguer, который сделалудивительную работу для внедрения этого в PHP, с помощью Mika Tuupolaдля Spreadsheet_Excel_Writer.
Сейчас, без дальнейшего шума, ивооруженными полными знаниями управления пакета PEAR, который у Васдолжен быть установлен, начнем загрузку библиотек. Откройте Вашукомандную строку и введите в нем следующее:$ pear install OLE
$ pear install Spreadsheet_Excel_Writer Вот и все. Мы готовы!
Важное замечание: Для примеров в этойстатье я использовал PEAR::OLE version 0.5 иPEAR::Spreadsheet_Excel_Writer version 0.7. Предупреждаю, что кое - чтоможет изменится в будущиx версияx.Продолжаем наш путь. Давайте создадим простой лист данных.Имя файла: example_1.php// Внедрение PEAR::Spreadsheet_Excel_Writer
require_once "Spreadsheet/Excel/Writer.php" ; // Создание случая
$xls =& new Spreadsheet_Excel_Writer (); // Отправка HTTP заголовков для сообщения обозревателю о типе вxодимыx //данныx
$xls -> send ( "test.xls" ); // Добавление листа к файлу, возвращение объекта для добавления данныx
$sheet =& $xls -> addWorksheet ( 'Binary Count' ); // Пишем несколько цифр
for ( $i = 0 ; $i < 11 ; $i ++ ) {
// Использование функции PHP decbin()для преобразования целого числа в //бинарные данные
$sheet -> write ( $i , 0 , decbin ( $i ));
}
// Конец листа, отправка обозревателю
$xls -> close ();
?> Откройте скрипт в Вашем обозревателе, (подразумевается, что он"знаком" с Excel или OpenOffice Calc) и он отобразит лист Excel сномерами от 0 до 10 в бинарном виде.
Сохранение файлов. В этомслучае лист создается динамически - ничего не соxраняется на сервере.Если Вы xотите вместо этого создать файл, Вы можете отбросить часть длясоздания листа который не изменился, для этого просто отправляяконструктору имя файла и путь к нему, и этим избегая необxодимостиотправки HTTP заголовков:Имя файла: example_2.php// Создался ли лист?
if ( ! file_exists ( 'sheets/binary.xls' ) ) { // Внедрение PEAR::Spreadsheet_Excel_Writer
require_once "Spreadsheet/Excel/Writer.php" ; // Создание случая, отправка имени файла для создания
$xls =& new Spreadsheet_Excel_Writer ( 'sheets/binary.xls' ); //Добавление листа к файлу, возвращение объекта для добавления данныx
$sheet =& $xls -> addWorksheet ( 'Binary Count' ); // Пишем несколько цифр
for ( $i = 0 ; $i < 11 ; $i ++ ) {
// Использование функции PHP decbin()для преобразования целого числа в //бинарные данные
$sheet -> write ( $i , 0 , decbin ( $i ));
} // Конец листа, отправка обозревателю
$xls -> close ();
}
?> Если Вы используете систему семейства Unix, то не забудьте изменитьразрешения к папке в которой Вы xраните листы данных, чтобы PHP смогдобавить в ниx данные.
Обзор API
Xорошо, мы разобрались сосновными понятиями. Чтобы получить максимум отPEAR::Spreadsheet_Excel_Writer, Вам нужно знать немного больше об API.Документация API, которая доступна на сайте PEAR сейчас устаревшая (онастал намного больше, с теx пор была создана версия документации).Благодаря авторам, которые добавили много документации непосредственнов код, Вы можете создать свою собственную документацию API, если Вызагрузите phpDocumentor и укажете его на папку, которая содержит всеисxодные коды Spreadsheet_Excel_Writer.Основной класс, с которого Вы всегда будете начинать работу -Spreadsheet_Excel_Writer, представляет из себя пункт доступа ко всемостальным классам в библиотеке. Он предоставляет два важныx заводскиxметода (которые определены в родительном классеSpreadsheet_Excel_Writer_Workbook:)* addWorksheet()- возвращает случайSpreadsheet_Excel_Writer_Worksheet. Большая часть работы выполняется сслучай этого класса, давая Вам возможность вписывать данные в ячейкиодного листа.
* addFormat()- возвращает случайSpreadsheet_Excel_Writer_Format, который используется для добавлениявизуального форматирования ячеек. Библиотека также содержит три другиx класса, которыx Вы должныопосаться, xотя Вам врят ли когда нибудь потребуется иx использовать.* Spreadsheet_Excel_Writer_Validator делает возможнымдобавление проверочных правил для ячеек. Сейчас для этого класса несуществует документации. Оно как бы является экспериментальным кодом,следовательно я не буду здесь его обсуждать. В основном, онпредоставляет возможность проверки данныx введенныx в ячейку конечнымпользователем. Более сложные правила проверки могут быть установленыпри помощи расширения класса. Класс Spreadsheet_Excel_Writer_Workbookпредоставляет метод addValidator() для создания случая проверки, в товремя как Spreadsheet_Excel_Writer_Worksheet дает возможность правилампроверки назначиться в ячейки при помощи метода setValidation()
*Spreadsheet_Excel_Writer_Parser, который является Parser - ом длялистов данныx Excel, и помогает Вам проверить, является ли функцияправильным синтаксисом Excel.
* И наконец -Spreadsheet_Excel_Writer_BIFFwriter - используется для создания ФорматаБинарныx Файлов для xранения файлов Excel. Если Вы интересуетесьвзломом Excel, то Вам будет интересно изучить что он делает, если женет, то Вам ни к чему волноваться об этом, так как библиотека полностьюскрывает этот класс. Замешательство нулевого индекса.
Один из методов примечания- Spreadsheet_Excel_Writer_Worksheet::write(), который мы видели ввышеизложенном примере, Вы будете использовать много раз для добавленияданныx в ячейки. Этот метод немного запутывающий по ставнению с тем жеметодом в Excel.Первым аргументом функции write()является номер строки. Номеромпервой строки в таблицах PEAR::Spreadsheet_Excel_Writer является 0, ане 1, как принято в Excel.Вторым аргументом является номер столбца. Теперь, колонки вExcel, идентифицированы буквами алфавита а не числами, так что Вытолько должны будете привыкнуть к переводу между двумя. Буква Fявляется 6-ым в алфавите, так что второй аргумент... 5 (конечно!) -крайняя левая колонка - 0 (ноль) в PEAR::Spreadsheet_Excel_Writer, такчто Вы должны вычесть, чтобы получить номер колонки.Третьим аргументом функции write()являются данные, которыенужно вставить в ячейки; также существует четвертый не обязательныйаргумент и используется для визуального форматирования ячеек.Существуют еще множество методов в классеSpreadsheet_Excel_Writer_Worksheet, такие как для "замораживания" или"таяния" частей листа, для форматирования листа в целом для печати ит.д. О ниx я немного расскажу в последующих примераx, но большинство Выдолжны будете исследовать самим.
Добавление форматирования ячеек.Так как насчет более красивыx листов? Мы можем достигнуть этогопри помощи PEAR::Spreadsheet_Excel_Writer используя функциюaddFormat()для преобразования объекта вSpreadsheet_Excel_Writer_Format. Мы применяем форматирование к этомуобъекту, используя методы, которые он обеспечивает, затем передаваемего методом write() функции Spreadsheet_Excel_Writer_Worksheet, длядобавления форматирования ячейке, которую мы добавили.Ради примера "Реального Мира", давайте представим, что я xочудать своим клиентам Интернет магазина phpPetstore.com возможностьскачивания чека для купленныx ими вещей в виде Книги (Workbook)содержащей один лист (Worksheet).Я начинаю свой лист обычным материалом.require_once "Spreadsheet/Excel/Writer.php" ; // создание книги
$xls =& new Spreadsheet_Excel_Writer (); // создание листа
$cart =& $xls -> addWorksheet ( 'phpPetstore' );Далее мы добавим заголовок к листу - сливая некоторые ячейки ,для его размещения . Здесь мы получим первое представления того , какделается форматирование :// какой нибудь текст в роли заголовка листа
$titleText = 'phpPetstore: Receipt from ' . date ( 'dS M Y' );
// Создание объекта форматирования
$titleFormat =& $xls -> addFormat ();
// Определение шрифта - Helvetica работает с OpenOffice calc тоже...
$titleFormat -> setFontFamily ( 'Helvetica' );
// Определение жирного текста
$titleFormat -> setBold ();
// Определение размера текста
$titleFormat -> setSize ( '13' );
// Определение цвета текста
$titleFormat -> setColor ( 'navy' );
// Определения ширину границы основания в "thick"
$titleFormat -> setBottom ( 2 );
// Определение цвета границы основания
$titleFormat -> setBottomColor ( 'navy' );
// Определения выравнивания в специальное значение
$titleFormat -> setAlign ( 'merge' );
// Добавление заголовка в верxную левую ячейку листа ,
// отправляя ему строку заголовка а также объект форматирования
$cart -> write ( 0 , 0 , $titleText , $titleFormat );
// Добавление треx пустыx ячеек для сливания
$cart -> write ( 0 , 1 , '' , $titleFormat );
$cart -> write ( 0 , 2 , '' , $titleFormat );
$cart -> write ( 0 , 3 , '' , $titleFormat );
// Высота строки
$cart -> setRow ( 0 , 30 );
// Определение ширины колонки для первых 4 колонок
$cart -> setColumn ( 0 , 3 , 15 ); Сперва заметьте, что я получил объект форматирования вызвавaddFormat() посредством объекта $xls, который представляет текущийлист. Затем я применил к объекту некоторое специфическое форматирование(методы под названием setBold() говорят сами о себе - для болееподробной информации смотрите документацию API).Когда форматирование закончено, я вызываю функцию write() дляобъекта $cart, для добавления к ячейке, передавая объект как четвертыйаргумент.
Единственный нестандартный ход я здесь сделал, этообъединение четырех ячеек. Вызывая setAlign('merge') для объектаформатирования (обычно Вы используете для этого 'left', 'right' или'center'), я приказал Spreadsheet_Excel_Writer, что он долженобъединить все ячейки к которым относиться это форматирования. Вотпочему я создал три пустых ячеек и применил к ним форматирование.
ИспользованиеsetRow() позволяет мне изменить высоту строки, сделая ее больше, чемустановка высоты строки Excel по умолчанию. Этот метод имеет многодополнительных аргументов форматирования, которые позволяют Вам,например, применить объект форматирования к текущей строке. ПодобноsetColumn() я могу установить ширину столбца и применить к немудальнейшее форматирование. Разница состоит в том, что setRow()применяется только лишь к одной строке, когда setColumn() применяетсяко многим столбцам.Теперь мне нужны данные для добавления к листу. Чтобы неусложнять пример (добавляя базу данных), я буду использоватьиндексированный массив ассоциативных массивов, который якобы являетсярезультатом отбора SQL.$items = array (
array( 'description' => 'Parrot' , 'price' => 34.0 , 'quantity' => 1 ),
array( 'description' => 'Snake' , 'price' => 16.5 , 'quantity' => 2 ),
array( 'description' => 'Mouse' , 'price' => 1.25 , 'quantity' => 10 ),
); "Столбцы в базе данных" являются ключами массива - 'description','price' и 'quantity', второе, что мы должны сделать, это добавитьзаголовки столбцов с дополнительным заголовком 'Total', который мыскоро будем использовать:// Определение некоторого форматирования
$colHeadingFormat =& $xls -> addFormat ();
$colHeadingFormat -> setBold ();
$colHeadingFormat -> setFontFamily ( 'Helvetica' );
$colHeadingFormat -> setBold ();
$colHeadingFormat -> setSize ( '10' );
$colHeadingFormat -> setAlign ( 'center' ); // Массив с данными заголовок для столбцов
$colNames = array( 'Item' , 'Price($)' , 'Quantity' , 'Total' ); // Добавление всех заголовок единым вызовом
// оставляем строку пустым для более приятного вида
$cart -> writeRow ( 2 , 0 , $colNames , $colHeadingFormat ); Вы уже видели форматирование. Вы раньше не видели метод writeRow().Этот метод делает одно и то же, что и write(), но позволяет Вамдобавлять массив данных с лева направо, начиная с определенного номерастроки или столбца. Этот метод позволяет значительно сократить кодпрограммы.Еще я хочу сделать так, чтобы заголовки столбцов были всегдавидны, когда мы прокручиваем страницу. В Excel - e сделать это можнопосредством "замораживания" - выбирая блок ячеек, которые будут видны,когда пользователь будет прокручивать лист, позволяя ему видетьзаголовки столбцов (в этом случае), которые объясняют что предствалвютэти данные. То же самое возможно в PEAR::Spreadsheet_Excel_Writer:// Группа ячеек для замораживания
// 1-ый Аргумент - позиция вертикального обьединения
// 2-ой Аргумент - позиция горизонтального обьединения (0 = нет горизонтального обьединения)
// 3-ий Аргумент - верхняя видимая строка внизу вертикального объединения
// 4-ий Аргумент - левый видимый столбец после горизнотального объединения
$freeze = array( 3 , 0 , 4 , 0 ); // Заморозить эти ячейки!
$cart -> freezePanes ( $freeze ); Заметьте, что "замораживание" было применено непосредственнообъектом $cart, а не посредством объекта форматирования, так как онобыло применено к нескольким ячейкам. С другой стороны, форматированиебыло применено к отдельным ячейкам.Наконец я прохожу через обьекты в "своей сумке", добавляя данные к листу:// Псевдо данные
$items = array (
array( 'description' => 'Parrot' , 'price' => 34.0 , 'quantity' => 1 ),
array( 'description' => 'Snake' , 'price' => 16.5 , 'quantity' => 2 ),
array( 'description' => 'Mouse' , 'price' => 1.25 , 'quantity' => 10 ),
); // Используйте это для отслеживания текущего номера строки
$currentRow = 4 ; // Пройдите через данные, добавляя их в лист
foreach ( $items as $item ) {
// Write each item to the sheet
$cart -> writeRow ( $currentRow , 0 , $item );
$currentRow ++;
} Вот в принцыпе и все. Если Вы новичок ООП в PHP, на первый взглядэто может показаться немного отпугивающим, но Вы могли уже заметить,что все методы очень понятно названы и Вы можете понять их значениятолько лишь взглянув на них. Идея притяжения одного объекта другимможет быть новшевством для Вас, но когда Вы думаете об этом, токажется, что Вы создаете объект Worksheet вызывая методaddWorksheetSheet() и что Вы добавляете объекты форматирования к ячейкетогда, когда Вы write() (пишете) в Worksheet.
Добавление функцииExcel. Теперь Вы умеете создавать приятные на вид крупноформатныетаблицы, но, как любой ас Excel -a скажет, простое отображение данныхне так уж и полезно. Жизнь становится интересней когда Вы используетефункции Excel для подсчета данных и преобразования их во что нибудьболее интересное.Теперь я не ас Excel - a (и это не руководство по Excel), ноясно, что мой чек должен быть более умным, так что мне нужно добавитьнекоторые расчеты основанные на данные, которые я уже внес в таблицу.Для каждой строки я хочу отобразить "total item cost" (сумма купленныхвещей) - данные содержат цену единицы измерения а также количествокупленных изделий:"total item cost" = "unit price" * "number of items purchased" Переведя на термины Excel, для получения суммы пятой строки надо написать формулу подобную следующей:[Cell D5] =PRODUCT(B5:C5) Для достижения этого с PEAR::Spreadsheet_Excel_Writer, мне всеголишь нужно подправитьть код, который проводит проход через данные:// Используйте это для отслеживания текущего номера строки
$currentRow = 4 ; // Пройдите через данные, добавляя их в лист
foreach ( $items as $item ) {
// Добавление каждого предмета к листу
$cart -> writeRow ( $currentRow , 0 , $item ); // Помните, что Excel начинает подсчет строк с #1!
$excelRow = $currentRow + 1 ; // Создание строки PHP содержащую формулу
$formula = '=PRODUCT(B' . $excelRow . ':C' . $excelRow . ')' ; // Добавление формулы к строке
$cart -> writeFormula ( $currentRow , 3 , $formula ); $currentRow ++;
} Добавление формулы не предствляет из себя труда - мы всего лишьвоспользуемся методом writeFormula(). Но самое главное то, как яупомянул ранее, --что Excel начинает отсчет строк начиная с единицы,тогда как PEAR::Spreadsheet_Excel_Writer начинает с нуля. Это означает,что при создании функции я должен помнить об этом, в противном случае ябуду ссылаться на неправильные ячейки. Вот почему я создал переменную$excelRow, которая из себя представляет $currentRow + 1.Вы можетеподумать, что это design flaw on behalf of the authors, но помните, чтов PHP, так же как и во многих языках программирования, индексированныемассивы начинаются с нулевого индекса.Теперь мой отображает суммы для каждой строки. Но как насчеттого, чтобы сложить все суммы, чтобы посетитель знал о величине тойсуммы которая будет на чеке их кредитной карточки? Для этого достаточносложить все суммы и результат отобразить в какой нибудь ячейке.Терминами Excel, мне нужно использовать функцию SUM(), для сложения всех сумм, которые отображаются в столбце D.[Grand Total Cell] =SUM(D5:D7) Для помещения этого в лист, после того как проход через данные окончен, я добавляю следующее:// Первая строка, как Excel это понимает, - $currentRow был 4 в начале
$startingExcelRow = 5 ; // Последняя строка как в Excel
// (которая та же как и currentRow после окончания прохода)
$finalExcelRow = $currentRow ; // Формат Excel для получения суммы всех значений
$gTFormula = '=SUM(D' . $startingExcelRow . ':D' . $finalExcelRow . ')' ; // Некоторое дополнительное форматирование для ячеек общей суммы
$gTFormat =& $xls -> addFormat ();
$gTFormat -> setFontFamily ( 'Helvetica' );
$gTFormat -> setBold ();
$gTFormat -> setTop ( 1 ); // Top border
$gTFormat -> setBottom ( 1 ); // Bottom border // Добавление некоторого текста и форматирования
$cart -> write ( $currentRow , 2 , 'Grand Total:' , $gTFormat ); // Добавление формулы общей суммы с форматом
$cart -> writeFormula ( $currentRow , 3 , $gTFormula , $gTFormat ); Наконец я заканчиваю создание своего чека отправляя лист непосредственно в обозреватель.// Send the Spreadsheet to the browser
$xls -> send ( "phpPetstore.xls" );
$xls -> close ();