每日归档： 2022年8月18日

Java: EasyExcel填充并导出多个sheet

java标志
image-3504

描述: 根据模板文件填充数据,导出单个文件,但单个文件包含多个sheet(模板一致,只是填充的内容有差异).

依赖:

1
2
3
4
5
6
7
8
9
10
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.22</version>
</dependency>

方法:

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
  public void export(HttpServletResponse response, String ids)
      throws IOException {
    var fileClassPath =
        "classpath:export_template" + File.separator + "template.xlsx";

    var idsArray = ids.split(",");
    var terminalIdsLength = idsArray.length;
    // 一定要复制一个临时新文件再操作,不然poi会一致提示sheet name重复!
    //
    // 临时文件File
    var tempFileClasPath =
        ResourceUtils.getURL(ResourceUtils.CLASSPATH_URL_PREFIX).getPath()
            + File.separator
            + "export_template"
            + File.separator
            + UUID.randomUUID()
            + "-template.xlsx";
    File newTempFile = new File(tempFileClasPath);
    if (newTempFile.exists()) {
      newTempFile.createNewFile();
    }
    Resource resource = new ClassPathResource(fileClassPath);
    InputStream resourceInputStream = null;
    File templateFile = null;
    if (resource.exists()) {
      resourceInputStream = resource.getInputStream();
    } else {
      templateFile = ResourceUtils.getFile(fileClassPath);
    }
    XSSFWorkbook xssWorkbook = null;
    if (terminalIdsLength > 0) {
      ExcelWriter excelWriter = null;

      try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
        if (resourceInputStream != null) {
          FileCopyUtils.copy(resource.getFile(), newTempFile);
        } else {
          FileCopyUtils.copy(templateFile, newTempFile);
        }
        xssWorkbook = new XSSFWorkbook(newTempFile);
        xssWorkbook.setSheetName(0, idsArray[0]);
        for (int i = 1; i < terminalIdsLength; i++) {
          var sheetName = idsArray[i];
          xssWorkbook.cloneSheet(0, sheetName);
        }

        xssWorkbook.write(bos);
        byte[] bArray = bos.toByteArray();
        InputStream is = new ByteArrayInputStream(bArray);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=export.xlsx");

        // 读取模板并进行填充
        excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(is).build();


        for (var id : idsArray) {
          if (StringUtils.hasText(id)) {
            // 查询数据.
            var tOrder = orderService.selectOrderById(id);
            if (tOrder != null) {
              var userName = tOrder.getUserName();
              var phone = tOrder.getPhone();
              if (StringUtils.hasText(userName) && StringUtils.hasText(phone)) {
                ExportVo exportVo = new ExportVo();
                BeanUtils.copyProperties(tOrder, exportVo);
                // 设置给指定的sheet
                WriteSheet writeSheet = EasyExcel.writerSheet(id).build();
                excelWriter.fill(exportVo, writeSheet);
              }
            }
          }
        }
        excelWriter.finish();
      } catch (InvalidFormatException e) {
        log.error("导出异常,消息: {}",e.getMessage());
      } finally {
        if (excelWriter != null) {
          excelWriter.close();
        }
        if (xssWorkbook != null) {
          xssWorkbook.close();
        }
        // 用完把临时新文件删除.
        if (newTempFile.exists()) {
          newTempFile.delete();
        }
      }
    }
  }