QTP学习成果之一（自动补考勤脚本）

    休完产假回来上班，每天有一小时哺乳假，结果到月末的时候这个哺乳假需要自己在考勤系统里补，每个月光哺乳假就有二十来条，快累死我了。这两天写了QTP脚本，实现了自动补考勤功能，真是方便不少。

   介绍一下我的学习成果：

   公司是弹性工作时间，可选择在8:30－9:30之间某个时点上班，在17:30－18:30之间相应工作满9小时的时间点下班,9：30之后上班为迟到，17：30之前下班记为早退。哺乳假一小时，可放在早上上班时间前也可以放在下午下班时间后。缺勤情况有：早退、迟到、即迟到又早退、全天未打卡四种情况。针对以上情况分析脚本需要处理的情况如下：

以下是某月本人的考勤缺勤数据（excel文档）：

QTP脚本如下：

RunAction "登录 [登录]", oneIteration
'打开本地excel文件；
Set  excel=CreateObject("Excel.Application")
Set penexcel=excel.Workbooks.Open("E:\缺勤数据.xls")

'获取EXCEL表格的行数：
Rows_count =openexcel.ActiveSheet.UsedRange.Rows.Count
'获取EXCEL表格的列数：
'Columns_count =openexcel.ActiveSheet.UsedRange.Columns.count
'print " 行数："&Rows_count&"  列数："&Columns_count
Dim  i ,Zhuangtai,Clocktime, Style
For i =2 to Rows_count
 '获取考勤状态的值：
    Zhuangtai =openexcel.ActiveSheet.Range("G"&i).value
 '获取打卡日期的值：
   Clocktime =openexcel.ActiveSheet.Range("D"&i).value
   '获取请假类型的值：
 Style=openexcel.ActiveSheet.Range("H"&i).value
 '获取上班打卡时间：
 WorkingTime = CDate(openexcel.ActiveSheet.Range("E"&i).value)
 '获取下班打卡时间：
 ClosingTime = CDate(openexcel.ActiveSheet.Range("F"&i).value)
 
If Zhuangtai ="全天未打卡" Then

 Select Case Style
 Case "正常考勤"
     RunAction "全天正常考勤 [全天正常考勤]", oneIteration,Clocktime
 Case "带薪年假"
     RunAction "全天带薪年假 [全天带薪年假]", oneIteration,Clocktime
    Case "因公外出"
     RunAction "全天因公外出 [全天因公外出]", oneIteration,Clocktime
    Case "事假"
     RunAction "全天事假 [全天事假]", oneIteration,Clocktime
    Case "病假"
     RunAction "全天病假 [全天病假]", oneIteration,Clocktime
 End Select

End If

If Zhuangtai ="迟到" Then
      If   DateDiff("n",ClosingTime,"18:30")>=0 Then
       'print "我是下午18：30或18：30之前打的卡"
     If  DateDiff("n",WorkingTime,ClosingTime)>=480Then
       BegTime = Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
       EndTime =Clocktime&" "&CDate(openexcel.ActiveSheet.Range("E"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime
    Else
       BegTime = Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
       EndTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("E"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime  
       BegTime = Clocktime&" "&DateAdd("h",-9,openexcel.ActiveSheet.Range("F"&i).value)
       EndTime = Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
       EndTime= DateAdd("n",-1,EndTime)
       RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime  
     End If
       Else
      'print "我是下午18：30之后打的卡"
    If  DateDiff("n",WorkingTime,"18:30")>=480Then
       BegTime = Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
       EndTime =Clocktime&" "&CDate(openexcel.ActiveSheet.Range("E"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime
    Else
       BegTime = Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
       EndTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("E"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime  
       BegTime = Clocktime&" "&"9:30"
       EndTime =  Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
       EndTime= DateAdd("n",-1,EndTime)
       RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
    End If     
      End If

End If

If  Zhuangtai ="早退"Then
      If   DateDiff("n",WorkingTime,"8:30:00")>0 Then
       'print "我是早上八点半前打的卡"
     If  DateDiff("n","8:30:00",ClosingTime)>=480Then
       BegTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("F"&i).value)
       EndTime =Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime
      'print "哺乳时间:"
       'Print "BegTime:"&BegTime
       'Print "EndTime:"&EndTime
       ' Print "-- -- -- -- -- -- -- -- ---- -- --  -- -- -- -- "
    Else
       BegTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("F"&i).value)
       EndTime = Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime  
       BegTime =  Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
       BegTime = DateAdd("n",1,BegTime)
       EndTime =  Clocktime&" "&"17:30"
       RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime  
     End If
       Else
      'print "我是早上八点半或八点半后打的卡"
    If  DateDiff("n",WorkingTime,ClosingTime)>=480Then
       BegTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("F"&i).value)
       EndTime =Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime
    Else
       BegTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("F"&i).value)
       EndTime = Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime  
       BegTime = Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
       BegTime = DateAdd("n",1,BegTime)
       EndTime =  Clocktime&" "&DateAdd("h",9,openexcel.ActiveSheet.Range("E"&i).value)
       RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
    End If     
      End If  
End If

If   Zhuangtai ="即迟到又早退"Then
       If   DateDiff("n","9:30",WorkingTime)> DateDiff("n",ClosingTime,"17:30")Then
       '迟到的时间比早退的时间多，则先补迟到哺乳假
       BegTime = Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
       EndTime =Clocktime&" "&CDate(openexcel.ActiveSheet.Range("E"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime
    '继续判断是否迟到
If  DateDiff("n",DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value),"9:30")>=0 Then
       '补完迟到哺乳假后，不再迟到，则仅需补早退带薪休假
    BegTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("F"&i).value)
    EndTime =Clocktime&" "&DateAdd("h",8,openexcel.ActiveSheet.Range("E"&i).value)
    RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
    Else
    '补完迟到哺乳假后还迟到，则先补迟到带薪休假，再补早退带薪休假
     BegTime = Clocktime&" "&"9:30"
     EndTime =  Clocktime&" "&DateAdd("h",-1,openexcel.ActiveSheet.Range("E"&i).value)
     EndTime = DateAdd("n",-1,EndTime)
     RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
     BegTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("F"&i).value)
     EndTime =  Clocktime&" "&"18:30"
        RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
    End If
 
  Else
    '迟到的时间比早退的时间少或一样多，则先补早退哺乳假
       BegTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("F"&i).value)
       EndTime =Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
       RunAction "哺乳时间 [哺乳时间]", oneIteration,BegTime,EndTime
    '继续判断是否早退
    If   DateDiff("n","17:30",DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value))>=0Then
       '补完早退哺乳假后，不再早退，则仅需补迟到带薪休假
    BegTime = Clocktime&" "&DateAdd("h",-8,openexcel.ActiveSheet.Range("F"&i).value)
    EndTime =Clocktime&" "&CDate(openexcel.ActiveSheet.Range("E"&i).value)
    RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
    Else
    '补完早退哺乳假后还早退，则先补早退带薪休假，再补迟到带薪休假
    BegTime = Clocktime&" "&DateAdd("h",1,openexcel.ActiveSheet.Range("F"&i).value)
    BegTime = DateAdd("n",1,BegTime)
    EndTime =Clocktime&" "&"17:30"
    RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
    BegTime = Clocktime&" "&"8:30"
    EndTime = Clocktime&" "&CDate(openexcel.ActiveSheet.Range("E"&i).value)
    RunAction "小时带薪年假 [小时带薪年假]", oneIteration,BegTime,EndTime
    End If
  End If
      
End If

Next
'保存并退出EXCEL
openexcel.save
openexcel.Close
excel.Quit
set penexcel=nothing
Set  excel=nothing

总结：关于时间的处理主要用到VBScript函数DateAdd和DateDiff。把这两函数学习明白也基本上差不多了。