35-ExcelKit导出

Spring Booot +Vue 实现导出Excel表格功能

假设在各种环境都搭建好的情况下，引入使用第三方插件 ExcelKit

• 引入依赖

<dependency>
    <groupId>com.wuwenze</groupId>
    <artifactId>ExcelKit</artifactId>
    <version>2.0.72</version>
</dependency>
<dependency>
    <groupId>xml-apis</groupId>
    <artifactId>xml-apis</artifactId>
    <version>1.4.01</version>
</dependency>

<!-- mysql、 mybatis、druid、lombok省略-->

• 配置实体类（对应数据库中用户表）

@Data
@Excel("user")
@Table(name = "tb_user")
public class User {
    @Id
    @ExcelField(value = "编号", width = 50)
    private Long id;

    @ExcelField(value = "用户名", width = 100)
    private String username;

    @ExcelField(value = "昵称", width = 100)
    private String nickname;

    @ExcelField(value = "邮箱", width = 150)
    private String email;

    @ExcelField(value = "电话号码", width = 100)
    private String phoneNumber;

    private Integer status;

    @ExcelField(value = "创建时间", dateFormat = "yyyy年MM月dd日 HH:mm:ss", width = 180)
    private Date createTime;

    @ExcelField(value = "修改时间", dateFormat = "yyyy年MM月dd日 HH:mm:ss",width = 180)
    private Date modifiedTime;

    @ExcelField(
            value = "性别",
            readConverterExp = "男=1,女=0",
            writeConverterExp = "1=男,0=女"
            ,width = 50
    )
    private Integer sex;

    @ExcelField(value = "密码盐值", width = 100)
    private String salt;

    @ExcelField(//
            value = "用户类型",
            readConverterExp = "超级管理员=0,普通用户=1",
            writeConverterExp = "0=超级管理员,1=普通用户"
            ,width = 80
    )
    private Integer type;

    @ExcelField(value = "用户密码", width = 100)
    private String password;

    @ExcelField(value = "出生日期", dateFormat = "yyyy/MM/dd",width = 100)
    private Date birth;
	
    private Long departmentId;//不导出该字段

    @ExcelField(value = "头像url", width = 200)
    private String avatar;
}

• 编写Controller接口

@RestController
@RequestMapping("/system/user")
public class UserController {
   
    @Autowired
    private UserService userService;
    
     /**
     * 导出excel
     */
    @PostMapping("/excel")
    public void export(HttpServletResponse response) {
        List<User> users = this.userService.findAll();
        ExcelKit.$Export(User.class, response).downXlsx(users, false);//此行是主要代码
    }
}

• Vue前台代码

<el-button @click="downExcel" icon="el-icon-download">导出</el-button>

<script>
    export default{
methods: {
    /**
    * 导出用户表格
    */
    downExcel() {
        const $this = this;
        const res = axios
        .request({
            url: "system/user/excel",
            method: "post",
            responseType: "blob"
        })
        .then(res => {
            if (res.headers["content-type"] === "application/json") {
                return $this.$message.error(
                    "Subject does not have permission [user:export]"
                );
            }
            const data = res.data;
            let url = window.URL.createObjectURL(data); // 将二进制文件转化为可访问的url
            const a = document.createElement("a");
            document.body.appendChild(a);
            a.href = url;
            a.download = "用户列表.xls";
            a.click();
            window.URL.revokeObjectURL(url);
        });
    },
  }
}
</script>

• 测试

点击导出按钮就可以下载了，打开表格查看用户信息。