### 带有表头的Excel 导出 whz 2021-8-6 **位置：** Common\Tools\ExcelTools.class.php **参数：** * @param $title array 标题 * @param $data array 内容 二维数组 * @param $filename string 文件名 * @param $header string 标题 * @param $conditions string 条件 * @param $conditions $h 条件行行高 * return resource; */ **调用：** ~~~ use Common\Tools\ExcelTools; // 调用封装Excel工具类 public function export() { $title = array('姓名', '城市', '手机'); $data = array( array('李明', '深圳', '13812345678'), array('王伟', '深圳', '15887654321') ); $filename = '同学通讯录'; $header = '二年级一班同学通讯录'; $conditions = '户籍：深圳'; $e = new ExcelTools(); $e->exportHeadExcel($title, $data , $filename , $header, $conditions); } ~~~ **完整代码：** ~~~ /* * 导出带有表头的Excel * * whz 2021-8-5 * @param $title array 标题 * @param $data array 内容 二维数组 * @param $filename string 文件名 * @param $header string 标题 * @param $conditions string 条件 * @param $conditions $h 条件行行高 * return resource; */ public function exportHeadExcel($title, $data, $filename, $header, $conditions, $h=30) { $this->_title = $title; $objPHPExcel = new \PHPExcel(); $chr = ['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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK']; //Excel内容 $head_num = count($title); $obj = $objPHPExcel->setActiveSheetIndex(0); $obj->mergeCells('A1:'.$chr[$head_num-1].'1'); //第一行 $obj->setCellValue('A1',$header); $obj->mergeCells('A2:'.$chr[$head_num-1].'2'); //第二行 $obj->setCellValue('A2',$conditions); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center'); //水平居中 $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVERTICAL('center'); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20); //设置字体大小 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(40);//行高 $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight($h);//行高 $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setWrapText(true); $active_sheet = $objPHPExcel->getActiveSheet(); if (!is_null($this->getTitle())) { $i='A'; $j = 3; foreach ($this->getTitle() as $key => $value) { $active_sheet->setCellValue($i . $j, $value); $i++; } $j++; } foreach ($data as $key => $value) { $i = 'A'; foreach ($value as $k => $v) { // setCellValueExplicit 修改单元格的值为文本格式，解决首位0被去掉的BUG if (preg_match("/^[0]+\d+$/", $v)) { $active_sheet->setCellValueExplicit($i . $j, $v, 'str'); } elseif(preg_match("/^\d{12,100}$/", $v)) { $active_sheet->setCellValueExplicit($i . $j, $v, 'str'); } else { $active_sheet->setCellValue($i . $j, $v); } $i++; } $j++; } $filename =$filename.date('YmdHis',time()); // $objWrite = \PHPExcel_IOFactory::createWriter($phpExcel,"Excel2007"); $objWrite = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5"); // $objWrite->save($filename); ob_end_clean();//清空缓冲区并关闭输出缓冲，解决在Linux服务器导出始终乱码 header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=$filename.xls"); header("Content-Type:application/octet-stream"); header("Content-Transfer-Encoding:binary"); header("Pragma:no-cache"); $objWrite->save('php://output'); exit(); } ~~~