PHP导出Excel

发布于：2022-05-17 09:20:53  栏目：技术文档

set_time_limit(0);
ini_set('memory_limit','1024M');
$letter_all=[];
$letter=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];


//标题字段导出用户信息field要和数据库字段名称一致
$title_arr=[
['title'=>'姓名','field'=>'name'],
['title'=>'性别','field'=>'sex'],
['title'=>'手机号码','field'=>'phone'],
];

//通过标题字段生成excel字母例如3个字段，生成A-C
$item=0;
foreach($title_arras$k=>$v){
if($item==26){
$item=0;
}
$n=ceil(($k+1)/26);
if($n==1){
array_push($letter_all,$letter[$item]);
}else{
$n=$n-2;
array_push($letter_all,$letter[$n].$letter[$item]);
}
$item++;
}
//引入excel类
//vendor('PHPExcel.PHPExcel');
include_once(ROOT_PATH.'extend/lib/PHPExcel.php');

$fileName="学生数据".date('YmdHi');//导出标题
$PHPExcel=new\PHPExcel();

$PHPExcel->setActiveSheetIndex(0);
$PHPExcel->getActiveSheet()->setTitle($fileName);


#生成Excel首行标题
foreach($title_arras$k=>$v){

$PHPExcel->getActiveSheet()->setCellValue($letter_all[$k].'1',$v['title']);
$PHPExcel->getActiveSheet()->getStyle($letter_all[$k].'1')->getFont()->setBold(true);//字体加粗
//$PHPExcel->getActiveSheet()->getColumnDimension($letter_all[$k])->setAutoSize(true);//内容自适应
}

#生成标题对应内容
foreach($listas$k=>$v){
foreach($title_arras$kk=>$vv){
$value=$v[$vv['field']];
//性别字段存储数字12，1男2女
switch($vv['field']){
case'sex':
if(!empty($value)){
$value=$value==1?'男':'女';
}
break;
}
$PHPExcel->getActiveSheet()->setCellValue($letter_all[$kk].($k+2),$value);

}

}

//保存为2003格式
//$objWriter=new\PHPExcel_Writer_Excel5($PHPExcel);

//生成2007格式
$objWriter=\PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');

header("Pragma:public");
header("Expires:0");
header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");

//多浏览器下兼容中文标题
$encoded_filename=urlencode($fileName);
$ua=$_SERVER["HTTP_USER_AGENT"];
if(preg_match("/MSIE/",$ua)){
header('Content-Disposition:attachment;filename="'.$encoded_filename.'.xlsx"');
}elseif(preg_match("/Firefox/",$ua)){
header('Content-Disposition:attachment;filename*="utf8\'\''.$fileName.'.xlsx"');
}else{
header('Content-Disposition:attachment;filename="'.$fileName.'.xlsx"');
}

header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');

相关推荐