easyexcel 自定义格式化

你的名字 2023-03-01 10:42 55阅读 0赞

--------------------

### 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**

![2020072418292485.png][]

**write2**

![20200724183035109.png][]

**read**

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

[2020072418292485.png]: /images/20230208/a9d8744c4ff04b7da92729df791510d0.png
[20200724183035109.png]: https://img-blog.csdnimg.cn/20200724183035109.png