PHP

高不少#xff0c;完全可以替代PHPExcel#xff08;PHPExcel已不再维护…荆轲刺秦王

PhpSpreadsheet

高不少完全可以替代PHPExcelPHPExcel已不再维护。

使用

PhpSpreadsheet

可以轻松读取和写入Excel文档支持Excel的所有操作。

PhpSpreadsheet

PHPExcel

安装这也更加符合规范不可以将本地文件夹复制到服务器环境这是不生效的。

使用起来就更加轻松

PhpOffice\PhpSpreadsheet\IOFactory;

use

PhpOffice\PhpSpreadsheet\Spreadsheet;

如果没有数据合计为0则不显示

\PhpOffice\PhpSpreadsheet\Writer\Exception*

throws

\think\db\exception\DataNotFoundException*

throws

\think\db\exception\ModelNotFoundException*

throws

ext_hr_tb(){$year_month$_SESSION[hr_trim][year_month];$yeardate(Y,strtotime($year_month));$month(int)date(m,strtotime($year_month));$where_str

and

db(hr_cal)-alias(c)-join(sys_user

c.user_idu.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)

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

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]

$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]

$header

使用array_filter筛选出值为0的变量$variablesWithZero

function

count($variablesWithZero);$asciiValue

ord(T);

哺乳假];if($total_arr[holiday_hour_total]

$this-array_column_remove($data,

holiday_hour);}if($total_arr[abs_hour_total]

$this-array_column_remove($data,

abs_hour);}if($total_arr[marry_leave_total]

$this-array_column_remove($data,

marry_leave);}if($total_arr[baby_leave_total]

$this-array_column_remove($data,

baby_leave);}if($total_arr[over_leave_total]

$this-array_column_remove($data,

over_leave);}if($total_arr[work_err_leave_total]

$this-array_column_remove($data,

work_err_leave);}if($total_arr[f_baby_leave_total]

$this-array_column_remove($data,

f_baby_leave);}if($total_arr[l_baby_leave_total]

$this-array_column_remove($data,

l_baby_leave);}$b

$b);$this-downExcel($year_month,

$header,

\PhpOffice\PhpSpreadsheet\Writer\Exception*/public

function

$list_data){//实例化Spreadsheet对象$spreadsheet

new

getActiveSheet();//定义一个excel的header表头//$header

[A1ID,B1昵称,C1登录名,D1手机号,E1邮箱];foreach

($header

{$sheet-setCellValue($key,$value);}$i

array_keys($list_data[0]);foreach

($list_data

$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

//清除缓冲区,避免乱码//将输出重定向到客户端的web浏览器header(Content-Type:

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);header(Content-Disposition:

attachment;filename

max-age0);//如果浏览器为IE9header(Cache-Control:

max-age1);//如果通过SSL向IE提供服务header(Expires:

Mon,

must-revalidate);//HTTP/1.1header(Pragma:

IOFactory

{unset($item[$column_key]);return

$item;},

前面数据列表不再多言各家业务尽不相同主要说一下我的思路首先拿到所有列然后根据合计是否为0判断

thinkphp5