springboot easyexcel导入/导出excel自定义时间格式

        当使用java的easyExcel进行excel导入和导出时，如何接收表格中的各种自定义时间格式字符串转成LocalDateTime及导出时如何将LocalDateTime转为各种时间格式字符串。

一、 新建自定义时间格式转换类

        

package com.logwing.wyadmin.framework.excel;

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.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.temporal.ChronoField;
import java.util.Date;

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) {
        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("-")) {
                DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                        .appendPattern("yyyy-MM-dd[[' 'HH][:mm][:ss]")
                        .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                        .parseDefaulting(ChronoField.MINUTE_OF_HOUR, 0)
                        .parseDefaulting(ChronoField.SECOND_OF_MINUTE, 0)
                        .parseDefaulting(ChronoField.MILLI_OF_SECOND, 0)
                        .toFormatter();

                result = LocalDateTime.parse(value, formatter);
            }
            else if(value.contains("/")) {
                DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                        .appendPattern("yyyy/MM/dd[[' 'HH][:mm][:ss]")
                        .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                        .parseDefaulting(ChronoField.MINUTE_OF_HOUR, 0)
                        .parseDefaulting(ChronoField.SECOND_OF_MINUTE, 0)
                        .parseDefaulting(ChronoField.MILLI_OF_SECOND, 0)
                        .toFormatter();

                result = LocalDateTime.parse(value, formatter);
            }
        }
        return result;
    }

    @Override
    public WriteCellData<?> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        var dateTimeFormatProperty = contentProperty.getDateTimeFormatProperty();
        var formatter = "yyyy-MM-dd HH:mm:ss";
        if (dateTimeFormatProperty != null) {
            formatter = dateTimeFormatProperty.getFormat();
        }

        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern(formatter)));
    }

}

二、实体类中使用

在为easyExcel定义的实体类中心添加自定义注解，例如

@ExcelProperty(value = "维运开航日期", converter = LocalDateTimeConverter.class)
@DateTimeFormat("yyyy/MM/dd") // 指定导出格式
private LocalDateTime sailingDateFormatter;

在converter中引入自定义的接收和转换规则

@DateTimeFormat的作用是定义导出的格式，在contentProperty方法中的contentProperty.getDateTimeFormatProperty()的getFormat方法可以取到这边定义的格式，从而在写excel时转为对应格式（注意判断contentProperty.getDateTimeFormatProperty()是否为null，没定义DateTimeFormat时就会为null，判空避免空指针）