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

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

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

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

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



2011 年 5 月 17 日

六年前,我写了一篇文章 “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 标记都包含 accountgenreimagesavgranksize 属性。这些属性存有每个 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.txtdata_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---

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

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

选择您的昵称



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

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

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


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


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=XML, Open source
ArticleID=659378
ArticleTitle=结合高级 Excel 导出功能和 PHP 创建 pivot 表
publish-date=05172011