使用hutool导出Excel循环表头和数据、自动合并单元格、样式修改-蒲公英云

引入hutool工具包

<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>commons-net</groupId>
            <artifactId>commons-net</artifactId>
            <version>3.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

部分代码段

public void export(ParamDto data, HttpServletResponse response) {
        try (OutputStream out = response.getOutputStream()) {
            List<String> taskList = null;
            List<Map<String, Object>> list = new ArrayList<>();
            List<Map<String, Object>> titleList = new ArrayList<>();
            if (StringUtils.isEmpty(data.getTaskName())) {
                taskList = getTaskList(data.getDepartment());
            }
            ExcelWriter writer = ExcelUtil.getWriter();
            writer.setColumnWidth(-1, 18);
            // 设置样式
            StyleSet style = writer.getStyleSet();
            CellStyle cellStyle = style.getCellStyle();
            cellStyle.setWrapText(true);
            CellStyle headCellStyle = style.getHeadCellStyle();
            //水平居中
            headCellStyle.setAlignment(HorizontalAlignment.LEFT);
            headCellStyle.setWrapText(true);
            //设置内容字体
            Font font = writer.createFont();
            //加粗
            font.setBold(true);
            //设置标题字体大小
            font.setFontHeightInPoints((short) 16);
            headCellStyle.setFont(font);
            writer.setStyleSet(style);
            writer.renameSheet(0, "评价记录");
            if (CollectionUtils.isEmpty(taskList)) {
                list = this.getActivityList(data, setList());
                titleList = this.getActivityList(data, setTitleList());
                if(!CollectionUtils.isEmpty(list)) {
                    //创建标题和表头信息
                    if (!CollectionUtils.isEmpty(titleList)) {
                        writer.merge(15, titleList.get(0).get("bumenchejian") + "评价记录", true);
                        writer.merge(3, StringUtils.join("单位:", titleList.get(0).get("bumenchejian")), true);
                        writer.writeRow(new ArrayList<>());
                        layout(writer, titleList, 0);
                        writer.setRowHeight(2, 40);
                    }
                    setHeader(writer);
                    writer.write(list, true);
                }
            } else {
                Integer num = 0;
                for (int i = 0; i < taskList.size(); i++) {
                    list = this.getActivityList(data.setTaskName(taskList.get(i)), setList());
                    titleList = this.getActivityList(data.setTaskName(taskList.get(i)), setTitleList());
                    if(!CollectionUtils.isEmpty(list)) {
                        //创建标题和表头信息
                        if (!CollectionUtils.isEmpty(titleList)) {
                            if(i==0) {
                                writer.merge(15, titleList.get(0).get("bumenchejian") + "评价记录", true);
                            }else {
                                writer.writeRow(new ArrayList<>());
                            }
                            writer.merge(3, StringUtils.join("单位:", titleList.get(0).get("bumenchejian")), true);
                            writer.writeRow(new ArrayList<>());
                            writer.setRowHeight(num + 2, 40);
                            layout(writer, titleList, num);
                        }else {
                            writer.merge(15, "评价记录", true);
                            writer.merge(3, StringUtils.join("单位:"), true);
                            writer.writeRow(new ArrayList<>());
                            writer.writeRow(new ArrayList<>());
                            layout(writer, num);
                        }
                        num += list.size() + 4;
                        setHeader(writer);
                        writer.write(list, true);
                    }
                }
            }
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            String name = new String("评价记录".getBytes("utf-8"), "iso-8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xls");
            response.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, "fileName");
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            IoUtil.close(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void setHeader(ExcelWriter writer) {
        Map<String, String> objectMap = new LinkedHashMap<>();
        objectMap.put("xuhao", "序号");
        objectMap.put("zuoyebuzhou", "作业步骤");
        objectMap.put("weixianyuanhuoqianza", "危险源");
        objectMap.put("zhuyaohouguo", "主要后果");
        objectMap.put("gongchengcuoshi", "工程控制");
        objectMap.put("guanlicuoshi", "管理措施");
        objectMap.put("peixunjiaoyu", "培训教育");
        objectMap.put("getifanghu", "个体防护");
        objectMap.put("yingjichuzhi", "应急处置");
        objectMap.put("lquzhi", "L");
        objectMap.put("equzhi", "E");
        objectMap.put("cquzhi", "C");
        objectMap.put("dzhi7", "D");
        objectMap.put("pingjiajibie", "F");
        objectMap.put("guankongjibie", "FF");
        objectMap.put("jianyixinzenggaijinc", "措施");
        writer.setHeaderAlias(objectMap);
    }
    public static void layout(ExcelWriter writer, List<Map<String, Object>> list, Integer i) {
        writer.merge(1 + i, 1 + i, 4, 10,
                StringUtils.join("风险点岗位:", list.get(0).get("fengxiandiangangwei"))
                        .replace("[", "").replace("]", ""), true);
        writer.merge(1 + i, 1 + i, 11, 15,
                StringUtils.join("工作任务:", list.get(0).get("fengxiandianmingchen")), true);
        commonLayout(writer, i);
    }
    public static void layout(ExcelWriter writer, Integer i) {
        writer.merge(1 + i, 1 + i, 4, 10,
                StringUtils.join("风险点岗位:")
                        .replace("[", "").replace("]", ""), true);
        writer.merge(1 + i, 1 + i, 11, 15,
                StringUtils.join("工作任务:"), true);
        commonLayout(writer, i);
    }
    public static void commonLayout(ExcelWriter writer, Integer i) {
        writer.merge(2 + i, 3 + i, 0, 0, "序号", true);
        writer.merge(2 + i, 3 + i, 1, 1, "作业步骤", true);
        writer.merge(2 + i, 3 + i, 2, 2, "危险源", true);
        writer.merge(2 + i, 3 + i, 3, 3, "主要后果", true);
        writer.merge(2 + i, 2 + i, 4, 8, "A", true);
        writer.merge(2 + i, 3 + i, 9, 9, "L", true);
        writer.merge(2 + i, 3 + i, 10, 10, "E", true);
        writer.merge(2 + i, 3 + i, 11, 11, "C", true);
        writer.merge(2 + i, 3 + i, 12, 12, "D", true);
        writer.merge(2 + i, 3 + i, 13, 13, "F", true);
        writer.merge(2 + i, 3 + i, 14, 14, "FF", true);
        writer.merge(2 + i, 3 + i, 15, 15, "措施", true);
    }