【无标题】使用EasyExcel实现动态表头导出、多sheet页导出功能

随着Easyexcel的应用，逐渐有了些复杂功能需要实现，如：动态表头、多个sheet页。本文记录下实现过程

首先，当然是引入依赖

<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.1.1</version>
</dependency>

接下来是实现过程：

（过程为实际的应用过程，实现了具体业务，过于繁琐，可直接去看总结）

一、动态表头

1.要导出的实体类添加关键注解 @ExcelProperty

public class IndmanageEntity implements Serializable {
    /**
     * 主键
     */
    @ExcelIgnore
    private Long id;
    /**
     * 指标编码
     */
    @ExcelProperty("指标编码")
    @ColumnWidth(20)
    private String typecode;
    /**
     * 指标名称
     */
    @ExcelProperty("指标名称")
    @ColumnWidth(20)
    private String typename;
    /**
     * 归属部门
     */
    @ExcelProperty("归属部门")
    @ColumnWidth(20)
    private String belongpart;
    /**
     * 归属业务线
     */
    @ExcelProperty("归属业务线")
    @ColumnWidth(20)
    private String belongbusline;
    /**
     * 指标来源
     */
    @ExcelProperty("指标来源")
    @ColumnWidth(20)
    private String indsource;
    /**
     * 指标来源报告
     */
    @ExcelProperty("指标来源报告")
    @ColumnWidth(20)
    private String indsourcereport;
    /**
     * 监管通知文件名称
     */
    @ExcelProperty("监管通知文件名称")
    @ColumnWidth(20)
    private String notifyfilename;
    /**
     * 通知发布时间
     */
    @ExcelProperty("通知发布时间")
    @ColumnWidth(20)
    private String notifytime;
    /**
     * 报送监管机构名称
     */
    @ExcelProperty("报送监管机构名称")
    @ColumnWidth(20)
    private String agencyname;
    /**
     * 指标计算方法
     */
    @ExcelProperty("指标计算方法")
    @ColumnWidth(20)
    private String zbjsff;
    /**
     * 指标定义口径
     */
    @ExcelProperty("指标定义口径")
    @ColumnWidth(20)
    private String zbdykj;
    /**
     * 指标对接的公司系统名称
     */
    @ExcelProperty("指标对接的公司系统名称")
    @ColumnWidth(20)
    private String companyname;
    /**
     * 事实表
     */
    @ExcelProperty("事实表")
    @ColumnWidth(20)
    private String facttable;
    /**
     * 指标对接的公司系统计算方法
     */
    @ExcelProperty("指标对接的公司系统计算方法")
    @ColumnWidth(20)
    private String companymethod;
    /**
     * 指标对应字段
     */
    @ExcelProperty("指标对应字段")
    @ColumnWidth(20)
    private String indfields;
    /**
     * 模型备注
     */
    @ExcelProperty("模型备注")
    @ColumnWidth(20)
    private String modelnotes;

    /**
     * 指标渠道
     */
    @ExcelProperty("指标渠道")
    @ColumnWidth(20)
    private String salechnl;

    /**
     * 指标值
     */
    @ExcelIgnore
    private Map<String,String> sumMap;
 }

2.serviceImpl写法

	@Override
    public void exportIndmanage(HttpServletResponse response, IndmanageQuery query) {
        try {
            //查询列表
            List<IndmanageEntity> indmanageEntityList = indManageMapper.queryIndmanage(query);
            if (!CollectionUtils.isEmpty(indmanageEntityList)) {
                //拼装表头
                List<List<String>> headList = generateHeadList(query);
                //拼装表体
                List<List<String>> body = new ArrayList<>();

                indmanageEntityList.stream().forEach(entity -> {
                    if(!StringUtils.isEmpty(entity.getTypecode())) {
                        List<String> body1 = new ArrayList<>();
                        body1.add(entity.getTypename());
                        body1.add(entity.getBelongpart());
                        body1.add(entity.getBelongbusline());
                        body1.add(entity.getIndsource());
                        body1.add(entity.getNotifyfilename());
                        body1.add(entity.getNotifytime());
                        body1.add(entity.getAgencyname());
                        body1.add(entity.getZbjsff());
                        body1.add(entity.getZbdykj());


                        //Map<String, String> sumMap = new HashMap<>();

                        IndsumQuery indsumQuery = new IndsumQuery();
                        indsumQuery.setTypecode(entity.getTypecode());
                        indsumQuery.setSalechnl(query.getSalechnl());
                        indsumQuery.setHeadcomname(query.getHeadcomname());
                        indsumQuery.setProvincecomname(query.getProvincecomname());
                        indsumQuery.setBranchname(query.getBranchname());
                        indsumQuery.setYears(query.getYears());


                        List<IndsumEntity> yearlist = indManageMapper.queryIndsumPage(indsumQuery);
                        if (null != yearlist && yearlist.size() > 0) {
                            if(StringUtils.isEmpty(query.getSalechnl())){
                                body1.add("全渠道");
                            }else {
                                Map<String, Object> map = new HashMap<>();
                                map.put("codetype", "salechnl");
                                map.put("code", yearlist.get(0).getSalechnl());
                                List<String> names = indManageMapper.queryNameByCode(map);
                                body1.add(names.get(0));
                            }
                            String type0 = yearlist.get(0).getType0();
                            if ("Y".equals(type0)) {
                                //body1.add(generateDivide1(new BigDecimal(yearlist.get(0).getDchild()), new BigDecimal(yearlist.get(0).getDmom())));
                                body1.add(generateDivide2(yearlist.get(0).getDchild(), yearlist.get(0).getDmom()));
                            } else {
                                //sumMap.put(indsumQuery.getYears(), yearlist.get(0).getDchild());
                                body1.add(yearlist.get(0).getDchild());
                            }

                        } else {
                            body1.add("");
                        }


                        List<String> quartor = query.getQuartor();
                        if (null != quartor && quartor.size() > 0) {
                            for (String q : quartor) {
                                indsumQuery.setQuartor(q);
                                List<IndsumEntity> sumEntityList = indManageMapper.queryIndsumPage(indsumQuery);
                                if (null != sumEntityList && sumEntityList.size() > 0) {
                                    String type0 = sumEntityList.get(0).getType0();
                                    if ("Y".equals(type0)) {
                                        body1.add(generateDivide2(sumEntityList.get(0).getDchild(), sumEntityList.get(0).getDmom()));
                                    } else {
                                        body1.add(sumEntityList.get(0).getDchild());
                                    }
                                } else {
                                    body1.add("");
                                }
                            }
                        }

                        List<String> months = query.getMonths();
                        if (null != months && months.size() > 0) {
                            for (String m : months) {
                                indsumQuery.setQuartor(null);
                                indsumQuery.setMonths(m);
                                List<IndsumEntity> sumEntityList = indManageMapper.queryIndsumPage(indsumQuery);
                                if (null != sumEntityList && sumEntityList.size() > 0) {
                                    String type0 = sumEntityList.get(0).getType0();
                                    if ("Y".equals(type0)) {
                                        //body1.add(generateDivide1(new BigDecimal(sumEntityList.get(0).getDchild()), new BigDecimal(sumEntityList.get(0).getDmom())));
                                        body1.add(generateDivide2(sumEntityList.get(0).getDchild(), sumEntityList.get(0).getDmom()));
                                    } else {
                                        //sumMap.put(indsumQuery.getYears() + m, sumEntityList.get(0).getDchild());
                                        body1.add(sumEntityList.get(0).getDchild());
                                    }
                                } else {
                                    body1.add("");
                                }
                            }
                        }


                        //entity.setSumMap(sumMap);

                        body.add(body1);
                    }
                });

                //导出
                EasyExcel.write(response.getOutputStream()).head(headList).sheet().doWrite(body);
            }
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.info("导出指标管理列表出错");
        }
    }

3、表头拼接方法

public List<List<String>> generateHeadList(IndmanageQuery query) {
        List<List<String>> headList = new ArrayList<>();
        //pinzhuangHead("指标编码",headList);
        pinzhuangHead("指标名称", headList);
        pinzhuangHead("归属部门", headList);
        pinzhuangHead("归属业务线", headList);
        pinzhuangHead("指标来源报告", headList);
        pinzhuangHead("监管通知文件名称", headList);
        pinzhuangHead("通知发布时间", headList);
        pinzhuangHead("报送监管机构名称", headList);
        pinzhuangHead("指标计算方法", headList);
        pinzhuangHead("指标定义口径", headList);
        pinzhuangHead("指标渠道", headList);
        pinzhuangHead(query.getYears(), headList);

        List<String> quartor = query.getQuartor();
        if (null != quartor && quartor.size() > 0) {
            for (String q : quartor) {
                pinzhuangHead(query.getYears() +"Q" + q, headList);
            }
        }

        List<String> months = query.getMonths();
        if (null != months && months.size() > 0) {
            for (String m : months) {
                pinzhuangHead(m, headList);
            }
        }
        return headList;
    }

public void pinzhuangHead(String str, List<List<String>> headList) {
        List<String> head0 = new ArrayList<>();
        head0.add(str);
        headList.add(head0);
    }

4、好了，到这里我们就实现了动态表头导出功能了，下面我们来总结一下

总结：

要实现动态表头，主要代码有三个：
1、表头为动态生成的：

	List<List<String>> headList = new ArrayList<>();
    List<String> head0 = new ArrayList<>();
    head0.add("111");
    List<String> head1 = new ArrayList<>();
    head1.add("222");
    headList.add(head0);
    headList.add(head1);

2、body也是动态生成的，注意要和表头对应上

	List<List<String>> body = new ArrayList<>();
    List<String> body1 = new ArrayList<>();
    body1.add("11101");
    body1.add("22201");
    body.add(body1);
    List<String> body2 = new ArrayList<>();
    body2.add("11102");
    body2.add("22202");
    body.add(body2);

3、使用EasyExcle导出

	OutputStream outputStream = new FileOutputStream("aaa.xlsx");
    EasyExcel.write(outputStream).head(headList).sheet().doWrite(body);

二、多sheet页

	List<A> aList = mapper.queryA();
	List<B> bList = mapper.queryB();
	
	ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
	excelWriter.write(aList, EasyExcel.writerSheet("A详情").head(A.class).build());
	excelWriter.write(bList, EasyExcel.writerSheet("B详情").head(B.class).build());
	excelWriter.finish();