easyexcel导入LocalDate和LocalDateTime时间格式的恼人问题

easyexcel在导入LocalDate和LocalDateTime格式时,会出现体会导入成功,但是映射后的data根本就没有数据的问题.

1.产生原因 我们进行日期编写时,excel会自动将横杠转成斜杠,导致日期获取不到

1.对于LocalDate的处理

自定义converter,使其适配yyyy/MM/dd格式的日期

package com.xx.control.platform.dto.data.poi.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.apache.poi.ss.usermodel.DateUtil;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;

/**
 * @author hjh
 */
public class LocalDateConverter implements Converter<LocalDate> {

    @Override
    public Class<LocalDate> supportJavaTypeKey() {
        return LocalDate.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if(null==cellData) {
            return null;
        }
        LocalDate result=null;
        if(cellData.getType()==CellDataTypeEnum.NUMBER) {
            if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
                Date date= DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
                        globalConfiguration.getUse1904windowing(), null);
                result =date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            } else {
                Date date=  DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
                        contentProperty.getDateTimeFormatProperty().getUse1904windowing(), null);
                result =date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            }
        }if(cellData.getType()==CellDataTypeEnum.STRING) {
            String value=cellData.getStringValue();
            if(value.contains("-")) {
                try {
                    result= LocalDate.parse(cellData.getStringValue());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            else if(value.contains("/")) {
                try {
                    result= LocalDate.parse(new SimpleDateFormat("yyyy/MM/dd").format( value) );
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    @Override
    public WriteCellData<?> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
    }

}

在接收excel对象中加上converter = LocalDateConverter.class

@ExcelProperty(value = "减员时间",converter = LocalDateConverter.class)
    /*@DateTimeFormat("yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd")*/
    private LocalDate reduceTime;

2.对于LocalDateTime的处理

也需要自定义converter,让他能适配/的格式

package com.xx.control.platform.dto.data.poi.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import org.apache.poi.ss.usermodel.DateUtil;

import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;

/**
 * @author hjh
 */
public class LocalDateTimeConverter implements Converter<LocalDateTime> {

    @Override
    public Class<LocalDateTime> supportJavaTypeKey() {
        return LocalDateTime.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public LocalDateTime convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if(null==cellData) {
            return null;
        }
        LocalDateTime result=null;
        if(cellData.getType()==CellDataTypeEnum.NUMBER) {
            if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
                Date date= DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
                        globalConfiguration.getUse1904windowing(), null);
                result =date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
            } else {
                Date date=  DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
                        contentProperty.getDateTimeFormatProperty().getUse1904windowing(), null);
                result =date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
            }
        }if(cellData.getType()==CellDataTypeEnum.STRING) {
            String value=cellData.getStringValue();
            if(value.contains("-")) {
                try {
                    result=  LocalDateTime.parse(value,DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                   /* result= LocalDateTime.parse(cellData.getStringValue());*/
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            else if(value.contains("/")) {
                try {
                    result= LocalDateTime.parse(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format( value) );
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    @Override
    public WriteCellData<?> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }

}

在导入的实体对象上也要加上converter = LocalDateTimeConverter.class)

 @ExcelProperty(value = "更新时间",converter = LocalDateTimeConverter.class)
   /* @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")*/
    private LocalDateTime renewTime;
    
    @ExcelProperty(value = "建档日期",converter = LocalDateTimeConverter.class)
    /*@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")*/
    private LocalDateTime createTime;

再导入不管什么格式的都能导入成功,如果想统一格式,可以对excel某一列单独设置单元格式