phpExcel数据内存溢出解决办法
发布:smiling 来源: PHP粉丝网 添加日期:2014-07-30 11:26:59 浏览: 评论:0
云平台需要进行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);
Tags: phpExcel数据 内存溢出
相关文章
- ·phpExcel导出大量数据出现内存溢出错误解决办法(2014-09-10)
- ·PHP对象相互引用的内存溢出实例分析(2021-04-10)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)