利用perl计算考勤表并且输出excel表格

?需要处理的excel的格式如下

?

转换器代码如下（防止中文乱码）(作者为:flw)：

?

 
 

package?MyExcelFormatter;?
  
  
use?strict;?
  
  
use?warnings;?
  
  
use?base?qw(Spreadsheet::ParseExcel::FmtDefault);?
  
  
use?Encode::CN;?
  
  
use?Encode?qw(from_to);?
  
  
sub?new()?{?
  
  
????return?bless?{};?
  
  
}?
  
  
sub?TextFmt(?$;$?)?{?
  
  
????my?$this?=?shift;?
  
  
????my?($value,?$code)?=?@_;?
  
  
????if?(?defined?$code?and?$code?eq?'ucs2'?){?
  
  
????????from_to(?$value,?'ucs2',?'gb2312'?);?
  
  
????}?
  
  
????return?$value;?
  
  
}?
  
  
1;?
 
 

 
 

#!/usr/bin/perl?
  
  
use?strict;?
  
  
use?warnings;?
  
  
use?Spreadsheet::ParseExcel;?
  
  
use?MyExcelFormatter;??#flw大神写的中文转化模块。?
  
  
use?Data::Dumper;?
  
  
use?Time::Local;?
  
  
use?POSIX?qw{strftime};?
  
  
use?Excel::Writer::XLSX;?
  
  
use?Encode?;?
  
  
sub?H{?
  
  
????my?$text?=?shift;?
  
  
????return??decode('gb2312',$text);??#?进行转码?
  
  
}?
  
  
my?(%hash,%mon);?
  
  
my?$fmt?=?new?MyExcelFormatter();?
  
  
my?$file?=?'kaoqin.xls';?
  
  
my?$xls?=?Spreadsheet::ParseExcel::Workbook->Parse(?$file,?$fmt?);?
  
  
my?@workSheet?=?@{?$xls->{Worksheet}?};?
  
  
open??FH,">douzi.txt"?or?die?"$!";?
  
  
foreach?my?$sheet?(?@workSheet?){?
  
  
????my?$sheetName?=?$sheet->get_name();?
  
  
????my?(?$minRow,?$maxRow?)?=?$sheet->row_range();??#索引所有的行?
  
  
????my?(?$minCol,?$maxCol?)?=?$sheet->col_range();???#索引所有的列?
  
  
????foreach?my?$row?(?1?..?$maxRow?){???????????#此处的行?从第二行开始?
  
  
????????my?$tmp;?
  
  
????????foreach?my?$col?(?$minCol?..?$maxCol?){?
  
  
????????????my?$cell?=?$sheet->get_cell(?$row,?$col?);?
  
  
????????????next?unless?$cell;?
  
  
????????????????$tmp?.=?$cell->value."?";?
  
  
????????}????????
  
  
????????my($name,$date,$time)?=?(split/s+/,$tmp)[1,3,4];???#提取姓名，日期，时间?
  
  
???????"$date?$time"?=~?/(d+)-(d+)-(d+)s+(d+):(d+):(d+)/;?????
  
  
????my?$time_unix?=?timelocal($6,$5,$4,$3,$2-1,$1);?
  
  
????my?$time_9clock?=?timelocal(0,30,9,$1);?
  
  
????push?@{$hash{$name}{$date}},$time_unix,$time_9clock;?
  
  
????$mon{$date}++;???#此处可以自己定义上班的时间%mon;我这里直接读取了一个月所有的时间。?
  
  
}?
  
  
}?
  
  
my?$workbook?=?Excel::Writer::XLSX->new('test.xlsx');??#准备写入excel?
  
  
map{?my?$name=$_;my?$num=1;?
  
  
???my??$worksheet?=?$workbook->add_worksheet(H($name));??#以每个人作为一个工作簿?
  
  
???$worksheet->write_row("B1",[H('上班时间'),H('下班时间'),H('工作时间')]);?
  
  
????for?(map{$_->[0]}sort{$a->[2]?<=>?$b->[2]||$a->[3]?<=>$b->[3]}map{[$_,split/-/]}keys?%mon){?
  
  
????????#按照日期时间排序?
  
  
????????my?@arr;?
  
  
????????$num++;??#定义行号?
  
  
????????push?@arr,$_;?
  
  
?????????if?(defined($hash{$name}{$_}->[0])){?
  
  
????????if($hash{$name}{$_}->[0]?>=?$hash{$name}{$_}->[-1]){??#定义早晨打卡时间，如果大于则记为迟到?
  
  
????????????push?@arr,H("迟到");?
  
  
????????}else{?
  
  
????????????push?@arr,strftime("%Y-%m-%d?%H:%M:%St",localtime($hash{$name}{$_}->[0]));??
  
  
????????}?
  
  
????????my??$work_night?=?strftime("%Y-%m-%d?%H:%M:%St",localtime($hash{$name}{$_}->[-2]));?#定义下班时间?
  
  
????????my?$work_time?=?$hash{$name}{$_}->[-2]-$hash{$name}{$_}->[0];#时间戳计算?
  
  
????????my?$work_hours?=??$work_time/3600;???#工作时间长度?
  
  
????????if($work_time<32400?and?$work_time>0){??#如果少于工作的时间则为早退?
  
  
????????????push?@arr,$work_night;?
  
  
??????????????push?@arr,H(sprintf("早退,实际工作时间:%2.1fn",$work_hours));?
  
  
????????}elsif($work_time?==?0?and?$hash{$name}{$_}->[-2]<($hash{$name}{$_}->[-1]+10800)?){??
  
  
???????????#如果一天只打了一次卡，且在早晨12点之前打过卡，则定义为下班未打卡?
  
  
????????????push?@arr?,H('下班未打卡');?
  
  
????????}?
  
  
????????else{?
  
  
????????????push?@arr,$work_night;?
  
  
????????????push?@arr,H(sprintf("%2.1fn",$work_hours));?
  
  
????????}?
  
  
?????????}else{?
  
  
????????????push?@arr,H('缺勤');??#没有打过一次卡的记为缺勤?
  
  
?????????}?
  
  
?????????$worksheet->write_row("A$num",@arr);?
  
  
????}?
  
  
????}keys?%hash;?
  
  
close?FH;?
 
 

输出excel

?