search

Home >php教程 >php手册 >phpExcel数据内存溢出解决办法

phpExcel数据内存溢出解决办法

WBOY
WBOYOriginal
2016-05-25 16:43:303145browse

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

先看实例,代码如下:

<?php
require_once &#39;PHPExcel.php&#39;;
require_once &#39;PHPExcel/Writer/Excel5.php&#39;;
require_once ("..includemysqlconn.php");
$sdate = $_POST["sdate"]; //接受传递过来的生成时间段
$edate = $_POST["edate"];
//$sdate=&#39;2009-01-01&#39;;
//$edate=&#39;2009-04-01&#39;;
$cancel_time = date("YmdHis");
$data = new MysqlConn();
$data->connect();
$sql = "select * from employee_addminus where (oper_time between &#39;$sdate&#39; and &#39;$edate&#39;) 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(&#39;月增减变动报表&#39;);
//*************************************
//
//设置宽度，这个值和EXCEL里的不同，不知道是什么单位，略小于EXCEL中的宽度
$objActSheet->getColumnDimension(&#39;A&#39;)->setWidth(20);
$objActSheet->getColumnDimension(&#39;B&#39;)->setWidth(10);
$objActSheet->getColumnDimension(&#39;C&#39;)->setWidth(6);
$objActSheet->getColumnDimension(&#39;D&#39;)->setWidth(20);
$objActSheet->getColumnDimension(&#39;E&#39;)->setWidth(12);
$objActSheet->getColumnDimension(&#39;F&#39;)->setWidth(10);
$objActSheet->getColumnDimension(&#39;G&#39;)->setWidth(20);
$objActSheet->getColumnDimension(&#39;H&#39;)->setWidth(18);
$objActSheet->getColumnDimension(&#39;I&#39;)->setWidth(12);
$objActSheet->getColumnDimension(&#39;J&#39;)->setWidth(8);
$objActSheet->getColumnDimension(&#39;K&#39;)->setWidth(8);
$objActSheet->getColumnDimension(&#39;L&#39;)->setWidth(12);
$objActSheet->getColumnDimension(&#39;M&#39;)->setWidth(10);
$objActSheet->getColumnDimension(&#39;N&#39;)->setWidth(10);
$objActSheet->getRowDimension(1)->setRowHeight(30);
$objActSheet->getRowDimension(2)->setRowHeight(27);
$objActSheet->getRowDimension(3)->setRowHeight(16);
//设置单元格的值
$objActSheet->setCellValue(&#39;A1&#39;, &#39;章贡区医疗保险局职工月增减变动报表&#39;);
//合并单元格
$objActSheet->mergeCells(&#39;A1:N1&#39;);
//设置样式
$objStyleA1 = $objActSheet->getStyle(&#39;A1&#39;);
$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objFontA1 = $objStyleA1->getFont();
$objFontA1->setName(&#39;宋体&#39;);
$objFontA1->setSize(18);
$objFontA1->setBold(true);
//设置居中对齐
$objActSheet->getStyle(&#39;A2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;B2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;C2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;D2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;E2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;F2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;G2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;H2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;I2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;J2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;K2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;L2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;M2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->getStyle(&#39;N2&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objActSheet->setCellValue(&#39;A2&#39;, &#39;现所在单位&#39;);
$objActSheet->setCellValue(&#39;B2&#39;, &#39;姓名&#39;);
$objActSheet->setCellValue(&#39;C2&#39;, &#39;性别&#39;);
$objActSheet->setCellValue(&#39;D2&#39;, &#39;身份证号码&#39;);
$objActSheet->setCellValue(&#39;E2&#39;, &#39;参保时间&#39;);
$objActSheet->setCellValue(&#39;F2&#39;, &#39;增减原因&#39;);
$objActSheet->setCellValue(&#39;G2&#39;, &#39;原所在单位&#39;);
$objActSheet->setCellValue(&#39;H2&#39;, &#39;增减时间&#39;);
$objActSheet->setCellValue(&#39;I2&#39;, &#39;退休时间&#39;);
$objActSheet->setCellValue(&#39;J2&#39;, &#39;原工资&#39;);
$objActSheet->setCellValue(&#39;K2&#39;, &#39;现工资&#39;);
$objActSheet->setCellValue(&#39;L2&#39;, &#39;定点医院&#39;);
$objActSheet->setCellValue(&#39;M2&#39;, &#39;操作人&#39;);
$objActSheet->setCellValue(&#39;N2&#39;, &#39;备注&#39;);
//设置边框
$objActSheet->getStyle(&#39;A2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;A2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;A2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;A2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;B2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;B2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;B2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;B2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;C2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;C2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;C2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;C2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;D2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;D2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;D2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;D2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;E2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;E2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;E2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;E2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;F2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;F2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;F2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;F2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;G2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;G2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;G2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;G2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;H2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;H2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;H2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;H2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;I2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;I2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;I2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;I2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;J2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;J2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;J2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;J2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;K2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;K2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;K2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;K2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;L2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;L2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;L2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;L2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;M2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;M2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;M2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;M2&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;N2&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;N2&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;N2&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objActSheet->getStyle(&#39;N2&#39;)->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(&#39;B&#39; . $n)->getNumberFormat()->setFormatCode(&#39;@&#39;);
    $objActSheet->getStyle(&#39;E&#39; . $n)->getNumberFormat()->setFormatCode(&#39;@&#39;);
    
    $objActSheet->getRowDimension($n)->setRowHeight(16);     
    $objActSheet->getStyle(&#39;A&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;A&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;A&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;A&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;B&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;B&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;B&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;B&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;C&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;C&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;C&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;C&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;D&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;D&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;D&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;D&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;E&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;E&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;E&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;E&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;F&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;F&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;F&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;F&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;G&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;G&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;G&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;G&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;H&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;H&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;H&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;H&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;I&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;I&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;I&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;I&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;J&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;J&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;J&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;J&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;K&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;K&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;K&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;K&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;L&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;L&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;L&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;L&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;M&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;M&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;M&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;M&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;N&#39;.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;N&#39;.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;N&#39;.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle(&#39;N&#39;.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );   
    $xb = "男";
    if ($sex == 1) {
        $xb = "女";
    }
    $objActSheet->setCellValue(&#39;A&#39; . $n, $new_company);
    $objActSheet->setCellValue(&#39;B&#39; . $n, $personName);
    $objActSheet->setCellValue(&#39;C&#39; . $n, $xb);
    $objActSheet->setCellValue(&#39;D&#39; . $n, &#39; &#39; . $idcard . &#39; &#39;);
    $objActSheet->setCellValue(&#39;E&#39; . $n, $start_time);
    $objActSheet->setCellValue(&#39;F&#39; . $n, $reason);
    $objActSheet->setCellValue(&#39;G&#39; . $n, $old_company);
    $objActSheet->setCellValue(&#39;H&#39; . $n, $oper_time);
    $objActSheet->setCellValue(&#39;I&#39; . $n, $retire_time);
    $objActSheet->setCellValue(&#39;J&#39; . $n, $old_wages);
    $objActSheet->setCellValue(&#39;K&#39; . $n, $new_wages);
    $objActSheet->setCellValue(&#39;L&#39; . $n, $hospital);
    $objActSheet->setCellValue(&#39;M&#39; . $n, $operator);
    $objActSheet->setCellValue(&#39;N&#39; . $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=&#39;_blank&#39;>点击下载电子表</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); 

                           


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP中php_ldap.dll不能加载怎么办 Next article：PHP脚本实现Magento权限设置与缓存清理

Related articles

See more