转换器:
package com.leadtrans.report.config;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import java.math.BigDecimal;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
/**
* @author: Tyler
* @createDate: 2021/12/9
*/
public class ConverterDateTime implements Converter<LocalDate> {
@Override
public Class<?> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if(cellData.getNumberValue()==null)
return null;
BigDecimal numberValue = cellData.getNumberValue();
System.out.println("numberValue:"+numberValue);
long second = numberValue.multiply(new BigDecimal("86400")).longValue();
System.out.println("second:"+second);
Instant instant = Instant.ofEpochSecond(second-2209190400L);
return LocalDate.ofInstant(instant, ZoneId.systemDefault());
}
@Override
public WriteCellData<?> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
}
}
model
@ExcelProperty(value = "ATD",converter = ConverterDateTime.class)
private LocalDate ATD;//实际装货
@ExcelProperty(value = "ATA",converter = ConverterDateTime.class)
private LocalDate ATA;//实际卸货
PS: excel中的单元格 , 不能是文本类型。
否则需要修改转换器中 cellData.getNumberValue() 为cellData.getStringValue()