利用PHPExcel类实现Excel数据批量操作

胡必波

（广州工商学院，广东 广州 510850）

1 引言

为满足广大师生的报修需求，我院后勤集团与信息化处整合了水、电、暖、网络、固定电话、一卡通等维修类别中师生报修率最高的若干报修项目，推出了校园一站式服务网上“报修投诉平台”，简化了以往需要向不同单位申报故障的繁琐过程，报修申请人只需登录平台，进入“我要报修”页面，填写信息申请报修。该系统中学生报修信息由一站式服务中心的管理人员以Excel表格的形式提供。本系统采用PHP+MySQL作为开发平台，开发过程中MySQL数据库与Excel表有进行导入导出等交互的开发需求。为此我们选用了微软公司提供的类库PHPExcel。本文就PHPExcel类库中有关Excel导入数据库及将数据库中的内容导出的实现思路分别作介绍。

2 PHPExcel概述

PHPExcel是用来操作Office Excel文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格，如Excel(BIFF).xls、Excel 2007(OfficeOpenXML).xlsx、CSV、Gnumeric、PDF、HTML等。更难得的是还可以在Excel中设置图片、表格、字体大小、颜色等非常具体的格式。PHPExcel目前最高版本为1.8.0。

3 PHPExcel原理

图1 PHPExcel基本架构

如图1所示，PHPExcel在内存中是将数据以电子表格的形式来呈现的，我们只要编写前端代码调用PHPExcel对象模型，就可以很方便地创建一个Web电子表格。和我们使用桌面电子表格软件一样，PHPExcel所创建的电子表格包含一个或多个工作表，工作表的单元格又包含数据、公式、图像等。

4 一站式服务系统中Excel批量数据操作功能实现

4.1 数据导入功能的实现

以网络端口保修数据导出为例。该功能的实现是将网络端口保修数据导入到MySQL中，将Excel表格中的数据导入数据库使用的是PHPExcel类库中的类PHPExcel_Reader_Excel5。具体的实现思路为首先创建PHPExcel_Reader_Excel5的对象$objReader，然后调用它的setReadDataOnly方法设置数据源为可读，接着调用它的load方法设置要导入的Excel文件，最后通过调用getCellByColumnAndRow方法从Excel获取数据并将数据插入到数据库中。

4.2 数据导出功能的实现

该功能的实现是将某阶段内的历史数据从数据库中导出到指定的Excel表格中。首先通过调用PHPExcel类库中的PHPExcel类创建Excel文件，并通过调用其方法设置文档属性以及Sheet索引。然后通过调用活动单元格对象的方法setCellValue在表格的第一行设置字段名称。最后通过调用数据库的数据将数据写入Excel文件，写入方法同样使用的是setCellValue方法。运行效果如图2所示。

图2 从一站式服务系统导出的报修数据

实现功能的核心代码如下：

//生成数据

$column=2;

$objActSheet=$objPHPExcel-＞getActiveSheet();

foreach($data as$key=＞ $rows){//行写入

$span=ord("B");

foreach($rows as$keyName=＞$value){//列写入

$j=getExcelColumnValue($span-65);

$objActSheet-＞setCellValue("A".$column,$column-1);

$objActSheet-＞setCellValue($j.$column,$value);

$span++;

4.3 经验与技巧

4.3.1 乱码的处理

（1）解决生成文件名的导出Excel乱码

乱码原因：客户使用的中文版Windows系统平台，Windows平台的文件名编码为gb2312(gbk)，而我们网页编码一般都采用utf-8(国际化)编码，这时当我们：header("Content-Disposition∶inline;filename="".$filename.".xls"")时就会出现乱码，假如你的网页编码就是gb2312那就不用考虑编码问题了。

解决办法：对$filename转码，执行：iconv('utf-8","gb2312",$filename)。假如你的环境不支持iconv函数可以换别的函数，只要能将$filename的编码转为gbk就行。但是这样问题又会来了，linux用户又会出现文件名乱码（因为linux平台文件名不是gbk编码）。考虑到这个问题采用两个办法：第一，放弃一部分客户，毕竟windows系统用户占绝大部分；第二，像gmail一样，提供两个下载地址：一个文件名gbk编码，一个文件名utf-8编码。

（2）解决Excel内数据的乱码

PHP导出Excel乱码的原因：网页编码与Excel编码不一致。

解决办法：定义Excel的字符集：header("Content-Type∶application/vnd.ms-excel;charset=UTF-8")，让其与你的网页编码一致即可解决Excel内数据乱码的问题。

PHPExcel导出到xls文件的时候出现内存乱码，解决代码如下：ob_end_clean();//清除缓冲区，避免乱码，通过这个函数清除缓冲区，避免不必要的数据干扰生成的Excel文件导致Excel文件打不开。

4.3.2 分页导出

实现分页导出的思路是进入导出页面时，传递查询相关的参数。导出页面再做相关的分页查询处理。

＜li＞＜a href="simple-download-xls.php?xiaoqu=＜?php echo$GLOBALS['xiaoqu_query'];?＞

&name=＜?php echo$GLOBALS['name_query'];?＞&start=＜?php echo$start_row;?＞"＞导出数据＜/a＞＜/li＞

4.3.3 内存溢出

云平台下使用PHPExcel进行excel数据批量导入，小数据量下测试都没有问题，可是一到正式环境下，数据超过千条（一行十列为一条数据），就报内存溢出。在不进行特殊设置的情况下，PHPExcel将读取的单元格信息保存在内存中，我 们 可 以 通 过 代 码 PHPExcel_Settings∶setCacheStorage-Method()来设置不同的缓存方式，已达到降低内存消耗的目的。可采用的处理方式如下：

（1）将单元格数据序列化后保存在内存中

PHPExcel_CachedObjectStorageFactory∶cache_in_memory_serialized;

（2）将单元格序列化后再进行Gzip压缩，然后保存在内存中

PHPExcel_CachedObjectStorageFactory∶cache_in_memory_gzip;

（3）缓存在临时的磁盘文件中，速度可能会慢一些

PHPExcel_CachedObjectStorageFactory∶cache_to_dis -cISAM;

（4）保存在php∶//temp

PHPExcel_CachedObjectStorageFactory∶cache_to_php -Temp;

（5）保存在memcache中

PHPExcel_CachedObjectStorageFactory∶cache_to_memcache

这里限于篇幅不再展开论述。

5 结语

本文以实际项目校园一站式服务系统导出的Excel表为计算基础，介绍了PHPExcel的基本原理，分析了一站式服务系统中Excel批量数据导入导出的实现思路。并以导出具体操作为例，给出了实现其功能的相关算法。在项目实施过程中，针对可能出现的导出文件乱码问题、内存溢出问题，进行了分析和研究，并提出了相应的解决办法。在此基础上还进行了功能拓展，介绍了实现分页批量导出Excel表数据技巧。在此应用基础上，还需要进一步研究的问题有：优化Excel批量数据导出效果，提高导出文件美化效果。

[1]郑阿奇.PHP实用教程（第2版）[M].北京：电子工业出版社，2014.

[2]唐俊.PHP+MySQL网站开发技术（项目式）[M].北京：人民邮电出版社，2013.

[3]Maarten Balliauw，Mark Baker.PHPExcel developer documentation.2014.

[4]丁月光.PHPExcel文件读写[J].电脑编程技巧与维护，2013，(9)：14-18.