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');
相关推荐
© 2020 asciim码
人生就是一场修行