Створення зведених таблиць за допомогою розширеного експорту Excel і PHP
Часто використовувані скорочення
- CSV: Comma-separated value - значення, розділені комами
- W3C: Консорціум World Wide Web
- XML: Extensible Markup Language - розширювана мова розмітки
Зведена таблиця - це таблиця, в якій користувач може динамічно вибирати, які поля використовуються в якості заголовків рядків і стовпців, і які дані відображаються в кожному осередку. Поля можна перегрупувати на льоту, що дозволяє отримувати відповіді на складні питання і будувати цікаві моделі представлення даних.
На малюнку 1 зображена проста таблиця Excel. У першому рядку містяться імена полів (Account, Genre, Images, Average Ran, Total Size), а в наступних рядках - самі дані. В даному випадку ми маємо справу з даними Web-сайту, на якому зберігаються зображення (наприклад, Flickr). Кожен рядок містить інформацію про зазначену облікового запису, включаючи жанр і кількість завантажених зображень, їх середній рейтинг, а також загальний обсяг в байтах.
Малюнок 1. Проста таблиця Excel
Для побудови зведеної таблиці в Excel необхідно вибрати стовпці даних, які будуть використовуватися в ній, а потім вибрати пункт PivotTable Report в меню Data. Після цього вам буде запропоновано вказати, які дані будуть використовуватися в майстра звітів. По завершенні роботи майстра в файлі Excel додасться новий лист.
На малюнку 2 зображено порожня зведена таблиця з плаваючою панеллю інструментів зведеної таблиці. Сторінка складається з областей, в які можна перетягнути кожен елемент поля для генерації звіту.
Малюнок 2. Створення зведеної таблиці
На малюнку 3 показаний більш детальний вид панелі інструментів PivotTable. Зверху розташовані меню PivotTable і кілька кнопок управління рівнями укладення таблиці, а знизу перераховані всі доступні поля (Account, Genre, Images, Average Ran, Total Size).
Малюнок 3. Панель інструментів зведеної таблиці
Для настройки зведеної таблиці необхідно вибирати поля і перетягувати їх до відповідних області сторінки. На малюнку 4 ви бачите результат перетягування полів genre, account і total size в область даних, розташовану в лівій частині звіту.
Малюнок 4. Зведена таблиця, заповнена даними
Тепер звіт відображає рядки, згруповані спочатку за жанром (genre), а потім за обліковими записами (account). У ньому ми бачимо загальний обсяг завантажених зображень для кожного облікового запису, результуючий обсяг для кожного жанру, і, нарешті, повний загальний обсяг всіх завантажених зображень.
При збереженні електронної таблиці Excel в форматі XML всі дані і параметри зведеної таблиці поміщаються в XML-файл. Фрагмент такого файлу показаний в лістингу 1.
Лістинг 1. Приклад експорту з Excel
Наш приклад PHP-додатки створює XML-файл Excel з двома листами: перший лист містить вихідні дані, а другий - зведену таблицю PivotTable. Ця зведена таблиця має всіма доступними динамічними властивостями, як якщо б вона була створена безпосередньо в Excel.
Перш ніж почати, необхідно отримати дані для нашої електронної таблиці.
Отримання даних
Щоб спростити собі задачу, створіть XML-файл з даними, як показано в лістингу 2.
Лістинг 2. файл data.xml
Кореневим елементом XML є елемент users. що містить вкладені елементи для кожного облікового запису користувача. Кожен елемент user містить атрибути account. genre. images. avgrank і size. Ці атрибути містять релевантні дані для кожного облікового запису.
Щоб прочитати ці дані, я написав простий файл data.php. що містить єдину функцію load_data. Ця функція зчитує XML-дані і повертає їх у вигляді масиву хеш-таблиць. Код цієї функції представлений в лістингу 3.
Лістинг 3. Файл data.php
Код починається зі створення об'єкта DOMDocument. який використовується для завантаження XML з файлу. Далі в циклі foreach виконуються ітерації по всіх облікових записів користувачів, і їх атрибути зберігаються в масиві даних, який потім повертаються викликає об'єкту.
Крім функції зчитування даних ми визначимо ще одну функцію print_file. яка роздруковує повне вміст файлу. Код цієї функції представлений в лістингу 4.
Лістинг 4. Файл helpers.php
Функція print_file потрібно нам по одній простій причині. Оскільки структура XML-файла Excel досить складна, то найпростішим способом його створення є отримання початкового файлу з XML і використання його фрагментів в якості вихідного коду експортованого файлу. У цьому випадку спочатку береться фрагмент файлу, починаючи від початку і закінчуючи тим місцем, де визначається перша книга, і поміщається в файл body_header.txt. Фрагмент файлу, починаючи від того місця, де закінчується остання книга, і до кінця, поміщається в файл body_footer.txt.
Такі ж файли data_header.txt і data_footer.txt розташовані на початку і кінці таблиці на вкладці Data.
Створення простої електронної таблиці Excel
Тепер, коли у нас є функції зчитування даних і друку в файл, можна приступати до створення електронних таблиць Excel. У лістингу 5 показаний перший варіант коду експорту в Excel.
Лістинг 5. Файл helpers.php
Спочатку виконується зчитування даних, а потім створюються файли body_header.txt і data_header.txt. Потім за допомогою XML-коду визначається таблиця, перший рядок якої містить заголовки. Далі в циклі foreach виконуються ітерації по всіх елементах даних і створюються рядки з даними.
У процесі виконання програми її висновок може бути збережений у файлі, який можна відкрити в Excel. В результаті у вас повинна вийти таблиця, зображена на малюнку 5.
Малюнок 5. Основна таблиця, експортована з PHP
На малюнку 5 ви бачите дані, відформатовані відповідно до вихідним файлом; в першому рядку відображаються заголовки полів, а в наступних рядках - самі дані.
Очевидно, що ви не захочете використовувати в точності такий код для своїх завдань експорту, оскільки в вашому випадку і поля, і дані будуть іншими. Проте, процес створення робочого листа залишається таким же: спочатку створюється файл Excel, потім з нього вирізаються фрагменти заголовка і нижнього колонтитула, після чого ви вставляєте в лист ваші власні дані.
Створення зведеної таблиці
Листи зведеної таблиці трохи складніше, але ідея та ж сама. Спочатку виконується читання даних, а потім роздруківка файлу body_header.txt. Після цього роздруковуються сторінки даних, зведеної таблиці і файлу body_footer.txt. Цей код представлений в лістингу 6.
Лістинг 6. Файл build2.php
Також представляють інтерес виклики функції header на початку сценарію, оскільки вони повідомляють браузеру про те, що необхідно робити з файлом. Тема Content-type повідомляє браузеру про те, що ця електронна таблиця повинна бути збережена у вигляді файлу з ім'ям pivot.xml.
Основну частину роботи виконують функції експорту, певні в файлі exporters.php. як показано в лістингу 7.
Лістинг 7. Файл exporters.php
Я видалив з лістингу більшу частину коду цих функцій для економії місця. Повний код ви можете знайти в файлі excelpivotxml.zip (див. Розділ Завантаження). Перша функція export_data виконує ті ж самі дії, що і перший сценарій (експорт даних). Для створення вкладки зведеної таблиці функція export_pivot спочатку створює робочий лист, а потім додає таблицю даних, опції листа і XML-код PivotTable. Після завершення створення робочого аркуша виконується підсумкове створення PivotCache.
Чесно кажучи, я не знаю, чому для визначення зведеної таблиці потрібно так багато XML-коду. В цьому випадку виразно використовується більше коду, ніж для створення простої таблиці з даними. Однак гарна новина полягає в тому, що ви можете створити зведену таблицю в Excel, зберегти її у вигляді XML-файла і використовувати його в якості шаблону для написання свого власного коду.
Коли PHP-сценарій закінчить роботу, збережіть результат в файл і відкрийте його в Excel. У підсумку ви повинні отримати файл, який ви бачите на малюнку 6.
Малюнок 6. Експортована зведена таблиця
У зведеній таблиці на малюнку 6 відображаються загальний обсяг завантажених зображень для кожного облікового запису і повний загальний обсяг всіх завантажених зображень. Зведена таблиця Excel була створена коректно, тому у вікні відображається панель інструментів PivotTable. Тепер за допомогою цієї панелі користувач може додавати поля і налаштовувати таблицю.
висновок
XML-формат, який використовується в Excel, може виявитися трохи складним, але ви можете використовувати збережений XML-файл в якості основи для створення власної продукції, що експортується електронної таблиці. Цей підхід істотно спрощує процес генерації файлів і дозволяє користувачам створювати електронні таблиці в такому вигляді, в якому вони хочуть їх бачити, після чого ці таблиці можна експортувати і наповнювати даними на льоту. Це чудовий спосіб надати вашим клієнтам нові дані, не залишаючи світ PHP і XML.