使用easyexcel模板导出的两个坑（Map空数据列错乱和不支持嵌套对象）_easyexcel 导出数据移位

在项目当中，很多同事喜欢用Map来存储数据，尤其是从数据库查询数据然后存放到List<Map<String,Object>>当中。这样会导致通过excel模板导出时数据错乱。
当前easyexcel版本

 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>3.1.1</version>
 </dependency>
1
2
3
4
5

假如存在以下模板（limitManage2.xlsx）。

首先通过List<Map<String,Object>>填充数据(故意将REMARK字段的值部分设置为空，规则是列表中索引为偶数的为空)

   private static Map<String, Object> data2(){
        Map<String, Object> params = new HashMap<>();
        List<Map<String, String>> result = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            Map<String, String> curMap = new HashMap<>();
            curMap.put("CREATE_DATE", "2018-06-12");
            curMap.put("STATUS", "已生效");
            curMap.put("LIMIT_CYCLE_START", "2018-06-12");
            curMap.put("LIMIT_CYCLE_MTR", "2018-06-12");
            curMap.put("BRANCH_INSTITUTION_NAME", "资产管理部");
            curMap.put("SURPLUS_LIMIT", "88888888" + i);
            curMap.put("CREATOR", "系统管理员" + i);
            curMap.put("GET_LIMIT", "101000000");
            curMap.put("PK", "2417");
            curMap.put("TOTAL_LIMIT", "88888888");
            if (i % 2 == 0) {
                curMap.put("REMARK", "定制额度" + i);
            }else {
//                curMap.put("REMARK","");
            }
            result.add(curMap);
        }
        params.put("bean", result);
        return params;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

创建测试类

    @Test
    public void compositeFillMapIssue() {
        String templateFileName =
                TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "limitManage2.xlsx";
        System.out.println(new File(templateFileName).getPath());
        String fileName = TestFileUtil.getPath() + "compositeFill" + System.currentTimeMillis() + ".xlsx";

        try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            Map<String, Object> data2 = data2();
            FillConfig fillConfig = FillConfig.builder().build();
            data2.forEach((key,value)->{
                excelWriter.fill(new FillWrapper(key, (List<Map<String, String>>)value),fillConfig, writeSheet);
            });
        }
        System.out.println(new File(fileName).getPath());
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

测试类路径 src/test/java/com/alibaba/easyexcel/test/demo/fill/FillTest.java
测试模板路径 src/test/resources/demo/fill/limitManage2.xlsx

执行结果如下所示

从上面的结果不难看出，备注栏的信息都错乱了。

通过测试发现，将Map转为对象即可。

pojo定义如下

package com.alibaba.easyexcel.test.demo.fill;

import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@EqualsAndHashCode
public class FillData2 {

    private String CREATE_DATE;
    private String STATUS;
    private String LIMIT_CYCLE_START;
    private String LIMIT_CYCLE_MTR;
    private String BRANCH_INSTITUTION_NAME;
    private String SURPLUS_LIMIT;
    private String CREATOR;
    private String GET_LIMIT;
    private String PK;
    private String TOTAL_LIMIT;
    private String REMARK;
    private FillData3 fillData3;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

package com.alibaba.easyexcel.test.demo.fill;

import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;


@Getter
@Setter
@EqualsAndHashCode
public class FillData3 {

    private String REMARK3;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

创建测试数据

  private static Map<String, List<FillData2>> data3(){
        Map<String, List<FillData2>> params = new HashMap<>();
        List<FillData2> result = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            FillData2 curMap = new FillData2();

            curMap.setCREATE_DATE("2018-06-12");
            curMap.setSTATUS("已生效");
            curMap.setLIMIT_CYCLE_START("2018-06-12");
            curMap.setLIMIT_CYCLE_MTR("2018-06-12");
            curMap.setBRANCH_INSTITUTION_NAME("资产管理部");
            curMap.setSURPLUS_LIMIT("88888888" + i);
            curMap.setCREATOR("系统管理员" + i);
            curMap.setGET_LIMIT("101000000");
            curMap.setPK("2417");
            curMap.setTOTAL_LIMIT("88888888");
            if (i % 2 == 0) {
                curMap.setREMARK("定制额度" + i);
            }else {
//                curMap.put("REMARK","");
            }
            FillData3 fillData3 = new FillData3();
            fillData3.setREMARK3(curMap.getREMARK() +">>");
            curMap.setFillData3(fillData3);
            result.add(curMap);
        }
        params.put("bean", result);
        return params;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

创建测试类

    @Test
    public void compositeFillMapIssue3() {
        String templateFileName =
                TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "limitManage2.xlsx";
        System.out.println(new File(templateFileName).getPath());
        String fileName = TestFileUtil.getPath() + "compositeFill" + System.currentTimeMillis() + ".xlsx";

        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            Map<String, List<FillData2>> data2 = data3();
            data2.forEach((key,value)->{
                excelWriter.fill(new FillWrapper(key, value), writeSheet);
            });
        }
        System.out.println(new File(fileName).getPath());
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

测试结果如下所示

从测试结果可以看出，上面空数据错乱的问题解决了，但是最后一列没有任何数据。对应的模板为{bean.fillData3.REMARK}，从这里看出easyexcel对于一个对象中包含复杂对象数据的模板导出不支持。