最近做项目遇到一个导出excel的需求,马上想到了PHPExcel插件,以前一直以为导出挺简单的,没想到这次遇到了那么坑!在此记录一下!
要导出的excel表格样式:
基本上就这个样子吧,总共96列!
具体代码贴上:
$objPHPExcel = new \PHPExcel();
// 设置文档属性
$objPHPExcel->getProperties()->setCreator("zhuanli")
->setLastModifiedBy("zhuanli")
->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("Test result file");
//生成列名,A-Z AA-AZ BA-BZ CA-CT
$col_arr=range("A","Z");
foreach (range("A","Z") as $val){
array_push($col_arr,'A'.$val);
}
foreach (range("A","Z") as $val){
array_push($col_arr,'B'.$val);
}
foreach (range("A","T") as $val){
array_push($col_arr,'C'.$val);
}
// 字体和样式
/**设置 二 SOOPAT链接获取信息*/
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12); //设置全局默认的字体大小
$objPHPExcel->getActiveSheet()->getStyle('A1:A2')->getFont()->getColor()->setARGB('#FF0000'); //第二行【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('A1:A2')->getFont()->setBold(true);//设置【是否有结果】字体加粗
/**这里是设置K2:S2单元格背景色 start*/
$objPHPExcel->getActiveSheet()->getStyle('K2:S2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('K2:S2')->getFill()->getStartColor()->setARGB('00FFC000');
/**这里是设置K2:S2单元格背景色 end*/
$objPHPExcel->getActiveSheet()->getStyle('K2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('K2')->getFont()->setBold(true);//设置字体加粗
/**设置 三 百度信用网获取信息 */
$objPHPExcel->getActiveSheet()->getStyle('T2:V2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('T2:V2')->getFill()->getStartColor()->setARGB('00FFFF00');
$objPHPExcel->getActiveSheet()->getStyle('T2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('T2')->getFont()->setBold(true);//设置字体加粗
/**设置 四 SIPO网站信息摘录以及图片文字分析 和*/
$objPHPExcel->getActiveSheet()->getStyle('W2:AS2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('W2:AS2')->getFill()->getStartColor()->setARGB('0092D050');
$objPHPExcel->getActiveSheet()->getStyle('W2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('Y2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('Z2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AN2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AO2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AP2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AQ2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AR2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AS2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('W2')->getFont()->setBold(true);//设置字体加粗
/**设置 五 搜索引擎搜索结果摘录(一)背景色 和*/
$objPHPExcel->getActiveSheet()->getStyle('AT2:BN2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('AT2:BN2')->getFill()->getStartColor()->setARGB('00FFFF00');
$objPHPExcel->getActiveSheet()->getStyle('BO2:BZ2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('BO2:BZ2')->getFill()->getStartColor()->setARGB('0092D050');
$objPHPExcel->getActiveSheet()->getStyle('CA2:CF2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('CA2:CF2')->getFill()->getStartColor()->setARGB('000070C0');
$objPHPExcel->getActiveSheet()->getStyle('AT2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
$objPHPExcel->getActiveSheet()->getStyle('AT2')->getFont()->setBold(true);//设置字体加粗
/**设置 五 搜索引擎搜索结果摘录(二)背景色 和*/
$objPHPExcel->getActiveSheet()->getStyle('CG2:CR2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('CG2:CR2')->getFill()->getStartColor()->setARGB('00FFC000');
$objPHPExcel->getActiveSheet()->getStyle('CG2')->getFont()->getColor()->setARGB('#FF0000'); //【是否有结果】文字颜色
// 设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('K1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('T1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('W1')->getAlignment()->setVertical(PHPExcel_Style_Align