首先日期格式避免转换,接收时直接用文本格式接收:
excel中输入日期默认保存的格式:2021/1/3
package net.dgg.hr.modules.emp.entity; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; import java.io.Serializable; /** * @program: hr-service * @Description: 转正信息 * @Author: zwx * @Date: 2021/7/23 16:29 */ @Data public class CorrectInformationExcel implements Serializable { @ExcelProperty(value = "工号", index = 0) private String workCode; @ExcelProperty(value = "直接上级工号", index = 1) private String directWorkCode; @ExcelProperty(value = "应转正日期", index = 2) private String correctedDate; @ExcelProperty(value = "转正考试日期", index = 3) private String correctExamDate; @ExcelProperty(value = "转正考试时间", index = 4) private String correctExamTime; @ExcelProperty(value = "转正考试成绩", index = 5) private String correctExamGrade; @ExcelProperty(value = "转正考试结果", index = 6) private String correctExamResult; }
//校验应转正日期 String correctedDate = list.get(i).getCorrectedDate(); if (!StringUtils.isEmpty(correctedDate)){ DateUtil.setParams("yyyy/MM/dd","yyyy-MM-dd"); String s = DateUtil.formatExcelDate(correctedDate); if (StringUtils.isEmpty(s)){ throw new Exception("EXCEL表格第"+(i+rowNo)+"行第3列应转正日期格式不正确"); } empBatchParam.setId(id); empBatchParam.setDueTime(s); }
package net.dgg.hr.modules.util; /** * @program: hr-service * @Description: easyExcel日期格式化工具类 * @Author: zwx * @Date: 2021/7/26 14:00 */ import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.time.DateUtils; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; public class DateUtil { static private MyDateFormat myDateFormat; static private String format = "yyyy-MM-dd"; static private String storageFormat = "yyyy-MM-dd"; static private boolean easy = false; public static MyDateFormat getMyDateFormat(String format) { DateUtil.myDateFormat = new MyDateFormat(format); return DateUtil.myDateFormat; } public static void setParams(String format, String storageFormat, boolean easy) { DateUtil.format = format; DateUtil.storageFormat = storageFormat; DateUtil.easy = easy; } public static void setParams(String format, String storageFormat) { DateUtil.format = format; DateUtil.storageFormat = storageFormat; } /** * 格式化Excel时间(yyyy-MM-dd) * * @param str * @return */ public static String formatExcelDate(String str) { //数据的格式 int yearIndex = StringUtils.indexOf(format, "yyyy"); int monthIndex = StringUtils.indexOf(format, "MM"); int dayIndex = StringUtils.indexOf(format, "dd"); String year = format.substring(yearIndex + 4, monthIndex); String month = format.substring(monthIndex + 2, dayIndex); //存储的格式 int storageYearIndex = StringUtils.indexOf(storageFormat, "yyyy"); int storageMonthIndex = StringUtils.indexOf(storageFormat, "MM"); int storageDayIndex = StringUtils.indexOf(storageFormat, "dd"); String storageYear = storageFormat.substring(storageYearIndex + 4, storageMonthIndex); String storageMonth = storageFormat.substring(storageMonthIndex + 2, storageDayIndex); if (StringUtils.isBlank(str)) { return ""; } else if (StringUtils.isNumeric(str)) { /*数字*/ Calendar calendar = new GregorianCalendar(1900, 0, -1); Date gregorianDate = calendar.getTime(); return DateUtil.getMyDateFormat(storageFormat).format(DateUtils.addDays(gregorianDate, Integer.parseInt(str))); } else if (str.matches("^(\\d{4}" + year + "\\d{1,2})$") && easy) { /*yyyy/MM*/ Date date = DateUtil.getMyDateFormat("yyyy" + year + "MM").parse(str); return DateUtil.getMyDateFormat("yyyy" + storageYear + "MM").format(date); } else if (str.matches("^(\\d{4}" + year + "\\d{1,2}" + month + "\\d{1,2})$")) { /*yyyy/MM/dd*/ Date date = DateUtil.getMyDateFormat("yyyy" + year + "MM" + month + "dd").parse(str); return DateUtil.getMyDateFormat("yyyy" + storageYear + "MM" + storageMonth + "dd").format(date); } else { return ""; } } public static String formatExcelDate(String str, boolean easyTemp) { //数据的格式 int yearIndex = StringUtils.indexOf(format, "yyyy"); int monthIndex = StringUtils.indexOf(format, "MM"); int dayIndex = StringUtils.indexOf(format, "dd"); String year = format.substring(yearIndex + 4, monthIndex); String month = format.substring(monthIndex + 2, dayIndex); //存储的格式 int storageYearIndex = StringUtils.indexOf(storageFormat, "yyyy"); int storageMonthIndex = StringUtils.indexOf(storageFormat, "MM"); int storageDayIndex = StringUtils.indexOf(storageFormat, "dd"); String storageYear = storageFormat.substring(storageYearIndex + 4, storageMonthIndex); String storageMonth = storageFormat.substring(storageMonthIndex + 2, storageDayIndex); if (StringUtils.isBlank(str)) { return ""; } else if (StringUtils.isNumeric(str)) { /*数字*/ Calendar calendar = new GregorianCalendar(1900, 0, -1); Date gregorianDate = calendar.getTime(); return DateUtil.getMyDateFormat(storageFormat).format(DateUtils.addDays(gregorianDate, Integer.parseInt(str))); } else if (str.matches("^(\\d{4}" + year + "\\d{1,2})$") && easyTemp) { /*yyyy/MM*/ Date date = DateUtil.getMyDateFormat("yyyy" + year + "MM").parse(str); return DateUtil.getMyDateFormat("yyyy" + storageYear + "MM").format(date); } else if (str.matches("^(\\d{4}" + year + "\\d{1,2}" + month + "\\d{1,2})$")) { /*yyyy/MM/dd*/ Date date = DateUtil.getMyDateFormat("yyyy" + year + "MM" + month + "dd").parse(str); return DateUtil.getMyDateFormat("yyyy" + storageYear + "MM" + storageMonth + "dd").format(date); } else { return ""; } } /** * 自动填充0 * * @param format * @param str * @return */ public static String autoAdd0(String format, String str) { Date date = DateUtil.getMyDateFormat(format).parse(str); return DateUtil.getMyDateFormat(format).format(date); } /*日期内部类*/ public static class MyDateFormat { private SimpleDateFormat simpleDateFormat; public MyDateFormat(String format) { this.simpleDateFormat = new SimpleDateFormat(format); } public String format(Date date) { if (date == null) { return ""; } else { return simpleDateFormat.format(date); } } public Date parse(String str) { if (str == null) { return null; } else { try { return simpleDateFormat.parse(str); } catch (ParseException e) { return null; } } } } }