JAVA ECXCEL 考勤导入查询

最新推荐文章于 2024-05-19 09:00:00 发布

原创最新推荐文章于 2024-05-19 09:00:00 发布 · 587 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java #webview #html

这是一个关于考勤系统的HTML页面和JavaScript实现，包括查询、导入和处理考勤记录的功能。页面包含员工姓名、开始日期、结束日期等输入字段，以及查询、导入和导出按钮。JS部分负责表格初始化、查询、刷新和导入操作。控制器部分处理导入的考勤记录，如批量导入、加班、补卡、外出和请假记录。接口定义了相关服务，如获取考勤列表、处理导入数据等。整个系统实现了考勤数据的管理和处理流程。

一、HTML

<!DOCTYPE html>
<html lang="zh_CN" xmlns:th="http://www.thymeleaf.org"
     xmlns:shiro="http://www.pollix.at/thymeleaf/shiro">
<meta charset="utf-8">
<head th:include="include::header"></head>
<body class="gray-bg">
<div class="wrapper wrapper-content ">
   <div class="col-sm-12">
      <div class="ibox">
         <div class="ibox-body">
            <div class="form-inline" role="form">
               <table >
                  <tr>
                     <td align="center" style="margin-left: 30px;width: 220px">
                           <label class=" control-label">员工姓名</label>
                     </td>
                     <td style="margin-left: 30px;width: 85px">
                        <input id="employeeName"  name="employeeName" class="form-control" type="text" shiro:hasPermission="daily:employeeName" disabled="true">
                     </td>
                     <td style="margin-left: 30px;width: 85px">
                        <label class=" control-label">开始日期</label>
                     </td>
                     <td style="margin-left: 30px;width: 85px">
                        <input id="dailyDateStart"  name="dailyDateStart" class="form-control" type="text">
                     </td>
                     <td style="margin-left: 30px;width: 85px">
                        <label class=" control-label">结束日期</label>
                     </td>
                     <td style="margin-left: 30px;width: 85px">
                        <input id="dailyDateEnd"  name="dailyDateEnd" class="form-control" type="text">
                     </td>
                     <td style="margin-left: 30px;width: 85px">
                        <label class=" control-label"> 用户ID</label>
                     </td>
                     <td style="margin-left: 30px;width: 85px">
                        <input id="userCode"  name="userCode" class="form-control" type="text" disabled="true"  >

                     </td>
                     <td style="margin-left: 30px;width: 85px">
                     </td>
                     <td style="margin-left: 30px;width: 85px">

                     </td>
<!--                     <td align="center" width="10%">-->
<!--                           <label class=" control-label">忘打卡</label>-->
<!--                     </td>-->
<!--                     <td width="15%">-->
<!--                        <select  name="isForget" id="isForget" class="form-control">-->
<!--                           <option value="" >全部</option>-->
<!--                           <option value="1" >是</option>-->
<!--                           <option value="2" >否</option>-->
<!--                        </select>-->
<!--                     </td>-->
<!--                     <td align="center" width="10%">-->
<!--                           <label class=" control-label">外出</label>-->
<!--                     </td>-->
<!--                     <td width="15%">-->
<!--                        <select  name="isOutside" id="isOutside" class="form-control">-->
<!--                           <option value="" >全部</option>-->
<!--                           <option value="1" >是</option>-->
<!--                           <option value="2" >否</option>-->
<!--                        </select>-->
<!--                     </td>-->
<!--                     <td align="center" width="10%">-->
<!--                        <label class=" control-label">出差</label>-->
<!--                     </td>-->
<!--                     <td width="15%">-->
<!--                        <select  name="isBusTrip" id="isBusTrip" class="form-control">-->
<!--                           <option value="" >全部</option>-->
<!--                           <option value="1" >是</option>-->
<!--                           <option value="2" >否</option>-->
<!--                        </select>-->
<!--                     </td>-->
                  </tr>
<!--                  <tr>-->
<!--                     <td align="center" width="10%">-->
<!--                        <label class=" control-label">迟到</label>-->
<!--                     </td>-->
<!--                     <td width="15%">-->
<!--                        <select  name="isComeLate" id="isComeLate" class="form-control">-->
<!--                           <option value="" >全部</option>-->
<!--                           <option value="1" >是</option>-->
<!--                           <option value="2" >否</option>-->
<!--                        </select>-->
<!--                     </td>-->
<!--                     <td align="center" width="10%">-->
<!--                           <label class=" control-label">请假</label>-->
<!--                     </td>-->
<!--                     <td width="15%">-->
<!--                        <select  name="isLeave" id="isLeave" class="form-control">-->
<!--                           <option value="" >全部</option>-->
<!--                           <option value="1" >是</option>-->
<!--                           <option value="2" >否</option>-->
<!--                        </select>-->
<!--                     </td>-->
<!--                     <td width="10%">-->
<!--                        <label class=" control-label">加班是否有效</label>-->
<!--                     </td>-->
<!--                     <td width="15%">-->
<!--                        <select  name="isOvertimeValid" id="isOvertimeValid" class="form-control">-->
<!--                           <option value="" >全部</option>-->
<!--                           <option value="1" >有效</option>-->
<!--                           <option value="2" >无效</option>-->
<!--                        </select>-->
<!--                     </td>-->
<!--                     <td colspan="2" width="25%">&nbsp;</td>-->
<!--                  </tr>-->
               </table>
               <div style="margin-top: 20px">
                  <button class="btn btn-success" onclick="reLoad()" style="margin-left: 30px;width: 85px">
                     <i class="fa fa-search" aria-hidden="true"></i>查询
                  </button>
                  <button class="btn btn-success" onclick="choose('cardRecord')" shiro:hasPermission="daily:cardRecord" style="margin-left: 30px">
                     <i class="fa" aria-hidden="true"></i>考勤导入
                  </button>
<!--                  <button class="btn btn-success" onclick="choose('fillRecord')" shiro:hasPermission="daily:fillRecord" style="margin-left: 30px">-->
<!--                     <i class="fa" aria-hidden="true"></i>补卡导入-->
<!--                  </button>-->
<!--                  <button class="btn btn-success" onclick="choose('leaveRecord')" shiro:hasPermission="daily:leaveRecord" style="margin-left: 30px">-->
<!--                     <i class="fa" aria-hidden="true"></i>请假导入-->
<!--                  </button>-->
<!--                  <button class="btn btn-success" onclick="choose('busTrip')" shiro:hasPermission="daily:busTrip" style="margin-left: 30px">-->
<!--                     <i class="fa" aria-hidden="true"></i>出差导入-->
<!--                  </button>-->
<!--                  <button class="btn btn-success" onclick="choose('outSide')" shiro:hasPermission="daily:outSide" style="margin-left: 30px">-->
<!--                     <i class="fa" aria-hidden="true"></i>外出导入-->
<!--                  </button>-->
                  <button class="btn btn-success" onclick="choose('sumTimeRecord')" shiro:hasPermission="daily:sumTimeRecord" style="margin-left: 30px">
                     <i class="fa" aria-hidden="true"></i>汇总导入
                  </button>
                  <button class="btn btn-primary" onclick="choose('inOutTime')" shiro:hasPermission="daily:inOutTime" style="margin-left: 30px">
                     <i class="fa" aria-hidden="true"></i>模板下载
                  </button>
               </div>
            </div>
            <form enctype="multipart/form-data" method="post" id="cardRecordForm">
               <input type="file" name="cardRecord" id="cardRecord" style="display:none;" onchange="importRecord('cardRecord','cardRecordForm','/daily/importCardRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>
            </form>
<!--            <form enctype="multipart/form-data" method="post" id="fillRecordForm">-->
<!--               <input type="file" name="fillRecord" id="fillRecord" style="display:none;" onchange="importRecord('fillRecord','fillRecordForm','/daily/importFillRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>-->
<!--            </form>-->
<!--            <form enctype="multipart/form-data" method="post" id="leaveRecordForm">-->
<!--               <input type="file" name="leaveRecord" id="leaveRecord" style="display:none;" onchange="importRecord('leaveRecord','leaveRecordForm','/daily/importLeaveRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>-->
<!--            </form>-->
<!--            <form enctype="multipart/form-data" method="post" id="overTimeForm">-->
<!--               <input type="file" name="overTime" id="overTime" style="display:none;" onchange="importRecord('overTime','overTimeForm','/daily/importOverTimeRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>-->
<!--            </form>-->
            <form enctype="multipart/form-data" method="post" id="sumTimeForm">
               <input type="file" name="sumTimeRecord" id="sumTimeRecord" style="display:none;" onchange="importRecord('sumTimeRecord','sumTimeForm','/daily/importSumTimeRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>
            </form>
<!--            <form enctype="multipart/form-data" method="post" id="busTripForm">-->
<!--               <input type="file" name="busTrip" id="busTrip" style="display:none;" onchange="importRecord('busTrip','busTripForm','/daily/importBusTripRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>-->
<!--            </form>-->
<!--            <form enctype="multipart/form-data" method="post" id="outSideForm">-->
<!--               <input type="file" name="outSide" id="outSide" style="display:none;" onchange="importRecord('outSide','outSideForm','/daily/importOutSideRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>-->
<!--            </form>-->
<!--            <form enctype="multipart/form-data" method="post" id="inOutTimeForm">-->
<!--               <input type="file" name="inOutTime" id="inOutTime" style="display:none;" onchange="importExcel('inOutTime','inOutTimeForm','/daily/importInOutTimeRecordExcel')" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"/>-->
<!--            </form>-->
         </div>

         <table id="exampleTable" data-mobile-responsive="true">
         </table>
      </div>
   </div>
</div>
</div>
<div th:include="include :: footer"></div>
<script type="text/javascript" src="/js/appjs/oa/daily/dailyList.js?v=20191113"></script>
<script type="text/javascript" src="/js/jquery.form.js"></script>
<script type="text/javascript" src="/js/plugins/bootstrap/bootstrap-datepicker.js" charset="UTF-8"></script>
<script type="text/javascript" src="/js/plugins/bootstrap/locales/bootstrap-datepicker.zh-CN.min.js" charset="UTF-8"></script>

<script type="text/javascript">
   $('#dailyDateStart').datepicker({
      language: 'zh-CN',
      format: 'yyyy-mm-dd',
      autoclose: true
   });
</script>

<script type="text/javascript">
   $('#dailyDateEnd').datepicker({
      language: 'zh-CN',
      format: 'yyyy-mm-dd',
      autoclose: true
   });
</script>

<!--shiro控制bootstraptable行内按钮看见性 -->
<div shiro:lacksPermission="daily:edit">
   <script>
        $("#exampleTable").on('all.bs.table',function(data){
            $(".mcEdit").remove()
        });
console.log(JSON.stringify())
   </script>
</div>
</body>
</html>

二、JS

var prefix = "/daily";
$(function() {
   load();
})

function reLoad(){
    $('#exampleTable').bootstrapTable('refresh');
}

function load() {
    $('#exampleTable').bootstrapTable('destroy');
   $('#exampleTable').bootstrapTable({
      method : 'get', // 服务器数据的请求方式 get or post
      url : prefix + "/getDailyList", // 服务器数据的加载地址
      striped : true, // 设置为true会有隔行变色效果
      dataType : "json", // 服务器返回的数据类型
      pagination : true, // 设置为true会在底部显示分页条
      // queryParamsType : "limit",
      // //设置为limit则会发送符合RESTFull格式的参数
      singleSelect : false, // 设置为true将禁止多选
      iconSize : 'outline',
      toolbar : '#exampleToolbar',
      contentType : "application/x-www-form-urlencoded",
      // //发送到服务器的数据编码类型
      pageSize : 10, // 如果设置了分页，每页数据条数
      pageNumber : 1, // 如果设置了分布，首页页码
      search : false, // 是否显示搜索框
      showColumns : false, // 是否显示内容下拉框（选择显示的列）
        showRefresh: false,                  //是否显示刷新按钮
      sidePagination : "server", // 设置在哪里进行分页，可选值为"client" 或者 "server"

      queryParams : function(params) {
            var ee=  window.sessionStorage.getItem("UID");
            $('#userCode').val(ee);
            console.log('当前用户',ee);
         return {
            // 说明：传入后台的参数包括offset开始索引，limit步长，sort排序列，order：desc或者,以及所有列的键值对
                limit : params.limit,
                offset : params.offset,
                employeeName:$.trim($("#employeeName").val()),
                dailyDateStart:$.trim($("#dailyDateStart").val()),
                dailyDateEnd:$.trim($("#dailyDateEnd").val()),
                userCode:$.trim($("#userCode").val())
                // isForget:$.trim($("#isForget").val()),
                // isOutside:$.trim($("#isOutside").val()),
                // isBusTrip:$.trim($("#isBusTrip").val()),
                // isComeLate:$.trim($("#isComeLate").val()),
                // isLeave:$.trim($("#isLeave").val()),
                // isOvertimeValid:$.trim($("#isOvertimeValid").val())
         };
      },
      // 返回false将会终止请求
      columns : [
         {
            field : 'id',
            title : '操作',
                align:'center',
                formatter : function(value, row, index) {
                        var e = '<a  href="#" mce_href="#"  class="mcedit" onclick="edit(\''
                            + row.id
                            + '\')">编辑</a> ';
                        return e ;
                }
         },
            {
                field : 'workTime',
                align:'center',
                title : '工作时间'
            },
         {
            field : 'employeeName',
                align:'center',
            title : '员工姓名'
         },{
                field : 'dailyDate',
                align:'center',
                title : '打卡日期'
            },
         {
            title : '上班打卡时间',
                align:'center',
            field : 'checkinTime'
         },
            {
                title : '下班打卡时间',
                align:'center',
                field : 'checkoutTime'
            },
            {
                title : '上下时差',
                align:'center',
                field : 'diffTime'
            },
            {
                title : '考勤说明',
                align:'center',
                field : 'explainTime'
            },
            {
                title : '考勤描述',
                align:'center',
                field : 'remarks'
            }
            // {
            //     title : '时间差',
            //     align:'center',
            //     field : 'overtimeHour'
            // },
            // {
            //     title : '加班是否有效',
            //     align:'center',
            //     field : 'isOvertimeValid',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>有效</span>';
            //         } else{
            //             return '<span>无效</span>';
            //         }
            //     }
            // },
            // {
            //     title : '缺勤时长(时)',
            //     align:'center',
            //     field : 'absenceHour'
            // },
            // {
            //     title : '迟到分钟数(分钟)',
            //     align:'center',
            //     field : 'lateMinute'
            // },
            // {
            //     title : '忘打卡',
            //     align:'center',
            //     field : 'isForget',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>是</span>';
            //         } else{
            //             return '<span>否</span>';
            //         }
            //     }
            // },{
            //     title : '外出',
            //     align:'center',
            //     field : 'isOutside',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>是</span>';
            //         } else{
            //             return '<span>否</span>';
            //         }
            //     }
            // },{
            //     title : '出差',
            //     align:'center',
            //     field : 'isBusTrip',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>是</span>';
            //         } else{
            //             return '<span>否</span>';
            //         }
            //     }
            // },{
            //     title : '迟到',
            //     align:'center',
            //     field : 'isComeLate',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>是</span>';
            //         } else{
            //             return '<span>否</span>';
            //         }
            //     }
            // },{
            //     title : '早退',
            //     align:'center',
            //     field : 'isLeaveEarly',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>是</span>';
            //         } else{
            //             return '<span>否</span>';
            //         }
            //     }
            // },{
            //     title : '请假',
            //     align:'center',
            //     field : 'isLeave',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>是</span>';
            //         } else{
            //             return '<span>否</span>';
            //         }
            //     }
            // },{
            //     title : '请假类型',
            //     align:'center',
            //     field : 'leaveType',
            //     formatter:function(value,row,index){
            //         if (value == 1) {
            //             return '<span>调休</span>';
            //         } else if(value == 2){
            //             return '<span>事假</span>';
            //         }else if(value == 3){
            //             return '<span>病假</span>';
            //         }else if(value == 4){
            //             return '<span>年假</span>';
            //         }else if(value == 5){
            //             return '<span>其他</span>';
            //         }else if(value == 6){
            //             return '<span>产假</span>';
            //         }else if(value == 7){
            //             return '<span>婚假</span>';
            //         }
            //     }
            // },{
            //     title : '请假时长(小时)',
            //     align:'center',
            //     field : 'leaveHour'
            // }
      ]
   });
}




//详情
function edit(id){
    var detailPage = layer.open({
        type : 2,
        title : '编辑考勤记录',
        maxmin : true,
        shadeClose : false, // 点击遮罩关闭层
        area : [ '800px', '520px' ],
        content : prefix + '/edit/' + id // iframe的url
    });
    layer.full(detailPage);
}

function choose(id){
    $("#"+id).val('');
    $("#"+id).click();
}


function importRecord(id,formId,url){
    var file = $("#"+id).val();
    //没有选择文件清空
    if(!file){
        return;
    }
    var exec = (/[.]/.exec(file)) ? /[^.]+$/.exec(file.toLowerCase()) : '';
    if (exec != "xlsx" && exec !="xls") {
        alert("文件格式错误，请下载模块导入!");
        return false;
    }

    $('#'+formId).ajaxSubmit({
        url:url,
        cache:false,
        dataType:'json',
        success: function(result) {
            if (result.code == "000000") {
                layer.msg(result.msg);
                reLoad();
            } else {
                layer.alert(result.msg);
            }
        }
    });
}
三、controller

import com.dj.constants.WebConstants;
import com.dj.erp.common.annotation.Log;
import com.dj.erp.domain.common.PageUtils;
import com.dj.erp.domain.oa.Daily;
import com.dj.erp.domain.oa.DailySummary;
import com.dj.erp.service.oa.DailyService;
import com.dj.erp.service.oa.DailySummaryService;
import com.dj.util.ResponseResult;
import com.dj.util.excel.ExcelHelper;
import com.dj.util.excel.HssfExcelHelper;
import com.dj.util.excel.XssfExcelHelper;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * @Description 考勤记录
 * @Author jinjian
 * @Date 2022/4/20
 * @Version 1.0
 **/
@Controller
@RequestMapping("/daily")
public class DailyController {

    private static final Logger log = LoggerFactory.getLogger(DailyController.class);

    @Autowired
    private DailyService dailyService;

    @Autowired
    private DailySummaryService dailySummaryService;

    @GetMapping
    String index() {
        return "oa/daily/dailyList";
    }

    @GetMapping("/edit/{id}")
    String edit(@PathVariable("id") Integer id, Model model) {

        Daily daily = dailyService.getDaily(id);
        model.addAttribute("obj", daily);
        model.addAttribute("id", id);
        return "oa/daily/edit";
    }


    @RequestMapping("/getDailyList")
    @ResponseBody
    PageUtils getDailyList(@RequestParam Map<String, Object> param) {
        return dailyService.getDailyList(param);
    }

    @RequestMapping("/updateDaily")
    @ResponseBody
    ResponseResult updateDaily(Daily daily) {
        ResponseResult result = new ResponseResult();
        try {
            dailyService.updateDaily(daily);
            result.setCode(WebConstants.SUCCESS);
            result.setMsg("修改成功");
            return result;
        } catch (Exception e) {
            log.error("updateDaily error",e);
            result.setCode(WebConstants.ERROR);
            result.setMsg("修改失败");
            return result;
        }

    }

    @RequestMapping("/importCardRecordExcel")
    @ResponseBody
    @Log("批量导入打卡记录")
    public ResponseResult importCardRecordExcel(HttpServletRequest request, @RequestParam("cardRecord") MultipartFile file) {

        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."), file.getOriginalFilename().length());
        // 文件保存路径
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload/" + UUID.randomUUID().toString() + suffix;
        try {
            // 转存文件
            file.transferTo(new File(filePath));
            //读取excel
            String[] fieldNames = new String[]{"workTime","userCode","employeeName","dailyDate", "checkinTime", "checkoutTime","diffTime","explainTime","remarks"};
            int[] cells = new int[]{1,2, 3, 4, 5,6,7,8,9};
            ExcelHelper eh3 = null;
            if (suffix.indexOf("xlsx") > -1) {
                eh3 = XssfExcelHelper.getInstance(new File(filePath));
            } else {
                eh3 = HssfExcelHelper.getInstance(new File(filePath));
            }
            //开始读取测试
            List<Daily> list = eh3.readExcel(Daily.class, fieldNames, cells, true);
            dailyService.dealCardRecord(list);
            return new ResponseResult(WebConstants.SUCCESS, "导入成功", null);

        } catch (Exception e) {
            log.error("importCardRecordExcel error:", e);
            return new ResponseResult(WebConstants.ERROR, "导入失败", null);
        }
    }

    @RequestMapping("/importSumTimeRecordExcel")
    @ResponseBody
    @Log("批量导入汇总记录")
    public ResponseResult importSumTimeRecordExcel(HttpServletRequest request, @RequestParam("sumTimeRecord") MultipartFile file) {

        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."), file.getOriginalFilename().length());
        // 文件保存路径
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload/" + UUID.randomUUID().toString() + suffix;
        try {
            // 转存文件
            file.transferTo(new File(filePath));
            //读取excel
            String[] fieldNames = new String[]{"userCode","year","month","employeeName","department","cardRecord", "lateCount1", "lateCount2","lateCount3","noRecordCount1","noRecordCount2","noRecordCount3","casualLeaveHours","absenceHours","sickLeaveHours","annualLeaveDays","marriageLeaveDays","outLeaveDays","matemityLeaveDays","businessTripDays","surplusAnnualLeaveDays","employmentDate","leaveDate","casualLeaveSum","overtimeHour","leaveInLieu","remainder","remark"};
            int[] cells = new int[]{1,2, 3, 4, 5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28};
            ExcelHelper eh3 = null;
            if (suffix.indexOf("xlsx") > -1) {
                eh3 = XssfExcelHelper.getInstance(new File(filePath));
            } else {
                eh3 = HssfExcelHelper.getInstance(new File(filePath));
            }
            //开始读取测试
            List<DailySummary> list = eh3.readExcel(DailySummary.class, fieldNames, cells, true);
            dailySummaryService.batchDailySummary(list);
            return new ResponseResult(WebConstants.SUCCESS, "导入成功", null);

        } catch (Exception e) {
            log.error("importSumRecordExcel error:", e);
            return new ResponseResult(WebConstants.ERROR, "导入失败", null);
        }
    }

    @RequestMapping("/importLeaveRecordExcel")
    @ResponseBody
    @Log("批量导入请假记录")
    public ResponseResult importLeaveRecordExcel(HttpServletRequest request, @RequestParam("leaveRecord") MultipartFile file) {

        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."), file.getOriginalFilename().length());
        // 文件保存路径
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload/" + UUID.randomUUID().toString() + suffix;
        try {
            // 转存文件
            file.transferTo(new File(filePath));
            //读取excel
            String[] fieldNames = new String[]{"checkoutTime", "checkinTime", "leaveHour", "employeeName","leaveTypeName","beginDate","endDate","leaveDay"};
            int[] cells = new int[]{3, 4, 5,7, 13,15,16,17};
            ExcelHelper eh3 = null;
            if (suffix.indexOf("xlsx") > -1) {
                eh3 = XssfExcelHelper.getInstance(new File(filePath));
            } else {
                eh3 = HssfExcelHelper.getInstance(new File(filePath));
            }

            List<Daily> list = eh3.readExcel(Daily.class, fieldNames, cells, true);
            dailyService.leaveRecord(list);
            return new ResponseResult(WebConstants.SUCCESS, "导入成功", null);

        } catch (Exception e) {
            log.error("importLeaveRecordExcel error:", e);
            return new ResponseResult(WebConstants.ERROR, "导入失败", null);
        }
    }

    @RequestMapping("/importFillRecordExcel")
    @ResponseBody
    @Log("批量导入补卡记录")
    public ResponseResult importFillRecordExcel(HttpServletRequest request, @RequestParam("fillRecord") MultipartFile file) {

        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."), file.getOriginalFilename().length());
        // 文件保存路径
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload/" + UUID.randomUUID().toString() + suffix;
        try {
            // 转存文件
            file.transferTo(new File(filePath));
            //读取excel
            String[] fieldNames = new String[]{"employeeName", "checkinTime", "checkoutTime"};
            int[] cells = new int[]{4, 5, 6};
            ExcelHelper eh3 = null;
            //开始读取测试
            List<Daily> list = eh3.readExcel(Daily.class, fieldNames, cells, true);
            dailyService.fillRecord(list);
            return new ResponseResult(WebConstants.SUCCESS, "导入成功", null);

        } catch (Exception e) {
            log.error("importFillRecordExcel error:", e);
            return new ResponseResult(WebConstants.ERROR, "导入失败", null);
        }
    }

    @RequestMapping("/importOverTimeRecordExcel")
    @ResponseBody
    @Log("批量导入加班记录")
    public ResponseResult importOverTimeRecordExcel(HttpServletRequest request, @RequestParam("overTime") MultipartFile file) {

        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."), file.getOriginalFilename().length());
        // 文件保存路径
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload/" + UUID.randomUUID().toString() + suffix;
        try {
            // 转存文件
            file.transferTo(new File(filePath));
            //读取excel
            String[] fieldNames = new String[]{"workTime","userCode","employeeName","dailyDate", "checkinTime", "checkoutTime","diffTime","explainTime","remarks"};
            int[] cells = new int[]{1,2, 3, 4, 5,6,7,8,9};
            ExcelHelper eh3 = null;
            if (suffix.indexOf("xlsx") > -1) {
                eh3 = XssfExcelHelper.getInstance(new File(filePath));
            } else {
                eh3 = HssfExcelHelper.getInstance(new File(filePath));
            }
            //开始读取测试
            List<Daily> list = eh3.readExcel(Daily.class, fieldNames, cells, true);
            dailyService.overTimeRecord(list);
            return new ResponseResult(WebConstants.SUCCESS, "导入成功", null);

        } catch (Exception e) {
            log.error("importOverTimeRecordExcel error:", e);
            return new ResponseResult(WebConstants.ERROR, "导入失败", null);
        }
    }

    @RequestMapping("/importOutSideRecordExcel")
    @ResponseBody
    @Log("批量导入外出记录")
    public ResponseResult importOutSideRecordExcel(HttpServletRequest request, @RequestParam("outSide") MultipartFile file) {

        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."), file.getOriginalFilename().length());
        // 文件保存路径
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload/" + UUID.randomUUID().toString() + suffix;
        try {
            // 转存文件
            file.transferTo(new File(filePath));
            //读取excel
            String[] fieldNames = new String[]{"employeeName", "dailyDate", "checkinTime", "checkoutTime"};
            int[] cells = new int[]{3, 4, 5, 6};
            ExcelHelper eh3 = null;
            if (suffix.indexOf("xlsx") > -1) {
                eh3 = XssfExcelHelper.getInstance(new File(filePath));
            } else {
                eh3 = HssfExcelHelper.getInstance(new File(filePath));
            }
            //开始读取测试
            List<Daily> list = eh3.readExcel(Daily.class, fieldNames, cells, true);
            dailyService.outSideRecord(list);
            System.out.println(list  +"测试结果list001");
            System.out.println(fieldNames +"测试结果fieldnames001");
            System.out.println(filePath +"测试结果fielpath001");
            System.out.println(file +"测试结果file001");



            File file1 = new File(filePath);   //creating a new file instance
            FileInputStream fis = new FileInputStream(file1);   //obtaining bytes from the file
//creating Workbook instance that refers to .xlsx file
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            XSSFSheet sheet = wb.getSheetAt(0);     //creating a Sheet object to retrieve object
            Iterator<Row> itr = sheet.iterator();    //iterating over excel file
            while (itr.hasNext())
            {
                Row row = itr.next();
                Iterator<Cell> cellIterator = row.cellIterator();   //iterating over each column
                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType())
                    {
                        case Cell.CELL_TYPE_STRING:    //field that represents string cell type
                            System.out.print(cell.getStringCellValue() + "\t\t\t");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:    //field that represents number cell type
                            System.out.print(cell.getNumericCellValue() + "\t\t\t");
                            break;
                        default:
                    }
                }
                System.out.println(""+"mytest 000111111");
            }

            System.out.println(list  +"测试结果list002");
            System.out.println(fieldNames +"测试结果fieldnames002");
            System.out.println(filePath +"测试结果fielpath002");
            System.out.println(file +"测试结果file002");
            System.out.println(file1 +"测试结果file1002");





            return new ResponseResult(WebConstants.SUCCESS, "导入成功", null);

        } catch (Exception e) {
            log.error("importOutSideRecordExcel error:", e);
            return new ResponseResult(WebConstants.ERROR, "导入失败", null);

        }
    }

    @RequestMapping("/importBusTripRecordExcel")
    @ResponseBody
    @Log("批量导入出差记录")
    public ResponseResult importBusTripRecordExcel(HttpServletRequest request, @RequestParam("busTrip") MultipartFile file) {

        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."), file.getOriginalFilename().length());
        // 文件保存路径
        String filePath = request.getSession().getServletContext().getRealPath("/") + "upload/" + UUID.randomUUID().toString() + suffix;
        try {
            // 转存文件
            file.transferTo(new File(filePath));
            //读取excel
            String[] fieldNames = new String[]{"employeeName", "beginDate", "endDate"};
            int[] cells = new int[]{9, 17, 19};
            ExcelHelper eh3 = null;
            if (suffix.indexOf("xlsx") > -1) {
                eh3 = XssfExcelHelper.getInstance(new File(filePath));
            } else {
                eh3 = HssfExcelHelper.getInstance(new File(filePath));
            }
            //开始读取测试
            List<Daily> list = eh3.readExcel(Daily.class, fieldNames, cells, true);
            dailyService.overTimeRecord(list);
            return new ResponseResult(WebConstants.SUCCESS, "导入成功", null);

        } catch (Exception e) {
            log.error("importBusTripRecordExcel error:", e);
            return new ResponseResult(WebConstants.ERROR, "导入失败", null);
        }
    }

四、接口定义

import java.io.InputStream;
import java.util.List;
import java.util.Map;

/**
 * @Description TODO
 * @Author jinjian
 * @Date 2022/04/20
 * @Version 1.0
 **/
public interface DailyService {

    List<Daily> excelToList(InputStream dailyList);

    PageUtils getDailyList(Map<String,Object> param);

    void batchAddDaily(List<Daily> dailyList);

    void updateDaily(Daily daily) throws Exception;

    Daily getDaily(Integer id);

    /**
     * 处理导入的考勤记录
     * @param dailyList
     * @throws Exception
     */
    void dealCardRecord(List<Daily> dailyList) throws Exception;

    /**
     *  处理加班导入记录
     * @param dailyList
     * @throws Exception
     */
    void overTimeRecord(List<Daily> dailyList) throws Exception;

    /**
     * 处理补卡导入记录
     * @param dailyList
     * @throws Exception
     */
    void fillRecord(List<Daily> dailyList) throws Exception;

    /**
     * 处理外出导入记录
     * @param dailyList
     * @throws Exception
     */
    void outSideRecord(List<Daily> dailyList) throws Exception;

    /**
     * 处理请假导入记录
     * @param dailyList
     * @throws Exception
     */
    void leaveRecord(List<Daily> dailyList) throws Exception;

    /**
     * 处理出差导入记录
     * @param dailyList
     * @throws Exception
     */
    void busTripRecord(List<Daily> dailyList) throws Exception;

 
}

五、接口实现

import com.dj.erp.service.oa.DailyService;
import com.dj.util.ListUtil;
import com.dj.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Stream;

/**
 * @Description 考勤记录服务类
 * @Author jinjian
 * @Date 2022/8/20
 * @Version 1.0
 **/
@Service
public class DailyServiceImpl implements DailyService {

    @Autowired
    private DailyDao dailyDao;

    @Autowired
    private ScheduleDateDao scheduleDateDao;


    @Override
    public List<Daily> excelToList(InputStream dailyList) {
        return null;
    }

    @Override
    public PageUtils getDailyList(Map<String, Object> param) {
        List<Daily> list = dailyDao.getDailyList(param);
        int count = dailyDao.getDailyCount(param);
        return new PageUtils(list, count);
    }

    @Override
    public void batchAddDaily(List<Daily> dailyList) {
        List<List<Daily>> splitList = ListUtil.splitList(dailyList, 500);
        for (List<Daily> list : splitList) {
            dailyDao.batchAddDaily(list);
        }

    }

    @Override
    public void updateDaily(Daily daily) throws Exception {
        dailyDao.updateDaily(daily);
    }

    @Override
    public Daily getDaily(Integer id) {
        return dailyDao.getDailyById(id);
    }


    @Override
    public void dealCardRecord(List<Daily> dailyList) throws Exception {
//        Daily daily = dailyList.get(0);
//        Map<String, Integer> scheduleMap = getScheduleMap(daily.getDailyDate(), "/");
//        calculationDateTime(dailyList, scheduleMap);
//        Map<String, Object> param = new HashMap<>();
//        param.put("dailyList", dailyList);
//        dailyDao.deleteDaily(param);
//        batchAddDaily(dailyList);
        dailyDao.batchAddDaily(dailyList);
    }

    /**
     * 计算工时
     *
     * @param dailyList   考勤记录
     * @param scheduleMap 排班日期
     * @throws Exception
     */
    private void calculationDateTime(List<Daily> dailyList, Map<String, Integer> scheduleMap) throws Exception {
        Integer isWeekDay = null;
        SimpleDateFormat df = new SimpleDateFormat("HH:mm");
        //上班规定最晚打卡时间
        Date workStartTime = df.parse("09:30");
        //午饭休息一个小时
        long lunchTime = 3600000;
        //晚饭半个小时
        long dinnerTime = 1800000;
        //工作时间8小时
        long workTime = 28800000;
        //上午下班时间
        Date amEndTime = df.parse("12:00");
        //下午上班时间
        Date pmStartTime = df.parse("13:00");
        //上班打卡时间
        Date checkinTime = null;
        //下班打卡时间
        Date checkoutTime = null;

        for (Daily temp : dailyList) {

            temp.setDailyDate(dealDate(temp.getDailyDate()));
            isWeekDay = scheduleMap.get(temp.getDailyDate());
            temp.setIsBusTrip(OAConstant.isBusTrip.NO);//出差
            temp.setIsOutside(OAConstant.isOutside.NO);//外出
            if (temp.getIsOvertimeValid() == null) {
                temp.setIsOvertimeValid(OAConstant.isOvertimeValid.NO);//加班是否生效
            }
            temp.setIsLeave(OAConstant.isLeave.NO);//是否请假
            temp.setLeaveHour(0f);//请假时长
            //休息日
            if (isWeekDay != null && isWeekDay == 2) {
                //加班场景
                temp.setIsForget(OAConstant.isForget.NO);
                temp.setIsComeLate(OAConstant.isComeLate.NO);
                temp.setIsLeaveEarly(OAConstant.isLeaveEarly.NO);
                temp.setAbsenceHour(0f);
                temp.setLateMinute(0);
                if (!StringUtil.isBlank(temp.getCheckinTime()) && !StringUtil.isBlank(temp.getCheckoutTime())) {
                    checkinTime = df.parse(temp.getCheckinTime());
                    checkoutTime = df.parse(temp.getCheckoutTime());
                    BigDecimal[] overTime = new BigDecimal(checkoutTime.getTime() - checkinTime.getTime()).divideAndRemainder(new BigDecimal(lunchTime));
                    Double sum = 0d;
                    //余数超过半个小时，则加半个小时
                    if (overTime[1].intValue() > dinnerTime) {
                        sum = overTime[0].intValue() + 0.5;
                    } else {
                        sum = overTime[0].doubleValue();
                    }
                    temp.setOvertimeHour(sum.floatValue());
                } else {
                    temp.setOvertimeHour(0f);
                }
            } else {//工作日
                //忘打卡场景
                if (StringUtil.isBlank(temp.getCheckinTime()) || StringUtil.isBlank(temp.getCheckoutTime())) {
                    temp.setIsForget(OAConstant.isForget.YES);
                    temp.setIsComeLate(OAConstant.isComeLate.NO);
                    temp.setIsLeaveEarly(OAConstant.isLeaveEarly.NO);
                    temp.setAbsenceHour(8f);
                    temp.setLateMinute(0);
                    temp.setOvertimeHour(0f);
                    continue;
                } else {
                    if (temp.getIsForget() == null) {
                        temp.setIsForget(OAConstant.isForget.NO);
                    }
                }
                checkinTime = df.parse(temp.getCheckinTime());
                checkoutTime = df.parse(temp.getCheckoutTime());
                //迟到
                if (checkinTime.getTime() > workStartTime.getTime()) {
                    temp.setIsComeLate(OAConstant.isComeLate.YES);
                    BigDecimal[] lateMinute = new BigDecimal(checkinTime.getTime() - workStartTime.getTime()).divideAndRemainder(new BigDecimal(60000));
                    temp.setLateMinute(lateMinute[0].intValue());
                } else {
                    temp.setLateMinute(0);
                    temp.setIsComeLate(OAConstant.isComeLate.NO);
                }

                long timeCount = checkoutTime.getTime() - checkinTime.getTime() - lunchTime;

                //缺勤，不满8小时
                if (timeCount < workTime) {
                    BigDecimal[] absenceHour = new BigDecimal(workTime - timeCount).divideAndRemainder(new BigDecimal(lunchTime));
                    Double sum = 0d;
                    //余数超过半个小时，则加半个小时
                    if (absenceHour[1].intValue() > dinnerTime) {
                        sum = absenceHour[0].intValue() + 0.5;
                    } else {
                        sum = absenceHour[0].doubleValue();
                    }
                    temp.setAbsenceHour(sum.floatValue());
                    temp.setIsLeaveEarly(OAConstant.isLeaveEarly.YES);
                } else {
                    temp.setIsLeaveEarly(OAConstant.isLeaveEarly.NO);
                    temp.setAbsenceHour(0f);
                }
                //加班
                if (timeCount - workTime - dinnerTime > 0) {
                    BigDecimal[] overHour = new BigDecimal(timeCount - workTime - dinnerTime).divideAndRemainder(new BigDecimal(lunchTime));
                    Double sum = 0d;
                    //余数超过半个小时，则加半个小时
                    if (overHour[1].intValue() > dinnerTime) {
                        sum = overHour[0].intValue() + 0.5;
                    } else {
                        sum = overHour[0].doubleValue();
                    }
                    temp.setOvertimeHour(sum.floatValue());
                } else {
                    temp.setOvertimeHour(0f);
                }

            }

        }
    }

    @Override
    public void overTimeRecord(List<Daily> dailyList) throws Exception {
//        List<Daily> resultList = new ArrayList<Daily>();
//        List<String> dateList = null;
//        Daily temp = null;
//        for (Daily daily : dailyList) {
//            if (daily.getBeginDate().equals(daily.getEndDate())) {
//                daily.setDailyDate(dealDate(daily.getBeginDate()));
//                daily.setIsOvertimeValid(OAConstant.isOvertimeValid.YES);
//                resultList.add(daily);
//            } else {
//                dateList = getBetweenDate(daily.getBeginDate(), daily.getEndDate());
//                for (String date : dateList) {
//                    temp = new Daily();
//                    temp.setEmployeeName(daily.getEmployeeName());
//                    temp.setDailyDate(date);
//                    temp.setIsOvertimeValid(OAConstant.isOvertimeValid.YES);
//                    resultList.add(daily);
//                }
//            }
//        }
//        dailyDao.batchUpdate(resultList);
        dailyDao.batchAddDaily(dailyList);
    }

    /**
     * 获取两个日期间隔的所有日期
     *
     * @param start 格式必须为'2018-01-25'
     * @param end   格式必须为'2018-01-25'
     * @return
     */
    public List<String> getBetweenDate(String start, String end) {
        List<String> list = new ArrayList<>();
        LocalDate startDate = LocalDate.parse(start);
        LocalDate endDate = LocalDate.parse(end);

        long distance = ChronoUnit.DAYS.between(startDate, endDate);
        if (distance < 1) {
            return list;
        }
        Stream.iterate(startDate, d -> {
            return d.plusDays(1);
        }).limit(distance + 1).forEach(f -> {
            list.add(f.toString());
        });
        return list;
    }

    @Override
    public void fillRecord(List<Daily> dailyList) throws Exception {
        Map<String, Object> param = new HashMap<>();
        param.put("dailyList", dailyList);
        List<Daily> list = dailyDao.getDailyList(param);
        if (list.size() == 0) {
            return;
        }
        Map<String, Daily> importMap = getDailyMap(dailyList);
        Daily temp = null;
        SimpleDateFormat df = new SimpleDateFormat("HH:mm");
        //上下班分割时间
        Date normalTime = df.parse("12:00");
        Date signTime = null;
        for (Daily daily : list) {
            temp = importMap.get(daily.getEmployeeName() + daily.getDailyDate());
            if (temp == null) {
                continue;
            }
            if (StringUtil.isBlank(daily.getCheckinTime())) {
                daily.setCheckinTime(temp.getSignTime());
            } else if (StringUtil.isBlank(daily.getCheckoutTime())) {
                daily.setCheckoutTime(temp.getSignTime());
            } else {
                signTime = df.parse(temp.getSignTime());
                if (signTime.getTime() >= normalTime.getTime()) {
                    daily.setCheckoutTime(temp.getSignTime());
                } else {
                    daily.setCheckinTime(temp.getSignTime());
                }
            }

        }
        Map<String, Integer> scheduleMap = getScheduleMap(dailyList.get(0).getDailyDate(), "-");
        calculationDateTime(list, scheduleMap);
        param.clear();
        param.put("dailyList", list);
        dailyDao.deleteDaily(param);
        batchAddDaily(list);

    }

    @Override
    public void outSideRecord(List<Daily> dailyList) throws Exception {
        Map<String, Object> param = new HashMap<>();
        param.put("dailyList", dailyList);
        List<Daily> list = dailyDao.getDailyList(param);
        if (list.size() == 0) {
            return;
        }
        for (Daily daily : list) {
            daily.setCheckinTime("08:30");
            daily.setCheckoutTime("17:30");
            daily.setIsOutside(OAConstant.isOutside.YES);
            daily.setIsBusTrip(OAConstant.isBusTrip.NO);
            daily.setIsLeave(OAConstant.isLeave.NO);
            daily.setIsForget(OAConstant.isForget.NO);
            daily.setIsComeLate(OAConstant.isComeLate.NO);
            daily.setIsLeaveEarly(OAConstant.isLeaveEarly.NO);
            daily.setAbsenceHour(0f);
            daily.setLateMinute(0);
        }
        param.clear();
        param.put("dailyList", list);
        dailyDao.deleteDaily(param);
        batchAddDaily(list);

    }

    /**
     * 获取休息日集合
     *
     * @return
     */
    private List<String> getPlayDateList() {
        Map<String, Object> param = new HashMap<>();
        param.put("isWeekday", 2);
        return scheduleDateDao.getDateList(param);
    }

    @Override
    public void leaveRecord(List<Daily> dailyList) throws Exception {

        List<String> playDateList = getPlayDateList();
        Daily temp = null;
        List<Daily> newDailyList = new ArrayList<Daily>();
        for (Daily daily : dailyList) {
            if (StringUtil.isBlank(daily.getBeginDate()) || StringUtil.isBlank(daily.getEndDate()) || StringUtil.isBlank(daily.getEmployeeName())) {
                continue;
            }
            //一天内请假场景
            if (daily.getBeginDate().equals(daily.getEndDate())) {
                daily.setIsLeave(OAConstant.isLeave.YES);
                daily.setDailyDate(daily.getBeginDate());
                if (daily.getLeaveDay() != null && daily.getLeaveDay() != 0d) {
                    daily.setLeaveHour((float) (daily.getLeaveDay() * 8));
                }
                daily.setLeaveType(getLeaveType(daily.getLeaveTypeName()));
                newDailyList.add(daily);
            } else {
                //处理跨天请假场景
                List<String> dateList = getBetweenDate(daily.getBeginDate(), daily.getEndDate());

                for (String date : dateList) {
                    if (playDateList.contains(date)) {
                        continue;
                    }
                    temp = new Daily();
                    temp.setEmployeeName(daily.getEmployeeName());
                    temp.setDailyDate(date);
                    temp.setLeaveType(getLeaveType(daily.getLeaveTypeName()));
                    newDailyList.add(temp);
                }
            }
        }

        Map<String, Object> param = new HashMap<>();
        param.put("dailyList", newDailyList);
        List<Daily> list = dailyDao.getDailyList(param);
        if (list.size() == 0) {
            return;
        }
        dealLeaveData(newDailyList, list);

    }

    /**
     * 将请假类型名称转换为请假类型值
     *
     * @param leaveTypeName
     * @return
     */
    private int getLeaveType(String leaveTypeName) {
        int leaveType = 0;
        switch (leaveTypeName) {
            case "调休":
                leaveType = 1;
                break;
            case "事假":
                leaveType = 2;
                break;
            case "病假":
                leaveType = 3;
                break;
            case "年假":
                leaveType = 4;
                break;
            case "其他":
                leaveType = 5;
                break;
            case "产假":
                leaveType = 6;
                break;
            case "婚假":
                leaveType = 7;
                break;
        }
        return leaveType;
    }

    /**
     * 计算请假时间
     *
     * @param newDailyList 解析excel得到的请假数据
     * @param list         存在的考勤记录
     */
    private void dealLeaveData(List<Daily> newDailyList, List<Daily> list) {
        Map<String, Daily> dailyMap = getDailyMap(newDailyList);
        Daily dailyTemp = null;
        for (Daily daily : list) {
            dailyTemp = dailyMap.get(daily.getEmployeeName() + daily.getDailyDate());
            if (dailyTemp == null) {
                continue;
            }
            //没有打卡记录，则是请假8小时
            if (StringUtil.isBlank(daily.getCheckinTime()) && StringUtil.isBlank(daily.getCheckoutTime())) {
                daily.setCheckinTime("08:30");
                daily.setCheckoutTime("17:30");
                daily.setLeaveType(dailyTemp.getLeaveType());
                daily.setLeaveHour(8f);
                daily.setIsLeave(OAConstant.isLeave.YES);
                daily.setOvertimeHour(0f);
                daily.setAbsenceHour(0f);
                daily.setLateMinute(0);
                daily.setIsForget(OAConstant.isForget.NO);
                daily.setIsOvertimeValid(OAConstant.isOvertimeValid.NO);
                daily.setIsBusTrip(OAConstant.isBusTrip.NO);
                daily.setIsOutside(OAConstant.isOutside.NO);
                daily.setIsComeLate(OAConstant.isComeLate.NO);
                daily.setIsLeaveEarly(OAConstant.isLeaveEarly.NO);
            } else {
                //有打卡记录，则将缺勤时长更改为请假时长，缺勤时长和迟到置0
                daily.setLeaveType(dailyTemp.getLeaveType());
                daily.setLeaveHour(daily.getAbsenceHour());
                daily.setIsLeave(OAConstant.isLeave.YES);
                daily.setOvertimeHour(0f);
                daily.setAbsenceHour(0f);
                daily.setLateMinute(0);
                daily.setIsForget(OAConstant.isForget.NO);
                daily.setIsOvertimeValid(OAConstant.isOvertimeValid.NO);
                daily.setIsBusTrip(OAConstant.isBusTrip.NO);
                daily.setIsOutside(OAConstant.isOutside.NO);
                daily.setIsComeLate(OAConstant.isComeLate.NO);
                daily.setIsLeaveEarly(OAConstant.isLeaveEarly.NO);
            }
        }

        Map<String, Object> param = new HashMap<>();
        param.put("dailyList", list);
        dailyDao.deleteDaily(param);
        batchAddDaily(list);
    }


    @Override
    public void busTripRecord(List<Daily> dailyList) throws Exception {
        List<String> playDateList = getPlayDateList();
        Daily temp = null;
        List<Daily> newDailyList = new ArrayList<Daily>();
        for (Daily daily : dailyList) {
            List<String> dateList = getBetweenDate(daily.getBeginDate(), daily.getEndDate());
            for (String date : dateList) {
                if (playDateList.contains(date)) {
                    continue;
                }
                temp = new Daily();
                temp.setEmployeeName(daily.getEmployeeName());
                temp.setDailyDate(date);
                temp.setCheckinTime("08:30");
                temp.setCheckoutTime("17:30");
                temp.setOvertimeHour(0f);
                temp.setAbsenceHour(0f);
                temp.setLateMinute(0);
                temp.setIsForget(OAConstant.isForget.NO);
                temp.setIsOvertimeValid(OAConstant.isOvertimeValid.NO);
                temp.setIsBusTrip(OAConstant.isBusTrip.YES);
                temp.setIsOutside(OAConstant.isOutside.NO);
                temp.setIsLeave(OAConstant.isLeave.NO);
                temp.setIsComeLate(OAConstant.isComeLate.NO);
                temp.setIsLeaveEarly(OAConstant.isLeaveEarly.NO);
                temp.setLeaveHour(0f);
                newDailyList.add(temp);
            }
        }

        Map<String, Object> param = new HashMap<>();
        param.put("dailyList", newDailyList);
        dailyDao.deleteDaily(param);
        batchAddDaily(newDailyList);

    }


    private Map<String, Daily> getDailyMap(List<Daily> dailyList) {
        Map<String, Daily> map = new HashMap<>();
        if (dailyList == null || dailyList.size() == 0) {
            return map;
        }
        for (Daily daily : dailyList) {
            map.put(daily.getEmployeeName() + daily.getDailyDate(), daily);
        }
        return map;
    }

    /**
     * 将日期格式转为2018-08-01
     *
     * @param date 日期，格式为：2018/7/1
     * @return
     */
    private String dealDate(String date) {
        //如果日期是'-'连接，直接返回
        if (date.indexOf("-") > -1) {
            return date;
        }
        String[] arr = date.split("/");
        StringBuffer newDate = new StringBuffer();
        newDate.append(arr[0]);
        if (arr[1].length() == 1) {
            newDate.append("-0").append(arr[1]);
        } else {
            newDate.append("-").append(arr[1]);
        }
        if (arr[2].length() == 1) {
            newDate.append("-0").append(arr[2]);
        } else {
            newDate.append("-").append(arr[2]);
        }
        return newDate.toString();
    }

    private Map<String, Integer> getScheduleMap(String dailyDate, String regex) {
        Map<String, Integer> scheduleMap = new HashMap<>();
        String[] arr = dailyDate.split(regex);
        StringBuffer key = new StringBuffer();
        key.append(arr[0]);
        if (arr[1].length() == 1) {
            key.append("0").append(arr[1]);
        } else {
            key.append(arr[1]);
        }
        Map<String, Object> param = new HashMap<>();
        param.put("scheduleDate", key.toString());
        List<ScheduleDate> scheduleDateList = scheduleDateDao.getScheduleList(param);
        if (scheduleDateList.size() == 0) {
            return scheduleMap;
        }
        for (ScheduleDate scheduleDate : scheduleDateList) {
            scheduleMap.put(scheduleDate.getDate(), scheduleDate.getIsWeekday());
        }
        return scheduleMap;
    }
//
//
//    public static List<Daily> excelToShopIdList(InputStream inputStream) throws IOException {
//        Workbook workbook = WorkbookFactory.create(inputStream);
//            inputStream.close();
//            // 在工作簿获取目标工作表
//            Sheet sheet = workbook.getSheetAt(0);
//            // 获取到最后一行
//            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
//            // 该集合用来储存行对象
//            ArrayList<Daily> detaileds = new ArrayList<>();
//            // 遍历整张表，从第二行开始，第一行的表头不要，循环次数不大于最后一行的值
//            for (int i = 1; i < physicalNumberOfRows; i++) {
//                // 该对象用来储存行数据
//                Daily daily = new Daily();
//                // 获取当前行数据
//                Row row = sheet.getRow(i);
//                // 获取目标单元格的值并存进对象中
//                daily.setWorkTime(row.getCell(0).getStringCellValue());
//                daily.setEmployeeName((row.getCell(1).getStringCellValue()));
//                daily.setDailyDate(row.getCell(2).getStringCellValue());
//                daily.setCheckinTime(row.getCell(3).getStringCellValue());
//                daily.setCheckoutTime((row.getCell(4).getStringCellValue()));
//                daily.setDiffTime((row.getCell(5).getStringCellValue()));
//                daily.setExplainTime(row.getCell(6).getStringCellValue());
//                daily.setRemarks(row.getCell(7).getStringCellValue());
//                // 把对象放到集合里
//                detaileds.add(daily);
//                System.out.println("获取到的当前行数据：" + daily);
//            }
//            return detaileds;
//
//    }


}

六、实体类

mport java.io.Serializable;

/**
 * @Description 考勤记录对象
 * @Author jinjian
 * @Date 2022/04/13
 * @Version 1.0
 **/
public class Daily implements Serializable {
    private static final long serialVersionUID = -7611355236436301485L;
    private Integer id;
    private String workTime;//工作时间
    private String userCode;//员工姓名
    private String employeeName;//员工姓名
    private String dailyDate;//打卡日期
    private String checkinTime;//上班打卡时间
    private String checkoutTime;//下班打卡时间
    private String diffTime;//时间差
    private String explainTime;//考勤说明
    private String remarks;//考勤描述
    private Float overtimeHour;//加班时长
    private Float absenceHour;//缺勤时长
    private Integer lateMinute;//迟到分钟数
    private Integer isForget;//1:忘记打卡，2:没有忘打卡
    private Integer isOvertimeValid;//加班时长是否有效，1:有效,2:无效
    private Integer isBusTrip;//是否出差，1:是,2:否
    private Integer isOutside;//是否外出，1:是,2:否
    private Integer isLeave;//是否请假,1:是，2:否
    private Integer isComeLate;//是否迟到,1:是，2:否
    private Integer isLeaveEarly;//是否早退,1:是，2:否
    private Float leaveHour;//请假时长
    private Double leaveDay;//请假天数
    private Integer leaveType;//请假类型，1：调休，2：事假，3：病假，4：年假，5：其他
    private String leaveTypeName;//请假类型，导入解析使用
    private String beginDate; //开始日期
    private String endDate; //结束日期
    private String signTime;//签卡时间
    private String username;//ERPUSER

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getWorkTime() {
        return workTime;
    }

    public void setWorkTime(String workTime) {
        this.workTime = workTime;
    }

    public String getDiffTime() {
        return diffTime;
    }

    public void setDiffTime(String diffTime) {
        this.diffTime = diffTime;
    }

    public String getExplainTime() {
        return explainTime;
    }

    public void setExplainTime(String explainTime) {
        this.explainTime = explainTime;
    }

    public String getRemarks() {
        return remarks;
    }

    public void setRemarks(String remarks) {
        this.remarks = remarks;
    }

    public String getLeaveTypeName() {
        return leaveTypeName;
    }

    public void setLeaveTypeName(String leaveTypeName) {
        this.leaveTypeName = leaveTypeName;
    }

    public Double getLeaveDay() {
        return leaveDay;
    }

    public void setLeaveDay(Double leaveDay) {
        this.leaveDay = leaveDay;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }

    public String getDailyDate() {
        return dailyDate;
    }

    public void setDailyDate(String dailyDate) {
        this.dailyDate = dailyDate;
    }

    public String getCheckinTime() {
        return checkinTime;
    }

    public void setCheckinTime(String checkinTime) {
        this.checkinTime = checkinTime;
    }

    public String getCheckoutTime() {
        return checkoutTime;
    }

    public void setCheckoutTime(String checkoutTime) {
        this.checkoutTime = checkoutTime;
    }

    public Float getOvertimeHour() {
        return overtimeHour;
    }

    public void setOvertimeHour(Float overtimeHour) {
        this.overtimeHour = overtimeHour;
    }

    public Float getAbsenceHour() {
        return absenceHour;
    }

    public void setAbsenceHour(Float absenceHour) {
        this.absenceHour = absenceHour;
    }

    public Integer getLateMinute() {
        return lateMinute;
    }

    public void setLateMinute(Integer lateMinute) {
        this.lateMinute = lateMinute;
    }

    public Integer getIsForget() {
        return isForget;
    }

    public void setIsForget(Integer isForget) {
        this.isForget = isForget;
    }

    public Integer getIsOvertimeValid() {
        return isOvertimeValid;
    }

    public void setIsOvertimeValid(Integer isOvertimeValid) {
        this.isOvertimeValid = isOvertimeValid;
    }

    public Integer getIsBusTrip() {
        return isBusTrip;
    }

    public void setIsBusTrip(Integer isBusTrip) {
        this.isBusTrip = isBusTrip;
    }

    public Integer getIsOutside() {
        return isOutside;
    }

    public void setIsOutside(Integer isOutside) {
        this.isOutside = isOutside;
    }

    public Integer getIsLeave() {
        return isLeave;
    }

    public void setIsLeave(Integer isLeave) {
        this.isLeave = isLeave;
    }

    public Integer getIsComeLate() {
        return isComeLate;
    }

    public void setIsComeLate(Integer isComeLate) {
        this.isComeLate = isComeLate;
    }

    public Integer getIsLeaveEarly() {
        return isLeaveEarly;
    }

    public void setIsLeaveEarly(Integer isLeaveEarly) {
        this.isLeaveEarly = isLeaveEarly;
    }

    public String getBeginDate() {
        return beginDate;
    }

    public void setBeginDate(String beginDate) {
        this.beginDate = beginDate;
    }

    public String getEndDate() {
        return endDate;
    }

    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }

    public String getSignTime() {
        return signTime;
    }

    public void setSignTime(String signTime) {
        this.signTime = signTime;
    }

    public Float getLeaveHour() {
        return leaveHour;
    }

    public void setLeaveHour(Float leaveHour) {
        this.leaveHour = leaveHour;
    }

    public Integer getLeaveType() {
        return leaveType;
    }

    public void setLeaveType(Integer leaveType) {
        this.leaveType = leaveType;
    }
}