* Мова розробки: PHP 5.3 +
* Охоплені теми: MySQL, PHP, PHPExcel, PDO
* Рівень складності: середній
* Час на завершення: 1 година
* Прикріплені файли
Файлу формату CSV (Comma Separated Value або «Значення, розділені комами») зазвичай досить для експорту даних MySQL в таблицю Excel. Проте, в фалах CSV присутні тільки дані. У реальному таблиці Excel є форматування, формули і, часом, навіть графічні елементи. Тобто тут різниця дорівнює відмінностей між звичайною передачею даних і професійним звітом.
Даний посібник розповість вам про те, як скористатися PHP-компонентами (які поширюються з відкритим вихідним кодом) для створення справжніх таблиць Excel з покажчиків MySQL SELECT. Цікаво? Тоді давайте приступимо!
Основна взаємодія між таблицями даних і електронними таблицями
Значення SQL SELECT повертає нам набір результатів, а точніше, добірку колонок з іменами, в осередках яких містяться дані. У PHP / MySQL кожна клітинка результату може бути (і часто так і буває) представлена у вигляді асоціативного масиву, в якому ключ до масиву - це назва колонки.
Електронна таблиця Excel - це певна кількість сторінок, в яких можна бачити пронумеровані рядки і впорядковані за алфавітом колонки.
Метод полягає в тому, щоб одне значення SQL було задано одним робочим сторінці, чого можна домогтися за рахунок збігу імені колонки в асоціативному масиві, який був повернутий командою вилучення PDO, і букви колонки в електронній таблиці.
Етап 1: вчимося правильно виставляти найменування колонок за алфавітом
Ключовий момент даної техніки полягає в алгоритмі називання колонок, який дозволяє вам правильно називати колонки в алфавітному порядку. Як було зазначено раніше, в електронних таблицях Excel колонки ідентифікуються буквами, а рядки визначаються числами. Якщо вас запит містить менше 26 колонок, ви можете просто зупинитися на букві Z. Проте, багато запити містять набагато більше колонок.
Схема найменування колонок в Excel
Коли назви колонок доходять до букви Z, до імені починає додаватися ще одна буква, тобто A ... Z, AA, AB ... AZ. Після AZ піде BA (як можна бачити нижче).
Алгоритм найменування колонок
Наступний код PHP показує нам, як правильно називати колонки від А до ZZ.
Даний алгоритм не піде далі ZZ. Алгоритм виконується (трохи в іншому вигляді) за допомогою додаткового файлу в призначеній для користувача функції MySqlExcelBuilder :: mapColumns.
Етап 2: Форматування і тестування SQL-значень
Представлена нижче схема відображає нам спрощене взаємодія клієнт / замовлення. Значення SQL CREATE для наведеної нижче схеми включені в заархівований в zip-формат файл xls_sample.sql, який прикладений до керівництва.
«Швидке і брудне» значення SELECT
Зазвичай тут можна просто швидко витягти дані з значення SQL в таблицях:
Головна перевага подібного методу полягає в економії часу. Хоча результати рідко виглядають прийнятно.
PHPMyAdmin - це інструмент для адміністрування баз даних MySQL, який часто надається разом з планом хостингу. Для структурування і тестування ваших сторінок з електронними таблицями, ви можете використовувати різні SQL-інструменти, які йдуть в комплекті з PHPMyAdmin.
Наступний скріншот відображає нам результат вищенаведеного запиту:
Якщо розглянути колонки ближче, то видно, що їх імена цілком значущі і зручні для програмістів, але навряд чи це буде привабливим для ділових користувачів.
Імена колонок написані з маленької літери, і замість пробілів ми бачимо нижнє підкреслювання.
Отримуємо привабливіший результат з значення SELECT
Нам потрібно відформатувати наше значення SELECT, щоб воно виглядало серйозно і привабливо. Отже, використовуючи інструмент PHPMyAdmin, відредагуйте значення SQL так, щоб назвами колонок були реальні слова, і щоб відображалися тільки потрібні користувачам колонки. Переформатувати значення SQL виглядає приблизно так:
Що в результаті дає:
Вищенаведений прототип демонструє нам те, як може виглядати електронна таблиця.
Етап 3: Відображення результатів MySQL на сторінці електронної таблиці
Клас MySqlExcelBuilder включає в себе функціонал, що потребується для того, щоб додати значення SQL в сторінку електронної таблиці Excel за допомогою PDO і PHPExcel. Повний клас знаходиться в супутньому zip-файлі.
Даний клас дозволяє на сторінках із заданим ім'ям розмістити випадкове число результатів SQL. Наступний уривок коду відображає нам важливі дані.
* $ Pdo - це об'єкт даних в PHP, що використовується для створення запиту до бази даних.
* $ PhpExcel - це об'єкт PHPExcel, що використовується для створення та управління електронною таблицею.
* $ Sql_pages - утримує SQL-значення і інформацію про ім'я та форматуванні сторінки.
* Конструктор (не відображене) ініціалізує дані з PDO і PHPExcel.
Підготовка кожної сторінки
Нижчевикладене зображення електронної таблиці - це прототип, створений в Excel для того, щоб показати нам, як повинна виглядати електронна таблиця.
Функція add_page використовується для додавання значення SQL в сторінки з ім'ям:
sql_pages утримує інформацію, яку ми хочемо використовувати для занесення sql на сторінки.
Приклад використання користувальницької функції
Даний код є прикладом того, як слід використовувати призначену для користувача функцію add_page:
Нижчевикладене зображення відображає нам, як для користувача функція add_page повинна бути визначена на електронній таблиці.
Етап 4: Розробка електронної таблиці
Якщо ви розумієте, як управляти електронною таблицею Excel за допомогою миші і клавіатури, то ви напевно швидко освоїте PHPExcel. PHPExcel заснований на принципі управління моделлю електронної таблиці при використанні команд, схожих з командами, які ви привласнюєте в Excel. У документації для розробників по PHPExcel все розписано більш докладно.
Користувацька функція getExcel ()
Користувацька функція getExcel () використовує PHPExcel для створення окремих робочих таблиць з SQL-значень, які ви визначали в 3 етапі. Після того, як будуть створені робочі таблиці, об'єкт PHPExcel повертається до запитуваного. Нижче ви можете бачити опис 4 основних відділів користувальницької функції getExcel.
А. Повторення на сторінках
Основний цикл даної користувальницької функції повторюється на сторінках, які раніше були додані за допомогою add_page. У кожному повторенні, в об'єкті phpExcel створюється наступна сторінка, а потім додаються дані. Користувацька функція createSheet в PHPExcel використовується для створення нових робочих таблиць для кожної раніше доданої сторінки.
Зображення, наведене нижче, відображає нам процес взаємодії коду з таблицею. column_map обговорюється далі в керівництві.
Б. Логіка «Початок сторінки»
На початку кожної сторінки є спеціальне форматування. Спочатку для кожної конкретної сторінки з бази даних витягується рядок, яка виконує завдання, потрібні для твору форматування на початку сторінки. Вона залучає для користувача функцію mapColumns, яку ми обговорювали в етапі №1. PHPExcel, як і Excel, використовує пару БукваЧісло для визначення конкретної комірки. У MySqlExcelBuilder це розглядається у вигляді ключа осередки (cellKey). Ключ осередку створюється за рахунок з'єднання заголовка стовпчика з числом рядки.
Деякі додаткові аспекти, які слід враховувати в вищенаведеному уривку коду:
* SetCellValue - вносить актуального значення в поле. Врахуйте, що осередок є частиною робочої таблиці. Певна осередок визначається змінної cellKey.
* GetStyle - даний параметр повертає дані для атрибута стилю конкретної комірки, тому даним значенням можна управляти.
* GetColumnDimension - це метод об'єкта робочої таблиці. Ширина колонки прив'язана до змінної col.
В. Заповнення даними
Завдяки підготовчих робіт і вказівкою колонок процес, в дійсності, тепер буде заповнювати відповідну клітинку відповідними даними. Ми розглядаємо робочу таблицю на наявність певної колонки даних, створюємо ключ осередку, а потім вносимо значення в клітинку.
Г. Додаємо формули
Остання частина getExcel () показує нам, як в робочу таблицю PHPExcel добвалять формули. В даному випадку, тут все відноситься до колонки. PHPExcel вносить формули в осередку точно також, як ви б вносили їх в робочу таблицю в Excel. Значення осередків починається зі знака рівності (=), а потім слід формула. В даному випадку, у нас йде сума кількох осередків. Дивимося далі:
А ось так виглядає код:
Етап 5: Вносимо останні штрихи
Після того як ви вже отримали робочу таблицю Excel, заповнену за допомогою бази даних MySQL і getExcel, прийшов час внести фінальні штрихи. У нашому прикладі ми задали заголовок кожної робочої таблиці.
Етап 6: Збереження файлу
PHPExcel використовує виробництво об'єктів для створення елемента, який би вписував дані в ваші таблиці в правильному форматі. В даному випадку ми використовували Excel5, так як його можуть прочитати навіть старі програми, тобто даний звіт автоматично стає доступним більшій аудиторії.
Нижчевикладене зображення відображає нам підсумковий скріншот з нашим проектом. Двохсторінкова таблиця, наповнена даними з баз даних і оформлена для користувача заголовками:
Як тільки дані з бази будуть в об'єкті PHPExcel, ви можете використовувати інші функції класу PHPExcel для відтворення додаткового форматування, додавання більшої кількості формул, збереження їх у файли різних форматів і багато іншого.
Клас MySqlExcelBuilder, наприклад, може бути розширений для використання функцій PHPExcel, щоб заповнити існуючий шаблон електронної таблиці даними з бази MySQL. Так як PDO є інтерфейсом бази даних, DSN в конструкторі MySqlExcelBuilder може бути без проблем змінений для роботи з іншими базами даних.
Спасибі за те, що були з нами!