Шесть лет назад я написал статью "Чтение и запись данных Excel из приложений PHP" (EN), в которой рассказывалось об использовании PHP для экспорта данных в виде электронных таблиц Microsoft® Excel®. Эта статья оказалось достаточно популярной для того, чтобы получить свое продолжение. В первой статье я лишь поверхностно коснулся всей мощи Excel. В этой статье я приведу пример использования возможностей экспорта Excel и покажу, как использовать более продвинутую функцию – сводные таблицы.

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

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

На рисунке 1 изображена простая таблица Excel. В первой строке содержатся имена полей (Account, Genre, Images, Average Ran, Total Size), а в последующих строках – сами данные. В данном случае мы имеем дело с данными Web-сайта, на котором хранятся изображения (например, Flickr). Каждая строка содержит информацию об указанной учетной записи, включая жанр и количество загруженных изображений, их средний рейтинг, а также общий объем в байтах.

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

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

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

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

Теперь отчет отображает строки, сгруппированные сначала по жанру (genre), а затем по учетным записям (account). В нем мы видим общий объем загруженных изображений для каждой учетной записи, результирующий объем для каждого жанра, и, наконец, полный общий объем всех загруженных изображений.

При сохранении электронной таблицы Excel в формате XML все данные и параметры сводной таблицы помещаются в XML-файл. Фрагмент такого файла показан в листинге 1.

<?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.

<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.

<?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.

<?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.

<?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 вы видите данные, отформатированные в соответствии с исходным файлом; в первой строке отображаются заголовки полей, а в последующих строках – сами данные.

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

В начало

Создание сводной таблицы

Листы сводной таблицы немного сложнее, но идея та же самая. Сначала выполняется чтение данных, а затем распечатка файла body_header.txt. После этого распечатываются страницы данных, сводной таблицы и файла body_footer.txt. Этот код представлен в листинге 6.

<?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.

<?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 отображаются общий объем загруженных изображений для каждой учетной записи и полный общий объем всех загруженных изображений. Сводная таблица 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 и связанных технологий.
  
  
• В разделе подкастов на сайте developerWorks (EN) можно слушать интересные интервью и обсуждения, ориентированные на разработчиков программного обеспечения.
  
  
• Смотрите демонстрационные материалы по запросу на сайте developerWorks (EN), ориентированные как на новичков, так и на опытных разработчиков.
  
  

Обсудить

• Следите за блогом developerWorks в Твиттере (EN).
  
  

Об авторе

Оценить эту статью

Комментарии

В начало

Содержание

• Создание сводных таблиц с помощью расширенного экспорта Excel и PHP
• Получение данных
• Создание простой электронной таблицы Excel
• Создание сводной таблицы
• Заключение
• Загрузка
• Ресурсы
• Об авторе
• Комментарии