### 帶有表頭的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(); } ~~~