PHP导出exl 包含thinkphp和原生php两种写法,原生的写法可初步解决内存溢出

在thinkphp下面我们调用了类库PHPExcel,但是大家都知道 PHPExcel的执行效率实在不高。所以我测试的在导出超过3千行数据的时候,就会崩溃或者内存溢出。

这样的情况下我们可以采用php原生写法,这个写法效率会高很多,初步测试1万行应该没啥问题的。需要的人可以试试。

thinkphp3.1.3写法


function export($list, $params = array()) 
	{
		set_time_limit(0);
		ini_set("memory_limit", "4096M");
		Vendor('phpexcel.PHPExcel');
		$excel = new PHPExcel();
		$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;  
			$cacheSettings = array( ' memoryCacheSize '  => '512MB' );  
			PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); 
		$excel->getProperties()->setCreator("数据")->setLastModifiedBy("数据")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("report file");
		$sheet = $excel->setActiveSheetIndex(0);
		$rownum = 1;
		foreach ($params['columns'] as $key => $column) 
		{
			$sheet->setCellValue(column($key, $rownum), $column['title']);
			if (!empty($column['width'])) 
			{
				
				$sheet->getColumnDimension(column_str($key))->setWidth($column['width']);
			}
		}
		$rownum++;
		foreach ($list as $row) 
		{
			$len = count($row);
			for ($i = 0; $i < $len; $i++) 
			{
				$value = $row[$params['columns'][$i]['field']];
				$sheet->setCellValue(column($i, $rownum), $value);
			}
			$rownum++;
			
		}
		$excel->getActiveSheet()->setTitle($params['title']);
		$filename = urlencode($params['title'] . '-' . date('Y-m-d-H-i', time()));
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
		header('Cache-Control: max-age=0');
		$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
		$writer->save('php://output');
		exit;
	}

 

php原生写法:

 

function export($data, $params = array())
{
set_time_limit(0);
ini_set("memory_limit", "1024M");
// 导出到本地
$filename = urlencode($params['title'] . '-' . date('Y-m-d-H-i', time()));
header ( "Content-type:application/vnd.ms-excel" );
header ( "Content-Disposition:filename=".$filename.".csv" );
header ('Cache-Control: max-age=0');
$fp = fopen('php://output', 'a'); //打开PHP文件句柄,php://output 表示直接输出到浏览器
$head=$params['columns']; //定义标题
foreach ($head as $key => $column)
{
$head[$key] = iconv('utf-8', 'GB18030', $column['title']); //将中文标题转换编码,否则乱码
}
fputcsv($fp, $head); //将标题名称通过fputcsv写到文件句柄
foreach ($data as $k => $row) { //重组数组
$len = count($row);
for ($i = 0; $i < $len; $i++)
{
$rows[$k][$params['columns'][$i]['field']] = $row[$params['columns'][$i]['field']];
}
$rownum++;
}
$limit = 30000;
$num = 0; //计数器
foreach ( $rows as $v ) { //循环数据
$num++;
if($num == $limit){
ob_flush(); //释放内存
flush();
}
$rows = array();
foreach ( $v as $kk => $vv){
$rs[$kk] = iconv('utf-8', 'GB18030', $vv); //转译编码
}
fputcsv($fp, $rs);
}
exit;
}

调用方法:我把调用方法换行了这样看起来清楚一些当然你可以把上述2个函数设置为不一样的名字,随便调用哪个都可以


export($list, array("title" => "订单导出",
"columns" => array(
array('title' => '订单ID', 'field' => 'id', 'width' => 15),
array('title' => '会员编号', 'field' => 'username', 'width' => 12),
array('title' => '姓名', 'field' => 'name', 'width' => 12),
array('title' => '电话', 'field' => 'mobile', 'width' => 18),
array('title' => '订单金额', 'field' => 'pay_money', 'width' => 12),
array('title' => '加入时间', 'field' => 'shijian', 'width' => 20)
) ) );

QQ截图20180522170328

 

© 版权声明
THE END
喜欢就支持一下吧
点赞0
分享
评论 共3条

请登录后发表评论

    • 宇凡的头像-易微帮
    • cialis prices0
      I'm now not certain where you are getting your info, but good topic. I needs to spend a while learning more or understanding more. Thanks for magnificent information I used to be searching for this information for my mission.
      5年前
    • 宇凡的头像-易微帮
    • cheap cialis0
      Your style is unique compared to other folks I've read stuff from. Many thanks for posting when you've got the opportunity, Guess I'll just book mark this blog.
      5年前
    • 宇凡的头像-易微帮
    • buy cialis0
      Hello! This is my first visit to your blog! We are a group of volunteers and starting a new initiative in a community in the same niche. Your blog provided us valuable information to work on. You have done a outstanding job!
      5年前