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> {@Overridepublic 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<>(" ");}@Overridepublic 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";@Overridepublic Class<LocalDate> supportJavaTypeKey() {return LocalDate.class;}@Overridepublic 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));}@Overridepublic 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;}}