Excel на PHP - PHP та MySQL програмування

Зміст

Створення таблиць Excel засобами PHP

Очевидно, що у Вас є елементарні знання Excel, але нічого занадто серйозного.

Ознайомлення з PEAR::Spreadsheet_Excel_Writer. Працювати можна як самостійно, і через спеціалізований програмний FrameWork.

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

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

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

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

Давайте знімемо капелюхи перед Xavier Noguer, який зробив дивовижну роботу для впровадження цього PHP, за допомогою Mika Tuupola для Spreadsheet_Excel_Writer.

Зараз, без подальшого шуму, та озброєними повними знаннями управління пакету PEAR, який має бути встановлений, почнемо завантаження бібліотек. Відкрийте Ваш командний рядок та введіть у ньому наступне:

От і все. Ми готові!

Важливе зауваження : Для прикладів у цій статті я використав PEAR::OLE version 0.5 та PEAR::Spreadsheet_Excel_Writer version 0.7. Попереджаю, що дещо може зміниться в майбутніх версіях.

Продовжуємо наш шлях. Давайте створимо простий аркуш даних.

Відкрийте скрипт у Вашому браузері, (мається на увазі, що він «знаком» з Excel або Openoffice Calc) і він відобразить лист Excel з номерами від 0 до 10 у бінарному вигляді.

Збереження файлів Excel засобами PHP

У цьому випадку аркуш створюється динамічно – нічого не зберігається на сервері. Якщо Ви хочете замість цього створити файл, Ви можете відкинути частину для створення аркуша, який не змінився, для цього просто відправляючи конструктору ім'я файлу та шлях до нього, і цим уникаючи необхідності відправки HTTP заголовків:

Якщо Ви використовуєте систему сімейства Unix, то не забудьте змінити дозволи до папки, в якій Ви зберігаєте аркуші даних, щоб PHP зміг додати до них дані.

API PHP для роботи з Excel таблицями

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

• addWorksheet() - повертає випадок Spreadsheet_Excel_Writer_Worksheet. Більшість роботи виконується з випадку цього класу, даючи Вам можливість вписувати дані в осередки одного листа.
• addFormat() - повертає випадок Spreadsheet_Excel_Writer_Format, який використовується для додавання візуального форматування осередків.

Бібліотека також містить три інші класи, яких Ви повинні побоюватися, хоча Вам навряд чи колись буде потрібно їхвикористовувати.

• Spreadsheet_Excel_Writer_Validator уможливлює додавання перевірочних правил для осередків. Наразі для цього класу не існує документації. Воно як би є експериментальним кодом, отже я не буду його обговорювати. В основному він надає можливість перевірки даних введених в комірку кінцевим користувачем. Більш складні правила перевірки можна встановити з допомогою розширення класу. Клас Spreadsheet_Excel_Writer_Workbook надає метод addValidator() для створення випадку перевірки, в той час як Spreadsheet_Excel_Writer_Worksheet дає можливість правилам перевірки призначатися до осередків за допомогою методу setValidation()
• Spreadsheet_Excel_Writer_Parser, який є Parser - ом для аркушів даних Excel, і допомагає Вам перевірити, чи є функція правильним синтаксисом Excel.
• І нарешті – Spreadsheet_Excel_Writer_BIFFwriter – використовується для створення Формату Бінарних Файлів для зберігання файлів Excel. Якщо Ви цікавитеся зломом Excel, то Вам буде цікаво вивчити, що він робить, якщо ж ні, то Вам ні до чого хвилюватися про це, оскільки бібліотека повністю приховує цей клас.

Нульовий індекс Excel комірки

Один з методів примітки - 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, такі як для «заморожування» або «танення» частин аркуша, для форматування аркуша в цілому для друку і так далі. Про них я трохи розповім у наступних прикладах, але більшість ви повинні будете досліджувати самим.

Форматування Excel осередків засобами PHP

Так як щодо красивіших листів? Ми можемо досягти цього за допомогою PEAR::Spreadsheet_Excel_Writer, використовуючи функцію addFormat() для перетворення об'єкта в Spreadsheet_Excel_Writer_Format. Ми застосовуємо форматування до цього об'єкта, використовуючи методи, які він забезпечує, потім передаємо його методом write() функції Spreadsheet_Excel_Writer_Worksheet для додавання форматування комірці, яку ми додали.

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

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

Далі ми додамо заголовок до листа – зливаючи деякі осередки, для його розміщення. Тут ми отримаємо перше уявлення про те, як робиться форматування:

Спочатку зауважте, що я отримав об'єкт форматування викликавши addFormat() за допомогою об'єкта $xls, який представляє поточнийлист. Потім я застосував до об'єкта деяке специфічне форматування (методи під назвою setBold() говорять про себе - для більш докладної інформації дивіться документацію API).

Коли форматування закінчено, я викликаю функцію write() для об'єкта $cart, додавання до комірки, передаючи об'єкт як четвертий аргумент.

Єдиний нестандартний хід я тут зробив, це об'єднання чотирьох осередків. Викликаючи setAlign('merge') для об'єкта форматування (зазвичай Ви використовуєте для цього 'left', 'right' або 'center'), я наказав Spreadsheet_Excel_Writer, що він повинен об'єднати всі комірки до яких це форматування відноситься. Ось чому я створив три порожні осередки і застосував до них форматування.

Використання setRow() дозволяє мені змінити висоту рядка, зробивши його більше, ніж установка висоти рядка Excel за умовчанням. Цей метод має багато додаткових аргументів форматування, які дозволяють, наприклад, застосувати об'єкт форматування до поточного рядка. Подібно до setColumn() я можу встановити ширину стовпця і застосувати до нього подальше форматування. Різниця полягає в тому, що setRow() застосовується лише до одного рядка, коли setColumn() застосовується до багатьох стовпців.

Тепер потрібні дані для додавання до аркуша. Щоб не ускладнювати приклад (додаючи базу даних), я використовуватиму індексований масив асоціативних масивів, який нібито є результатом відбору SQL.

"Стовпці в таблиці" є ключами масиву - 'description', 'price' і 'quantity', друге, що ми повинні зробити, це додати заголовки стовпців з додатковим заголовком 'Total', який ми скоро будемо використовувати:

Ви вже бачили форматування. Ви раніше не бачили метод writeRow(). Цей метод робить те саме, що іwrite(), але дозволяє Вам додавати масив даних зліва направо, починаючи з певного номера рядка або стовпця. Цей метод дозволяє значно зменшити код програми.

Ще я хочу зробити так, щоб заголовки стовпців були завжди видно, коли ми прокручуємо сторінку. В Excel - e зробити це можна за допомогою «заморожування» - вибираючи блок осередків, які будуть видно, коли користувач прокручуватиме лист, дозволяючи йому бачити заголовки стовпців (у цьому випадку), які пояснюють, що представляють ці дані. Теж можливо в PEAR::Spreadsheet_Excel_Writer:

Зауважте, що «заморожування» було застосоване безпосередньо об'єктом $cart, а не за допомогою об'єкта форматування, оскільки воно було застосоване до кількох осередків. З іншого боку, форматування було застосовано до окремих осередків.

Нарешті я проходжу через об'єкти у своїй сумці, додаючи дані до листа:

Ось у принципі і все. Якщо Ви новачок ООП в PHP, на перший погляд це може здатися трохи відлякуючим, але Ви могли вже помітити, що всі методи дуже зрозуміло названі і Ви можете зрозуміти їх значення тільки глянувши на них. Ідея тяжіння одного об'єкта іншим може бути нововведенням для Вас, але коли Ви думаєте про це, то здається, що Ви створюєте об'єкт Worksheet викликаючи метод addWorksheetSheet() і що Ви додаєте об'єкти форматування до комірки тоді, коли Ви write() (пишете) в Worksheet.

Додавання/створення функцій у Excel засобами PHP

Тепер Ви вмієте створювати приємні на вигляд великоформатні таблиці, але, як будь-який ас Excel -a скаже, просте відображення даних не таке вже й корисне. Життя стає цікавішим, коли Ви використовуєте функції Excel для підрахунку даних і перетворення їх у щось цікавіше.

Тепер я неас Excel - a (і це не посібник з Excel), але ясно, що мій чек повинен бути розумнішим, так що мені потрібно додати деякі розрахунки на основі даних, які я вже вніс до таблиці. Для кожного рядка я хочу відобразити «total item cost» (сума куплених речей) - дані містять ціну одиниці виміру та кількість куплених виробів:

Перевівши на терміни Excel, для отримання суми п'ятого рядка треба написати таку формулу:

Для досягнення цього з PEAR::Spreadsheet_Excel_Writer, мені лише потрібно підправити код, який проводить прохід через дані:

Додавання формули не є складним - ми всього лише скористаємося методом writeFormula(). Але найголовніше те, як я згадав раніше, що Excel починає відлік рядків починаючи з одиниці, тоді як PEAR::Spreadsheet_Excel_Writer починає з нуля. Це означає, що при створенні функції я повинен пам'ятати про це, інакше я посилатимуся на неправильні осередки. Ось чому я створив змінну $excelRow, яка являє собою $currentRow + 1.Ви можете подумати, що це design flaw on behalf of the authors, але пам'ятайте, що в PHP, так само як і в багатьох мовах програмування, індексовані масиви починаються з нульового індексу.

Тепер мій відображає суми для кожного рядка. Але як щодо того, щоб скласти всі суми, щоб відвідувач знав про величину тієї суми, яка буде на чеку їхньої кредитної картки? Для цього достатньо скласти всі суми і результат відобразити в якомусь осередку.

Термінами Excel мені потрібно використовувати функцію SUM(), для складання всіх сум, які відображаються в стовпці D.

Для розміщення цього листа, після того як прохід через дані закінчено, я додаю наступне:

Нарешті я закінчую створеннясвого чека відправляючи лист безпосередньо у браузер.

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

UPD: 2016-05-20. Тема з експортом даних до Excel практично не актуальна.

---