hutool 导出复杂excel(动态合并行和列)

实现效果：

 

 

基础表格导出：其中的查询及实体类未贴出，白嫖党请自行修改导出数据及表头

    public void excelExport(@RequestBody QueryAttendanceStatisticalPageListBO queryAttendanceStatisticalPageListBO, HttpServletResponse response){
        queryAttendanceStatisticalPageListBO.setPageType(0);
        List<HrmAttendanceStatistical> list = new LinkedList<>(attendanceStatisticalService.queryPageList(queryAttendanceStatisticalPageListBO).getList());
        try (ExcelWriter writer = ExcelUtil.getWriter()) {
            // 表头处理
            List<String> rowHead = CollUtil.newLinkedList("序号", "部门", "姓名", "入职时间", "职位", "应出勤天数", "实际出勤天数", "年假（天）"
                    , "加班（天）", "调休（天）", "事假（天）", "迟到/早退（次）", "迟到/早退（分钟）", "迟到/早退（扣款）", "缺卡次数", "缺卡扣款", "全勤奖", "备注（调休）", "签名确认");
            List<List<Object>> rows = new LinkedList<>();
            // 第二行表头
            LinkedList<Object> header2 = CollUtil.newLinkedList();
            for (int i = 0; i < 17; i++) {
                header2.add("");
            }
            if (queryAttendanceStatisticalPageListBO.getMonth() > 3) {
                header2.add("3月前");
            }
            header2.add("年假");
            if (queryAttendanceStatisticalPageListBO.getMonth() > 2) {
                header2.add((queryAttendanceStatisticalPageListBO.getMonth() - 2) + "月");
            }
            if (queryAttendanceStatisticalPageListBO.getMonth() > 1) {
                header2.add((queryAttendanceStatisticalPageListBO.getMonth() - 1) + "月");
            }
            header2.add(queryAttendanceStatisticalPageListBO.getMonth() + "月");
            header2.add("签名确认");
            // 标题
            writer.merge(header2.size() - 1, "韩味" + queryAttendanceStatisticalPageListBO.getMonth() + "月考勤统计表");
            // 写入表头
            writer.writeHeadRow(rowHead);
            writer.writeHeadRow(header2);
            // 表头合并
            for (int i = 0; i < header2.size(); i++) {
                if (i < 17) {
                    writer.merge(1, 2, i, i, null, false);
                } else if (i == header2.size() - 1) {
                    writer.merge(1, 2, i, i, "签名确认", true);
                }
            }
            writer.merge(1, 1, 17, header2.size() - 2, null, false);
            // 数据整理
            if (list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    HrmAttendanceStatistical item = list.get(i);
                    LinkedList<Object> row = CollUtil.newLinkedList(
                            i + 1
                            , item.getDingtalkDeptName()
                            , item.getName()
                            , DateUtil.format(item.getHiredDate(), "yyyy/MM/dd")
                            , item.getPosition()
                            , item.getShouldAttendanceDays()
                            , item.getRealAttendanceDays()
                            , item.getLeaveYear()
                            , item.getWorkOvertime()
                            , item.getLeavePaid()
                            , item.getLeaveThing()
                            , item.getLateArrivalTimes()
                            , item.getLateArrivalMinutes()
                            , item.getLateArrivalDeductions()
                            , item.getLackAttendance()
                            , item.getLackAttendanceDeductions()
                            , item.getPerfectAttendanceReward()
                    );
                    if (queryAttendanceStatisticalPageListBO.getMonth() > 3) {
                        row.add(item.getThreeMonthVacation());
                    }
                    row.add(item.getYearVacation());
                    if (queryAttendanceStatisticalPageListBO.getMonth() > 2) {
                        row.add(item.getTwoMonthVacation());
                    }
                    if (queryAttendanceStatisticalPageListBO.getMonth() > 1) {
                        row.add(item.getLastMonthVacation());
                    }
                    row.add(item.getThisMonthVacation());
                    row.add("");
                    rows.add(row);
                }
            }
            writer.write(rows, true);
            layout(writer, list);
            writer.setFreezePane(3);
            // 设置宽高
            writer.setColumnWidth(0, 8);
            for (int i = 1; i < header2.size() - 1; i++) {
                writer.setColumnWidth(i, 10);
            }
            writer.setColumnWidth(1, 15);
            writer.setColumnWidth(header2.size() - 1, 15);
            writer.setRowHeight(0, 40);
            for (int i = 1; i < list.size() + 3; i++) {
                writer.setRowHeight(i, 25);
            }
            //设置内容字体
            Font allFont = writer.createFont();
            allFont.setFontHeightInPoints((short) 10);
            //第二个参数表示是否忽略头部样式
            writer.getStyleSet().setFont(allFont, true);
            // 自动换行
            writer.getStyleSet().setWrapText();
            CellStyle headCellStyle = writer.getHeadCellStyle();
            //设置表头自动换行
            headCellStyle.setWrapText(true);
            // 设置头部样式
            Cell cell = writer.getCell(0, 0);
            CellStyle cellStyle = cell.getCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font font = writer.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setBold(true);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            //自定义标题别名
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            // 弹出下载对话框的文件名，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("韩味" + queryAttendanceStatisticalPageListBO.getMonth() + "月考勤统计表", "utf-8") + ".xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        } catch (Exception e) {
            log.error("导出考勤统计异常：", e);
        }
    }

动态合并行：根据表格数据中的部门名称动态合并行

    /**
     * 整理导出表格的样式
     * @param writer
     * @param list
     */
    private static void layout(ExcelWriter writer, List<HrmAttendanceStatistical> list) {
        Map<String, List<String>> groupBy = new LinkedHashMap<>(list.stream().map(HrmAttendanceStatistical::getDingtalkDeptName).collect(Collectors.groupingBy(item -> item)));
        final Integer[] mergeRowIndex = {3};
        Set<String> keySet = groupBy.keySet().stream().sorted(Comparator.comparing(item -> item)).collect(Collectors.toCollection(LinkedHashSet::new));
        keySet.forEach(item -> {
            int count = groupBy.get(item).size();
            if (count > 1) {
                writer.merge(mergeRowIndex[0], mergeRowIndex[0] + count - 1, 1, 1, item + "（" + count + "人）", false);
            } else if (count == 1){
                writer.writeCellValue(1, mergeRowIndex[0], item + "（" + count + "人）");
            }
            mergeRowIndex[0] += count;
        });
    }

注意：如果合并操作在writer.write(rows)之前，合并生效，但是覆盖的字符串（content参数）不生效，同时，宽高设置、字体设置、样式设置等，最好都放在write()方法后面。