当前位置:首页 > PHP教程 > php高级应用 > 列表

phpExcel数据内存溢出解决办法

发布:smiling 来源: PHP粉丝网  添加日期:2014-07-30 11:26:59 浏览: 评论:0 

云平台需要进行excel数据批量导入,使用的是phpExcel工具,小曲同学的代码都写好后,小数据量下测试都没有问题,可是一到正式环境下,数据超过千条,一行十列为一条数据,就报内存超出.

先看实例,代码如下:

  1. <?php  
  2. require_once 'PHPExcel.php';        
  3. require_once 'PHPExcel/Writer/Excel5.php';     
  4. require_once("..includemysqlconn.php");    
  5. $sdate=$_POST["sdate"];//接受传递过来的生成时间段    
  6. $edate=$_POST["edate"];    
  7. //$sdate='2009-01-01';    
  8. //$edate='2009-04-01';    
  9. $cancel_time=date("YmdHis");    
  10.    
  11. $data=new MysqlConn();    
  12. $data->connect();    
  13. $sql="select * from employee_addminus where (oper_time between '$sdate' and '$edate') and isCanceled=0";    
  14.        
  15. // 创建一个处理对象实例        
  16. $objExcel = new PHPExcel();        
  17.        
  18. // 创建文件格式写入对象实例, uncomment        
  19. $objWriter = new PHPExcel_Writer_Excel5($objExcel);       
  20.      
  21. //设置文档基本属性        
  22. $objProps = $objExcel->getProperties();        
  23. $objProps->setCreator("章贡区医疗保险局");        
  24. $objProps->setLastModifiedBy("章贡区医疗保险局");        
  25. $objProps->setTitle("章贡区医疗保险局职工月增减变动报表");        
  26. $objProps->setSubject("章贡区医疗保险局职工月增减变动报表");        
  27. $objProps->setDescription("章贡区医疗保险局职工月增减变动报表");        
  28. $objProps->setKeywords("章贡区医疗保险局职工月增减变动报表");        
  29. $objProps->setCategory("变动报表");        
  30.        
  31. //*************************************        
  32. //设置当前的sheet索引,用于后续的内容操作。        
  33. //一般只有在使用多个sheet的时候才需要显示调用。        
  34. //缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0        
  35. $objExcel->setActiveSheetIndex(0);        
  36. $objActSheet = $objExcel->getActiveSheet();        
  37.        
  38. //设置当前活动sheet的名称        
  39. $objActSheet->setTitle('月增减变动报表');        
  40.        
  41. //*************************************        
  42. //        
  43. //设置宽度,这个值和EXCEL里的不同,不知道是什么单位,略小于EXCEL中的宽度    
  44. $objActSheet->getColumnDimension('A')->setWidth(20);     
  45. $objActSheet->getColumnDimension('B')->setWidth(10);     
  46. $objActSheet->getColumnDimension('C')->setWidth(6);     
  47. $objActSheet->getColumnDimension('D')->setWidth(20);     
  48. $objActSheet->getColumnDimension('E')->setWidth(12);     
  49. $objActSheet->getColumnDimension('F')->setWidth(10);     
  50. $objActSheet->getColumnDimension('G')->setWidth(20);     
  51. $objActSheet->getColumnDimension('H')->setWidth(18);     
  52. $objActSheet->getColumnDimension('I')->setWidth(12);     
  53. $objActSheet->getColumnDimension('J')->setWidth(8);     
  54. $objActSheet->getColumnDimension('K')->setWidth(8);     
  55. $objActSheet->getColumnDimension('L')->setWidth(12);     
  56. $objActSheet->getColumnDimension('M')->setWidth(10);     
  57. $objActSheet->getColumnDimension('N')->setWidth(10);     
  58.    
  59. $objActSheet->getRowDimension(1)->setRowHeight(30);     
  60. $objActSheet->getRowDimension(2)->setRowHeight(27);     
  61. $objActSheet->getRowDimension(3)->setRowHeight(16);     
  62.      
  63. //设置单元格的值      
  64. $objActSheet->setCellValue('A1''章贡区医疗保险局职工月增减变动报表');     
  65. //合并单元格    
  66. $objActSheet->mergeCells('A1:N1');     
  67. //设置样式    
  68. $objStyleA1 = $objActSheet->getStyle('A1');        
  69. $objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  70. $objFontA1 = $objStyleA1->getFont();        
  71. $objFontA1->setName('宋体');        
  72. $objFontA1->setSize(18);      
  73. $objFontA1->setBold(true);        
  74.    
  75. //设置居中对齐    
  76. $objActSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  77. $objActSheet->getStyle('B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  78. $objActSheet->getStyle('C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  79. $objActSheet->getStyle('D2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  80. $objActSheet->getStyle('E2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  81. $objActSheet->getStyle('F2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  82. $objActSheet->getStyle('G2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  83. $objActSheet->getStyle('H2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  84. $objActSheet->getStyle('I2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  85. $objActSheet->getStyle('J2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  86. $objActSheet->getStyle('K2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  87. $objActSheet->getStyle('L2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  88. $objActSheet->getStyle('M2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  89. $objActSheet->getStyle('N2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
  90.    
  91. $objActSheet->setCellValue('A2''现所在单位');     
  92. $objActSheet->setCellValue('B2''姓名');     
  93. $objActSheet->setCellValue('C2''性别');     
  94. $objActSheet->setCellValue('D2''身份证号码');     
  95. $objActSheet->setCellValue('E2''参保时间');     
  96. $objActSheet->setCellValue('F2''增减原因');     
  97. $objActSheet->setCellValue('G2''原所在单位');     
  98. $objActSheet->setCellValue('H2''增减时间');     
  99. $objActSheet->setCellValue('I2''退休时间');     
  100. $objActSheet->setCellValue('J2''原工资');     
  101. $objActSheet->setCellValue('K2''现工资');     
  102. $objActSheet->setCellValue('L2''定点医院');     
  103. $objActSheet->setCellValue('M2''操作人');     
  104. $objActSheet->setCellValue('N2''备注');     
  105.    
  106. //设置边框    
  107. $objActSheet->getStyle('A2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  108.     $objActSheet->getStyle('A2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  109.     $objActSheet->getStyle('A2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  110.     $objActSheet->getStyle('A2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  111.     $objActSheet->getStyle('B2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  112.     $objActSheet->getStyle('B2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  113.     $objActSheet->getStyle('B2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  114.     $objActSheet->getStyle('B2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  115.         $objActSheet->getStyle('C2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  116.     $objActSheet->getStyle('C2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  117.     $objActSheet->getStyle('C2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  118.     $objActSheet->getStyle('C2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  119.         $objActSheet->getStyle('D2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  120.     $objActSheet->getStyle('D2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  121.     $objActSheet->getStyle('D2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  122.     $objActSheet->getStyle('D2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  123.         $objActSheet->getStyle('E2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  124.     $objActSheet->getStyle('E2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  125.     $objActSheet->getStyle('E2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  126.     $objActSheet->getStyle('E2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  127.         $objActSheet->getStyle('F2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  128.     $objActSheet->getStyle('F2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  129.     $objActSheet->getStyle('F2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  130.     $objActSheet->getStyle('F2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  131.             $objActSheet->getStyle('G2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  132.     $objActSheet->getStyle('G2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  133.     $objActSheet->getStyle('G2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  134.     $objActSheet->getStyle('G2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  135.             $objActSheet->getStyle('H2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  136.     $objActSheet->getStyle('H2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  137.     $objActSheet->getStyle('H2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  138.     $objActSheet->getStyle('H2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  139.         $objActSheet->getStyle('I2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  140.     $objActSheet->getStyle('I2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  141.     $objActSheet->getStyle('I2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  142.     $objActSheet->getStyle('I2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  143.         $objActSheet->getStyle('J2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  144.     $objActSheet->getStyle('J2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  145.     $objActSheet->getStyle('J2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  146.     $objActSheet->getStyle('J2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  147.         $objActSheet->getStyle('K2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  148.     $objActSheet->getStyle('K2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  149.     $objActSheet->getStyle('K2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  150.     $objActSheet->getStyle('K2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  151.         $objActSheet->getStyle('L2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  152.     $objActSheet->getStyle('L2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  153.     $objActSheet->getStyle('L2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  154.     $objActSheet->getStyle('L2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  155.         $objActSheet->getStyle('M2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  156.     $objActSheet->getStyle('M2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  157.     $objActSheet->getStyle('M2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  158.     $objActSheet->getStyle('M2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  159.             $objActSheet->getStyle('N2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  160.     $objActSheet->getStyle('N2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  161.     $objActSheet->getStyle('N2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  162.     $objActSheet->getStyle('N2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  163.    
  164. $query=$data->query($sql);    
  165. $i=1;    
  166. //从数据库取值循环输出    
  167. while($result=mysql_fetch_row($query)){    
  168. $personName=$result[1];    
  169. $idcard=$result[2];    
  170. $old_company=$result[3];    
  171. $new_company=$result[4];    
  172. $sex=$result[5];    
  173. $start_time=$result[6];    
  174. $reason=$result[7];    
  175. $retire_time=$result[8];    
  176. $old_wages=$result[9];    
  177. $new_wages=$result[10];    
  178. $hospital=$result[11];    
  179. $remarks=$result[12];    
  180. $operator=$result[13];    
  181. $oper_time=$result[14];    
  182.    
  183.     $n=$i+2;    
  184.         
  185.     $objActSheet->getStyle('B'.$n)->getNumberFormat()->setFormatCode('@');    
  186.     $objActSheet->getStyle('E'.$n)->getNumberFormat()->setFormatCode('@');    
  187.         
  188.     $objActSheet->getRowDimension($n)->setRowHeight(16);     
  189.         
  190.     $objActSheet->getStyle('A'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  191.     $objActSheet->getStyle('A'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  192.     $objActSheet->getStyle('A'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  193.     $objActSheet->getStyle('A'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  194.     $objActSheet->getStyle('B'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  195.     $objActSheet->getStyle('B'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  196.     $objActSheet->getStyle('B'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  197.     $objActSheet->getStyle('B'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  198.         $objActSheet->getStyle('C'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  199.     $objActSheet->getStyle('C'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  200.     $objActSheet->getStyle('C'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  201.     $objActSheet->getStyle('C'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  202.         $objActSheet->getStyle('D'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  203.     $objActSheet->getStyle('D'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  204.     $objActSheet->getStyle('D'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  205.     $objActSheet->getStyle('D'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  206.         $objActSheet->getStyle('E'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  207.     $objActSheet->getStyle('E'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  208.     $objActSheet->getStyle('E'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  209.     $objActSheet->getStyle('E'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  210.         $objActSheet->getStyle('F'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  211.     $objActSheet->getStyle('F'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  212.     $objActSheet->getStyle('F'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  213.     $objActSheet->getStyle('F'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  214.             $objActSheet->getStyle('G'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  215.     $objActSheet->getStyle('G'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  216.     $objActSheet->getStyle('G'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  217.     $objActSheet->getStyle('G'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  218.             $objActSheet->getStyle('H'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  219.     $objActSheet->getStyle('H'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  220.     $objActSheet->getStyle('H'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  221.     $objActSheet->getStyle('H'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  222.         $objActSheet->getStyle('I'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  223.     $objActSheet->getStyle('I'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  224.     $objActSheet->getStyle('I'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  225.     $objActSheet->getStyle('I'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  226.         $objActSheet->getStyle('J'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  227.     $objActSheet->getStyle('J'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  228.     $objActSheet->getStyle('J'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  229.     $objActSheet->getStyle('J'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  230.         $objActSheet->getStyle('K'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  231.     $objActSheet->getStyle('K'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  232.     $objActSheet->getStyle('K'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  233.     $objActSheet->getStyle('K'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  234.         $objActSheet->getStyle('L'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  235.     $objActSheet->getStyle('L'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  236.     $objActSheet->getStyle('L'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  237.     $objActSheet->getStyle('L'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  238.             $objActSheet->getStyle('M'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  239.     $objActSheet->getStyle('M'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  240.     $objActSheet->getStyle('M'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  241.     $objActSheet->getStyle('M'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  242.             $objActSheet->getStyle('N'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  243.     $objActSheet->getStyle('N'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  244.     $objActSheet->getStyle('N'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  245.     $objActSheet->getStyle('N'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
  246.         
  247.     $xb="男";    
  248.     if($sex==1){    
  249.         $xb="女";    
  250.     }    
  251.         
  252.     $objActSheet->setCellValue('A'.$n$new_company);     
  253.     $objActSheet->setCellValue('B'.$n$personName);     
  254.     $objActSheet->setCellValue('C'.$n$xb);     
  255.     $objActSheet->setCellValue('D'.$n' '.$idcard.' ');     
  256.     $objActSheet->setCellValue('E'.$n$start_time);     
  257.     $objActSheet->setCellValue('F'.$n$reason);     
  258.     $objActSheet->setCellValue('G'.$n$old_company);     
  259.     $objActSheet->setCellValue('H'.$n$oper_time);     
  260.     $objActSheet->setCellValue('I'.$n$retire_time);     
  261.     $objActSheet->setCellValue('J'.$n$old_wages);     
  262.     $objActSheet->setCellValue('K'.$n$new_wages);     
  263.     $objActSheet->setCellValue('L'.$n$hospital);     
  264.     $objActSheet->setCellValue('M'.$n$operator);     
  265.     $objActSheet->setCellValue('N'.$n$remarks);     
  266.     $i++;    
  267. //*************************************        
  268. //输出内容        
  269. //        
  270.    
  271. $outputFileName = "tables/".$cancel_time."addminus.xls";        
  272. //到文件        
  273. $objWriter->save($outputFileName);        
  274. //下面这个输出我是有个页面用Ajax接收返回的信息    
  275. echo("<a href="tables/".$cancel_time."addminus.xls" mce_href="tables/".$cancel_time."addminus.xls" target='_blank'>点击下载电子表</a>"); 
  276. ?> 

小数据量没问题,但是大数据量时出现数据导入内存溢出经过查询之后,找到了解决办法.

版本:1.7.6,在不进行特殊设置的情况下,phpExcel将读取的单元格信息保存在内存中,我们可以通过如下代码:

PHPExcel_Settings::setCacheStorageMethod();

来设置不同的缓存方式,已达到降低内存消耗的目的.

1、将单元格数据序列化后保存在内存中,代码如下:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized; 

2、将单元格序列化后再进行Gzip压缩,然后保存在内存中,代码如下:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip; 

3、缓存在临时的磁盘文件中,速度可能会慢一些,代码如下:

PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

4、保存在php://temp,代码如下:

PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;  

5、保存在memcache中,代码如下:

PHPExcel_CachedObjectStorageFactory::cache_to_memcache

举例,第4种方式,代码如下:

  1. $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;   
  2. $cacheSettings = array' memoryCacheSize '  => '8MB'   
  3.                       );   
  4. PHPExcel_Settings::setCacheStorageMethod($cacheMethod$cacheSettings);  

第5种,代码如下:

  1. $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;   
  2. $cacheSettings = array'memcacheServer'  => 'localhost',   
  3.                        'memcachePort'    => 11211,   
  4.                       'cacheTime'       => 600   
  5.                      );   
  6. PHPExcel_Settings::setCacheStorageMethod($cacheMethod$cacheSettings); 

Tags: phpExcel数据 内存溢出

分享到: