云平台需要进行excel数据批量导入,使用的是phpexcel工具,小曲同学的代码都写好后,小数据量下测试都没有问题,可是一到正式环境下,数据超过千条,一行十列为一条数据,就报内存超出.
先看实例,代码如下:
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 (点击下载电子表);?>
小数据量没问题,但是大数据量时出现数据导入内存溢出经过查询之后,找到了解决办法.
版本：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);