Читання та запис даних Excel із додатків PHP

Використання підтримки XML

Пакет Microsoft Office 2003 для операційної системи Microsoft Windows® відкрив для розробників низку нових можливостей, з якими вони не стикалися раніше. Звичайно, у вашому розпорядженні був набір нових функцій, однак найбільшою перевагою стало додавання підтримки файлових XML-форматів. У Office 2003 можна зберегти електронну таблицю Microsoft Excel у XML-форматі та використовувати ХМL-файл так само, як і його двійковий еквівалент. Те саме стосується і Microsoft Word.

У цій статті я покажу, як написати Web-додаток на PHP, який використовує ці формати для читання даних з електронної таблиці Excel та запису їх у БД, а також для експорту вмісту таблиці БД до електронної таблиці Excel.

Створення бази даних

Синтаксис для створення схеми бази даних MySQL виглядає наступним чином.

Лістинг 1. SQL код для створення схеми бази даних

Створіть базу даних за допомогою інструмента командного рядка Mysqladmin: mysqladmin --user=root create names . Потім завантажте дані про таблицю з файлу схеми: mysql --user=root names . Ім'я користувача та пароль, що використовуються, залежать від налаштувань вашого екземпляра MySQL, але сама ідея не змінюється – спочатку створюється база даних, а потім за допомогою SQL-файлу створюються таблиці з необхідними полями.

Отримання даних для імпорту

Тепер потрібно отримати дані, які імпортуватимуться. Для цього створіть новий файл Excel. У верхньому осередку кожного стовпця введіть значенняFirst,Middle,Last таEmail. Після цього додайте кілька рядків даних (рисунок 1).

Рисунок 1. Дані для імпорту

Ви можете створити список будь-якої довжини абозмінити поля так, як вважаєте за потрібне. PHP-сценарій імпортування, що розглядається в цій статті, беззастережно ігнорує перший рядок даних, вважаючи його заголовком. У робочому додатку, ви можете зчитувати та виконувати розбір рядка заголовків, щоб визначати, які поля містяться в стовпцях, та вносити відповідні зміни до логіки імпорту.

Останній крок – це збереження файлу у форматі XML. Для цього клацніть пункт менюFile > Save As і в діалоговому вікні Save As виберіть форматXML Spreadsheet з розкривного спискуSave as type (рисунок 2).

Малюнок 2. Збереження файлу як електронної таблиці XML

Після створення XML-файлу можна приступати до написання програми PHP.

Імпорт даних

Імпорт даних починається зі створення нескладної сторінки, де вибирається вхідний XML-файл Excel (рисунок 3).

Рисунок 3. Вибір вхідного файлу XML Excel

Код цієї сторінки показано у лістингу 2.

Лістинг 2. Код сторінки завантаження

Я надав файлу розширення .php, хоча насправді це зовсім не PHP. Це звичайний HTML-файл, що дозволяє користувачеві вказати файл і передати його на сторінку import.php, де починається все найцікавіше.

Читання XML-даних Excel

Для простоти розділив написання сторінки import.php на два етапи. На першому етапі виконується розбір XML-даних та їх виведення у формі таблиці. З другого краю етапі додається логіка, у якій реалізовано додавання записів до бази даних.

У лістингу 3 показаний приклад файлу XML Excel 2003.

Лістинг 3. Приклад XML-файлу Excel

Перший об'єкт Worksheet містить реальні дані. Дані в цьому об'єкті розташовуються всередині тега Table, який всвою чергу містить набір тегів Row і Cell. У кожному тезі Cell розташовується пов'язаний з ним тег Data, що містить дані клітинки. У прикладі дані завжди представлені у вигляді рядків (тип String).

За замовчуванням при створенні нового документа Excel створюються три робочі аркуші з іменами Sheet1, Sheet2 та Sheet3. Я не став видаляти другий і третій аркуш, тому наприкінці лістингу ви можете бачити ці порожні аркуші.

У лістингу 4 показано першу версію сценарію import.php.

Лістинг 4. Перша версія сценарію імпорту

Сценарій починається з читання тимчасового файлу, завантаженого в об'єкт DOMDocument. Потім виконується пошук усіх тегів Row. Перший рядок пропускається відповідно до логіки обробки змінної $first_row . Далі для кожного рядка виконується циклічний аналіз кожного тега Cell, що міститься в ньому.

Наступна хитрість полягає у визначенні шпальти, в якому ви знаходитесь. Як видно з лістингу 3, у тегу Cell не вказано номер рядка або стовпця – слід стежити за цим сценарій. Насправді все ще трохи складніше. Насправді тег Cell містить атрибут ss:Index , що вказує, у якому стовпці знаходиться осередок, якщо у поточному рядку присутні порожні стовпці. Це те, що шукає код функції getAttribute('index') .

Після визначення індексу нічого складного. Значення осередку поміщається у локальний елемент, пов'язаний із цим полем. Потім наприкінці рядка викликається функція add_person додавання даних про людину в результуючий набір

Наприкінці з допомогою звичайних функцій PHP знайдені дані виводяться як HTML-таблиці (рисунок 4).

Малюнок 4. Виведення даних у вигляді HTML-таблиці

Наступним кроком потрібно завантажити інформацію до бази даних.

Додавання інформації до бази даних

Після додавання отриманого вмісту рядка до структури PHP необхідно занести його до бази даних. Для цього я додав код, який використовує модуль Pear DB (листинг 5).

Лістинг 5. Друга версія сценарію імпорту

На малюнку 5 показано виведення даних у браузері Firefox.

Рисунок 5. База даних

Результат виглядає не дуже красиво, але це не має значення. Важливо те, що за допомогою операторів prepare та execute можна додати інформацію до бази даних. Щоб показати це, я створив ще одну сторінку під назвою list.php, яка відображатиме інформацію, отриману з бази даних (листинг 6).

Лістинг 6. List.php

Ця проста сторінка починається із застосування SQL-функції select до таблиці names. Після цього створюється таблиця, в яку за допомогою методу fetchInto додаються всі рядки.

На малюнку 6 показані дані, які відображаються цією сторінкою.

Малюнок 6. Дані на сторінці list.php

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

Створення файлу XML для експорту в Excel

Завершальним кроком є створення файлу XML Excel. Я почав з того, що скопіював XML-міст Excel у PHP-сценарій (листинг 7), оскільки це найпростіший спосіб отримати XML-файл Excel, який буде коректно проаналізований (оскільки Excel дуже вимогливий до свого XML-формату).

Лістинг 7. Сторінка експорту XML

Сценарій починається з того, що для результуючого виводу встановлюється формат XML. Це важливо, оскільки в іншому випадку браузери сприйматимуть цейкод просто як неправильний HTML.

Я змінив частину коду, що містить SQL запит, таким чином, щоб результати запиту зберігалися в масив. В даному випадку необхідно помістити в атрибут ss:ExpandedRowCount кількість рядків плюс один рядок (що відповідає за заголовки). Якби це була звичайна сторінка звіту, додатковий рядок заголовків був би не потрібний.

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

Рисунок 7. Отриманий вміст XML у браузері Firefox

Не дуже вражає. Але подивіться, що станеться, коли я відкрию це посилання в Internet Explorer (рисунок 8).

Рисунок 8. Отриманий вміст XML у браузері Internet Explorer

Відчуйте різницю. Тепер це повноцінна електронна таблиця з форматуванням, відкрита у браузері (звісно, у Firefox ви можете клацнути правою кнопкою миші на посиланні, зберегти XML-код у файлі і, таким чином, відкрити його).

Технічні прийоми, які відкривають нові можливості

Як і в будь-яких передових технологій, дана методика має певні обмеження. Наприклад, цей метод поки не працює на Macintosh, оскільки остання версія Office для Mac не підтримує файли XML.

Іншою проблемою може стати налагодження файлів. У разі навіть незначної помилки в XML-коді вбудований об'єкт Excel перейде у стан, у якому Excel вважатиме, що цей об'єкт вже відкритий, і відмовиться запускатися. Це можна виправити лише шляхом перезапуску програми.

Але навіть за таких умов даний метод відкриває PHP-розробникам, які не мають собі рівних можливостей. Як часто ви стикалися з необхідністю вручну (комірка за коміркою, абзац за абзацом) переносити в Web-додатки дані,що містяться в Excel, Word та їм подібних джерелах? За допомогою технології імпорту, такої, як ця, ваша проблема може бути вирішена. Ви можете зчитувати дані безпосередньо з електронної таблиці чи документа.

Те саме можна сказати і про експорт даних. Мова HTML відмінно підходить для відображення статей та нотаток, але вона ніколи не призначалася для візуалізації табличних даних. За допомогою описаної тут методики ви можете згенерувати електронну таблицю (з формулами, форматуванням та іншими можливостями) у тому вигляді, з яким звикли працювати користувачі.

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

Схожі теми

Оригінал статті Read and write Excel data with PHP (EN).
PHP.net (EN) – слідкуйте за останніми новинами про PHP, завантажуйте продукти та обмінюйтесь досвідом з іншими користувачами.
Microsoft Office Online – отримайте інформацію та підтримку Microsoft Office на домашній сторінці продукту.
World Wide Web Consortium (W3C) (EN) – офіційне джерело інформації щодо стандартів XML.
XML у Office 2003: обмін інформацією з використанням Desktop XML (видавництво Prentice Hall, 2003 р.) (EN) – вичерпне посібник, написаний Чарльзом Ф. Голдфарбом (Charles F. Goldfarb) та Прісцилою Уолмслі (Priscilla Walmsley).