荆轲刺秦王
在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');
//如果浏览器为IE9
header('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.1
header('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 目录下。
代码筛选列部分还需优化