easyexcel 自定义格式化

easyexcel默认不能转换localDateTime，自定义converter转换localDateTime

*******************

相关类与接口

Converter：T 为需要转换的java类型

public interface Converter<T> {

    Class supportJavaTypeKey();               //设置java类型
    CellDataTypeEnum supportExcelTypeKey();   //设置celldata类型

    T convertToJavaData(CellData var1, ExcelContentProperty var2, GlobalConfiguration var3) throws Exception;
    CellData convertToExcelData(T var1, ExcelContentProperty var2, GlobalConfiguration var3) throws Exception;
}

CellDataTypeEnum

public enum CellDataTypeEnum {
    STRING,
    DIRECT_STRING,
    NUMBER,
    BOOLEAN,
    EMPTY,
    ERROR,
    IMAGE;

CellData

public class CellData<T> extends AbstractCell {
    private CellDataTypeEnum type;
    private BigDecimal numberValue;
    private String stringValue;
    private Boolean booleanValue;
    private Boolean formula;
    private String formulaValue;
    private byte[] imageValue;
    private Integer dataFormat;
    private String dataFormatString;
    private T data;

*********
构造函数

    public CellData(CellData<T> other) {

    public CellData() {
    public CellData(T data) {
    public CellData(T data, String formulaValue) {

    public CellData(String stringValue) {
    public CellData(CellDataTypeEnum type, String stringValue) {
    public CellData(BigDecimal numberValue) {
    public CellData(byte[] imageValue) {
    public CellData(Boolean booleanValue) {
    public CellData(CellDataTypeEnum type) {

*********
普通方法

    public void setData(T data) {
    public void setFormula(Boolean formula) {
    public void setType(CellDataTypeEnum type) {
    public void setDataFormat(Integer dataFormat) {
    public void setDataFormatString(String dataFormatString) {

    public void setImageValue(byte[] imageValue) {
    public void setStringValue(String stringValue) {
    public void setFormulaValue(String formulaValue) {
    public void setBooleanValue(Boolean booleanValue) {
    public void setNumberValue(BigDecimal numberValue) {

    public T getData() {
    public Boolean getFormula() {
    public CellDataTypeEnum getType() {
    public Integer getDataFormat() {
    public String getDataFormatString() {

    public byte[] getImageValue() {
    public String getStringValue() {
    public String getFormulaValue() {
    public Boolean getBooleanValue() {
    public BigDecimal getNumberValue() {

    public void checkEmpty() {

*********
static 方法

    public static CellData newEmptyInstance() {
    public static CellData newEmptyInstance(Integer rowIndex, Integer columnIndex) {
    public static CellData newInstance(Boolean booleanValue) {
    public static CellData newInstance(Boolean booleanValue, Integer rowIndex, Integer columnIndex) {
    public static CellData newInstance(String stringValue, Integer rowIndex, Integer columnIndex) {
    public static CellData newInstance(BigDecimal numberValue, Integer rowIndex, Integer columnIndex) {

    public String toString() {

*******************

示例

header 类

Order

@Data
public class Order {

    private Integer id;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(converter = CustomConverter.class)
    private LocalDateTime createTime;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(converter = CustomConverter.class)
    private LocalDateTime payTime;

    @NumberFormat("##.00")
    private Double totalFee;
}

Order2

@Data
public class Order2 {

    private Integer id;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private LocalDateTime payTime;

    @NumberFormat("##.00")
    private Double totalFee;
}

自定义转换器

CustomConverter

public class CustomConverter implements Converter<LocalDateTime> {

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

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

    @Override
    public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }

    @Override
    public CellData<String> convertToExcelData(LocalDateTime localDateTime, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData<>(localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }
}

监听器

CustomListener

public class CustomListener extends AnalysisEventListener<Order>{

    @Override
    public void invoke(Order order, AnalysisContext analysisContext) {
        System.out.println(order);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("数据解析完成");
    }
}

测试类

Test

public class Test {

    private static final String file_path="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"write_test6.xlsx";
    private static final String file_path2="e:"+ File.separator+"java"+File.separator+"easyexcel"+File.separator+"write_test7.xlsx";

    public static void write(){
        EasyExcel.write(file_path,Order.class).sheet().doWrite(data());
    }

    public static void write2(){
        EasyExcel.write(file_path2, Order2.class)
                .registerConverter(new CustomConverter())
                .sheet().doWrite(data());
    }

    public static void read(){
        EasyExcel.read(file_path,Order.class,new CustomListener()).sheet().doRead();
    }

    private static List<Order> data(){
        List<Order> list=new ArrayList<>();

        Order order=new Order();
        order.setId(1);
        order.setCreateTime(LocalDateTime.now());
        order.setPayTime(LocalDateTime.now().plusMinutes(2L));
        order.setTotalFee(10d);
        list.add(order);

        return list;
    }

    public static void main(String[] args){
        write();
        write2();

        read();
    }
}

*******************

使用测试

write

write2

read

Order(id=1, createTime=2020-07-24T18:21:07, payTime=2020-07-24T18:23:07, totalFee=10.0)
数据解析完成