关于PHPEXCEL输出报表有上万条记录的时候貌似挂了？

Question

这是一个完整可用的phpexcel例子
<?php
require('includes/application_top.php');
error_reporting(E_ALL);
set_include_path(get_include_path() . PATH_SEPARATOR . 'classes/');//設置路徑include 'PHPExcel/PHPExcel.php';
include 'PHPExcel/PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->freezePane('A2');
$sharedStyle1 = new PHPExcel_Style();
$sharedStyle1->applyFromArray(
    array('fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('argb' => 'FFCCFFCC')
            ),
          'borders' => array(
                'bottom'=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
                'right' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM)
                )
         ));
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle2->applyFromArray(
    array('fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('argb' => 'fff4f4f4')
            )
         ));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:O1");   $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(50);$objPHPExcel->getActiveSheet()->setTitle('Student Export');//設置當前工作表的名稱
for($i=0;$i<15;$i++){
$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($i, 1)->getFont()->setBold(true);//設置第一行內容加粗
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1,'First Name');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 1,'Last Name');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 1,'Date of Birth');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 1,'Attendance Year');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 1,'Course studied');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 1,'Agency Booking / Direct Booking');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 1,'Student Country');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 1,'Student City');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, 1,'Student Home phone number');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, 1,'Student Mobile phone number');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, 1,'Student Email address');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, 1,'Emergency contact Name');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, 1,'Emergency contact phone number 1');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, 1,'Emergency contact phone number 2');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, 1,'Order Date');$number=1;
$orderdate='';
$firstname='';
$lastname='';
$dateodbirth='';
$attenddance='';
$studied='';
$booking='';
$country='';
$city='';
$home_phone='';
$mobile_phone='';
$email='';
$emer_name='';
$emer_phone1='';
$emer_phone2='';
$sql="select orders_id,date_purchased from orders order by orders_id desc";
$query=mysql_query($sql);
while($arr=mysql_fetch_array($query)){
$number++;
$orderdate=$arr['date_purchased'];
$sql2="select * from orders_extra_info where orders_id='".$arr['orders_id']."'";
$query2=mysql_query($sql2);
$num2=mysql_num_rows($query2);
if($num2>0){
$arr2=mysql_fetch_array($query2);
$firstname=$arr2['first_name'];
$lastname=$arr2['last_name'];
$dateodbirth=$arr2['date_of_birth'];
$attenddance=$arr2['o_attendaceyear'];
$booking=$arr2['o_agency'];
if($booking==1){$booking='Direct Sale';}
if($booking==2){$booking='Agent Sale';}
$country_id=$arr2['country'];
$city=$arr2['city'];
$home_phone=$arr2['phone1'];
$mobile_phone=$arr2['phone2'];
$email=$arr2['email'];
$emer_name=$arr2['emc_name'];
$emer_phone1=$arr2['emc_phone1'];
$emer_phone2=$arr2['emc_phone2'];
$cou_sql="select countries_id,countries_name from countries where countries_id='".$country_id."'";
$con_query=mysql_query($cou_sql);
$con_arr=mysql_fetch_array($con_query);
$country=$con_arr['countries_name'];
}
$sql3="select products_name,orders_id from orders_products where orders_id='".$arr['orders_id']."'";
$query3=mysql_query($sql3);
$num3=mysql_num_rows($query3);
if($num3>0){
$studied='';
while($arr3=mysql_fetch_array($query3)){
$studied.=$arr3['products_name'].",";
}
$studied=substr($studied,0,strlen($studied)-1);
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $number,$firstname);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $number,$lastname);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $number,$dateodbirth);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $number,$attenddance);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $number,$studied);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $number,$booking);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $number,$country);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $number,$city);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $number,$home_phone);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $number,$mobile_phone);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $number,$email);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $number,$emer_name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $number,$emer_phone1);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $number,$emer_phone2);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $number,$orderdate);
}
$objPHPExcel->setActiveSheetIndex(0);//設置打開excel時顯示哪個工作表
$excelName = 'Student_Export'.date("YmdHis").'.xls';//設置導出excel的文件名$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment; filename=".urlencode($excelName));
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');
require(DIR_WS_INCLUDES . 'application_bottom.php');
?>输出来的报表，当有上万条记录时，不能导出了，浏览器一直再转，只有小数据量的时候才有用，这时怎么办？

Answer 1

有以下几种缓存方式可以使用：
1). PHPExcel_CachedObjectStorageFactory::cache_in_memory;      默认情况下，如果你不初始化任何缓存方式，PHPExcel将使用内存缓存的方式。2).PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;      使用这种缓存方式，单元格会以序列化的方式保存在内存中，这是降低内存使用率性能比较高的一种方案。3). PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;     与序列化的方式类似，这种方法在序列化之后，又进行gzip压缩之后再放入内存中，这回跟进一步降低内存的使用，但是读取和写入时会有一些慢。4). PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;      当使用cache_to_discISAM这种方式时，所有的单元格将会保存在一个临时的磁盘文件中，只把他们的在文件中的位置保存在PHP的内存中，这 会比任何一种缓存在内存中的方式都慢，但是能显著的降低内存的使用。临时磁盘文件在脚本运行结束是会自动删除。5). PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;      类似cache_to_discISAM这种方式，使用cache_to_phpTemp时，所有的单元格会还存在php://temp I/O流中，只把他们的位置保存在PHP的内存中。PHP的php://memory包裹器将数据保存在内存中，php://temp的行为类似，但是当 存储的数据大小超过内存限制时，会将数据保存在临时文件中，默认的大小是1MB，但是你可以在初始化时修改它:

Answer 2

补充一下
PHPExcel 官网说，PHPExcel 以每个单元格 1K 的规模使用内存