Java Excel多表头导出 及导出对象中包含集合导出

1、导入依赖

这里导入的是常用的Alibaba的easyexcel，其实真正使用的是其依赖的poi相关依赖，导入poi相关依赖也可以

                    com.alibaba            easyexcel            3.2.1        

2、需求及效果展示

对应导出的一条数据，划线之前为基础数据，就只有一行；而划线后面的数据为list集合，且不同部门的集合长度不一致。

实际导出效果：

3、实体类部分展示

@Data@AllArgsConstructor@NoArgsConstructorpublic class ContractTimeReport {    //部门时效数据  为集合    private List countersignDeptTimes;    private List financeDeptTimes;    private List lawDeptTimes;    private List undertakeDeptTimes;    private List leaderDeptTimes;    //基础数据 为单行字段 省略很多其他        private String approveTypeName;        private LocalDateTime sendApprovalDate;    private Long sendApprovalDateLong;        private LocalDateTime approveEndTime;        private Long approveEndTimeLong;}

4、逻辑处理

//要导出的结果集List list = searchHitStream.map(SearchHit::getContent).collect(Collectors.toList());//需要将集合中的对象转换成map        List> mapList = getMaps(list);//导出excel的表头，删除了许多字段        List head = Arrays.asList("审签流程类型", "送审日期", "审批通过日期",                "部门名称", "员工姓名", "员工处理时长（小时）", "承办部门内部审签时长（小时）", "审签退回修改时长（小时）",                "部门名称", "员工姓名", "员工审签时长（小时）", "部门审签时长汇总（小时）",                "员工姓名", "员工审签时长（小时）", "部门审签时长汇总（小时）",                "员工姓名", "员工审签时长（小时）", "部门审签时长汇总（小时）");        HSSFWorkbook wb = null;        setResponseData(response);        ServletOutputStream outputStream = response.getOutputStream();        try {            wb = warpSingleWorkbook2("title", mapList, head);        } catch (Exception e) {                  }        wb.write(outputStream);

对象转map
所需依赖：

                    commons-beanutils            commons-beanutils            1.9.4        

    private List> getMaps(List list) {        List> mapList = new ArrayList<>();        for (ContractTimeReportExportVo contractTimeReport : list) {            //使用该方法将对象转成map后，对象中的list并没有同样转成map形式，所以需要再单独的将每个list转成map再赋值进去            BeanMap beanMap = new BeanMap(contractTimeReport);            HashMap map = new HashMap();            map.putAll(beanMap);            List financeDeptTimesList = contractTimeReport.getFinanceDeptTimes();            List financeDeptTimes = new ArrayList<>();            if (financeDeptTimesList != null) {                for (FinanceDeptTime deptTime : financeDeptTimesList) {                    BeanMap beanMap1 = new BeanMap(deptTime);                    HashMap map1 = new HashMap();                    map1.putAll(beanMap1);                    map1.remove("class");                    financeDeptTimes.add(map1);                }            }            List lawDeptTimeList1 = contractTimeReport.getLawDeptTimes();            List financeDeptTimes1 = new ArrayList<>();            if (lawDeptTimeList1 != null) {                for (LawDeptTime deptTime : lawDeptTimeList1) {                    BeanMap beanMap1 = new BeanMap(deptTime);                    HashMap map1 = new HashMap();                    map1.putAll(beanMap1);                    map1.remove("class");                    financeDeptTimes1.add(map1);                }            }            List countersignDeptTimeList = contractTimeReport.getCountersignDeptTimes();            List countersignDeptTimes = new ArrayList<>();            if (countersignDeptTimeList != null) {                for (CountersignDeptTime deptTime : countersignDeptTimeList) {                    BeanMap beanMap1 = new BeanMap(deptTime);                    HashMap map1 = new HashMap();                    map1.putAll(beanMap1);                    map1.remove("class");                    countersignDeptTimes.add(map1);                }            }            List undertakeDeptTimeList = contractTimeReport.getUndertakeDeptTimes();            List undertakeDeptTimes = new ArrayList<>();            if (undertakeDeptTimeList != null) {                for (UndertakeDeptTime deptTime : undertakeDeptTimeList) {                    BeanMap beanMap1 = new BeanMap(deptTime);                    HashMap map1 = new HashMap();                    map1.putAll(beanMap1);                    map1.remove("class");                    undertakeDeptTimes.add(map1);                }            }            map.put("financeDeptTimes", financeDeptTimes);            map.put("lawDeptTimes", financeDeptTimes1);            map.put("countersignDeptTimes", countersignDeptTimes);            map.put("undertakeDeptTimes", undertakeDeptTimes);            map.remove("class");            mapList.add(map);        }        return mapList;    }

主要处理逻辑：

public static HSSFWorkbook warpSingleWorkbook2(String title, List> mapList, List head) {        List> mergeParams = new ArrayList<>();        String[] str1 = {"globalsn", "localsn", "money", "vatRate", "vaTax", "unVatAmount", "contractName", "classifyName",                "weSignName", "otherSignName", "templateName", "moneyTypeName", "priorityName", "degreeName", "selectModelName",                "budgetName", "takerName", "approveTypeName", "sendApprovalDate", "approveEndTime"};        String lawDeptTimes = "lawDeptTimes";        String financeDeptTimes = "financeDeptTimes";        String countersignDeptTimes = "countersignDeptTimes";        String undertakeDeptTimes = "undertakeDeptTimes";        String[] str2 = {"deptName", "staffName", "personTimeHour", "deptTimeHour", "backTimeHour"};        String[] str3 = {"deptName", "staffName", "personTimeHour", "deptTimeHour"};        String[] str4 = {"staffName", "personTimeHour", "deptTimeHour"};        String[] str5 = {"staffName", "personTimeHour", "deptTimeHour"};        String[] str6 = {"allTimeHour", "signTimeHour", "finishTimeHour"};        // 如果要设置背景色 最好用 XSSFWorkbook        HSSFWorkbook book = new HSSFWorkbook();        HSSFSheet sheet = book.createSheet(title);        sheet.setDefaultColumnWidth(20);        HSSFCellStyle style = book.createCellStyle();        style.setAlignment(HorizontalAlignment.CENTER);        style.setVerticalAlignment(VerticalAlignment.CENTER);        HSSFCellStyle headStyle = book.createCellStyle();        headStyle.setAlignment(HorizontalAlignment.CENTER);        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);        headStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());        // 生成表头第一行        HSSFRow headRow0 = sheet.createRow(0);        setExcelValue(headRow0.createCell(0), "title", headStyle);        mergeParams.add(Arrays.asList(0, 0, 0, 37));		//生成表头第二行        HSSFRow headRow1 = sheet.createRow(1);        mergeParams.add(Arrays.asList(1, 1, 20, 24));        mergeParams.add(Arrays.asList(1, 1, 25, 28));        mergeParams.add(Arrays.asList(1, 1, 29, 31));        mergeParams.add(Arrays.asList(1, 1, 32, 34));        setExcelValue(headRow1.createCell(20), "承办部门", headStyle);        setExcelValue(headRow1.createCell(25), "会签部门", headStyle);        setExcelValue(headRow1.createCell(29), "财务部门", headStyle);        setExcelValue(headRow1.createCell(32), "法律部门", headStyle);        for (int i = 0; i < 20; i++) {            setExcelValue(headRow1.createCell(i), head.get(i), headStyle);        }        for (int i = 35; i < 38; i++) {            setExcelValue(headRow1.createCell(i), head.get(i), headStyle);        }		//生成表头第三行        HSSFRow headRow = sheet.createRow(2);        for (int i = 20; i < 35; i++) {            setExcelValue(headRow.createCell(i), head.get(i), headStyle);        }        for (int i = 0; i < 20; i++) {            List mergeParam = Arrays.asList(1, 2, i, i);            mergeParams.add(mergeParam);        }        for (int i = 35; i < 38; i++) {            List mergeParam = Arrays.asList(1, 2, i, i);            mergeParams.add(mergeParam);        }		//循环处理数据，主要逻辑是，找出单条数据中所有集合的最大长度，然后循环遍历所有集合给单元格赋值        int rowIndex = 3;        int commonTotalSize = 20;        for (Map map : mapList) {            // 记录合并的开始行            int startRowIndex = rowIndex;            HSSFRow bodyRow = sheet.createRow(rowIndex++);            for (int i = 0; i < str1.length; i++) {                setExcelValue(bodyRow.createCell(i), map.get(str1[i]), style);            }            for (int i = 35; i < 38; i++) {                setExcelValue(bodyRow.createCell(i), map.get(str6[i - 35]), style);            }                        List> list1 = (List>) map.get(undertakeDeptTimes);            List> list2 = (List>) map.get(countersignDeptTimes);            List> list3 = (List>) map.get(financeDeptTimes);            List> list4 = (List>) map.get(lawDeptTimes);            int maxLine = list1.size() > list2.size() ? list1.size() : list2.size();            maxLine = maxLine > list3.size() ? maxLine : list3.size();            maxLine = maxLine > list4.size() ? maxLine : list4.size();            if (list1.size() > 0) {                for (int i = 0; i < str2.length; i++) {                    setExcelValue(bodyRow.createCell(str1.length + i), list1.get(0).get(str2[i]), style);                }            }            if (list2.size() > 0) {                for (int i = 0; i < str3.length; i++) {                    setExcelValue(bodyRow.createCell(str1.length + str2.length + i), list2.get(0).get(str3[i]), style);                }            }            if (list3.size() > 0) {                for (int i = 0; i < str4.length; i++) {                    setExcelValue(bodyRow.createCell(str1.length + str2.length + str3.length + i), list3.get(0).get(str4[i]), style);                }            }            if (list4.size() > 0) {                for (int i = 0; i < str5.length; i++) {                    setExcelValue(bodyRow.createCell(str1.length + str2.length + str3.length + str4.length + i), list4.get(0).get(str5[i]), style);                }            }            for (int i = 1; i < maxLine; i++) {                HSSFRow bodyRow2 = sheet.createRow(rowIndex++);                if (list1.size() > i) {                    for (int j = 0; j < str2.length; j++) {                        setExcelValue(bodyRow2.createCell(str1.length + j), list1.get(i).get(str2[j]), style);                    }                }                if (list2.size() > i) {                    for (int j = 0; j < str3.length; j++) {                        setExcelValue(bodyRow2.createCell(str1.length + str2.length + j), list2.get(i).get(str3[j]), style);                    }                }                if (list3.size() > i) {                    for (int j = 0; j < str4.length; j++) {                        setExcelValue(bodyRow2.createCell(str1.length + str2.length + str3.length + j), list3.get(i).get(str4[j]), style);                    }                }                if (list4.size() > i) {                    for (int j = 0; j < str5.length; j++) {                        setExcelValue(bodyRow2.createCell(str1.length + str2.length + str3.length + str4.length + j), list4.get(i).get(str5[j]), style);                    }                }            }            //记录需要合并的单元格，后面统一合并            if (maxLine > 1) {                // 依次放入  起始行 结束行 起始列 结束列                for (int i = 0; i < commonTotalSize; i++) {                    List mergeParam = Arrays.asList(startRowIndex, rowIndex - 1, i, i);                    mergeParams.add(mergeParam);                }                for (int i = 35; i < 38; i++) {                    List mergeParam = Arrays.asList(startRowIndex, rowIndex - 1, i, i);                    mergeParams.add(mergeParam);                }            }        }        for (List list : mergeParams) {            sheet.addMergedRegion(new CellRangeAddress(list.get(0), list.get(1), list.get(2), list.get(3)));        }        mergeCell(sheet);        return book;    }//合并表头的单元格，根据自己的实际情况来配置参数    private static void mergeCell(HSSFSheet sheet) {        MergeOne(sheet, new CellRangeAddress(0, 0, 0, 37));        MergeOne(sheet, new CellRangeAddress(1, 1, 20, 24));        MergeOne(sheet, new CellRangeAddress(1, 1, 25, 28));        MergeOne(sheet, new CellRangeAddress(1, 1, 29, 31));        MergeOne(sheet, new CellRangeAddress(1, 1, 32, 34));        for (int i = 0; i < 20; i++) {            CellRangeAddress regionThird = new CellRangeAddress(1, 2, i, i);            MergeOne(sheet, regionThird);        }        for (int i = 20; i < 35; i++) {            CellRangeAddress regionThird = new CellRangeAddress(2, 2, i, i);            MergeOne(sheet, regionThird);        }        for (int i = 35; i < 38; i++) {            CellRangeAddress regionThird = new CellRangeAddress(1, 2, i, i);            MergeOne(sheet, regionThird);        }    }    private static void MergeOne(HSSFSheet sheet, CellRangeAddress region) {        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);    }    public static void setExcelValue(HSSFCell cell, Object value, HSSFCellStyle style) {        // 写数据        if (value == null) {            cell.setCellValue("");        } else {            if (value instanceof Integer || value instanceof Long) {                cell.setCellValue(Long.parseLong(value.toString()));            } else if (value instanceof BigDecimal) {                //((BigDecimal) value).setScale(1, RoundingMode.HALF_UP).doubleValue()                cell.setCellValue(value.toString());            } else {                cell.setCellValue(value.toString());            }            cell.setCellStyle(style);        }    }