EasyExcel导入导出数据类型转换

前言：

1、基本数据类型转换：当前原始的数据类型是interger类型，需要在导出时将其映射为对应的字符串，并且导入时可以将字符串重新映射为interger类型。

2、时间格式转换：数据从数据库中获取的类型为LocalDate类型，即yyyy-mm-dd，导出时需要将其格式化为yyyy-mm类型，导入时可以将yyyy-mm串重新映射为yyyy-mm-dd。

1、基本数据类型转换

实体类

    /** 产品类型  0-电芯 1-pack */
    @ExcelProperty(value = "产品类型",index = 2, converter = ProductTypeConverter.class)
    @ColumnWidth(15)
    private Integer productType;

配置数据类型转换器

import com.alibaba.excel.converters.Converter;
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;


public class ProductTypeConverter implements Converter<Integer> {

    @Override
    public WriteCellData<Integer> convertToExcelData(Integer i, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if (i == 0) {
            return new WriteCellData<>("电芯");
        } else if (i == 1) {
            return new WriteCellData<>("PACK");
        }
        return new WriteCellData<>(" ");
    }

    @Override
    public Integer convertToJavaData(ReadCellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if("电芯".equals(cellData.getStringValue())){
            return 0;
        } else if("PACK".equals(cellData.getStringValue())){
            return 1;
        }else {
            return -1;
        }
    }

}

2、时间格式转换

实体类

    /** 统计时间 */
    @ExcelProperty(value = "统计时间",index = 7,converter = DateConverter.class)
    @JsonFormat(pattern = "yyyy-MM-dd")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @ColumnWidth(20)
    private LocalDate statisticsTime;

配置的时间格式转换器

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.WriteConverterContext;
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.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 DateConverter implements Converter<LocalDate> {

    private static final String PATTERN_YYYY_MM = "yyyy-MM";


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


    @Override
    public WriteCellData<String> convertToExcelData(WriteConverterContext<LocalDate> context)  {
        LocalDate localDate = context.getValue();
        if (localDate == null) {
            return null;
        }
        Date date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());
        SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_YYYY_MM);
        return new WriteCellData<>(sdf.format(date));
    }

    @Override
    public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        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("-")) {
                DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                        .appendPattern("yyyy-MM-dd")
                        .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 = LocalDate.parse(value+"-01", formatter);
            }
            else if(value.contains("/")) {
                DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                        .appendPattern("yyyy/MM/dd")
                        .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 = LocalDate.parse(value+"-01", formatter);
            }
        }
        return result;
    }


}