前后端分离下EasyExcel的使用

项目环境:SpringBoot+Vue

依赖导入

<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.2</version>
</dependency>

tips

3.0.1版本 @ColumnWidth失效问题 用其他版本即可

实体类关联Excel

@ExcelProperty：value属性可用来设置表头名称
@ExcelPropertyvalue属性可用来设置表头名称

点击查看代码

@TableName(value = "five_insurances")
@Data
public class FiveInsurances implements Serializable {
    /**
     *
     */
    @ExcelProperty("编号")
    @ColumnWidth(10)
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 工号
     */
    @ExcelProperty("工号")
    @ColumnWidth(15)
    @TableField(value = "number")
    private String number;

    /**
     * 姓名
     */
    @ExcelProperty("姓名")
    @ColumnWidth(20)
    @TableField(value = "name")
    private String name;

    /**
     * 部门id
     */
    @ExcelProperty("部门")
    @ColumnWidth(20)
    @TableField(value = "dept_id")
    private Integer deptId;

    /**
     * 电话
     */
    @ExcelProperty("电话")
    @ColumnWidth(20)
    @TableField(value = "phone")
    private String phone;

    /**
     * 缴纳基数
     */
    @ExcelProperty({"社保", "缴纳基数"})
    @ColumnWidth(20)
    @TableField(value = "base_payment")
    private String basePayment;

    /**
     * 个人缴纳
     */
    @ExcelProperty({"社保", "个人", "缴纳费用"})
    @ColumnWidth(20)
    @TableField(value = "self_payment")
    private String selfPayment;

    /**
     * 工伤保险缴纳比例
     */
    @ExcelProperty({"社保", "企业", "工伤保险缴纳比例"})
    @ColumnWidth(20)
    @TableField(value = "ratio")
    private String ratio;

    /**
     * 企业缴纳
     */
    @ExcelProperty({"社保", "企业", "缴纳费用"})
    @ColumnWidth(20)
    @TableField(value = "com_payment")
    private String comPayment;

    /**
     * 备注
     */
    @ExcelProperty({"社保", "备注"})
    @ColumnWidth(30)
    @TableField(value = "remarks")
    private String remarks;

    @ExcelIgnore
    @TableField(exist = false)
    private Dept dept;

    @ExcelIgnore
    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

导出Excel

Controller

@GetMapping("/fihf")
@ApiOperation(value = "导出五险一金列表Excel")
@ApiImplicitParams(
            @ApiImplicitParam(dataType = "Interger",name = "page",value = "page==-1:查询所有;page==-2,返回空模板",required = false)
    )
public void exportList(HttpServletResponse response, @RequestParam(value = "page", defaultValue = "1") Integer page) throws Exception {
        PageBean<List<FiveInsurances>> pageBean = fiveInsurancesService.selectFiveInsurancesList(page);
        ExcelUtils.exportToWeb(response,"sheet1",FiveInsurances.class,pageBean.getData());
    }

前端Axios请求

exportFile(page = this.pageBean.current) {
    //复选框选中则设置page为-1,表示导出全部
    if (this.checked) {
        page = -1;
    }
    //关闭对话框
    this.dialogVisible = false;
    this.axios({
        method: 'get',
        url: baseURL + "fileExport/fihf",
        params: {
            page: page,
        },
        responseType: 'blob' //响应类型须设置为二进制文件流
    }).then((res) => {
        if (!res) {
            return
        }
        const link = document.createElement("a");//创建a标签
        let blob = new Blob([res.data], { type: "multipart/form-data" }); //设置