PHPExcel
是一个php语言读取导出数据、导入生成Excel的类库，使用起来非常方便，但有时会遇到以些问题，比如导出的数据超时，内存溢出等。
下面我们来说说这些问题和解决办法。
PHPExcel 版本：@version 1.8.0, 2014-03-02
能遇到这样的问题一般都是因为数据量大导致
1.PHPExcel 报错
报错提示：
'break' not in the 'loop' or 'switch' context
严格的讲这个不是PHPExcel的错误，是PHP版本的问题，大于PHP5.6以后，“break”必须要在循环体内执行（for ,foreach, while, switch）
此处无循环，解决办法：注释掉break;
Maximum execution time of 30 seconds exceeded
数据量过大，php执行超过30秒后就会报这样的信息
解决办法：
可修改php.ini 或直接在执行页面中添加
set_time_limit(0);
这样就设置了php的执行超时
3.内存溢出
超时解决好之后，等待了好几十秒后又来了个错误：
Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)
内存不足呀！
解决内存溢出我们分两步走，
第一步：
设置memory_limit
默认情况memory_limit 大小为100MB，当所需内存大于100MB就会溢出，所以设置足够大的值
ini_set("memory_limit", "1024M"); // 根据电脑配置不够继续增加
第二步：设置PHPExcel单元格缓存
单元格缓存是将所需PHPExcel内存单元格对象缓存到磁盘、memcache、MemoryGZip等，这样读取上会更耗时，但可以降低内存的消耗。
PHPExcel_CachedObjectStorageFactory 这个类中提供了这几个单元格缓存
const cache_in_memory = 'Memory';
const cache_in_memory_gzip = 'MemoryGZip'; #将单元格序列化后再进行Gzip压缩，然后保存在内存中
const cache_in_memory_serialized = 'MemorySerialized'; # 将单元格数据序列化后保存在内存中
const cache_igbinary = 'Igbinary'; #存储为紧密的二进制形式
const cache_to_discISAM = 'DiscISAM'; #缓存在临时的磁盘文件中，速度可能会慢一些
const cache_to_apc = 'APC'; #Alternative PHP Cache可选PHP缓存
const cache_to_memcache = 'Memcache'; #保存在memcache中
const cache_to_phpTemp = 'PHPTemp'; #保存在php://temp
const cache_to_wincache = 'Wincache';
const cache_to_sqlite = 'SQLite';
const cache_to_sqlite3 = 'SQLite3';
每一个worksheet都会有一个独立的缓存，当一个worksheet实例化时，就会根据设置或配置的缓存方式来自动创建。一旦你开始读取一个文件或者你已经创建了第一个worksheet，就不能在改变缓存的方式了。
MemorySerialized： 使用这种缓存方式，单元格会以序列化的方式保存在内存中，这是降低内存使用率性能比较高的一种方案。
MemoryGZip： 与序列化的方式类似，这种方法在序列化之后，又进行gzip压缩之后再放入内存中，这回跟进一步降低内存的使用，但是读取和写入时会有一些慢。
DiscISAM：当使用cache_to_discISAM这种方式时，所有的单元格将会保存在一个临时的磁盘文件中，只把他们的在文件中的位置保存在PHP的内存中，这会比任何一种缓存在内存中的方式都慢，但是能显著的降低内存的使用。临时磁盘文件在脚本运行结束是会自动删除。
PHPTemp： 类 似cache_to_discISAM这种方式，使用cache_to_phpTemp时，所有的单元格会还存在php://temp I/O流中，只把 他们的位置保存在PHP的内存中。PHP的php://memory包裹器将数据保存在内存中，php://temp的行为类似，但是当存储的数据大小超 过内存限制时，会将数据保存在临时文件中，默认的大小是1MB，但是你可以在初始化时修改它。php://temp文件在脚本结束是会自动删除。
4.大数据导出
微软的Excel设置单元格行数默认是6万行rows，相对的讲其实当我们超过1万行的时候已经是大数据的导出。
好比：有客户10000人，平均每人每天产生10条活动记录，要导出上周所有的客户活动记录： 10000*10*7=700000
估计看70万行的Excel这个人会疯掉的，我们的建议是分批次导出，按时间导出到不同的excel
下面是一个PHPExcel官方的Demo（已修改过）
define('EOL', '<br />');
$objPHPExcel = new \app\extensions\PHPExcel\PHPExcel();
ini_set("memory_limit", "1024M"); // 设置php可使用内存
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
die($cacheMethod . " 缓存方法不可用" . EOL);
echo date('H:i:s'), " 当前使用的缓存方法是： ", $cacheMethod, " 方式", EOL;
echo date('H:i:s'), " 开始设置文档属性", EOL;
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
echo date('H:i:s'), " 开始添加单元格标题", EOL;
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "Firstname");
$objPHPExcel->getActiveSheet()->setCellValue('B1', "Lastname");
$objPHPExcel->getActiveSheet()->setCellValue('C1', "Phone");
$objPHPExcel->getActiveSheet()->setCellValue('D1', "Fax");
$objPHPExcel->getActiveSheet()->setCellValue('E1', "Is Client ?");
// 设置单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
* 左对齐与 右对齐
* 可设置整列->getStyle('N') 可针对行rows设置getStyle('N3')
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
echo date('H:i:s'), " 设置隐藏C D列", EOL;
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
echo date('H:i:s'), " 设置大纲级别", EOL;
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1)
->setVisible(false)
->setCollapsed(true);
* 开始添加数据
for ($i = 2; $i <= 50000; $i++) {
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "FName $i")
->setCellValue('B' . $i, "LName $i")
->setCellValue('C' . $i, "PhoneNo $i")
->setCellValue('D' . $i, "FaxNo $i")
->setCellValue('E' . $i, true);
$objPHPExcel->getActiveSheet()->setTitle('供应商信息');
echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
$callStartTime = microtime(true);
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
echo date('H:i:s'), " 完成写入文件", EOL;
echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
下面模拟一个大数据的导出：
msyql中tcustomer表有数据4万多条
set_time_limit(0);
ini_set("memory_limit", "1024M");
\PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip; # 单元格缓存为MemoryGZip
然后导出所有4万多条数据
代码如下（Yii2）
* PHPExcel 数据导出
public
function
actionPhpexcel() {
define
('EOL', '<br />'
);
$objPHPExcel
=
new
\app\extensions\PHPExcel\PHPExcel();
ini_set
("memory_limit", "1024M");
//
设置php可使用内存
set_time_limit
(0);
#
设置执行时间最大值
$cacheMethod
= \PHPExcel_CachedObjectStorageFactory::
cache_in_memory_gzip;
if
(!\PHPExcel_Settings::setCacheStorageMethod(
$cacheMethod
)) {
die
(
$cacheMethod
. " 缓存方法不可用" .
EOL);
echo
date
('H:i:s'), " 当前使用的缓存方法是： ",
$cacheMethod
, " 方式",
EOL;
echo
date
('H:i:s'), " 开始设置文档属性",
EOL;
$objPHPExcel
->getProperties()->setCreator("dcb3688"
)
->setLastModifiedBy("dcb3688"
)
->setTitle("客户信息记录"
)
->setSubject("客户信息Document"
)
->setDescription("描述……"
)
->setKeywords("office 2007 php"
)
->setCategory("产品信息AAA"
);
echo
date
('H:i:s'), " 开始添加单元格标题",
EOL;
$objPHPExcel
->setActiveSheetIndex(0
);
$objPHPExcel
->getActiveSheet()->setCellValue('A1', "客户姓名"
);
$objPHPExcel
->getActiveSheet()->setCellValue('B1', "性别"
);
$objPHPExcel
->getActiveSheet()->setCellValue('C1', "Province"
);
$objPHPExcel
->getActiveSheet()->setCellValue('D1', "City"
);
$objPHPExcel
->getActiveSheet()->setCellValue('E1', "Town"
);
$objPHPExcel
->getActiveSheet()->setCellValue('F1', "Telephone"
);
$objPHPExcel
->getActiveSheet()->setCellValue('G1', "属相"
);
$objPHPExcel
->getActiveSheet()->setCellValue('H1', "星座"
);
$objPHPExcel
->getActiveSheet()->setCellValue('I1', "血型"
);
$objPHPExcel
->getActiveSheet()->setCellValue('J1', "Nid"
);
$objPHPExcel
->getActiveSheet()->setCellValue('K1', "Uid"
);
$objPHPExcel
->getActiveSheet()->setCellValue('L1', "Etime"
);
$objPHPExcel
->getActiveSheet()->setCellValue('M1', "Regtime"
);
$objPHPExcel
->getActiveSheet()->setCellValue('N1', "Signup"
);
$objPHPExcel
->getActiveSheet()->setCellValue('O1', "经度"
);
$objPHPExcel
->getActiveSheet()->setCellValue('P1', "纬度"
);
$objPHPExcel
->getActiveSheet()->setCellValue('Q1', "类型"
);
$objPHPExcel
->getActiveSheet()->setCellValue('R1', "状态"
);
* 单元格宽度
$objPHPExcel
->getActiveSheet()->getColumnDimension('F')->setAutoSize(
true
);
$objPHPExcel
->getActiveSheet()->getColumnDimension('J')->setWidth(45
);
$objPHPExcel
->getActiveSheet()->getColumnDimension('L')->setAutoSize(
true
);
$objPHPExcel
->getActiveSheet()->getColumnDimension('M')->setAutoSize(
true
);
$objPHPExcel
->getActiveSheet()->getColumnDimension('N')->setAutoSize(
true
);
* 左对齐与 右对齐
* 可设置整列->getStyle('N') 可针对行rows设置getStyle('N3')
$objPHPExcel
->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::
HORIZONTAL_RIGHT);
$objPHPExcel
->getActiveSheet()->getStyle('N3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::
HORIZONTAL_LEFT);
#
####################################开始添加数据###############################################################
* 分页分时间： 微软Execl最大值6万行， total/60000=文件个数， limit 60000, 60000
* $model->find()->offset($pages->offset)->limit(60000)->all()
$model
= \app\models\Tcustomer::find()->orderBy('regtime desc')->
all();
if
(!
empty
(
$model
)) {
foreach
(
$model
as
$key
=>
$value
) {
$Cellkey
=
$key
+ 2
;
$blood
= [1 => 'A型', 2 => 'B型', 3 => 'AB型', 4 => 'O型'
];
$objPHPExcel
->getActiveSheet()->setCellValue('A' .
$Cellkey
, mb_substr(
$value
->realname, 0, -1) . '*'
);
$objPHPExcel
->getActiveSheet()->setCellValue('B' .
$Cellkey
,
rand
(1, 2) == 1 ? '先生' : '女士'
);
$objPHPExcel
->getActiveSheet()->setCellValue('C' .
$Cellkey
,
$value
->
province);
$objPHPExcel
->getActiveSheet()->setCellValue('D' .
$Cellkey
,
$value
->
city);
$objPHPExcel
->getActiveSheet()->setCellValue('E' .
$Cellkey
,
$value
->
town);
$objPHPExcel
->getActiveSheet()->setCellValue('F' .
$Cellkey
,
$value
->telephone ?
substr
(
$value
->telephone, 0, 3) . '*********' : ''
);
$objPHPExcel
->getActiveSheet()->setCellValue('G' .
$Cellkey
,
$value
->
sx);
$objPHPExcel
->getActiveSheet()->setCellValue('H' .
$Cellkey
,
$value
->
constel);
$objPHPExcel
->getActiveSheet()->setCellValue('I' .
$Cellkey
,
$blood
[
array_rand
(
$blood
)]);
$objPHPExcel
->getActiveSheet()->setCellValue('J' .
$Cellkey
, mb_substr(
$value
->nid, 0, -3
));
$objPHPExcel
->getActiveSheet()->setCellValue('K' .
$Cellkey
,
$value
->
uid);
$objPHPExcel
->getActiveSheet()->setCellValue('L' .
$Cellkey
,
$value
->
exp
);
$objPHPExcel
->getActiveSheet()->setCellValue('M' .
$Cellkey
,
$value
->
regtime);
$objPHPExcel
->getActiveSheet()->setCellValue('N' .
$Cellkey
,
$value
->signup ?
$value
->signup : '无数据'
);
$objPHPExcel
->getActiveSheet()->setCellValue('O' .
$Cellkey
,
substr
(
$value
->lng, 0, 4) .
rand
(10000, 999999
));
$objPHPExcel
->getActiveSheet()->setCellValue('P' .
$Cellkey
,
substr
(
$value
->lat, 0, 3) .
rand
(10000, 999999
));
$objPHPExcel
->getActiveSheet()->setCellValue('Q' .
$Cellkey
,
$value
->type == 1 ? '意向客户' : '观望中客户'
);
$objPHPExcel
->getActiveSheet()->setCellValue('R' .
$Cellkey
,
$value
->status == 1 ? '已下单' : '已跟进'
);
}
else
{
die
(" 暂无数据" .
EOL);
#
######################################################################################################
$objPHPExcel
->getActiveSheet()->setTitle('客户信息'
);
echo
date
('H:i:s'), " 设置格式为Excel2007版格式",
EOL;
$callStartTime
=
microtime
(
true
);
$objWriter
= \PHPExcel_IOFactory::createWriter(
$objPHPExcel
, 'Excel2007'
);
$objWriter
->save(
str_replace
('.php', '.xlsx',
__FILE__
));
$callEndTime
=
microtime
(
true
);
$callTime
=
$callEndTime
-
$callStartTime
;
echo
date
('H:i:s'), " 设置生成的文件为： ",
str_replace
('.php', '.xlsx',
pathinfo
(
__FILE__
, PATHINFO_BASENAME)),
EOL;
echo
date
('H:i:s'), ' 写入Workbook中耗时 ',
sprintf
('%.4f',
$callTime
), " 秒",
EOL;
echo
date
('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(
true
) / 1024 / 1024), " MB",
EOL;
echo
date
('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(
true
) / 1024 / 1024), " MB",
EOL;
echo
date
('H:i:s'), " 完成写入文件",
EOL;
echo
date
('H:i:s'), ' 文件被创建在： ',
getcwd
(), '目录',
EOL;
执行后信息：
打开文件：
