1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-spring-boot-starter -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.5.0</version>
    <exclusions>
        <exclusion>
            <artifactId>hutool-all</artifactId>
            <groupId>cn.hutool</groupId>
        </exclusion>
    </exclusions>
</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

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;

/**
 * easyPoi框架导出excel
 */
@SneakyThrows
private void easyPoiExportExcel(HttpServletResponse response, List<DemoVO> demoVoList) {
    // 构建excel对象数据
    AtomicInteger index = new AtomicInteger(1);
    List<WriteVo> excelList = Optional.ofNullable(demoVoList).orElse(new ArrayList<>())
            .stream().map(s -> {
                WriteVo excel = new WriteVo();
                BeanUtils.copyProperties(s, excel);
                excel.setNumber(index.getAndIncrement());
                return excel;
            })
            .collect(Collectors.toList());

    Workbook sheets = ExcelExportUtil.exportExcel(new ExportParams("title", "sheetName"), DemoEasyExcelExcelVO.class, excelList);
    // 构建response
    response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode("fileName", StandardCharsets.UTF_8)
            .replaceAll("\\+", "%20")
            .replaceAll("%2F", "/")
            + ".xlsx");
    response.setContentType("application/vnd.ms-excel"); // application/json application/pdf
    response.setCharacterEncoding(StandardCharsets.UTF_8.name());
    sheets.write(response.getOutputStream());
    sheets.close();
}

 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

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class WriteVo {

//    @Excel(name = "开始时间",width = 15.0,importFormat = "HH:mm",exportFormat = "HH:mm")

    // 隐藏主键列
    @Excel(name = "id", width = 20.0, isColumnHidden = true)
    private Long id;

    @Excel(name = "序号", width = 20.0)
    private Integer number;

    @Excel(name = "开始日", format = "yyyy-MM-dd", width = 20.0)
    private LocalDate startDate;

    @Excel(name = "开始时间", format = "yyyy-MM-dd HH:mm:ss", width = 20.0)
    private LocalDateTime startTime;

    @Excel(name = "换电模式*", width = 20.0, replace = {"后背电池换电_0", "底盘电池换电_1", "_null"})
    private String swapMode;

    @Excel(name = "金额", numFormat = "0.00")
    private BigDecimal amount;

}

 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

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;

/**
 * easyPoi框架导出excel 使用模板
 */
@SneakyThrows
private void easyPoiExportExcelWithTemplate(HttpServletResponse response) {
    // 构建map
    Map<Integer, Map<String, Object>> map = new HashMap<>();
    HashMap<String, Object> sheet1 = new HashMap<>();
    sheet1.put("key1", "value1");
    sheet1.put("key2", "value2");
    map.put(0, sheet1);
    HashMap<String, Object> sheet2 = new HashMap<>();
    sheet2.put("key1", "value1");
    sheet2.put("key2", "value2");
    map.put(1, sheet2);
    // 创建临时文件,将模板文件内容复制到临时文件
    Path tempPath;
    try (FileInputStream fis = new FileInputStream(this.getClass().getResource("/").getPath() + "换电站运营周报表模板.xlsx")) {
        File tempFile = File.createTempFile("temp", ".xlsx");
        tempPath = tempFile.toPath();
        Files.copy(fis, tempPath, StandardCopyOption.REPLACE_EXISTING);
    }
    // 对临时文件写值,再写到ServletOutputStream
    TemplateExportParams templateExportParams = new TemplateExportParams(tempPath.toString(), map.keySet().toArray(new Integer[]{}));
    Workbook sheets = ExcelExportUtil.exportExcel(map, templateExportParams);
    sheets.write(response.getOutputStream());
    sheets.close();
    // 构建response
    response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode("fileName", StandardCharsets.UTF_8)
            .replaceAll("\\+", "%20")
            .replaceAll("%2F", "/")
            + ".xlsx");
    response.setContentType("application/vnd.ms-excel"); // application/json application/pdf
    response.setCharacterEncoding(StandardCharsets.UTF_8.name());
}