Home >Backend Development >PHP Tutorial >关于PHPEXCEL输出报表有上万条记录的时候貌似挂了？
关于PHPEXCEL输出报表有上万条记录的时候貌似挂了？
- ringa_leeOriginal
- 2018-05-12 10:22:032116browse
这是一个完整可用的phpexcel例子
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;$igetActiveSheet()->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');
?>输出来的报表，当有上万条记录时，不能导出了，浏览器一直再转，只有小数据量的时候才有用，这时怎么办？
回复讨论(解决方案)
有以下几种缓存方式可以使用：
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，但是你可以在初始化时修改它:应该最多值能导出2万条。有以下几种缓存方式可以使用：
1). PHPExcel_CachedObjectStorageFactory::cache_in_memory;
默认情况下，如果你不初始化任何缓存方式，PHPExcel将使用内存缓存的方式。
2).PHPExcel_CachedObjectStorageFactory::cache_in_memory_seria……
内存问题，标记~
补充一下
PHPExcel 官网说，PHPExcel 以每个单元格 1K 的规模使用内存
据说可以什么分批导入？有没有例子怎么去写
自己到网上搜一下就是了
没哟这样的需求，谁会去测试?
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：php读取大文件的一点疑问Next article：PHP执行实时进度