最新EasyExecl多sheet导出，列头动态获取，并进行相关样式优化

 废话不多说，直接上干货，下面这段是业务的处理层：

// 首个sheet页统计数据
List<JSONObject> recordsProjectTotalList = iScPositionRecordsService.getScPositionRecordsProjectTotal(jsonObject);
// 首个sheet页统计动态列头数据
List<JSONObject> projectList = scProjectManageService.getProjectNameList();
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("《项目现场品质检查考卷》", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        //EasyExcel.write(response.getOutputStream()).head(scoreHead(scAreaRecords,projectList)).sheet("模板").doWrite(dataList(recordsProjectTotalList));
// 这里 指定文件
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet writeSheet = null;
try {
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    WriteFont contentWriteFont = new WriteFont();
    for (int i = 0; i < projectList.size(); i++) {
         if (i == 0) {

             // 背景设置为蓝色
             headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
             WriteFont headWriteFont = new WriteFont();
             headWriteFont.setFontHeightInPoints((short)16);
             headWriteCellStyle.setWriteFont(headWriteFont);

             // 字体大小
             contentWriteFont.setFontHeightInPoints((short)12);
             contentWriteCellStyle.setWriteFont(contentWriteFont);
             // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
             HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
             // 每次都要创建writeSheet 这里注意必须指定sheetNo。这里注意.head()我这块使用的动态列头部数据处理，如果固定列可以使用对象实现
             writeSheet = EasyExcel.writerSheet(i, "构成")
                            .registerWriteHandler(horizontalCellStyleStrategy)
                            .registerWriteHandler(new Custemhandler())
                            .head(scoreHead(scAreaRecords, projectList))
                            .build();
             excelWriter.write(dataList(recordsProjectTotalList), writeSheet);
         }
         jsonObject.put("projectId", projectList.get(i).getString("id"));
         String projectName = projectList.get(i).getString("projectName");
         String weightRatio = projectList.get(i).getString("weightRatio");
         String projectTitle = "项目现场品质检查考卷(" + projectList.get(i).getString("projectName") + ")";
         ExcelPoiUtils.modExcelProperty(ExaminationPaperHeadData.class, projectTitle, 0);
         // 背景设置为蓝色
         headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
         WriteFont headWriteFont = new WriteFont();
         headWriteFont.setFontHeightInPoints((short)12);
         headWriteCellStyle.setWriteFont(headWriteFont);
         // 字体大小
         contentWriteFont.setFontHeightInPoints((short)9);
         contentWriteCellStyle.setWriteFont(contentWriteFont);
         // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
         contentWriteCellStyle.setWrapped(true);
         //设置 水平居中
         //contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
         //设置 垂直居中
         contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
         HorizontalCellStyleStrategy horizontalCellStyleStrategy1 = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
         // 每次都要创建writeSheet 这里注意必须指定sheetNo。这里注意ExaminationPaperHeadData.class，我这里因为列名相同所以用的同一个class 实际上可以一直变
         writeSheet = EasyExcel.writerSheet(i + 1, (i + 1) + "-" + projectName + "(" + weightRatio + "%)")
                        .registerWriteHandler(horizontalCellStyleStrategy1)
                        .head(ExaminationPaperHeadData.class)
                        .build();
         // 去数据库查询数据 这里去数据库查询（非首sheet页）每一sheet页的数据
         List<ExaminationPaperHeadData> scProjectPositionList = scProjectManageService.getScProjectTotalList(jsonObject);
         excelWriter.write(scProjectPositionList, writeSheet);
    }
} finally {
    // 千万别忘记finish 会帮忙关闭流
    if (excelWriter != null) {
        excelWriter.finish();
    }
}


private static List<List<String>> scoreHead(ScAreaRecords scAreaRecords, List<JSONObject> clazz) {
    List<List<String>> list = Lists.newArrayList();
    //标题
    String title = "项目现场品质检查考卷";
    String clazzInfo = "项目名称：" + scAreaRecords.getAreaName() + "      日期：" + DateUtil.normalFormat(scAreaRecords.getCheckTime());
    String peopleInfo = "考评人：" + scAreaRecords.getUserNames() + "                项目负责人：" + scAreaRecords.getOwnerName();
    for (int i = 0; i < clazz.size(); i++) {
         给首sheet动态列前多加一列，名模块
         if (i == 0) {
             List<String> head0 = Lists.newArrayList();
             head0.add(title);
             head0.add(clazzInfo);
             head0.add(peopleInfo);
             head0.add("模块");
             list.add(head0);
         }
         List<String> head = Lists.newArrayList();
         head.add(title);
         head.add(clazzInfo);
         head.add(peopleInfo);
         head.add(clazz.get(i).getString("projectName"));
         list.add(head);
         给首sheet动态列后多加一列，名合计
         if (i == clazz.size() - 1) {
             List<String> head0 = Lists.newArrayList();
             head0.add(title);
             head0.add(clazzInfo);
             head0.add(peopleInfo);
             head0.add("合计");
             list.add(head0);
         }
    }
    return list;
}

    private List<List<Object>> dataList(List<JSONObject> recordsProjectTotalList) {
        List<List<Object>> list = new ArrayList<List<Object>>();
        for (int i = 0; i < 3; i++) {
            List<Object> data = new ArrayList<Object>();
            String nameTitel = "";
            String field = "";
            if (i == 0) {
                nameTitel = "权重";
                field = "weightRatio";
            } else if (i == 1) {
                nameTitel = "检查项";
                field = "score";
            } else {
                nameTitel = "检查得分";
                field = "numTotal";
            }
            data.add(nameTitel);
            for (JSONObject jsonObject : recordsProjectTotalList) {
                if (StringUtils.equals(field, "weightRatio")) {
                    data.add(jsonObject.getString(field) + "%");
                } else {
                    data.add(jsonObject.getString(field));
                }
            }
            list.add(data);
        }
        return list;
    }

非首sheet页数据后面数据列是固定的，所以用了对象实现

@ColumnWidth(15)
@Data
public class ExaminationPaperHeadData {

    @ColumnWidth(10)
    @ExcelProperty({"项目现场品质检查考卷", "序号"})
    private String number;

    @ExcelProperty({"项目现场品质检查考卷", "具体点位"})
    private String postition;

    @ExcelProperty({"项目现场品质检查考卷", "巡查对象"})
    private String obj;

    @ColumnWidth(50)
    @ExcelProperty({"项目现场品质检查考卷", "标准要点内容"})
    private String standardRemark;

    @ExcelProperty({"项目现场品质检查考卷", "核查方法"})
    private String checkFunc;

    @ColumnWidth(50)
    @ExcelProperty({"项目现场品质检查考卷", "扣分规则"})
    private String ruleRemark;

    @ExcelProperty({"项目现场品质检查考卷", "配分"})
    private String score;

    @ExcelProperty({"项目现场品质检查考卷", "得分"})
    private String achievement;

    @ExcelProperty({"项目现场品质检查考卷", "问题描述"})
    private String remark;
}

对于非首sheet页数据标题进行变更这里写了一个工具类

public static <T> void modExcelProperty(Class<T> tClass, String value, int level) {
    modExcelProperty(tClass, value, level, (String)null);
}

public static <T> void modExcelProperty(Class<T> tClass, String value, int level, String propertyName) {
    try {
        Field[] var4 = tClass.getDeclaredFields();
        int var5 = var4.length;
        for(int var6 = 0; var6 < var5; ++var6) {
            Field field = var4[var6];
            if (propertyName == null || field.getName().equals(propertyName)) {
                ExcelProperty excelProperty = (ExcelProperty)field.getAnnotation(ExcelProperty.class);
                if (excelProperty != null) {
                    InvocationHandler h = Proxy.getInvocationHandler(excelProperty);
                    Field hField = h.getClass().getDeclaredField("memberValues");
                    hField.setAccessible(true);
                    Map memberValues = (Map)hField.get(h);
                    String[] values = excelProperty.value();
                    values[level] = value;
                    memberValues.put("value", values);
                 }
              }
          }
      } catch (NoSuchFieldException var13) {
            var13.printStackTrace();
      } catch (IllegalAccessException var14) {
            var14.printStackTrace();
    }

}

感谢灰灰的云深无迹和其他优秀博主，如有侵权联系删除