【ExcelUtil】实现文件写出到客户端下载全过程

发表于

前言

这是我参与11月更文挑战的第4天，活动详情查看：2021最后一次更文挑战 。

需求分析

将省份详情信息作为数据源导出 Excel，要求表头为中文，全部内容宽度实现自适应，并设置工作簿名称为省份表，导出文件名设置为导出省份信息。

代码实现

首先，分析表头字段包含哪些，写对应的 VO 对象：

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
java复制代码/**
 *@description: TODO 
 *@author: HUALEI
 *@date: 2021-11-21
 *@time: 19:29
 */
@Data
public class ProvinceExcelVO implements Serializable {

    private static final long serialVersionUID = 877981781678377000L;

    /**
     * 省份
     */
    private String province;

    /**
     * 省份的简称
     */
    private String abbr;

    /**
     * 省份的面积（km²）
     */
    private Integer area;

    /**
     * 省份的人口（万）
     */
    private BigDecimal population;

    /**
     * 省份的著名景点
     */
    private String attraction;

    /**
     * 省会的邮政编码
     */
    private String postcode;

    /**
     * 省会名
     */
    private String city;

    /**
     * 省会的别名
     */
    private String nickname;

    /**
     * 省会的气候类型
     */
    private String climate;

    /**
     * 省会的车牌号
     */
    private String carcode;
}

然后，就是根据所需信息写 SQL 进行查询，具体写法参见我的文章 【MP】还在用 QueryWrapper 吗？ - 掘金 (juejin.cn) ，具体写法不作赘述！

其次，写 Service 层，导出需要数据源，先得获取所有省份详情信息，作为导出 Excel 的数据源。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
java复制代码/**
 * 获取所有省份详情信息
 *
 * @return 导出 Excel 数据源
 */
List<ProvinceExcelVO> getAllProvinceDetails();

/**
 * 将省份详请信息以 Excel 的形式写出到客户端
 *
 * @param response HttpServletResponse 对象
 * @param dataSource 省份详情信息数据源
 * @param fileName 文件名
 * @param sheetName sheet 工作表名
 *
 * @throws IOException IO 流异常
 */
void exportProvinceDetailsExcel(HttpServletResponse response, List<ProvinceExcelVO> dataSource, String fileName, String sheetName) throws IOException;

有数据源还不行，还得将其写入到 Excel 当中，作为一个接口，调用后客户端下载该文件，所以要用到 HttpServletResponse 设置响应头和响应体信息。

接着，实现接口中的方法，这一步很关键，导出的格式、异常亦或是数据问题都出在这个地方，一定要考虑周全！！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
java复制代码@Override
public List<ProvinceExcelVO> getAllProvinceDetails() {
    List<Province> provinces = this.provinceMapper.selectByAll(new Province());

    if (CollUtil.isNotEmpty(CollUtil.removeNull(provinces))) {
        return provinces.stream()
                .map(p -> {
                    ProvinceExcelVO provinceExcelVO = new ProvinceExcelVO();
                    BeanUtil.copyProperties(p, provinceExcelVO);
                    BeanUtil.copyProperties(p.getCapital(), provinceExcelVO);
                    return provinceExcelVO;
                }).collect(Collectors.toList());
    }
    return null;
}

CollUtil.removeNull(provinces) 很关键！倘若数据库表中有空记录的话，这里不做移除就会引发 NPE 异常，这里做一个过滤操作，保险起见。

这里选择使用 BigWriter 而不是使用 Writer，主要是因为前者不容易引发内存溢出，对于大量数据的输出更为安全、可靠，虽然数据源的量并不大，用着舒心就完事了，并不用担心和 ExcelWriter 用法不一致。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
java复制代码@Override
public void exportProvinceDetailsExcel(HttpServletResponse response, List<ProvinceExcelVO> dataSource, String fileName, String sheetName) throws IOException {
    // 默认创建 xls 格式, 通过 isXlsx => true 创建 xlsx 格式
    // ExcelUtil.getWriter(true);
    ExcelWriter excelWriter = ExcelUtil.getBigWriter();

    // 设置表头别名
    excelWriter.addHeaderAlias("province", "省份名称");
    excelWriter.addHeaderAlias("abbr", "简称");
    excelWriter.addHeaderAlias("area", "面积（km²）");
    excelWriter.addHeaderAlias("population", "人口数量（万）");
    excelWriter.addHeaderAlias("attraction", "著名景点");
    excelWriter.addHeaderAlias("postcode", "邮政编码");
    excelWriter.addHeaderAlias("city", "省会城市");
    excelWriter.addHeaderAlias("nickname", "别称");
    excelWriter.addHeaderAlias("climate", "气候类型");
    excelWriter.addHeaderAlias("carcode", "车牌号");
    
    ......
    ......
}

设置表头别名，ExcelWriter 类提供了两种方法，一种是上面代码给出的，另一种则是通过键值对的方式进行设置，具体见 Hutool 工具不糊涂 - 掘金 (juejin.cn) ExcelUtil Excel 操作工具类，不过要注意的是使用无序的 HashMap 入参表头也跟着无序，需使用 LinkedHashMap 来保证保存的有序性。

image.png

表头别名设置好后，直接向 Excel 的 Workbook 中写入数据源即可，写入完后要响应客户端，写出文件到客户端，所以需要设置响应体和响应头，在响应头中传入导出文件名，如果是中文文件名的话，需要进行编码不能直接塞入，否则会出现文件名乱码问题，这时你就能看到弹出的下载框，点击后就能成功看到导出的数据列表了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
java复制代码
    excelWriter.write(dataSource, true);
    
    // response 为 HttpServletResponse 对象
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    
    // 中文文件名编码
    fileName = URLEncoder.encode(fileName, CharsetUtil.UTF_8) + ".xlsx";
    response.setHeader("Content-Disposition","attachment;filename="+ fileName);
    // 设置 Sheet 工作表名称
    excelWriter.renameSheet(sheetName);
    
    ServletOutputStream out = response.getOutputStream();
    excelWriter.flush(out, true);

    // 关闭 writer，释放内存
    excelWriter.close();
    // 关闭输出 Servlet 流
    IoUtil.close(out);

注意： 只有调用 flush 或者 close 方法后才会真正写出文件，并释放 Workbook 对象资源，否则带有数据的 Workbook 一直会常驻内存。

最后，在控制层暴露接口给前端进行调用：

1
2
3
4
5
6
7
8
9
10
11
java复制代码@GetMapping("provinces/excel/export")
public void provincesExcelExport(HttpServletResponse response) throws IOException {
    // 获取数据源
    List<ProvinceExcelVO> provinceExcelList = this.provinceService.getAllProvinceDetails();
    // 导出文件名
    String fileName = "导出省份信息";
    // Sheet 工作表名称
    String sheetName = "省份表";
    // 导出 Excel
    this.provinceService.exportProvinceDetailsExcel(response, provinceExcelList, fileName, sheetName);
}

0071E46E.gif

激动的心，颤抖的手，点击 SEND 按钮：

image.png

不看不知道，一看吓一跳：

image.png

摆在我眼前的问题该如何去解决呢？

一行代码 excelWriter.autoSizeColumnAll(); ？？并不能设置所有的列框为自动根据内容进行调整，于是被度娘告知需开启自动跟踪所有列自动调整大小，所以我们首先的获取当前 Sheet：

1
2
3
java复制代码
// 获取 sheet 表
SXSSFSheet sheet = (SXSSFSheet) excelWriter.getSheet();

这里需要将 Sheet 强转成 SXSSFSheet 类型，开启方法是属于 SXSSFSheet 类的

1
2
3
4
5
6
java复制代码
// 开启跟踪工作表中的所有列，以便自动调整大小
sheet.trackAllColumnsForAutoSizing();

// 列宽自适应，只有开启后才会生效
excelWriter.autoSizeColumnAll();

解决中文自适应宽度不足的问题，对已定义的每一列设置列宽：

1
2
3
4
5
6
7
8
9
10
java复制代码
// 获取表头行，作为数据的所属列
if (sheet.getRow(1) != null) {
    // 获取表头已定义单元格的数目
    int physicalNumberOfCells = sheet.getRow(1).getPhysicalNumberOfCells();
    for (int i = 0; i < physicalNumberOfCells; i++) {
        // 对已定义的每一列设置列宽，解决中文自适应宽度不足的问题
        sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
    }
}

接口测试

导出测试.gif

完美，撒花 ✿✿ヽ(°▽°)ノ✿

总结 & 思考

虽然实现了预期需求，但是我觉得还不够完美，代码不够优雅、简洁，并不具备通用性！！

如果导出的是其他数据列表呢，上述代码也不能照搬照抄的呀，所以说复用性较差，为了能优雅地对指定数据类型进行表格的导出，就必须对 Hutool 中的 ExcelUtil 进行二次封装或者自己站在 POI 包上造轮子，考虑到发量我毅然决然选择第一种方案，这波直接站在巨人的巨人的肩膀上进行开发。

20190825677236_pFKAVu.gif

考虑到 导出的效率 、 功能扩展性 以及 使用便捷性 ，在封装的基础上写一个自定义 @Excel 注解，实现一“解”多用，麻麻再也不用担心我不会写 Excel 导出接口了 (＾－＾)V

具体实现代码详解，请参考 【ExcelUtil】二次封装，注解驱动，用起来不要太舒服！ - 掘金 (juejin.cn) 。

对 Hutool 工具包中常用工具类（ExcelUtil…）有疑问，移步至 Hutool 工具不糊涂 - 掘金 (juejin.cn) 。

结尾

撰文不易，欢迎大家点赞、评论，你的关注、点赞是我坚持的不懈动力，感谢大家能够看到这里！Peace & Love。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%