关于PHPEXCEL输出报表有上万条记录的时候貌似挂了？
程序员文章站
2022-05-22 11:01:15
...
这是一个完整可用的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 的规模使用内存
据说可以什么分批导入？有没有例子怎么去写
自己到网上搜一下就是了
没哟这样的需求，谁会去测试?