``` <?php namespace app\admin\controller; use app\BaseController; class Export extends BaseController { public function download() { $enToCn = [ 'door_num' => '（原）門牌號', 'name' => '客戶姓名', 'id_card' => '身份證號', 'phone' => '客戶電話', 'type' => '用水戶類型', 'waterworks' => '所屬水廠', 'grid' => '所屬網格', 'recorder' => '添加人員', 'township' => '鄉鎮/區', 'village' => '村莊', 'site' => '水表位置（經緯度）', 'ton' => '水表噸數', 'address' => '詳細地址', 'pay_num' => '繳費賬號', 'check_text' => '審核狀態', 'create_time' => '創建時間', ]; //此數組的順序就是Excel表格展示的順序 key對應的是數據庫字段名 value對應的是Excel的表頭 $cnToEn = array_flip($enToCn); $intToCn = array_keys($cnToEn); $intToEn = array_keys($enToCn); $time = input('time/s', ''); $startTime = null; $endTime = null; if($time) { $startTime = explode(' - ', $time)[0]; $endTime = explode(' - ', $time)[1]; } //為防止一次性導出全表數據量過大而導出失敗，加上一個時間段的篩選 $data = \app\admin\model\Payform::order('id') ->append(['site','check_text']) ->where(function ($query) use ($startTime,$endTime){ if ($startTime&&$endTime) { $query->whereBetweenTime('create_time', $startTime, $endTime); } }) ->select() ->toArray(); $name = "用水戶信息統計表（導出）"; $excel = new \PHPExcel(); iconv('UTF-8', 'gb2312', $name); //針對中文名轉碼 $excel->setActiveSheetIndex(0); $sheel = $excel->getActiveSheet(); $sheel->setTitle($name); //設置表名 $sheel->getDefaultRowDimension()->setRowHeight(14.25);//設置默認行高 $sheel->getDefaultColumnDimension()->setWidth(18);//設置默認列寬 $letterArr = ['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']; foreach ($intToEn as $k => $v) { $sheel->setCellValue($letterArr[$k] . 1, $enToCn[$v]); } // 寫入內容 for($i=0; $i<count($data); $i++){ $j = $i+2; foreach ($intToEn as $k => $v) { $sheel->setCellValue($letterArr[$k] . $j, $data[$i][$v]."\t"); } } header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='.$name.'.xlsx'); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007'); $objWriter->save('php://output'); exit; } } ``` 注意：需要安裝php生成excel擴展? ?composer require phpoffice/phpexcel 注意：Excel表頭用的數組$letterArr = \['A','B','C','D','E','F','G','H','I',....\];可以使用下面方法生成 ``` function excelHead($columns) { $array = []; $n = 0; for ($i = 'A'; $i <= 'Z'; $i++) { if ($n<$columns) array_push($array, $i); $n++; } return $array; } ```