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

Создание гибких, экспортируемых электронных таблиц из XML-шаблонов

Дополните расширенные функции экспорта Microsoft Excel кодом на PHP для создания гибких сводных таблиц в формате XML, что позволит пользователям сортировать и анализировать данные сводных таблиц на лету.

Джек Д Херрингтон, главный инженер-программист, Leverage Software Inc.

Джек Д. Херрингтон (Jack D. Herrington) - главный инженер-программист с более чем двадцатилетним опытом работы. Он автор трех книг: "Генерирование кода в действии", "Podcasting Hacks" и "PHP Hacks". Написал более 30 статей. Вы можете связаться с Джеком по адресу jherr@pobox.com.



24.10.2011

Шесть лет назад я написал статью "Чтение и запись данных 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
Рисунок 1. Простая таблица Excel

Для построения сводной таблицы в Excel необходимо выбрать столбцы данных, которые будут использоваться в ней, а затем выбрать пункт PivotTable Report в меню Data. После этого вам будет предложено указать, какие данные будут использоваться в мастере отчетов. По завершении работы мастера в файле Excel добавится новый лист.

На рисунке 2 изображена пустая сводная таблица с плавающей панелью инструментов сводной таблицы. Страница состоит из областей, в которые можно перетащить каждый элемент поля для генерации отчета.

Рисунок 2. Создание сводной таблицы
Рисунок 2. Создание сводной таблицы

На рисунке 3 показан более подробный вид панели инструментов PivotTable. Сверху расположены меню PivotTable и несколько кнопок управления уровнями вложенности таблицы, а снизу перечислены все доступные поля (Account, Genre, Images, Average Ran, Total Size).

Рисунок 3. Панель инструментов сводной таблицы
Рисунок 3. Панель инструментов сводной таблицы

Для настройки сводной таблицы необходимо выбирать поля и перетаскивать их в соответствующие области страницы. На рисунке 4 вы видите результат перетаскивания полей genre, account и total size в область данных, расположенную в левой части отчета.

Рисунок 4. Сводная таблица, заполненная данными
Рисунок 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. Основная таблица, экспортированная из 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. Экспортированная сводная таблица

В сводной таблице на рисунке 6 отображаются общий объем загруженных изображений для каждой учетной записи и полный общий объем всех загруженных изображений. Сводная таблица Excel была создана корректно, поэтому в окне отображается панель инструментов PivotTable. Теперь с помощью этой панели пользователь может добавлять поля и настраивать таблицу.


Заключение

XML-формат, используемый в Excel, может оказаться немного сложным, но вы можете использовать сохраненный XML-файл в качестве основы для создания собственной экспортируемой электронной таблицы. Этот подход существенно упрощает процесс генерации файлов и позволяет пользователям создавать электронные таблицы в таком виде, в каком они хотят их видеть, после чего эти таблицы можно экспортировать и наполнять данными на лету. Это замечательный способ предоставить вашим клиентам новые данные, не покидая мир PHP и XML.


Загрузка

ОписаниеИмяРазмер
Пример для этой статьиexcelpivotxml.zip---

Ресурсы

Научиться

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=XML, Open source
ArticleID=767416
ArticleTitle=Совместное использование PHP и функций расширенного экспорта Excel для создания сводных таблиц
publish-date=10242011