关于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 的规模使用内存
据说可以什么分批导入?有没有例子怎么去写
自己到网上搜一下就是了
没哟这样的需求,谁会去测试?