Спільне використання 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
<Users> <user account = "Megan" genre = "Portraits" images = "20" avgrank = "4.0" size = "72000" /> <user account = "Hannah" genre = "Landscapes" images = "31" avgrank = "3.5" size = "83000" /> <user account = "Vicky" genre = "Floral" images = "25" avgrank = "4.2" size = "42000" /> <user account = "Ian" genre = " Portraits "images =" 40 "avgrank =" 3.7 "size =" 92000 "/> <user account =" Michael "genre =" Landscapes "images =" 23 "avgrank =" 3.8 "size =" 72000 "/> <user account = "Daniel" genre = "Landscapes" images = "29" avgrank = "4.4" size = "85000" /> </ users>Кореневим елементом 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'])?