如何使用 Hutool 创建通用导出 Excel 工具类

与旧日火葬

于 2024-07-19 02:54:23 发布

阅读量40

点赞数 1

文章标签： excel windows microsoft

ps：该工具类支持导出excel，分页，下拉框，合并单元格

Hutool 简介

Hutool是一个轻量级的Java工具包，提供了许多实用的功能，包括字符串处理、日期时间操作、文件操作、加密解密、Excel操作等等。它的设计简洁而高效，使得开发者能够更专注于业务逻辑而不是底层实现细节。

步骤一：导入Hutool依赖

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.x.x</version> <!-- 使用最新版本 -->
</dependency>

步骤二：创建Excel导出工具类

一，创建通用导出实体CommonDTO

@Data
@Schema(description = "通用导出")
public class CommonDTO {

//    @NotEmpty(message = "表头不能为空")
    @Schema(description = "表头(字段-名称对应关系)")
    Map<String,String> header;

//    @NotEmpty(message = "数据不能为空")
    @Schema(description = "数据(字段-值对应关系)")
    List<Map<String, Object>> data;

//    @NotBlank(message = "文件名不能为空")
    @Schema(description = "文件名")
    String name;

    @Schema(description = "下拉框")
    List<dropDown> dropDown;

    //设置需要合并单元格信息
    @Schema(description = "合并单元格")
    List<mergeCell>  mergeCell;

    //分页
    @Schema(description = "分页")
    List<pageBO> pageBo;

}

2，创建下拉框实体dropDown

@Data
public class dropDown {
    // 设置下拉框的起始行
    int firstRow;
    // 设置下拉框的结束行
    int lastRow;
    // 设置下拉框的起始列
    int firstCol;
    int lastCol;
    // 设置下拉框的值
    String[] dataValidationValues;
}

3，创建合并单元格实体mergeCell

@Data
public class mergeCell {
    int firstRow;
    // 设置合并单元格的结束行
    int lastRow;
    // 设置合并单元格的起始列
    int firstCol;
    int lastCol;
    // 设置合并单元格的值
    String mergeCellInfo;
}

4，创建分页实体pageBO

@Data
public class pageBO {
    // 设置sheet页码
    int pageNameRow;
    // 设置sheet页名称
    String pageNameValue;
}

5，创建导出到一页工具类

@SneakyThrows
    public void export(@RequestBody @Valid CommonDTO commonDTO, HttpServletResponse response) {

        ExcelWriter writer = ExcelUtil.getWriter();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        writer.renameSheet(0, "学生表");
        writer.renameSheet(0, "班级表");
//选定sheet页
        writer.setSheet("学生表");
        //设置表头样式
        writer.setHeaderAlias(commonDTO.getHeader());
        String[] headString = new String[]{};
        //遍历 commonDTO.header.keySet()将数据添加到headString
        for (String key : commonDTO.getHeader().keySet()) {
            headString = ArrayUtils.add(headString, commonDTO.getHeader().get(key));
        }
        List<String> headerList = CollUtil.newArrayList(headString);

        writer.writeHeadRow(headerList);
        //设置表头样式
        writer.getHeadCellStyle().setFont(StyleUtil.createFont(writer.getWorkbook(), (short) 0, (short) 12, "微软雅黑"));
        //设置内容样式
        writer.getStyleSet().setFont((short) 0, (short) 10, "微软雅黑", Boolean.TRUE);

//        合并单元格
        if (commonDTO.getName() != null) {
            //设置响应头
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(commonDTO.getName(), "UTF-8") + ".xls");
        } else {
            //设置响应头
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode("默认文件名", "UTF-8") + ".xls");
        }

//合并单元格
        if (commonDTO.getMergeCell() != null && commonDTO.getMergeCell().size() > 0) {
            for (int i = 0; i < commonDTO.getMergeCell().size(); i++) {
                writer.merge(commonDTO.getMergeCell().get(i).getFirstRow(),
                        commonDTO.getMergeCell().get(i).getLastRow(),
                        commonDTO.getMergeCell().get(i).getFirstCol(),
                        commonDTO.getMergeCell().get(i).getLastCol(),
                        commonDTO.getMergeCell().get(i).getMergeCellInfo(), Boolean.TRUE);
            }
        }
        //设置下拉框
        Sheet sheet = writer.getSheet();
        if (commonDTO.getDropDown() != null && commonDTO.getDropDown().size() > 0) {
            for (int i = 0; i < commonDTO.getDropDown().size(); i++) {
                dropDown dropDown = commonDTO.getDropDown().get(i);
                CellRangeAddressList addressList = new CellRangeAddressList(dropDown.getFirstRow(), dropDown.getLastRow(), dropDown.getFirstCol(), dropDown.getLastCol()); // 指定单元格范围，这里是第一行第一列
                // 创建下拉框数据
                DataValidationHelper validationHelper = sheet.getDataValidationHelper();
                DataValidationConstraint dvConstraint = validationHelper.createExplicitListConstraint(dropDown.dataValidationValues); // 设置下拉框选项
                DataValidation dataValidation = validationHelper.createValidation(dvConstraint, addressList); // 创建数据验证对象
                sheet.addValidationData(dataValidation); // 添加验证对象到工作表
            }
        }
        //写入数据
        writer.write(commonDTO.getData(), true);
        //设置自适应列宽
        writer.autoSizeColumnAll();
        writer.flush(response.getOutputStream(), true);
        // 关闭writer，释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(response.getOutputStream());
    }

6，创建分页工具类

因为分页每一页分页数据存放不一样，暂时未做出通用的

@SneakyThrows
    public void export2(@RequestBody @Valid CommonDTO commonDTO, HttpServletResponse response) {

        ExcelWriter writer = ExcelUtil.getWriter();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        List<pageBO> pageBo1 = commonDTO.getPageBo();
        for (int j = 0; j < pageBo1.size(); j++) {
            pageBO pageBO = pageBo1.get(j);
            int sheetIndex = j;
//选定sheet页
            writer.setSheet(pageBo1.get(sheetIndex).getPageNameValue());

            //设置表头样式
            writer.setHeaderAlias(commonDTO.getHeader());
            String[] headString = new String[]{};
            //遍历 commonDTO.header.keySet()将数据添加到headString
            for (String key : commonDTO.getHeader().keySet()) {
                headString = ArrayUtils.add(headString, commonDTO.getHeader().get(key));
            }
            List<String> headerList = CollUtil.newArrayList(headString);

            writer.writeHeadRow(headerList);
            //设置表头样式
            writer.getHeadCellStyle().setFont(StyleUtil.createFont(writer.getWorkbook(), (short) 0, (short) 12, "微软雅黑"));
            //设置内容样式
            writer.getStyleSet().setFont((short) 0, (short) 10, "微软雅黑", Boolean.TRUE);

//        合并单元格
            if (commonDTO.getName() != null) {
//            writer.merge(0,0,0,1, "基本信息", Boolean.TRUE);
//            writer.merge(0,0,2,3, "信息", Boolean.TRUE);

                //设置响应头
                response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(commonDTO.getName(), "UTF-8") + ".xls");
            } else {
                //设置响应头
                response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode("默认文件名", "UTF-8") + ".xls");
            }

//合并单元格
            if (commonDTO.getMergeCell() != null && commonDTO.getMergeCell().size() > 0) {
                for (int i = 0; i < commonDTO.getMergeCell().size(); i++) {
                    writer.merge(commonDTO.getMergeCell().get(i).getFirstRow(),
                            commonDTO.getMergeCell().get(i).getLastRow(),
                            commonDTO.getMergeCell().get(i).getFirstCol(),
                            commonDTO.getMergeCell().get(i).getLastCol(),
                            commonDTO.getMergeCell().get(i).getMergeCellInfo(), Boolean.TRUE);
                }
            }
            //设置下拉框
            Sheet sheet = writer.getSheet();
            if (commonDTO.getDropDown() != null && commonDTO.getDropDown().size() > 0) {
                for (int i = 0; i < commonDTO.getDropDown().size(); i++) {
                    dropDown dropDown = commonDTO.getDropDown().get(i);
                    CellRangeAddressList addressList = new CellRangeAddressList(dropDown.getFirstRow(), dropDown.getLastRow(), dropDown.getFirstCol(), dropDown.getLastCol()); // 指定单元格范围，这里是第一行第一列
                    // 创建下拉框数据
                    DataValidationHelper validationHelper = sheet.getDataValidationHelper();
                    DataValidationConstraint dvConstraint = validationHelper.createExplicitListConstraint(dropDown.dataValidationValues); // 设置下拉框选项
                    DataValidation dataValidation = validationHelper.createValidation(dvConstraint, addressList); // 创建数据验证对象
                    sheet.addValidationData(dataValidation); // 添加验证对象到工作表
                }
            }
            List<Map<String, Object>> data = commonDTO.getData();
            List<Map<String, Object>> sheetData = new ArrayList<>();
            for (Map<String, Object> datum : data) {
                String sex = datum.get("sex") + "";
                if (pageBO.getPageNameValue().equals(sex)) {
                    sheetData.add(datum);
                }
            }
            writer.write(sheetData, true);
            //设置自适应列宽
            writer.autoSizeColumnAll();
        }
        //删除sheet1，因为sheet1为空
        writer.getWorkbook().removeSheetAt(0);
        writer.flush(response.getOutputStream(), true);
        // 关闭writer，释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(response.getOutputStream());
    }

步骤三：创建测试类

//测试生成文档
    @PostMapping(value = "/test1", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public void test1(HttpServletResponse response) {
        CommonDTO commonDTO = new CommonDTO();
        commonDTO.setName("测试");
        LinkedHashMap<String, String> StringHashMap = new LinkedHashMap<>();

        StringHashMap.put("name", "姓名");

        StringHashMap.put("age", "年龄");

        StringHashMap.put("sex", "性别");

        StringHashMap.put("address", "地址");

        commonDTO.setHeader(StringHashMap);
        ArrayList<Map<String, Object>> arrayList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            Map<String, Object> HashMap = new HashMap<>();
            HashMap.put("name", "张三" + i);

            HashMap.put("age", "20" + i);

            HashMap.put("sex", "男");

            HashMap.put("address", "北京" + i);

            arrayList.add(HashMap);
        }
        for (int i = 0; i < 5; i++) {
            Map<String, Object> HashMap = new HashMap<>();
            HashMap.put("name", "李四" + i);

            HashMap.put("age", "20" + i);

            HashMap.put("sex", "女");

            HashMap.put("address", "北京" + i);

            arrayList.add(HashMap);
        }
//        for (int i = 0; i < 1; i++) {
//            Map<String, Object> HashMap = new HashMap<>();
//            HashMap.put("name","");
//
//            HashMap.put("age","");
//
//            HashMap.put("sex","");
//
//            HashMap.put("address","");
//
//            arrayList.add(HashMap);
//        }

//        设置下拉框值
        List<dropDown> dropDownList = new ArrayList<>();
        dropDown dropDown = new dropDown();
        dropDown.firstRow = 1;

        // 设置下拉框的结束行
        dropDown.lastRow = 500;

        // 设置下拉框的起始列
        dropDown.firstCol = 2;

        dropDown.lastCol = 2;
        dropDown.dataValidationValues = new String[]{"男", "女"};
        dropDownList.add(dropDown);

        dropDown dropDown2 = new dropDown();
        dropDown2.firstRow = 1;

        // 设置下拉框的结束行
        dropDown2.lastRow = 500;

        // 设置下拉框的起始列
        dropDown2.firstCol = 3;

        dropDown2.lastCol = 3;
        dropDown2.dataValidationValues = new String[]{"北京", "上海"};

        dropDownList.add(dropDown2);
        commonDTO.setDropDown(dropDownList);

        //设置合并单元格
        List<mergeCell> mergeCellList = new ArrayList<>();
        mergeCell mergeCell = new mergeCell();
        mergeCell.firstRow = 0;
        mergeCell.lastRow = 0;
        mergeCell.firstCol = 0;
        mergeCell.lastCol = 1;
        mergeCell.mergeCellInfo = "合并单元格标题1";
        mergeCellList.add(mergeCell);
        mergeCell mergeCell2 = new mergeCell();
        mergeCell2.firstRow = 0;
        mergeCell2.lastRow = 0;
        mergeCell2.firstCol = 2;
        mergeCell2.lastCol = 3;
        mergeCell2.mergeCellInfo = "合并单元格标题2";
        mergeCellList.add(mergeCell2);

        commonDTO.setMergeCell(mergeCellList);


        commonDTO.setData(arrayList);
        List<pageBO> pageBOList = new ArrayList<>();
        pageBO pageBO = new pageBO();
        pageBO.setPageNameRow(0);
        pageBO.setPageNameValue("男");
        pageBOList.add(pageBO);
        pageBO pageBO2 = new pageBO();
        pageBO2.setPageNameRow(1);
        pageBO2.setPageNameValue("女");
        pageBOList.add(pageBO2);


        commonDTO.setPageBo(pageBOList);
//        export(commonDTO,response);
        export2(commonDTO, response);
    }

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.

步骤四：postman模拟

如何使用 Hutool 创建通用导出 Excel 工具类_Hutool库

结论

通过 Hutool 提供的强大功能，我们可以轻松创建一个通用的导出 Excel 工具类，实现数据导出到 Excel 文件的功能。这种做法不仅提高了开发效率，还使得代码更加清晰和可维护。

希望本文对你有所帮助，如有任何问题或建议，请随时留言！

原创作者: u_16524317 转载于: https://blog.51cto.com/u_16524317/11491639

与旧日火葬

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
如何使用 Hutool 创建通用导出 Excel 工具类

ps：该工具类支持导出excel，分页，下拉框，合并单元格Hutool 简介Hutool是一个轻量级的Java工具包，提供了许多实用的功能，包括字符串处理、日期时间操作、文件操作、加密解密、Excel操作等等。它的设计简洁而高效，使得开发者能够更专注于业务逻辑而不是底层实现细节。步骤一：导入Hutool依赖登录后复制 &...
复制链接

扫一扫