php 使用phpoffice導出導出excel

荊軻刺秦王

在PHP中，可以使用 PhpSpreadsheet 庫來創建和導出Excel文件。PhpSpreadsheet 是一個純PHP 編寫的組件庫，它使用現代 PHP 寫法，代碼質量和性能比 PHPExcel 高不少，完全可以替代PHPExcel（PHPExcel已不再維護）。使用 PhpSpreadsheet 可以輕松讀取和寫入Excel文檔，支持Excel的所有操作。PhpSpreadsheet 是 PHPExcel 的下一個版本，

首先，通過 Composer 安裝 PhpSpreadsheet 庫：

composer require phpoffice/phpspreadsheet

需要注意的是，具體引入那個適合自己的版本

PhpSpreadsheet 1.x 系列：這個版本系列是基于 PHP 5.x 的。

PhpSpreadsheet 2.x 系列：這是第一個支持 PHP 7.x 的版本，引入了許多性能改進和新功能。

當然最新的版本已經支持到了php8 這里不做說明。

如果想指定版本則可以，以我本地的 php 5.6 為例：

composer require phpoffice/phpspreadsheet:^1.5

需要注意的是：phpoffice 必須要用 composer 安裝，這也更加符合規范，不可以將本地文件夾復制到服務器環境，這是不生效的。

使用起來就更加輕松：

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

結合實際案例，本次的需求是部分列如果沒有數據（合計為0，則不顯示）：

/*** @return void* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception* @throws \think\db\exception\DataNotFoundException* @throws \think\db\exception\ModelNotFoundException* @throws \think\exception\DbException* */public function ext_hr_tb(){$year_month=$_SESSION['hr_trim']['year_month'];$year=date('Y',strtotime($year_month));$month=(int)date('m',strtotime($year_month));$where_str=" 1=1 ";//月份過濾$where_str .= " and year=" . $year . " and month=" . $month;//考勤計算數據獲取$hr_cal_arr = db('hr_cal')->alias('c')->join('sys_user u', 'c.user_id=u.id')->where($where_str)->order('u.site_id asc')->field('c.id,u.user_gh,u.nickname,c.user_id,c.year,c.month,c.last_annual_num,c.last_repair_num,c.holiday_hour,c.local_note_hour,c.local_annual_num,c.tw_out_work_time,c.local_repair_num,c.local_num,c.intern_day,abs(c.casual_leave) as casual_leave,c.sick_leave,c.marry_leave,c.baby_leave,c.over_leave,c.work_err_leave,c.f_baby_leave,c.l_baby_leave,c.work_leave,c.abs_hour,c.bf_num,c.lunch_num,c.remark,c.is_lock')->select();$total_arr = ['holiday_hour_total' => 0,'abs_hour_total' => 0,'marry_leave_total' => 0,'baby_leave_total' => 0,'over_leave_total' => 0,'work_err_leave_total' => 0,'f_baby_leave_total' => 0,'l_baby_leave_total' => 0,];$data = array();foreach ($hr_cal_arr as $key => $val) {$temp_arr = array();$temp_arr['user_gh'] = get_cache_data('user_info', $val['user_id'], 'user_gh');$temp_arr['nickname'] = get_cache_data('user_info', $val['user_id'], 'nickname');$temp_arr['last_annual_num'] = c_z($val['last_annual_num']);$temp_arr['last_repair_num'] = c_z($val['last_repair_num']);$temp_arr['holiday_hour'] = c_z($val['holiday_hour']);$temp_arr['local_note_hour'] = c_z($val['local_note_hour']);$temp_arr['local_annual_num'] = c_z($val['local_annual_num']);$temp_arr['local_repair_num'] = c_z($val['local_repair_num']);$temp_arr['local_num'] = c_z($val['local_num']);$temp_arr['casual_leave'] = c_z($val['casual_leave']);$temp_arr['sick_leave'] = c_z($val['sick_leave']);$temp_arr['marry_leave'] = c_z($val['marry_leave']);$temp_arr['baby_leave'] = c_z($val['baby_leave']);$temp_arr['over_leave'] = c_z($val['over_leave']);$temp_arr['work_err_leave'] = c_z($val['work_err_leave']);$temp_arr['f_baby_leave'] = c_z($val['f_baby_leave']);$temp_arr['l_baby_leave'] = c_z($val['l_baby_leave']);$temp_arr['abs_hour'] = c_z($val['abs_hour']);$temp_arr['intern_day'] = $val['intern_day'];$temp_arr['remark'] = $val['remark'];array_push($data, $temp_arr);$total_arr['holiday_hour_total'] += $temp_arr['holiday_hour'];$total_arr['abs_hour_total'] += $temp_arr['abs_hour'];$total_arr['marry_leave_total'] += $temp_arr['marry_leave'];$total_arr['baby_leave_total'] += $temp_arr['baby_leave'];$total_arr['over_leave_total'] += $temp_arr['over_leave'];$total_arr['work_err_leave_total'] += $temp_arr['work_err_leave'];$total_arr['f_baby_leave_total'] += $temp_arr['f_baby_leave'];$total_arr['l_baby_leave_total'] += $temp_arr['l_baby_leave'];}
//        $header = [
//            'A1'=>'員工工號', 'B1'=>'員工姓名', 'C1'=>'上月結算年休', 'D1'=>'上月結算補休', 'E1'=>'節假日結加班費時數',
//            'F1'=>'本月申請休假', 'G1'=>'本次結算年休時數', 'H1'=>'本次結算補休時數', 'I1'=>'本次年休+補休', 'J1'=>'事假時數',
//            'K1'=>'病假時數', 'L1'=>'曠職時數', 'M1'=>'實習生出勤天數', 'N1'=>'備註', 'O1'=>'婚假',
//            'P1'=>'產假', 'Q1'=>'喪假', 'R1'=>'工傷假', 'S1'=>'陪產假', 'T1'=>'哺乳假'
//        ];//生成header頭算法// 使用array_filter篩選出值為0的變量$variablesWithZero = array_filter($total_arr, function ($value) {return $value === 0;});$zeroCount = count($variablesWithZero);$asciiValue = ord('T'); // 輸出 84$endCode = chr($asciiValue - $zeroCount);$letters = $this->generateRange('A', $endCode);$b = ['員工工號', '員工姓名', '上月結算年休', '上月結算補休', '節假日結加班費時數','本月申請休假', '本次結算年休時數', '本次結算補休時數', '本次年休+補休', '事假時數','病假時數', '曠職時數', '實習生出勤天數', '備註', '婚假','產假', '喪假', '工傷假', '陪產假', '哺乳假'];if($total_arr['holiday_hour_total'] == 0){unset($b[4]);$data = $this->array_column_remove($data, 'holiday_hour');}if($total_arr['abs_hour_total'] == 0){unset($b[11]);$data = $this->array_column_remove($data, 'abs_hour');}if($total_arr['marry_leave_total'] == 0){unset($b[14]);$data = $this->array_column_remove($data, 'marry_leave');}if($total_arr['baby_leave_total'] == 0){unset($b[15]);$data = $this->array_column_remove($data, 'baby_leave');}if($total_arr['over_leave_total'] == 0){unset($b[16]);$data = $this->array_column_remove($data, 'over_leave');}if($total_arr['work_err_leave_total'] == 0){unset($b[17]);$data = $this->array_column_remove($data, 'work_err_leave');}if($total_arr['f_baby_leave_total'] == 0){unset($b[18]);$data = $this->array_column_remove($data, 'f_baby_leave');}if($total_arr['l_baby_leave_total'] == 0){unset($b[19]);$data = $this->array_column_remove($data, 'l_baby_leave');}$b = array_values($b);$data = array_values($data);$header = array_combine($letters, $b);$this->downExcel($year_month, $header, $data);}/*** @param Request $request* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception*/public function downExcel($year_month, $header, $list_data){//實例化Spreadsheet對象$spreadsheet = new Spreadsheet();//獲取活動工作薄$sheet = $spreadsheet -> getActiveSheet();//定義一個excel的header表頭//$header = ['A1'=>'ID','B1'=>'昵稱','C1'=>'登錄名','D1'=>'手機號','E1'=>'郵箱'];foreach ($header as $key=>$value) {$sheet->setCellValue($key,$value);}$i = 2;//excel表格從第2行開始填入數據$keys = array_keys($list_data[0]);foreach ($list_data as $k => $v) {for ($index=1; $index<=count($keys); $index++){$sheet->setCellValueByColumnAndRow($index,$i,$v[$keys[$index-1]]);}//            $sheet->setCellValueByColumnAndRow(1,$i,$v['user_gh']);
//            $sheet->setCellValueByColumnAndRow(2,$i,$v['nickname']);
//            $sheet->setCellValueByColumnAndRow(3,$i,$v['last_annual_num']);
//            $sheet->setCellValueByColumnAndRow(4,$i,$v['last_repair_num']);
//            $sheet->setCellValueByColumnAndRow(5,$i,$v['holiday_hour']);
//
//            $sheet->setCellValueByColumnAndRow(6,$i,$v['local_note_hour']);
//            $sheet->setCellValueByColumnAndRow(7,$i,$v['local_annual_num']);
//            $sheet->setCellValueByColumnAndRow(8,$i,$v['local_repair_num']);
//            $sheet->setCellValueByColumnAndRow(9,$i,$v['local_num']);
//            $sheet->setCellValueByColumnAndRow(10,$i,$v['casual_leave']);
//
//            $sheet->setCellValueByColumnAndRow(11,$i,$v['sick_leave']);
//            $sheet->setCellValueByColumnAndRow(12,$i,$v['abs_hour']);
//            $sheet->setCellValueByColumnAndRow(13,$i,$v['intern_day']);
//            $sheet->setCellValueByColumnAndRow(14,$i,$v['remark']);
//            $sheet->setCellValueByColumnAndRow(15,$i,$v['marry_leave']);
//
//            $sheet->setCellValueByColumnAndRow(16,$i,$v['baby_leave']);
//            $sheet->setCellValueByColumnAndRow(17,$i,$v['over_leave']);
//            $sheet->setCellValueByColumnAndRow(18,$i,$v['work_err_leave']);
//            $sheet->setCellValueByColumnAndRow(19,$i,$v['f_baby_leave']);
//            $sheet->setCellValueByColumnAndRow(20,$i,$v['l_baby_leave']);$i++;//$i從2累加}//定義文件名稱，需要帶有定義的后綴名$filename = $year_month . '_hr_tb.xlsx';ob_end_clean(); //清除緩沖區,避免亂碼//將輸出重定向到客戶端的web瀏覽器header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="' . $filename . '"');header('Cache-Control: max-age=0');//如果瀏覽器為IE9header('Cache-Control: max-age=1');//如果通過SSL向IE提供服務header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');header('Cache-Control: cache, must-revalidate');//HTTP/1.1header('Pragma: public');//HTTP/1.0$writer = IOFactory ::createWriter($spreadsheet, 'Xlsx');$writer -> save('php://output');exit;}/*** @param $start* @param $end* @param $startNum* @param $endNum* @return array* $range = generateRange('A', 'Z', 1, 1);* print_r($range);*/function generateRange($start = 'A', $end = 'Z', $startNum = 1, $endNum = 1){$results = [];for ($letter = $start; strcmp($letter, $end) <= 0; $letter++) {for ($num = $startNum; $num <= $endNum; $num++) {$results[] = $letter . $num;}}return $results;}function array_column_remove($input, $column_key) {return array_map(function ($item) use ($column_key) {unset($item[$column_key]);return $item;}, $input);}

前面數據列表不再多言，各家業務盡不相同，主要說一下我的思路，首先拿到所有列，然后根據合計是否為0判斷是否刪除列。這個時候表頭就要做成動態的，動態生成 A1 到 XX 生成函數也放在下面了，題外話：對于 thinkphp5 而言，凡是 composer 安裝的插件一律放到 vendor 目錄下，而手動下載的安裝包（非composer管理）的，則放到?extend 目錄下。

代碼篩選列部分還需優化