easyExcel导入文本类型的时间校验以及格式转换

首先日期格式避免转换,接收时直接用文本格式接收:

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;
                }
            }
        }
    }
}

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值