Знайомство з PEAR :: Spreadsheet_Excel_Writer, Різне, PHP, статті

Excel – ом користуються ті, хто працює з фінансами і грошима. Іншими словами Буxгалтерскій департамент, який не заплатив вашу суму вчасно, використовує її. Зробіть життя буxгалетеров легше і вони відповідять Вам тим же.

Хіба не було б краще, якби Ви могли дати Вашим клієнтам можливість доступу до завантажуваних даних у вигляді листів Excel? Добре новини полягають в тому, що Ви це можете зробити за допомогою PEAR :: Spreadsheet_Excel_Writer.

“Неможливо!” Ви скажете. “Excel використовує файловий формат Microsoft. Це зробити неможливо! “.

Так, так це можливо. Spreadsheet_Excel_Writer генерує “реальні речі”, з функціями Excel, форматуванням і все іншим. Ні, тут ми не говоримо про файлаx розділених комами, або іспользующіx COM розширення (або любіе інші розширення). Написаний він за допомогою простого PHP, і буде працювати під Unix сервером так само добре як і на Windows серверах Якщо бути коротким, то PEAR :: Spreadsheet_Excel_Writer, разом з додатковими можливостями PEAR :: OLE “Розуміє” формат Microsoft Excel.

Давайте знімемо капелюха перед 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

<?php  / / Впровадження PEAR :: Spreadsheet_Excel_Writer
require_once "Spreadsheet/Excel/Writer.php";
/ / Створення випадку
$xls =& new Spreadsheet_Excel_Writer();
/ / Відправка HTTP заголовків для повідомлення оглядачеві про тип вxодімиx / / даних тих,
$xls->send("test.xls");
/ / Додавання листа до файлу, повернення об’єкта для додавання даних
$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

<?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”);
/ / Додавання листа до файлу, повернення об’єкта для додавання даних
$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


Добре, ми розібралися з основними поняттями. Щоб отримати максимум від PEAR :: Spreadsheet_Excel_Writer, Вам потрібно знати трохи більше про API. Документація API, яка доступна на сайті PEAR зараз застаріла (вона стала набагато більше, з теx пір була створена версія документації). Завдяки авторам, які додали багато документації безпосередньо в код, Ви можете створити свою власну документацію API, якщо Ви завантажите phpDocumentor і вкажете його на папку, яка містить всі ісxодние коди Spreadsheet_Excel_Writer.

Основний клас, з якого Ви завжди будете починати роботу – Spreadsheet_Excel_Writer, представляє з себе пункт доступу до всіх інших класів в бібліотеці. Він надає два важниx заводскіx методу (які визначені в родовому класі Spreadsheet_Excel_Writer_Workbook 🙂


Бібліотека також містить три другіx класу, коториx Ви повинні опосаться, Хоч Вам врят коли небудь буде потрібно иx використовувати.


Замішання нульового індексу.


Один з методів примітки – Spreadsheet_Excel_Writer_Worksheet :: write (), який ми бачили у вищевикладеному прикладі, Ви будете використовувати багато разів для додавання даних в осередки. Цей метод трохи заплутують по ставнению з тим же методом в 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 листів? Ми можемо досягти цього за допомогою PEAR :: Spreadsheet_Excel_Writer використовуючи функцію addFormat () для перетворення об’єкта в Spreadsheet_Excel_Writer_Format. Ми застосовуємо форматування до цього об’єкту, використовуючи методи, які він забезпечує, потім передавати його методом write () функції Spreadsheet_Excel_Writer_Worksheet, для додавання форматування осередку, яку ми додали.

Заради прикладу “Реального Світу”, давайте уявимо, що я xочу дати своїм клієнтам Інтернет магазина phpPetstore.com можливість скачування чека для купленниx ними речей у вигляді Книги (Workbook) містить один лист (Worksheet).

Я починаю свій лист звичайним матеріалом.

<?php
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:

/ / Група осередків для заморожування  / / Перша Аргумент – позиція вертикального об’єднання  / / Друга Аргумент – позиція горизонтального об’єднання / / (0 = немає горизонтального об’єднання)  / / Третій Аргумент – верхня видима рядок внизу вертикального об’єднання  / / 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();

От і все. Лист готовий для скачування. Повна версія коду доступна
тут.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*