phpExcel数据内存溢出解决办法
云平台需要进行excel数据批量导入,使用的是phpExcel工具,小曲同学的代码都写好后,小数据量下测试都没有问题,可是一到正式环境下,数据超过千条,一行十列为一条数据,就报内存超出.
先看实例,代码如下:
<?php require_once 'PHPExcel.php' ; require_once 'PHPExcel/Writer/Excel5.php' ; require_once ( "..includemysqlconn.php" ); $sdate = $_POST [ "sdate" ]; //接受传递过来的生成时间段 $edate = $_POST [ "edate" ]; //$sdate='2009-01-01'; //$edate='2009-04-01'; $cancel_time = date ( "YmdHis" ); $data = new MysqlConn(); $data ->connect(); $sql = "select * from employee_addminus where (oper_time between '$sdate' and '$edate') and isCanceled=0" ; // 创建一个处理对象实例 $objExcel = new PHPExcel(); // 创建文件格式写入对象实例, uncomment $objWriter = new PHPExcel_Writer_Excel5( $objExcel ); //设置文档基本属性 $objProps = $objExcel ->getProperties(); $objProps ->setCreator( "章贡区医疗保险局" ); $objProps ->setLastModifiedBy( "章贡区医疗保险局" ); $objProps ->setTitle( "章贡区医疗保险局职工月增减变动报表" ); $objProps ->setSubject( "章贡区医疗保险局职工月增减变动报表" ); $objProps ->setDescription( "章贡区医疗保险局职工月增减变动报表" ); $objProps ->setKeywords( "章贡区医疗保险局职工月增减变动报表" ); $objProps ->setCategory( "变动报表" ); //************************************* //设置当前的sheet索引，用于后续的内容操作。 //一般只有在使用多个sheet的时候才需要显示调用。 //缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0 $objExcel ->setActiveSheetIndex(0); $objActSheet = $objExcel ->getActiveSheet(); //设置当前活动sheet的名称 $objActSheet ->setTitle( '月增减变动报表' ); //************************************* // //设置宽度，这个值和EXCEL里的不同，不知道是什么单位，略小于EXCEL中的宽度 $objActSheet ->getColumnDimension( 'A' )->setWidth(20); $objActSheet ->getColumnDimension( 'B' )->setWidth(10); $objActSheet ->getColumnDimension( 'C' )->setWidth(6); $objActSheet ->getColumnDimension( 'D' )->setWidth(20); $objActSheet ->getColumnDimension( 'E' )->setWidth(12); $objActSheet ->getColumnDimension( 'F' )->setWidth(10); $objActSheet ->getColumnDimension( 'G' )->setWidth(20); $objActSheet ->getColumnDimension( 'H' )->setWidth(18); $objActSheet ->getColumnDimension( 'I' )->setWidth(12); $objActSheet ->getColumnDimension( 'J' )->setWidth(8); $objActSheet ->getColumnDimension( 'K' )->setWidth(8); $objActSheet ->getColumnDimension( 'L' )->setWidth(12); $objActSheet ->getColumnDimension( 'M' )->setWidth(10); $objActSheet ->getColumnDimension( 'N' )->setWidth(10); $objActSheet ->getRowDimension(1)->setRowHeight(30); $objActSheet ->getRowDimension(2)->setRowHeight(27); $objActSheet ->getRowDimension(3)->setRowHeight(16); //设置单元格的值 $objActSheet ->setCellValue( 'A1' , '章贡区医疗保险局职工月增减变动报表' ); //合并单元格 $objActSheet ->mergeCells( 'A1:N1' ); //设置样式 $objStyleA1 = $objActSheet ->getStyle( 'A1' ); $objStyleA1 ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objFontA1 = $objStyleA1 ->getFont(); $objFontA1 ->setName( '宋体' ); $objFontA1 ->setSize(18); $objFontA1 ->setBold(true); //设置居中对齐 $objActSheet ->getStyle( 'A2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'B2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'C2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'D2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'E2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'F2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'G2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'H2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'I2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'J2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'K2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'L2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'M2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->getStyle( 'N2' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet ->setCellValue( 'A2' , '现所在单位' ); $objActSheet ->setCellValue( 'B2' , '姓名' ); $objActSheet ->setCellValue( 'C2' , '性别' ); $objActSheet ->setCellValue( 'D2' , '身份证号码' ); $objActSheet ->setCellValue( 'E2' , '参保时间' ); $objActSheet ->setCellValue( 'F2' , '增减原因' ); $objActSheet ->setCellValue( 'G2' , '原所在单位' ); $objActSheet ->setCellValue( 'H2' , '增减时间' ); $objActSheet ->setCellValue( 'I2' , '退休时间' ); $objActSheet ->setCellValue( 'J2' , '原工资' ); $objActSheet ->setCellValue( 'K2' , '现工资' ); $objActSheet ->setCellValue( 'L2' , '定点医院' ); $objActSheet ->setCellValue( 'M2' , '操作人' ); $objActSheet ->setCellValue( 'N2' , '备注' ); //设置边框 $objActSheet ->getStyle( 'A2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'A2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'A2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'A2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N2' )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N2' )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N2' )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N2' )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $query = $data ->query( $sql ); $i =1; //从数据库取值循环输出 while ( $result =mysql_fetch_row( $query )){ $personName = $result [1]; $idcard = $result [2]; $old_company = $result [3]; $new_company = $result [4]; $sex = $result [5]; $start_time = $result [6]; $reason = $result [7]; $retire_time = $result [8]; $old_wages = $result [9]; $new_wages = $result [10]; $hospital = $result [11]; $remarks = $result [12]; $operator = $result [13]; $oper_time = $result [14]; $n = $i +2; $objActSheet ->getStyle( 'B' . $n )->getNumberFormat()->setFormatCode( '@' ); $objActSheet ->getStyle( 'E' . $n )->getNumberFormat()->setFormatCode( '@' ); $objActSheet ->getRowDimension( $n )->setRowHeight(16); $objActSheet ->getStyle( 'A' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'A' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'A' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'A' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'B' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'C' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'D' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'E' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'F' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'G' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'H' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'I' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'J' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'K' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'L' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'M' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N' . $n )->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N' . $n )->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N' . $n )->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $objActSheet ->getStyle( 'N' . $n )->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); $xb = "男" ; if ( $sex ==1){ $xb = "女" ; } $objActSheet ->setCellValue( 'A' . $n , $new_company ); $objActSheet ->setCellValue( 'B' . $n , $personName ); $objActSheet ->setCellValue( 'C' . $n , $xb ); $objActSheet ->setCellValue( 'D' . $n , ' ' . $idcard . ' ' ); $objActSheet ->setCellValue( 'E' . $n , $start_time ); $objActSheet ->setCellValue( 'F' . $n , $reason ); $objActSheet ->setCellValue( 'G' . $n , $old_company ); $objActSheet ->setCellValue( 'H' . $n , $oper_time ); $objActSheet ->setCellValue( 'I' . $n , $retire_time ); $objActSheet ->setCellValue( 'J' . $n , $old_wages ); $objActSheet ->setCellValue( 'K' . $n , $new_wages ); $objActSheet ->setCellValue( 'L' . $n , $hospital ); $objActSheet ->setCellValue( 'M' . $n , $operator ); $objActSheet ->setCellValue( 'N' . $n , $remarks ); $i ++; } //************************************* //输出内容 // $outputFileName = "tables/" . $cancel_time . "addminus.xls" ; //到文件 $objWriter ->save( $outputFileName ); //下面这个输出我是有个页面用Ajax接收返回的信息 echo ( "<a href=" tables/ ".$cancel_time." addminus.xls " mce_href=" tables/ ".$cancel_time." addminus.xls " target='_blank'>点击下载电子表</a>" ); ?>小数据量没问题,但是大数据量时出现数据导入内存溢出经过查询之后,找到了解决办法.
版本：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种方式,代码如下:
$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; $cacheSettings = array ( ' memoryCacheSize ' => '8MB' ); PHPExcel_Settings::setCacheStorageMethod( $cacheMethod , $cacheSettings );第5种,代码如下:
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache; $cacheSettings = array ( 'memcacheServer' => 'localhost' , 'memcachePort' => 11211, 'cacheTime' => 600 ); PHPExcel_Settings::setCacheStorageMethod( $cacheMethod , $cacheSettings );查看更多关于phpExcel数据内存溢出解决办法 - php高级应用的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did30080