namespaceapp\excel;
usePHPExcel;
usePHPExcel_IOFactory;
classExcelMultiSheetHelper
{
/**
*多表单数据导出
*@param$data
*$data=[
*[
*'title'=>'sheet1',
*'list'=>
*[
*['name'=>'sheet1_line1_name','item'=>'sheet1_line1_item'],
*['name'=>'sheet1_line2_name','item'=>'sheet1_line1_item']
*],
*'column'=>
*[
*'A'=>['title'=>'title_name','field'=>'name'],
*'B'=>['title'=>'title_item','field'=>'item']
*],
*],
*[
*'title'=>'sheet2',
*'list'=>[
*['name'=>'sheet2_line1_name','item'=>'sheet2_line1_item'],
*['name'=>'sheet2_line2_name','item'=>'sheet2_line1_item']
*],
*'column'=>
*[
*'A'=>['title'=>'title_name','field'=>'name'],
*'B'=>['title'=>'title_item','field'=>'item']
*],
*],
*];
*@paramstring$fileName
*/
publicstaticfunctionexportToExcel($data,$fileName='数据导出')
{
$objPHPExcel=newPHPExcel();//实例化Excel类
foreach($dataas$key=>$value){
$objPHPExcel->createSheet($key);
$objPHPExcel->setActiveSheetIndex($key);
$activeSheet=$objPHPExcel->getactivesheet();
$activeSheet->setTitle($value['title']);
$column=$value['column'];
$list=$value['list'];
foreach($listas$kk=>$vv){
$line=$kk+2;
foreach($columnas$kkk=>$vvv){
$title=$vvv['title'];
$field=$vvv['field'];
$activeSheet->getColumnDimension($kkk)->setWidth(15);//设置单元格宽
$fieldData=!empty($vv[$field])?$vv[$field]:'';
$activeSheet->getStyle($kkk.1)->getFont()->setName('宋体')->setBold(true);//字体加粗;//字体
$activeSheet->setcellvalue($kkk.1,$title);//第A列第1行，标题
$activeSheet->setcellvalue($kkk.$line,$fieldData);//第A列第$k行
}
}
}
if($objPHPExcel->getSheetCount()>1){
$objPHPExcel->removeSheetByIndex($objPHPExcel->getSheetCount()-1);//删除模板sheet
}
//最后通过浏览器输出
static::responseExcelFile($fileName,$objPHPExcel,'Excel5');
exit;
}
/**
*最后通过浏览器输出
*@param$fileName
*@param$objPHPExcel
*@paramstring$excelVersion
*/
publicstaticfunctionresponseExcelFile($fileName,$objPHPExcel,$excelVersion='Excel5')
{
if(static::isIE()){//解决文件名含中文时下载乱码问题。测试浏览器：IE8\IE11\Chrome41\Firefox38
$fileName=urlencode($fileName);
}
$fileName.=$excelVersion==='Excel5'?'.xls':'.xlsx';
ob_end_clean();
ob_start();
//添加fileDownloadcookie便于jquery.fileDownload.js判断下载状态
header('Set-Cookie:fileDownload=true;path=/');
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename="'.$fileName.'"');
header('Cache-Control:max-age=0');
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,$excelVersion);
$objWriter->save('php://output');
}
/**
*判断当前请求浏览器是否IE
*@returnbool
*/
privatestaticfunctionisIE()
{
$u_agent=$_SERVER['HTTP_USER_AGENT'];
return(preg_match('/MSIE/i',$u_agent)||preg_match('/Trident/i',$u_agent))&&!preg_match('/Opera/i',$u_agent);
}
}
参考博客
