当您初次登录到 developerWorks 时，将会为您创建一份概要信息。您在 developerWorks 概要信息中选择公开的信息将公开显示给其他人，但您可以随时修改这些信息的显示状态。您的姓名（除非选择隐藏）和昵称将和您在 developerWorks 发布的内容一同显示。

所有提交的信息确保安全。

当您初次登录到 developerWorks 时，将会为您创建一份概要信息，您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。您的昵称在 developerWorks 社区中必须是唯一的，并且出于隐私保护的原因，不能是您的电子邮件地址。

所有提交的信息确保安全。

My developerWorks:

我的通知：

退出

选择语言：

结合高级 Excel 导出功能和 PHP 创建 pivot 表

从 XML 模板构建灵活、可导出的电子表格

Jack D Herrington, 高级软件工程师, Fortify Software, Inc.

Jack Herrington 是一位生活和工作在海湾地区的工程师、作家和主持人。您可以通过 http://jackherrington.com 来关注他的工作和作品。

简介： 向 Microsoft Excel 中的高级导出功能添加 PHP 来创建 XML 格式的灵活的 pivot 表。然后，您和用户就可以动态地排序和分析 pivot 表中的数据了。

标记本文！

发布日期： 2011 年 5 月 17 日
级别： 初级
原创语言： 英文
访问情况： 15402 次浏览
评论：

六年前，我写了一篇文章 “Read and write Excel data with PHP”，介绍使用 PHP 将数据导出为 Microsoft® Excel® 电子表格。事实表明，那篇文章火了，足以保证此续篇文章也会火。第一篇文章只是浅尝辄止地涉及到 Excel 的强大功能。这篇文章中，我将向您展示如何使用其中一个更高级的特性——pivot 表，作为介绍 Excel 导出功能潜力的示例。

利用高级 Excel 导出功能和 PHP 开始构建 pivot 表

常用缩写词

CSV：逗号分隔值
W3C：万维网联盟
XML：可扩展标记语言

Pivot 表是这样一种数据表，用户可以动态地选择其中的哪些字段被用作行标题、列标题，以及被显示为每个单元格中的数据。用户可以动态地重新排列字段，从而能够回答复杂的问题，以及利用数据形成有趣的图案。

图 1 展示了一个基本的 Excel 电子表格。第一行包含字段名称（Account、Genre、Images、Average Ran、Total Size），后面的行包含数据。在本例中，您看到的是来自一个图像存储网站（例如 Flickr）的导出数据。每行包含一个给定帐户的信息，包括图像类型、已上传的图像数量、它们的平均评级和包含的所有图像的总大小（字节数）。

图 1. 基本的 Excel 表
一个基本 Excel 表的屏幕截图，内容是一个图像存储网站的帐户信息

要在 Excel 中构建 pivot 表，您需要选择将用在 pivot 表中的数据列，然后从 Data 菜单选择 PivotTable Report 菜单项。然后向导会提示您指定哪些数据用于报告。向导完成后，一个新的工作表被添加到 Excel 文件。

图 2 展示了一个空的 pivot 表，窗口上浮动着 PivotTable 工具栏。电子表格包含很多区域，您可以拖动其中的每个字段条目以生成报告。

图 2. 构建 pivot 表
空 pivot 表的屏幕截图，其中有一些区域用来接收拖放的行字段、列字段和数据条目

图 3 更详细地展示了 PivotTable 工具栏。顶部是 PivotTable 菜单和一些用于控制表深度的工具栏按钮。工具栏底部是一些可用的字段（Account、Genre、Images、Average Ran、Total Size）。

图 3. pivot 工具栏
pivotTable 工具栏的屏幕截图，带有菜单、按钮和可用的字段

要调整 pivot 表，您需要选择字段，并将它们拖放到页面上适当的区域。图 4 展示了依次拖动 genre、account 和 total size 到报告左侧数据区域的结果。

图 4. 一个填充了数据的 pivot 表
一个填充了数据的 pivot 表的屏幕截图

报告现在展示了首先按 genre 成组、然后按 account 成组的记录。然后展示了每个 account 的 sizes，再就是每个 genre 的 sizes 汇总，最后显示总计。调整显示数据的顺序就只是拖放字段而已。这对于探究数据是一个极其强大的机制，如果您的数据集很大或者很复杂，那么也值得花点精力去了解它。

以 XML 格式保存 Excel 电子表格时，所有数据和 pivot 表参数都存储在 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 应用程序构建一个 Excel XML 文件，其中为原始数据和 PivotTable 分别具有一个工作簿。此 pivot 表标签具有所有可用的动态功能，就像是您在 Excel 中创建的一样。

首先需要的是将放入电子表格中的数据。

回页首

得到数据

为了让工作更轻松，将数据存储在清单 2 那样的 XML 文件中。

清单 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 属性。这些属性存有每个 account 的相关数据。

要读取数据，一个简单的 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 循环迭代通过每个用户节点，并将它们的属性存储在 data 数组中。然后，此 data 数组被返回给调用者。

除了数据读取程序之外，另一个函数 print_file 用于输出文件的所有内容。清单 4 展示了此函数。

清单 4. helpers.php

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

采用 print_file 的原因很简单。Excel XML 文件很复杂，所以最简单的构建方式是从一个 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' );
?>

代码首先读入数据，然后输出主体和数据表头。然后，XML 代码定义表，表的第一行有表头。然后，foreach 循环迭代通过每个数据项并利用数据创建一个新行。

程序运行时，输出可以存储在一个将由 Excel 打开的文件中。结果应该类似于图 5。

图 5. 从 PHP 导出的基本数据表
pivot 表的屏幕截图，带有文件中初始格式化的数据

图 5 展示了出现在初始文件时被正确格式化的数据；第一行显示字段标题，后续的每一行提供数据。

显然，您不会将这里原原本本的代码用于您的 Excel 导出，因为您的字段和数据会有所不同。但是构建数据表的过程是相同的，首先是创建 Excel 文件，然后去掉表头和表尾部分，最后在数据表中注入您自己的数据。

回页首

构建 pivot 表

Pivot 表电子表格稍微复杂一点，但是思路基本上是相同的。脚本一开始是读入数据，然后输出主体表头。然后依次为数据、pivot 表和表尾输出页面。清单 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' );
?>

开始的头部函数调用也很有趣，因为它们告诉浏览器对文件做些什么。Content-type 头部告诉浏览器，脚本在导出一个 Excel 电子表格。Content-disposition 头部告诉浏览器该电子表格应该被存储为文件，并推荐一个名称，本例中是 pivot.xml。

大量的工作是用导出函数完成的，导出函数定义在清单 7 中的 exporters.php 文件中。

清单 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 做第一个脚本所做的事情，以导出数据。为了创建 pivot 表标签，export_pivot 函数首先创建一个工作表，然后添加数据表、工作表选项和 PivotTable XML。最后的 PivotCache 创建发生在工作表完成之后。

老实讲，我不知道为什么 pivot 表的定义需要这么多的 XML 代码。无疑，对于简单的数据表来说，需要的代码更多。但是好在可以在 Excel 中创建 pivot 表，保存为 XML，然后使用该 XML 代码作为您自己代码的模板。

运行 PHP 脚本之后，将结果保存到文件，然后在 Excel 中打开，会看到类似于图 6 中的表。

图 6. 导出的 pivot 表
电子表格的屏幕截图，带有一系列 account 和 totals，以及一个 grand total

在图 6 中，pivot 表展示了每一个 account、它们的大小 total，最后还有一个 grand total。Excel pivot 表已正确创建，所以窗口上显示一个 PivotTable 工具栏。使用该工具栏，用户可以添加字段和调整表。

回页首

结束语

Microsoft Excel 是一个相当强大的数据分析工具。但是它对存储在 PHP web 应用程序中的数据视而不见，除非您费点功夫将两者联系起来。当然，您可以导出 CSV 或另一种简单的文本格式，但是这给应用程序的用户带来负担，要他们去格式化数据和构建自己的报告。也意味着每次用户想要得到更新的数据时，都必须再次从新开始。

XML 格式 Excel 的使用可能要稍微复杂一点，但是您可以使用一个保存的 XML 文件作为构建您导出电子表格的模板。此方法大大简化了生成文件的过程，并且意味着您可以让用户构建他们想要看到的电子表格，然后您可以导出它们，用生动的数据动态地填充它们。这是一种优异的向客户提供新值的方式，同时仍然处于服务器的 PHP 和 XML 世界。

回页首

下载

描述	名字	大小	下载方法
本文源代码	excelpivotxml.zip		HTTP

关于下载方法的信息

参考资料

学习

Read and write Excel data with PHP: Using XML support（Jack D. Herrington，developerWorks，2010 年 8 月更新，最初发布于 2005 年 10 月）：阅读最初的 Excel PHP 文章，介绍了可以从（从 Microsoft Excel 2003 导出的）XML 读取数据的 PHP 中的 XML 支持。
Microsoft Office XML 格式（Wikipedia）：仔细研读这篇介绍 Excel 文件格式的文章，与类似的 XML 格式进行了一些比较，最值得一提的是 Open Office。
PHP: Hypertext Preprocessor 网站：访问可用的 PHP 最佳参考资源。
W3C 网站：探究这个很棒的网站，了解各种标准，包括与本文有关的 XML 标准。
Excel 中支持的文件格式：找到关于 Microsoft 网站的更多信息。
Microsoft Office XML 格式（Wikipedia）：所有 Microsoft Office 应用程序都支持 XML。如果 Excel 不符合您的需要，看其他哪种格式更适合您。
本作者的更多文章（Jack Herrington，developerWorks，2005 年 3 月至今）：阅读关于 Ajax、JSON、PHP、XML 和其他技术的文章。
developerWorks 中国网站 XML 技术专区：在 XML 专区获取提高您的专业技能所需的资源。
IBM InfoSphere Master Data Management Server 商业价值概述：找到丰富的 how-to 信息、工具和项目更新，帮助您用开放源码技术进行开发，并与 IBM 产品结合使用。还有我们最流行的文章和教程。
IBM XML 认证：了解如何才能成为一名 IBM 认证的 XML 和相关技术的开发人员。
XML 技术文档库：访问 developerWorks XML 专区，获得广泛的技术文章和技巧、教程、标准和 IBM 红皮书。此外，阅读更多的 XML 技巧。
developerWorks 技术活动和网络广播：随时关注这些活动中的技术。
developerWorks 播客：收听面向软件开发人员的有趣访谈和讨论。
developerWorks 演示中心：观看演示，内容包括面向初学者的产品安装和设置演示，以及为经验丰富的开发人员提供的高级功能。