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

港控/mmm° 2022-11-01 00:58 60阅读 0赞

引入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);
        }