SpringBoot系列【SpringBoot集成EasyExcel实现导出Excel文件】

1.引入pom依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.2.8</version>
</dependency>

2.常用注解

2.1 @ExcelProperty

指定当前字段对应excel中的那一列。可以根据名字或者Index去匹配。当然也可以不写，默认第一个字段就是index=0，以此类推。千万注意，要么全部不写，要么全部用index，要么全部用名字去匹配。千万别三个混着用，除非你非常了解源代码中三个混着用怎么去排序的。

2.2 @ExcelIgnore

默认所有字段都会和excel去匹配，加了这个注解会忽略该字段

2.3 @DateTimeFormat

日期转换，用String去接收excel日期格式的数据会调用这个注解。里面的value参照java.text.SimpleDateFormat

2.4 @NumberFormat

数字转换，用String去接收excel数字格式的数据会调用这个注解。里面的value参照java.text.DecimalFormat

2.5 @ExcelIgnoreUnannotated

默认不加ExcelProperty 的注解的都会参与读写,加了不会参与

3.新建导出实体类

@Data
public class FinanceAccountDownloadExcel implements Serializable {

    @ExcelProperty(index = 0, value = "账户名")
    private String accountName;

    @ExcelProperty(index = 1, value = "账号")
    private String account;

    @ExcelProperty(index = 2, value = "账户类型")
    private String typeName;

    @ExcelProperty(index = 3, value = "账户余额")
    private BigDecimal total;

    @ExcelProperty(index = 4, value = "总收益")
    private BigDecimal totalRevenue;

    @ExcelProperty(index = 5, value = "账户用途")
    private String inOutTypeName;

    @ExcelProperty(index = 6, value = "理财产品")
    private String product;
}

4.编写接口

@GetMapping("/downloadExcel")
    public void downloadExcel(FinanceAccountParam param, HttpServletRequest request, HttpServletResponse response) throws IOException {
        param.setPageSize(10000);
        //获取数据
        Page<FinanceAccount> financeAccountPage = this.financeAccountService.pageAll(buildPage(param), param);
        List<FinanceAccount> records = financeAccountPage.getRecords();
        List<FinanceAccountDownloadExcel> financeAccountDownloadExcels = BeanUtil.copyToList(records, FinanceAccountDownloadExcel.class);

        String filename = "账户信息.xlsx";
//        String userAgent = request.getHeader("User-Agent");
//        if (userAgent.contains("MSIE")|| userAgent.contains("Trident")){
            //不编码，下载出来的文件名会乱码，这样写，谷歌，Microsoft，edge，360安全，火狐， Trident内核（IE内核）不会乱码，如果出现乱码，放开注释，查看User-Agent
            filename = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
//        }
//        else {
//            filename = new String(filename.getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
//        }
        response.setContentType("application/vnd.ms-exce");
        response.addHeader("Content-Disposition","filename="+ filename);
        response.setCharacterEncoding(StandardCharsets.UTF_8.toString());
        EasyExcel.write(response.getOutputStream(),FinanceAccountDownloadExcel.class).sheet("账户信息表").doWrite(financeAccountDownloadExcels);
    }