Спільне використання PHP і функцій розширеного експорту Excel для створення зведених таблиць

Створення зведених таблиць за допомогою розширеного експорту Excel і PHP
Малюнок 1. Проста таблиця Excel
Малюнок 2. Створення зведеної таблиці
Малюнок 3. Панель інструментів зведеної таблиці
Малюнок 4. Зведена таблиця, заповнена даними
Лістинг 1. Приклад експорту з Excel
Отримання даних
Лістинг 2. файл data.xml
Лістинг 3. Файл data.php
Лістинг 4. Файл helpers.php
Створення простої електронної таблиці Excel
Лістинг 5. Файл helpers.php
Малюнок 5. Основна таблиця, експортована з PHP
Створення зведеної таблиці
Лістинг 6. Файл build2.php
Лістинг 7. Файл exporters.php
Малюнок 6. Експортована зведена таблиця
висновок
Ресурси для скачування

Створення гнучких, що експортуються електронних таблиць з XML-шаблонів

Шість років тому я написав статтю " Читання і запис даних Excel з додатків PHP "(EN), в якій розповідалося про використання PHP для експорту даних у вигляді електронних таблиць Microsoft® Excel®. Ця стаття виявилося досить популярною для того, щоб отримати своє продовження. У першій статті я лише поверхово торкнувся всієї потужності Excel. У цій статті я приведу приклад використання можливостей експорту Excel і покажу, як використовувати більш просунуту функцію - зведені таблиці.

Створення зведених таблиць за допомогою розширеного експорту 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

<? Xml version = "1.0"?> <Workbook xmlns = "urn: schemas-microsoft-com: office: spreadsheet" xmlns: o = "urn: schemas-microsoft-com: office: office" xmlns: x = "urn : schemas-microsoft-com: office: excel "xmlns: dt =" uuid: C2F41010-65B3-11d1-A29F-00AA00C14882 "xmlns: s =" uuid: BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882 "xmlns: rs =" urn: schemas-microsoft-com: rowset "xmlns: z =" # RowsetSchema "xmlns: ss =" urn: schemas-microsoft-com: office: spreadsheet "xmlns: html =" http://www.w3.org/ TR / REC-html40 "> <DocumentProperties xmlns =" urn: schemas-microsoft-com: office: office "> <Author> Jack Herrington </ Author> <LastAuthor> Jack Herrington </ LastAuthor> <Created> 2011-03- 26T21: 15: 20Z </ Created> <LastSaved> 2011-03-26T21: 45: 33Z </ LastSaved> <Company> Myself </ Company> <Version> 12.0 </ Version> </ DocumentProperties> ... </ Workbook>

Наш приклад 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

<? Php function load_data () {$ xmlDoc = new DOMDocument (); $ XmlDoc-> load ( "data.xml"); $ Data = array (); foreach ($ xmlDoc-> documentElement-> childNodes AS $ item) {if ($ item-> nodeType == XML_ELEMENT_NODE) {$ data [] = array ( "account" => $ item-> getAttribute ( "account"), "genre" => $ item-> getAttribute ( "genre"), "images" => $ item-> getAttribute ( "images"), "avgrank" => $ item-> getAttribute ( "avgrank"), "size "=> $ item-> getAttribute (" size ")); }} Return $ data; }?>

Код починається зі створення об'єкта DOMDocument, який використовується для завантаження XML з файлу. Далі в циклі foreach виконуються ітерації по всіх облікових записів користувачів, і їх атрибути зберігаються в масиві даних, який потім повертаються викликає об'єкту.

Крім функції зчитування даних ми визначимо ще одну функцію print_file, яка роздруковує повне вміст файлу. Код цієї функції представлений в лістингу 4 .

Лістинг 4. Файл helpers.php

<? Php function print_file ($ file) {print file_get_contents ($ file); }?>

Функція 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

<? Php require_once 'data.php'; require_once 'helpers.php'; require_once 'exporters.php'; $ Data = load_data (); print_file ( 'body_header.txt'); print_file ( 'data_header.txt'); ?> <Table ss: ExpandedColumnCount = "5" ss: ExpandedRowCount = "<? Php echo (count ($ data) + 1)?>" X: FullColumns = "1" x: FullRows = "1"> <Row> <Cell ss: StyleID = "s21"> <Data ss: Type = "String"> Account </ Data> </ Cell> <Cell ss: StyleID = "s21"> <Data ss: Type = "String"> Genre </ Data> </ Cell> <Cell ss: StyleID = "s21"> <Data ss: Type = "String"> Images </ Data> </ Cell> <Cell ss: StyleID = "s21"> <Data ss : Type = "String"> Average Ranking </ Data> </ Cell> <Cell ss: StyleID = "s21"> <Data ss: Type = "String"> Total Size </ Data> </ Cell> </ Row > <? php foreach ($ data as $ row) {?> <Row> <Cell> <Data ss: Type = "String"> <? php echo ($ row [ 'account'])?> </ Data> </ Cell> <Cell> <Data ss: Type = "String"> <? php echo ($ row [ 'genre'])?> </ Data> </ Cell> <Cell> <Data ss: Type = " Number "> <? php echo ($ row [ 'images'])?> </ Data> </ Cell> <Cell> <Data ss: Type =" Number "> <? php echo ($ row [ 'avgrank' ])?> </ Data> </ Cell> <Cell> <Data ss: Type = "Number"> <? php echo ($ row [ 'size'])?> </ Data> </ Cell> </ Row> <? php}?> </ Table> <? php print_file ( 'data_footer.txt'); print_file ( 'body_footer.txt'); ?>

Спочатку виконується зчитування даних, а потім створюються файли body_header.txt і data_header.txt. Потім за допомогою XML-коду визначається таблиця, перший рядок якої містить заголовки. Далі в циклі foreach виконуються ітерації по всіх елементах даних і створюються рядки з даними.

У процесі виконання програми її висновок може бути збережений у файлі, який можна відкрити в Excel. В результаті у вас повинна вийти таблиця, зображена на малюнку 5 .

Малюнок 5. Основна таблиця, експортована з PHP

на малюнку 5 ви бачите дані, відформатовані відповідно до вихідним файлом; в першому рядку відображаються заголовки полів, а в наступних рядках - самі дані.

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

Створення зведеної таблиці

Листи зведеної таблиці трохи складніше, але ідея та ж сама. Спочатку виконується читання даних, а потім роздруківка файлу body_header.txt. Після цього роздруковуються сторінки даних, зведеної таблиці і файлу body_footer.txt. Цей код представлений в лістингу 6 .

Лістинг 6. Файл build2.php

<? Php require_once 'data.php'; require_once 'helpers.php'; require_once 'exporters.php'; header ( 'Content-type: application / excel'); header ( 'Content-Disposition: attachment; filename = "pivot.xml"'); $ Data = load_data (); print_file ( 'body_header.txt'); export_data ($ data); export_pivot ($ data); print_file ( 'body_footer.txt'); ?>

Також представляють інтерес виклики функції header на початку сценарію, оскільки вони повідомляють браузеру про те, що необхідно робити з файлом. Тема Content-type повідомляє браузеру про те, що ця електронна таблиця повинна бути збережена у вигляді файлу з ім'ям pivot.xml.

Основну частину роботи виконують функції експорту, певні в файлі exporters.php, як показано в лістингу 7 .

Лістинг 7. Файл exporters.php

<? Php require_once 'helpers.php'; function export_data ($ data) {print_file ( 'data_header.txt'); ?> <Table ss: ExpandedColumnCount = "5" ss: ExpandedRowCount = "<? Php echo (count ($ data) + 1)?>" X: FullColumns = "1" x: FullRows = "1"> ... </ Table> <? php print_file ( 'data_footer.txt'); } Function export_pivot ($ data) {?> <Worksheet ss: Name = "Pivot 1"> <? Php export_pivot_table ($ data); export_pivot_worksheet_options ($ data); export_pivot_pivottable ($ data); ?> </ Worksheet> <? Php export_pivotcache ($ data); } Function export_pivot_table ($ data) {?> <Table ss: ExpandedColumnCount = "2" ss: ExpandedRowCount = "<? Php echo (count ($ data) + 5)?>" X: FullColumns = "1" x: FullRows = "1"> <Column ss: Width = "96.0" /> <Column ss: AutoFitWidth = "0" ss: Width = "49.0" /> <Row ss: Index = "3"> <Cell ss: StyleID = "s22"> <Data ss: Type = "String"> Sum of Total Size </ Data> </ Cell> <Cell ss: StyleID = "s24" /> </ Row> <Row> <Cell ss: StyleID = "s34"> <Data ss: Type = "String"> Account </ Data> </ Cell> <Cell ss: StyleID = "s24"> <Data ss: Type = "String"> Total </ Data> </ Cell> </ Row> <? php $ total = 0; foreach ($ data as $ row) {$ total + = intval ($ row [ 'size']); ?> <Row> <Cell ss: StyleID = "s22"> <Data ss: Type = "String"> <? Php echo ($ row [ 'account'])?> </ Data> </ Cell> <Cell ss: StyleID = "s26"> <Data ss: Type = "Number"> <? php echo ($ row [ 'size'])?> </ Data> </ Cell> </ Row> <? php}? > <Row> <Cell ss: StyleID = "s31"> <Data ss: Type = "String"> Grand Total </ Data> </ Cell> <Cell ss: StyleID = "s33"> <Data ss: Type = "Number"> <? php echo ($ total)?> </ Data> </ Cell> </ Row> </ Table> <? php} function export_pivot_pivottable ($ data) {?> <PivotTable xmlns = "urn: schemas-microsoft-com: office: excel "> ... </ PivotTable> <? php} function export_pivot_worksheet_options ($ data) {?> <WorksheetOptions xmlns =" urn: schemas-microsoft-com: office: excel ">. .. </ WorksheetOptions> <? php} function export_pivotcache ($ data) {?> <PivotCache xmlns = "urn: schemas-microsoft-com: office: excel"> ... <? php foreach ($ data as $ row ) {?> <row Col1 = "<? php echo ($ row [ 'account'])?>" Col2 = "<? php echo ($ row [ 'genre'])?>" Col3 = "<? php echo ($ row [ 'images'])?> "Col4 =" <? php echo ($ row [ 'avgrank'])?> "Col5 =" <? php echo ($ row [ 'size'])?> "xmlns =" # RowsetSchema "/> <? php}?> </ data> </ PivotCache> <? 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.

Ресурси для скачування

Схожі теми

Оригінал статті: Combine advanced spreadsheet export with PHP to create pivot tables (EN).
Читання і запис даних Excel в додатках PHP (EN) (Джек Д. Херрингтон, developerWorks; оновлена в серпні 2010, вперше опублікована в жовтні 2005) - прочитайте першу статтю про використання XML в PHP, що дозволяє зчитувати дані, експортовані з Excel 2003 в XML-форматі.
XML-формати Microsoft Office (EN) (Вікіпедія) - прочитайте обговорення файлового формату Excel в порівнянні з аналогічними форматами XML, особливо з форматом Open Office .
Web-сайт PHP: Hypertext Preprocessor website (EN) - відвідайте найкращий ресурс, присвячений PHP.
Web-сайт консорціуму W3C (EN) - на цьому чудовому сайті ви знайдете інформацію про різні стандарти, в тому числі і про стандарті XML (EN), які можуть застосовуватися до цієї статті,.
Підтримувані Excel формати файлів - отримаєте більше інформації на Web-сайті Microsoft.
XML-формати Microsoft Office (EN) (Wikipedia) - всі програми Microsoft Office підтримують XML. Якщо ви не використовуєте Excel, дізнайтеся про інші формати, які можуть виявитися корисними для вас.
Інші статті цього автора (EN) (Джек Д. Херрингтон, developerWorks, починаючи з березня 2005) - прочитайте інші статті Джека, присвячені Ajax, JSON, PHP, XML і інших технологій.
Сертифікація IBM XML (EN) - дізнайтеся, як стати сертифікованим IBM розробником в області XML і пов'язаних технологій.

Підпишіть мене на повідомлення до коментарів

Quot;?
Php function print_file ($ file) {print file_get_contents ($ file); }?
Php'; $ Data = load_data (); print_file ( 'body_header.txt'); print_file ( 'data_header.txt'); ?
Gt; <Table ss: ExpandedColumnCount = "5" ss: ExpandedRowCount = "<?
Php echo (count ($ data) + 1)?
Php foreach ($ data as $ row) {?
Gt; <Row> <Cell> <Data ss: Type = "String"> <?
Php echo ($ row [ 'account'])?

Fast : Интернет-провайдеры и сети