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 目录下。

代码筛选列部分还需优化