注：以某某公司为例，每日签到时间为8点整 每日签退时间为17点30分
规则：公司签到签退时间在OA平台中可以视实际情况调整，当天有请假并通过工作流审批通过为有效，当天因公外出并通过工作流审批通过为有效。
根据考勤机记录的打卡数据，判断当日员工的工作状态，并统计员工共打卡多少次，每一次打卡的时间。
并可以选择某月的数据导出员工考勤表格（包括本月每日签到、签退时间及状态，工作状态，本月共迟到多少次，早退多少次，外出多少次，请假多少次等信息）。
代码中有详细注释！！！
效果如下图：
/**
* 方法说明：Excel导入
* 创建时间：************
* @param request
* @return
*/
public Map<String, Object> importExcel(HttpServletRequest request, HttpServletResponse response) {
try {
TtySession ttySession = TtySessionFactory.getTtySession(request, response);
if(ttySession!=null){
WebUserView userModel=(WebUserView) ttySession.getAttribute(CommonParms.USER_SESSION_KEY);
if(userModel!=null){
String url = request.getRequestURL().toString();
url=url.split("emp")[1];
url = url.substring(1, url.length());
//Map<String,Map<String,String>> urlMap = userModel.getUserUrlMap();
//Map<String, String> inputMap = urlMap.get(url);
request.setCharacterEncoding("utf-8");
Map<String,Object> modelMap = new HashMap<String, Object>();
modelMap.put("result", "-1");
if(url!=null){
String excelName = request.getParameter("newName");
String excelPath1 = getTomcatPath1(request);
String excelPath = excelPath1+excelName;
String excelPath2 = WebUtils.getBasePath(request).replace("emp", "upload")+"/"+excelName;
List<WorkAttenceModel> workAttenceList = importExcel(excelPath);
if(workAttenceList!=null&&workAttenceList.size()>0){
for(WorkAttenceModel wal :workAttenceList){
workAttenceDao.addWorkAttence(wal);
}
if(userModel!=null){
String uploadCode = workAttenceList.get(1).getUploadCode();
String userName = userModel.getUserName();
String workCode = userModel.getPerCode();
UploadRecodeModel uploadModel = new UploadRecodeModel();
uploadModel.setUserName(userName);
uploadModel.setWorkCode(workCode);
uploadModel.setUploadCode(uploadCode);
uploadModel.setUploadUrl(excelPath2);
uploadModel.setCreateDate(DateUtils.getSystemDateAndTime());
if(uploadModel!=null){
uploadRecodeDao.addUploadRecode(uploadModel);
}
modelMap.put("result","1");
}
}else{
modelMap.put("result","2");
}
request.setAttribute("modelMap", modelMap);
String[] excludes = {};
JSONArray jsonObject = JSONArray.fromObject(modelMap, getJsonConfig(excludes));
String sRet=StrUtils.convertToJson(jsonObject.toString());
WebUtils.getPrintWriter(response, "UTF-8").print(sRet);
return modelMap;
}
}
}
} catch (Exception e) {
e.printStackTrace();
Logs.logger_out.error("WorkAttenceServerImpl-->uploadExcel();" + e.getLocalizedMessage() + "-:-" + e.getStackTrace() + "-:-" + e.getMessage());
}
return null;
}
/**
* 方法说明：考勤模块excel导入方法
* 创建时间：******************
* @param excelPath 文件地址
* @return
*/
@SuppressWarnings({ "unused"})//告诉编译器忽略指定的警告，不用在编译完成后出现警告信息。
public List<WorkAttenceModel> importExcel(String excelPath) {
try {
Map<String,PersonnelModel> perMap = null;
Map<String,WorkAttenceModel> attenceMap = null;
Map<String,OutWorkModel> outWorkMap = null;
Map<String,LeaveModel> askMap = null;
List<PersonnelModel> perList = personnelDao.getAllAttnPerList();
//查询人事表集合是否为空
if(perList!=null&&perList.size()>0){
//读取excel数据
File file = new File(excelPath);
Workbook book = Workbook.getWorkbook(file);
Sheet[] sheets = book.getSheets();
List<WorkAttenceModel> tempList = new ArrayList<WorkAttenceModel>();
List<ExcelModel> excelList = new ArrayList<ExcelModel>();
List<String> dateList = new ArrayList<String>();
for (Sheet sheet : sheets) {
// 获得第一个工作表对象
if (sheet!= null) {
int columnum = sheet.getColumns();// 得到列数
int rownum = sheet.getRows();// 得到行数
if(columnum==6){//判断excel格式是否为6列
for (int i = 1; i < rownum; i++) {
ExcelModel excel = new ExcelModel();
List<String> exList = new ArrayList<String>();
for (int j = 0; j < columnum; j++) {
// 得到第一列第一行的单元格
Cell cell1 = sheet.getCell(j, i);
String result = cell1.getContents();
result = result.trim();
exList.add(result);
}
if(exList.get(0)!=null&&!"".equals(exList.get(0))){
for (char c : exList.get(0).toCharArray()) {
if (isChinese(c)){
return null;//有一个中文字符就返回null
}
}
if(exList.get(0).matches(".*[a-zA-z].*")){
return null;//包含英文字母就返回null
}
excel.setWorkCode(exList.get(0));
}else{
return null;
}
if(exList.get(3)!=null&&!"".equals(exList.get(3))){
for (char c : exList.get(3).toCharArray()) {
if (isChinese(c)){
return null;//有一个中文字符就返回null
}
}
if(exList.get(3).matches(".*[a-zA-z].*")){
return null;//包含英文字母就返回null
}
excel.setDate(exList.get(3).replace("/", "-"));
}else{
return null;
}
for (char c : exList.get(4).toCharArray()) {
if (isChinese(c)){
return null;//有一个中文字符就返回null
}
}
if(exList.get(4).matches(".*[a-zA-z].*")){
return null;//包含英文字母就返回null
}
excel.setSignTimes(exList.get(4));
excelList.add(excel);
if(exList.get(3).indexOf("/")!=-1){
dateList.add(exList.get(3).replace("/", "-").split("-")[0]+"-"+exList.get(3).replace("/", "-").split("-")[1]);
}else{
return null;
}
}
}else{
return null;
}
}
}
String startDate = "";
String endDate = "";
//判断excel文件中内容是否为空
if(excelList!=null&&excelList.size()>0){
//得到数据的start与end时间
if(dateList!=null&&dateList.size()>0){
Collections.sort(dateList);
if(dateList.size()==1){
startDate = DateUtils.getFirstDayOfMonth(dateList.get(0));
endDate = DateUtils.getLastDayOfMonth(dateList.get(0));
}else{
startDate = DateUtils.getFirstDayOfMonth(dateList.get(0));
endDate = DateUtils.getLastDayOfMonth(dateList.get(dateList.size()-1));
}
}
//获取全部在职人员Map集合 key为工号 value为Model
perMap = new HashMap<String,PersonnelModel>();
for(PersonnelModel pl :perList){
if(pl!=null){
String key = pl.getPerCode();
perMap.put(key, pl);
}
}
if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
//获取全部考勤记录Map集合 key为工号+日期 value为Model
List<WorkAttenceModel> attList = workAttenceDao.getAllWorkAttenceList(startDate,endDate);
if(attList!=null&&attList.size()>0){
attenceMap = new HashMap<String,WorkAttenceModel>();
for(WorkAttenceModel wl :attList){
if(wl!=null){
String key = wl.getWorkCode()+wl.getAttenceDate();
attenceMap.put(key, wl);
}
}
}
//获取全部外出记录Map集合 key为姓名+日期 value为Model 只获取审批结果为同意的外出数据
List<OutWorkModel> outWorkList = outWorkDao.getAllOutWorkList(startDate,endDate);
if(outWorkList!=null&&outWorkList.size()>0){
outWorkMap = new HashMap<String,OutWorkModel>();
for(OutWorkModel owl :outWorkList){
if(owl!=null){
String key = owl.getOutPerson()+owl.getInsertDate();
outWorkMap.put(key, owl);
}
}
}
//获取全部请假记录Map集合 key为姓名+日期 value为Model
List<LeaveModel> askList = leaveDao.getAllAskLeaveList(startDate, endDate);
if(askList!=null&&askList.size()>0){
askMap = new HashMap<String,LeaveModel>();
for(LeaveModel al :askList){
if(al!=null){
String key = al.getApplicant()+al.getStartTime();
askMap.put(key, al);
}
}
}
}
String oaSignTime = "";
String oaSignOutTime = "";
//获取公司OA平台参数中的签到时间与签退时间
ParameterModel parModel1 = parameterDao.getParameterModelByKey(CommonParms.WORK_SIGN_TIME);
if(parModel1!=null){
oaSignTime = parModel1.getPayValue();
if(oaSignTime==null||"".equals(oaSignTime)||oaSignTime.indexOf("-")==-1){
return null;
}
}else{
return null;
}
ParameterModel parModel2 = parameterDao.getParameterModelByKey(CommonParms.WORK_SIGN_OUT_TIME);
if(parModel2!=null){
oaSignOutTime = parModel2.getPayValue();
if(oaSignOutTime==null||"".equals(oaSignOutTime)||oaSignOutTime.indexOf("-")==-1){
return null;
}
}else{
return null;
}
//生成上传码
String uploadCode = OrderCodeProducer.generate8AnnexCode();
//循环excelList内的内容
for(ExcelModel el :excelList){
//得到工号
String workCode= "";
if(el!=null){
workCode = el.getWorkCode();
if(workCode!=null&&!"".equals(workCode)){
if(workCode.length()==1){
workCode = "A000"+workCode;
}else if(workCode.length()==2){
workCode = "A00"+workCode;
}else if(workCode.length()==3){
workCode = "A0"+workCode;
}
}else{
return null;
}
}else{
return null;
}
PersonnelModel perModel = null;
if(workCode!=null&&!"".equals(workCode)){
perModel = perMap.get(workCode);
}
//判断是否为公司员工
if(perModel!=null){
WorkAttenceModel workAttenceModel=null;
//获取名字与日期
String userName = perModel.getPerName();
String attenceDate = el.getDate();
String [] attenceDateArray = null;
if(attenceDate!=null&&!"".equals(attenceDate)&&attenceDate.indexOf("-")!=-1){
attenceDateArray = attenceDate.split("-");
}
String yueDate = "";
String riDate = "";
if(attenceDateArray!=null&&attenceDateArray.length>2){
yueDate = attenceDateArray[1];
riDate = attenceDateArray[2];
}
if(yueDate!=null&&!"".equals(yueDate)&&riDate!=null&&!"".equals(riDate)){
if(yueDate.length()==1){
yueDate = "0"+yueDate;
}
if(riDate.length()==1){
riDate = "0"+riDate;
}
}
attenceDate = attenceDateArray[0]+"-"+yueDate+"-"+riDate;
//考勤key
String attenceKey = workCode+attenceDate;
if(attenceMap!=null&&attenceKey!=null&&!"".equals(attenceKey)){
workAttenceModel = attenceMap.get(attenceKey);
}
//判断是否为重复数据
if(workAttenceModel==null){
WorkAttenceModel attenceModel=new WorkAttenceModel();
String clockTime = el.getSignTimes();
String signTime = "";
String signStatus = "";
String signOutTime = "";
String signOutStatus = "";
int clockNum = 0;
//判断打卡时间是否为空
if(!"".equals(clockTime)){
String [] clockTime1 = clockTime.split(" ");
clockNum = clockTime1.length;
if(clockNum==1){
signTime = clockTime1[0];
String signTimeShi = "";
String signTimeFen = "";
if(signTime!=null&&!"".equals(signTime)&&signTime.indexOf(":")!=-1){
signTimeShi = signTime.split(":")[0];
signTimeFen = signTime.split(":")[1];
}
String oaSignTimeShi = oaSignTime.split("-")[0];
String oaSignOutTimeShi = oaSignOutTime.split("-")[0];
boolean bSignstatu = false;
boolean bSignShi = signTimeShi.matches("[0-9]+");
boolean bOaSignShi = oaSignOutTimeShi.matches("[0-9]+");
if(bSignShi&&bOaSignShi){
if(Integer.parseInt(signTimeShi)<Integer.parseInt(oaSignOutTimeShi)){
bSignstatu = false;
}else{
bSignstatu = true;
}
}else{
return null;
}
boolean bSignFen = signTimeFen.matches("[0-9]+");
if(bSignFen){
if(signTimeFen.substring(0, 1)=="0"){
signTimeFen = signTimeFen.substring(1);
}
}else{
return null;
}
String oaSignTimeFen = oaSignTime.split("-")[1];
boolean bSignStatus = false;
if(signTimeShi.substring(0, 1)=="0"){
signTimeShi = signTimeShi.substring(1);
}
if(Integer.parseInt(signTimeShi)<Integer.parseInt(oaSignTimeShi.substring(1))){
bSignStatus = true;
}else if(Integer.parseInt(signTimeShi)==Integer.parseInt(oaSignTimeShi.substring(1))){
if(Integer.parseInt(signTimeFen)<Integer.parseInt(oaSignTimeFen)){
bSignStatus = true;
}else{
bSignStatus = false;
}
}else{
bSignStatus = false;
}
String keys = userName+attenceDate;
OutWorkModel outModel = null;
if(outWorkMap!=null){
if(keys!=null&&!"".equals(keys)){
outModel = outWorkMap.get(keys);
}
}
LeaveModel askModel = null;
if(askMap!=null){
if(keys!=null&&!"".equals(keys)){
askModel = askMap.get(keys);
}
}
//只有一次打卡时间在10点之前
if(Integer.parseInt(signTimeShi)<10){
//判断打卡时间的时与oa平台参数签到时间时的对比前后
if(bSignStatus){//签到时间正常
//判断是否有外出记录 给签退时间状态赋值
if(outModel!=null){
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("0");
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("2");
attenceModel.setWorkStatus("0");
}else{
//判断是否有请假记录
if(askModel!=null){
String applyId = askModel.getId()+"";
String applyType = "1";
ApprovalModel appModel = approvalDao.getApprovalModel1(applyId, applyType);
if(appModel!=null){
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("0");
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("3");
attenceModel.setWorkStatus("0");
}else{
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("0");
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("1");
}
}else{
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("0");
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("1");
}
}
}else{//签到时间迟到
//判断是否有外出记录 赋值签退时间与签退状态
if(outModel!=null){
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("2");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("1");
}else{
//判断是否有请假记录
if(askModel!=null){
String applyId = askModel.getId()+"";
String applyType = "1";
ApprovalModel appModel = approvalDao.getApprovalModel1(applyId, applyType);
if(appModel!=null){
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("3");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("1");
}else{
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("1");
}
}else{
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("1");
}
}
}
}else if(Integer.parseInt(signTimeShi)>=16){//只有一次打卡记录在16点之后
//判断打卡时间的时与oa平台参数签退时间时的对比前后
if(bSignstatu){//签退时间正常
//判断是否有外出记录 为签到时间与状态赋值
if(outModel!=null){
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("0");
attenceModel.setSignTime("");
attenceModel.setSignStatus("2");
attenceModel.setWorkStatus("0");
}else{
//判断是否有请假记录
if(askModel!=null){
String applyId = askModel.getId()+"";
String applyType = "1";
ApprovalModel appModel = approvalDao.getApprovalModel1(applyId, applyType);
if(appModel!=null){
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("0");
attenceModel.setSignTime("");
attenceModel.setSignStatus("3");
attenceModel.setWorkStatus("0");
}else{
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("0");
attenceModel.setSignTime("");
attenceModel.setSignStatus("4");
attenceModel.setWorkStatus("1");
}
}else{
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("0");
attenceModel.setSignTime("");
attenceModel.setSignStatus("4");
attenceModel.setWorkStatus("1");
}
}
}else{//签退状态为早退
//判断是否有外出记录
if(outModel!=null){
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("1");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime("");
attenceModel.setSignStatus("2");
}else{
//判断是否有请假记录
if(askModel!=null){
String applyId = askModel.getId()+"";
String applyType = "1";
ApprovalModel appModel = approvalDao.getApprovalModel1(applyId, applyType);
if(appModel!=null){
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("1");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime("");
attenceModel.setSignStatus("3");
}else{
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("1");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime("");
attenceModel.setSignStatus("4");
}
}else{
attenceModel.setSignOutTime(signTime);
attenceModel.setSignOutStatus("1");
attenceModel.setWorkStatus("1");
attenceModel.setSignTime("");
attenceModel.setSignStatus("4");
}
}
}
}else{//打卡时间在10点与16点之间
//判断是否有外出记录
if(outModel!=null){
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("2");
attenceModel.setSignOutStatus("2");
attenceModel.setWorkStatus("0");
}else{
//判断是否有请假记录
if(askModel!=null){
String applyId = askModel.getId()+"";
String applyType = "1";
ApprovalModel appModel = approvalDao.getApprovalModel1(applyId, applyType);
if(appModel!=null){
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("3");
attenceModel.setSignOutStatus("3");
attenceModel.setWorkStatus("0");
}else{
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("4");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("2");
}
}else{
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("4");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("2");
}
}
}
}else if(clockNum>1){
if(clockTime1!=null&&clockTime1.length>1){
signTime = clockTime1[0];
signOutTime = clockTime1[clockNum-1];
}
String signTimeShi = "";
String signTimeFen = "";
String signOutTimeShi = "";
if(signTime.indexOf(":")!=-1&&signOutTime.indexOf(":")!=-1){
signTimeShi = signTime.split(":")[0];
signTimeFen = signTime.split(":")[1];
signOutTimeShi = signOutTime.split(":")[0];
if(signTimeShi==null||"".equals(signTimeShi)||signTimeFen==null||"".equals(signTimeFen)||signOutTimeShi==null||"".equals(signOutTimeShi)){
return null;
}
}else{
return null;
}
String oaSignTimeShi = oaSignTime.split("-")[0];
String oaSignOutTimeShi = oaSignOutTime.split("-")[0];
boolean bSignShi = signTimeShi.matches("[0-9]+");
boolean bSignFen = signTimeFen.matches("[0-9]+");
boolean bOutShi = signOutTimeShi.matches("[0-9]+");
boolean bOaSignShi = oaSignTimeShi.matches("[0-9]+");
boolean bOaOutShi = oaSignOutTimeShi.matches("[0-9]+");
if(!bSignShi||!bSignFen||!bOutShi||!bOaSignShi||!bOaOutShi){
return null;
}
if(signOutTimeShi.substring(0, 1)=="0"){
signOutTimeShi = signOutTimeShi.substring(1);
}
boolean bSignState = false;
if(Integer.parseInt(signOutTimeShi)<Integer.parseInt(oaSignOutTimeShi)){
bSignState = false;
}else{
bSignState = true;
}
if(signTimeFen.substring(0, 1)=="0"){
signTimeFen = signTimeFen.substring(1);
}
String oaSignTimeFen = oaSignTime.split("-")[1];
boolean bSignStatus = false;
if(signTimeShi.substring(0, 1)=="0"){
signTimeShi = signTimeShi.substring(1);
}
if(Integer.parseInt(signTimeShi)>Integer.parseInt(oaSignTimeShi.substring(1))){
bSignStatus = false;
}
if(Integer.parseInt(signTimeShi)<Integer.parseInt(oaSignTimeShi.substring(1))){
bSignStatus = true;
}else if(Integer.parseInt(signTimeShi)==Integer.parseInt(oaSignTimeShi.substring(1))){
if(Integer.parseInt(signTimeFen)<Integer.parseInt(oaSignTimeFen)){
bSignStatus = true;
}else{
bSignStatus = false;
}
}
String keys = userName+attenceDate;
OutWorkModel outModel = null;
if(outWorkMap!=null){
if(keys!=null&&!"".equals(keys)){
outModel = outWorkMap.get(keys);
}
}
LeaveModel askModel = null;
if(askMap!=null){
if(keys!=null&&!"".equals(keys)){
askModel = askMap.get(keys);
}
}
String workStatus1 = "";
String workStatus2 = "";
if(Integer.parseInt(signTimeShi)<10){//签到时间在10点之前
if(bSignStatus){
attenceModel.setSignStatus("0");
attenceModel.setSignTime(signTime);
workStatus1 = "0";
}else{
if(outModel!=null){
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("2");
workStatus1 = "0";
}else{
if(askModel!=null){
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("3");
workStatus1 = "0";
}else{
attenceModel.setSignTime(signTime);
attenceModel.setSignStatus("1");
workStatus1 = "1";
}
}
}
}else{//签到时间在10点之后
attenceModel.setSignTime("");
attenceModel.setSignStatus("4");
workStatus1 = "-";
}
//签退时间在16点之后
if(Integer.parseInt(signOutTimeShi)>=16){
if(bSignState){
attenceModel.setSignOutTime(signOutTime);
attenceModel.setSignOutStatus("0");
workStatus2 = "0";
}else{
if(outModel!=null){
attenceModel.setSignOutTime(signOutTime);
attenceModel.setSignOutStatus("2");
workStatus2 = "0";
}else{
if(askModel!=null){
attenceModel.setSignOutTime(signOutTime);
attenceModel.setSignOutStatus("3");
workStatus2 = "0";
}else{
attenceModel.setSignOutTime(signOutTime);
attenceModel.setSignOutStatus("1");
workStatus2 = "1";
}
}
}
}else{
attenceModel.setSignOutTime("");
attenceModel.setSignOutStatus("4");
workStatus2 = "-";
}
if("0".equals(workStatus1)&&"0".equals(workStatus2)){
attenceModel.setWorkStatus("0");
}else{
attenceModel.setWorkStatus("1");
}
if("-".equals(workStatus1)&&"-".equals(workStatus2)){
attenceModel.setWorkStatus("2");
}
}
}else if("".equals(clockTime)){
String keys = userName+attenceDate;
OutWorkModel outModel = null;
if(outWorkMap!=null){
if(keys!=null&&!"".equals(keys)){
outModel = outWorkMap.get(keys);
}
}
//判断是否有外出记录
if(outModel!=null){
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("2");
attenceModel.setSignOutStatus("2");
attenceModel.setWorkStatus("0");
}else{
//判断是否有请假记录
LeaveModel askModel = null;
if(askMap!=null){
askModel= askMap.get(keys);
}
if(askModel!=null){
String applyId = askModel.getId()+"";
String applyType = "1";
ApprovalModel appModel = approvalDao.getApprovalModel1(applyId, applyType);
if(appModel!=null){
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("3");
attenceModel.setSignOutStatus("3");
attenceModel.setWorkStatus("0");
}else{
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("4");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("2");
}
}else{
attenceModel.setSignTime("");
attenceModel.setSignOutTime("");
attenceModel.setSignStatus("4");
attenceModel.setSignOutStatus("4");
attenceModel.setWorkStatus("2");
}
}
}
attenceModel.setAttenceUserName(userName);
attenceModel.setWorkCode(workCode);
attenceModel.setAttenceDate(attenceDate);
attenceModel.setClockNum(clockNum+"");
attenceModel.setClockTime(clockTime);
attenceModel.setDept(perModel.getDepName());
attenceModel.setAttenceStatus(perModel.getAttenceStatus());
attenceModel.setUploadCode(uploadCode);
tempList.add(attenceModel);
}else{
continue;
}
}else{
continue;
}
}
}else{
return null;
}
book.close();
file = null;
return tempList;
}else{
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 方法说明：Excel考勤报表导出
* 创建时间：********************8
* @param request
* @return
*/
public Map<String, Object> exportExcel(HttpServletRequest request, HttpServletResponse response) {
try {
TtySession ttySession = TtySessionFactory.getTtySession(request, response);
if(ttySession!=null){
WebUserView userModel=(WebUserView) ttySession.getAttribute(CommonParms.USER_SESSION_KEY);
if(userModel!=null){
String url = request.getRequestURL().toString();
url=url.split("emp")[1];
url = url.substring(1, url.length());
Map<String,Map<String,String>> urlMap = userModel.getUserUrlMap();
Map<String, String> inputMap = urlMap.get(url);
request.setCharacterEncoding("utf-8");
Map<String,Object> modelMap = new HashMap<String, Object>();
modelMap.put("result", "1");
//inputMap!=null&&inputMap.size()>0
if(userModel!=null){
String creatTimeStart = "";//开始时间
String creatTimeEnd = "";//结束时间
String selectDate = request.getParameter("selectDate");//获取导出考勤数据的月份
String sheetName= "";
String filePath = getTomcatPath(request);
List<WorkAttenceModel> attList = null;
String title = "";
List<PersonnelModel> perList = personnelDao.getAllAttnPerList();
//判断人事集合是否为空
if(perList!=null&&perList.size()>0){
//取前台获取月的day日期第一天及最后一天
if(selectDate!=null&&!"".equals(selectDate)){
if(selectDate.split("-")[1].substring(0, 1).equals("0")){
sheetName = "hgkj"+selectDate.split("-")[1].substring(1, 2)+"MonthAttenceCount.xls";
title = "某某公司"+selectDate.split("-")[1].substring(1, 2)+"月份考勤统计报表";
String path = WebUtils.getBasePath(request).replace("emp", "upload")+"/"+sheetName;
modelMap.put("path", path);
}else{
sheetName = "hgkj"+selectDate.split("-")[1]+"MonthAttenceCount.xls";
title = "某某公司"+selectDate.split("-")[1]+"月份考勤统计报表";
String path = WebUtils.getBasePath(request).replace("emp", "upload")+"/"+sheetName;
modelMap.put("path", path);
}
creatTimeStart = DateUtils.getFirstDayOfMonth(selectDate);
creatTimeEnd = DateUtils.getLastDayOfMonth(selectDate);
}
attList = workAttenceDao.getWorkAttenceListByDate("", "", creatTimeStart, creatTimeEnd);
}else{
return null;
}
filePath = filePath + sheetName;
// 创建成成excel工具类
WritableWorkbook wwb;
// 获取输出流
OutputStream os = new FileOutputStream(filePath);
wwb = Workbook.createWorkbook(os);
// 生成excel文件wwb.createSheet(excel文件名, 第几页);
WritableSheet sheet = wwb.createSheet(sheetName, 0);
// Label是单元格内容的Model
Label label = null;
// jxl 是专门操作excel的工具类
jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 16);
// 生成一种字体
WritableCellFormat font = new WritableCellFormat(wfont);
//字体加粗
WritableFont font1 = new WritableFont(WritableFont.ARIAL,16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
WritableCellFormat font2 = new WritableCellFormat(wfont);//加粗
font2.setFont(font1);
// 字体水平居中
font.setAlignment(jxl.format.Alignment.CENTRE);
font2.setAlignment(jxl.format.Alignment.CENTRE);
// 字体垂直居中
font.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
font2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// 自动换行
font.setWrap(true);
// sheet操作列的属性
// 合并单元格 sheet.mergeCells(第几列，第几格，到第几列，到第几格)
sheet.mergeCells(0, 0, 11, 0);
// 单元格行高
sheet.setRowView(0, 750);
sheet.setColumnView(0, 10);
sheet.setColumnView(1, (int) 8.5);
sheet.setColumnView(2, (int) 8.5);
sheet.setColumnView(3, (int) 8.5);
sheet.setColumnView(4, (int) 8.5);
sheet.setColumnView(5, (int) 8.5);
sheet.setColumnView(6, (int) 8.5);
sheet.setColumnView(7, (int) 8.5);
sheet.setColumnView(8, (int) 8.5);
sheet.setColumnView(9, (int) 8.5);
sheet.setColumnView(10, (int) 8.5);
sheet.setColumnView(11, (int) 8.5);
// 创建label，给其内容
label = new Label(0, 0, title, font2);
// 写进excel
sheet.addCell(label);
int perNum = -2;
if(attList!=null&&attList.size()>0){
for(PersonnelModel pl :perList){
Map<String, WorkAttenceModel> attMap = new HashMap<String, WorkAttenceModel>();
String workCode = "";
perNum++;
if(pl!=null&&!"A001".equals(pl.getDepartment())){
workCode = pl.getPerCode();
}
if(workCode!=null&&!"".equals(workCode)){
for(WorkAttenceModel wl :attList){
String attWorkCode = "";
if(wl!=null){
attWorkCode = wl.getWorkCode();
}
if(attWorkCode!=null&&!"".equals(attWorkCode)){
if(workCode.equals(attWorkCode)){
attMap.put(wl.getAttenceDate(), wl);
}
}else{
continue;
}
}
}
if(attMap!=null&&attMap.size()>0){
wfont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 9);
font = new WritableCellFormat(wfont);
font.setAlignment(jxl.format.Alignment.CENTRE);
font.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
font.setWrap(true);
int lateSum = 0;
int earlySum = 0;
int askSum = 0;
String userName = "";
String userDept = "";
for(int i=1;i<=attMap.size();i++){
String key = "";
WorkAttenceModel waModel = null;
if(i<10){
key = selectDate+"-0"+i+"";
}else{
key = selectDate+"-"+i+"";
}
if(key!=null&&!"".equals(key)){
waModel = attMap.get(key);
String xingqi = DateUtils.getWeekDayByDate(key)+"";
if(xingqi.equals("1")){
xingqi = "周日";
}else if(xingqi.equals("2")){
xingqi = "周一";
}else if(xingqi.equals("3")){
xingqi = "周二";
}else if(xingqi.equals("4")){
xingqi = "周三";
}else if(xingqi.equals("5")){
xingqi = "周四";
}else if(xingqi.equals("6")){
xingqi = "周五";
}else if(xingqi.equals("7")){
xingqi = "周六";
}
String neirong1 =i+"日"+"\r\n"+"("+xingqi+")";
WritableFont header = new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
WritableCellFormat heaerline = new WritableCellFormat(header);
//文字垂直居中对齐
heaerline.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//文字水平居中对齐
heaerline.setAlignment(jxl.format.Alignment.CENTRE);
heaerline.setWrap(true);
heaerline.setBackground(Colour.YELLOW);
WritableFont header1 = new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
WritableCellFormat heaerline1 = new WritableCellFormat(header1);
//文字垂直居中对齐
heaerline1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//文字水平居中对齐
heaerline1.setAlignment(jxl.format.Alignment.CENTRE);
heaerline1.setWrap(true);
heaerline1.setBackground(Colour.YELLOW);
if(i<12){
if(xingqi.equals("周六")||xingqi.equals("周日")){
label = new Label(i, (perNum-2)*6+1, neirong1, heaerline);
sheet.addCell(label);
}else{
label = new Label(i, (perNum-2)*6+1, neirong1, heaerline1);
sheet.addCell(label);
}
}else if(i>=12&&i<23){
if(xingqi.equals("周六")||xingqi.equals("周日")){
label = new Label(i-11, (perNum-2)*6+3, neirong1, heaerline);
sheet.addCell(label);
}else{
label = new Label(i-11, (perNum-2)*6+3, neirong1, heaerline1);
sheet.addCell(label);
}
}else if(i>=23&&i<=attMap.size()){
if(xingqi.equals("周六")||xingqi.equals("周日")){
label = new Label(i-22, (perNum-2)*6+5, neirong1, heaerline);
sheet.addCell(label);
}else{
label = new Label(i-22, (perNum-2)*6+5, neirong1, heaerline1);
sheet.addCell(label);
}
}
if(waModel!=null){
userName = waModel.getAttenceUserName();
userDept = waModel.getDept();
String signDetails = "";
String signOutDetails = "";
String details = "";
String signTime = waModel.getSignTime();
String signOutTime = waModel.getSignOutTime();
String signStatus = waModel.getSignStatus();
String signOutStatus = waModel.getSignOutStatus();
if(signStatus.equals("0")){
signDetails = signTime;
}else if(signStatus.equals("1")){
signDetails = signTime;
lateSum++;
}else if(signStatus.equals("2")){
signDetails = "外出";
}else if(signStatus.equals("3")){
signDetails = "请假";
}else if(signStatus.equals("4")){
signDetails = "未打卡";
}
if(signOutStatus.equals("0")){
signOutDetails = signOutTime;
}else if(signOutStatus.equals("1")){
signOutDetails = signOutTime;
earlySum++;
}else if(signOutStatus.equals("2")){
signOutDetails = "外出";
}else if(signOutStatus.equals("3")){
signOutDetails = "请假";
}else if(signOutStatus.equals("4")){
signOutDetails = "未打卡";
}
if(signOutDetails.equals("请假")||signDetails.equals("请假")){
askSum++;
}
//工作状态异常
if(signOutStatus.equals("1")||signOutStatus.equals("4")||signStatus.equals("1")||signStatus.equals("4")){
WritableFont header3 = new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
WritableCellFormat heaerline3 = new WritableCellFormat(header3);
//文字垂直居中对齐
heaerline3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//文字水平居中对齐
heaerline3.setAlignment(jxl.format.Alignment.CENTRE);
heaerline3.setWrap(true);
if(signOutStatus.equals("4")&&signStatus.equals("4")){
if(i<12){
details = "未打卡";
label = new Label(i, (perNum-2)*6+2, details, heaerline3);
sheet.addCell(label);
}else if(i>=12&&i<23){
details = "未打卡";
label = new Label(i-11, (perNum-2)*6+4, details, heaerline3);
sheet.addCell(label);
}else if(i>=23&&i<=attMap.size()){
details = "未打卡";
label = new Label(i-22, (perNum-2)*6+6, details, heaerline3);
sheet.addCell(label);
}
}else{
if(i<12){
details = signDetails+"\r\n" + signOutDetails;
label = new Label(i, (perNum-2)*6+2, details, heaerline3);
sheet.addCell(label);
}else if(i>=12&&i<23){
details = signDetails+"\r\n" + signOutDetails;
label = new Label(i-11, (perNum-2)*6+4, details, heaerline3);
sheet.addCell(label);
}else if(i>=23&&i<=attMap.size()){
details = signDetails+"\r\n" + signOutDetails;
label = new Label(i-22, (perNum-2)*6+6, details, heaerline3);
sheet.addCell(label);
}
}
}else{//工作状态正常
if(signOutStatus.equals("2")&&signStatus.equals("2")){
if(i<12){
details = "全天外出";
label = new Label(i, (perNum-2)*6+2, details, font);
sheet.addCell(label);
}else if(i>=12&&i<23){
details = "全天外出";
label = new Label(i-11, (perNum-2)*6+4, details, font);
sheet.addCell(label);
}else if(i>=23&&i<=attMap.size()){
details = "全天外出";
label = new Label(i-22, (perNum-2)*6+6, details, font);
sheet.addCell(label);
}
}else if(signOutStatus.equals("3")&&signStatus.equals("3")){
WritableFont header4 = new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLUE);
WritableCellFormat heaerline4 = new WritableCellFormat(header4);
//文字垂直居中对齐
heaerline4.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//文字水平居中对齐
heaerline4.setAlignment(jxl.format.Alignment.CENTRE);
heaerline4.setWrap(true);
if(i<12){
details = "全天请假";
label = new Label(i, (perNum-2)*6+2, details, heaerline4);
sheet.addCell(label);
}else if(i>=12&&i<23){
details = "全天请假";
label = new Label(i-11, (perNum-2)*6+4, details, heaerline4);
sheet.addCell(label);
}else if(i>=23&&i<=attMap.size()){
details = "全天请假";
label = new Label(i-22, (perNum-2)*6+6, details, heaerline4);
sheet.addCell(label);
}
}else{
if(i<12){
details = signDetails+"\r\n" + signOutDetails;
label = new Label(i, (perNum-2)*6+2, details, font);
sheet.addCell(label);
}else if(i>=12&&i<23){
details = signDetails+"\r\n" + signOutDetails;
label = new Label(i-11, (perNum-2)*6+4, details, font);
sheet.addCell(label);
}else if(i>=23&&i<=attMap.size()){
details = signDetails+"\r\n" + signOutDetails;
label = new Label(i-22, (perNum-2)*6+6, details, font);
sheet.addCell(label);
}
}
}
}else{
continue;
}
}
}
String title1 = userName+"\r\n"+userDept+"\r\n"+"\r\n"+"迟到:"+lateSum+"\r\n"+"早退:"+earlySum+"\r\n"+"请假:"+askSum;
sheet.mergeCells(0, (perNum-2)*6+1, 0, (perNum-1)*6);
label = new Label(0, (perNum-2)*6+1,title1, font);
sheet.addCell(label);
}else{
continue;
}
}
}
SheetSettings setting = sheet.getSettings();
// 页边距start
setting.setTopMargin(0.0);
setting.setLeftMargin(0.5);
setting.setRightMargin(0.0);
setting.setBottomMargin(0.0);
// 页边距end
// 写入数据
wwb.write();
// 关闭文件
wwb.close();
String[] excludes = {};
JSONArray jsonObject = JSONArray.fromObject(modelMap, getJsonConfig(excludes));
String sRet=StrUtils.convertToJson(jsonObject.toString());
WebUtils.getPrintWriter(response, "UTF-8").print(sRet);
return modelMap;
}
}
}
} catch (Exception e) {
e.printStackTrace();
Logs.logger_out.error("WorkAttenceServerImpl-->exportExcel();" + e.getLocalizedMessage() + "-:-" + e.getStackTrace() + "-:-" + e.getMessage());
}
return null;
}
