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

作者: nick 分类: php 发布时间: 2011-05-18 10:57 ė 6没有评论

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

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

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

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

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

标记本文！

发布日期： 2011 年 5 月 17 日
级别： 初级
原创语言： 英文
访问情况 100 次浏览
建议： 0 (添加评论)

六年前，我写了一篇文章 “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 演示中心：观看演示，内容包括面向初学者的产品安装和设置演示，以及为经验丰富的开发人员提供的高级功能。

获得产品和技术

IBM 产品评估试用版软件：下载或在线试用 IBM SOA Sandbox，并开始使用来自 DB2®、Lotus®、Rational®、Tivoli® 和 WebSphere® 的应用程序开发工具和中间件产品。

讨论

XML 专区讨论论坛：参与任何一个 XML 相关讨论。
developerWorks 中文社区：查看开发人员推动的博客、论坛、组和 wikis，并与其他 developerWorks 用户交流。

关于作者

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

原文：http://www.ibm.com/developerworks/cn/xml/x-excelpivotphp/index.html

本文出自传播、沟通、分享，转载时请注明出处及相应链接。

本文永久链接: https://www.nickdd.cn/?p=1514

0excel, php, pivot, 表格

« Request对象错误ASP 0104:80004005不允许操作

《Tomcat Apache集群安装指南》PDF下载–windows和linux环境 »

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

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

常用缩写词

发表评论取消回复

分类目录

php 下的最新文章

随机文章

标签云

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

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

常用缩写词

发表评论 取消回复

分类目录

php 下的最新文章

随机文章

标签云

发表评论取消回复