PHP导出Excel，设置表格样式，填充颜色等较为复杂样式

// 注:只是在此做下记录,有兴趣的可以参考,不做实际教程文档

<?php//引入Li类对数据进行操作include_once('./Li.php');//引入Excel类库对对数据进行操作include_once('./Excel/PHPExcel.php');//开起error all机制error_reporting(E_ALL);//操作数据$Li = new Li;$whe = "1=1";if ($_GET) { if (isset($_GET['sou'])) { $sou = $_GET['sou']; $whe = "CONCAT(`id`,`nr`,`jl`,`bm`,`qrsj`,`bz`) LIKE '%$sou%'"; }}$sql = "select * from check_s where $whe ORDER BY id";$data = $Li->sel("$sql");//实例化Excel类date_default_timezone_set('Europe/London');$objPHPExcel = new PHPExcel();/*设置导出信息*/$objPHPExcel->getProperties()->setCreator("MTN") ->setLastModifiedBy("MTN") ->setTitle("数据EXCEL导出") ->setSubject("数据EXCEL导出") ->setDescription("备份数据") ->setKeywords("excel") ->setCategory("result file");//设置表头名$objPHPExcel->setActiveSheetIndex(0)//Excel的第A列，uid是你查出数组的键值，下面以此类推 ->setCellValue('A5', '序号') ->setCellValue('B5', '检查内容') ->setCellValue('E5', '检查记录') ->setCellValue('F5', '被检查部门') ->setCellValue('G5', '被检查人确认/时间') ->setCellValue('H5', '备注');//合并$objPHPExcel->getActiveSheet()->mergeCells('A1:C3');//给单元格设置值$objPHPExcel->getActiveSheet()->setCellValue('A1', 'LOGO');$objPHPExcel->getActiveSheet()->mergeCells('D1:E2');$objPHPExcel->getActiveSheet()->setCellValue('D1', '文件执行检查表');$objPHPExcel->getActiveSheet()->mergeCells('F1:G1');$objPHPExcel->getActiveSheet()->setCellValue('F1', '表格编号:BZY047-2015');$objPHPExcel->getActiveSheet()->setCellValue('H1', '版次:B');$objPHPExcel->getActiveSheet()->mergeCells('D3:E3');$objPHPExcel->getActiveSheet()->setCellValue('D3', '表格编制部门：考管部 使用部门： ');$objPHPExcel->getActiveSheet()->mergeCells('F2:G2');$objPHPExcel->getActiveSheet()->setCellValue('F2', '实施日期:2015.10.18');$objPHPExcel->getActiveSheet()->setCellValue('H2', '编号:');$objPHPExcel->getActiveSheet()->mergeCells('F3:H3');$objPHPExcel->getActiveSheet()->setCellValue('F3', '代替:BZY047-2010（A版）2010.06.24发布');$objPHPExcel->getActiveSheet()->mergeCells('A4:H4');$objPHPExcel->getActiveSheet()->setCellValue('A4', ' 文件名称：工作现场管理办法 文件编号：BY/GLZ6.4-01-2016 检查日期： 年 月 日 检查人：');$objPHPExcel->getActiveSheet()->mergeCells('B5:D5');//设置列宽$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(3);$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(8);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(23);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(9);$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(9);$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);//设置行高$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);$objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);$objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(20);//设置字体大小加粗$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setName('宋体')->setSize(24)->setBold(true);$objPHPExcel->getActiveSheet()->getStyle('F1:F3')->getFont()->setName('宋体')->setSize(8);$objPHPExcel->getActiveSheet()->getStyle('H1:H2')->getFont()->setName('宋体')->setSize(8);//遍历数据，准备导出$i = 6;foreach ($data as $key => $val) { //数据内容居中 $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->applyFromArray( array( 'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER ) ) ); //配置边框样式 $styleArray = array( 'borders' => array( 'allborders' => array( //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的 'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框 ), ), ); //执行边框样式 $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->applyFromArray($styleArray);//数据内容垂直居中 $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//内容过长自动换行 $objPHPExcel->getActiveSheet()->getStyle('A1:H' . $i)->getAlignment()->setWrapText(true); //设置合并 $objPHPExcel->getActiveSheet()->mergeCells('B' . $i . ':' . 'D' . $i); //设置好单元格横坐标 $clos = ++$key + 5; //进行导出操作 $objPHPExcel->setActiveSheetIndex(0) //Excel的第A列，uid是你查出数组的键值，下面以此类推 ->setCellValue('A' . $clos, $val['id']) ->setCellValue('B' . $clos, $val['nr']) ->setCellValue('E' . $clos, $val['jl']) ->setCellValue('F' . $clos, $val['bm']) ->setCellValue('G' . $clos, $val['qrsj']) ->setCellValue('H' . $clos, $val['bz']); $i++;}//设置sheet1操作表名$objPHPExcel->getActiveSheet()->setTitle('文件执行检查');$objPHPExcel->setActiveSheetIndex(0);header('Content-Type: application/vnd.ms-excel');header('Content-Disposition: attachment;filename="文件执行检查.xls"');header('Cache-Control: max-age=0');$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');$objWriter->save('php://output');exit;

转载于:https://www.cnblogs.com/zhaoxiaowei/p/7809005.html

相关资源：phpexcel导出excel 合并单元格 字体设置 内容自适应