Springboot 项目 使用 Easypoi 导入和导出 excel操作

Springboot 项目 使用 Easypoi 导入和导出 excel操作

一句话介绍 easypoi

easypoi 在apache 的poi 基础之上 封装了一些操作，使得操作数据更加方便

准备工作

• 准备数据库（如果没有准备数据库也可以不连）
• 导入依赖

<dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-base</artifactId>
     <version>3.2.0</version>
 </dependency>
 <dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-web</artifactId>
     <version>3.2.0</version>
 </dependency>
 <dependency>
     <groupId>cn.afterturn</groupId>
     <artifactId>easypoi-annotation</artifactId>
     <version>3.2.0</version>
</dependency>

• 如果项目启动报错，可能缺少google的依赖

<dependency>
	<groupId>com.google.guava</groupId>
	<artifactId>guava</artifactId>
	<version>25.1-jre</version>
</dependency>

• 代码中不理解的注解可以百度或者参考官当文档

导入excel 数据

这里是以导入用户信息举例，其他的大家自己进行转换

用户实体类（注意一定要序列化，最方便的还是Lombok 的注解）

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;

import java.io.Serializable;

public class ImportUser implements Serializable {

    @ExcelIgnore
    private String UserId;
    @Excel(name = "用户名")
    private String userName;
    @Excel(name = "密码")
    private String password;
    @Excel(name = "邮箱")
    private String email;
    @Excel(name = "邮箱是否验证",replace = {"是_1","否_0"})
    private Integer emailVerified;
    @Excel(name = "手机号")
    private String phone;
    @Excel(name = "unionId")
    private String unionId;
    @Excel(name = "openid")
    private String openId;
    @Excel(name = "昵称")
    private String nickName;
    @Excel(name = "公司")
    private String company;
    @Excel(name = "性别",replace = {"男_1","女_0","未知_2"})
    private Integer gender;
    @Excel(name = "地址")
    private String address;
    @Excel(name = "城市")
    private String city;
    @Excel(name = "省份")
    private String province;
    @Excel(name = "国家")
    private String country;

    public String getUserId() {
        return UserId;
    }

    public void setUserId(String userId) {
        UserId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getEmailVerified() {
        return emailVerified;
    }

    public void setEmailVerified(Integer emailVerified) {
        this.emailVerified = emailVerified;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getUnionId() {
        return unionId;
    }

    public void setUnionId(String unionId) {
        this.unionId = unionId;
    }

    public String getOpenId() {
        return openId;
    }

    public void setOpenId(String openId) {
        this.openId = openId;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    @Override
    public String toString() {
        return "ImportUser{" +
                "UserId='" + UserId + '\'' +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                ", emailVerified=" + emailVerified +
                ", phone='" + phone + '\'' +
                ", unionId='" + unionId + '\'' +
                ", openId='" + openId + '\'' +
                ", nickName='" + nickName + '\'' +
                ", company='" + company + '\'' +
                ", gender=" + gender +
                ", address='" + address + '\'' +
                ", city='" + city + '\'' +
                ", province='" + province + '\'' +
                ", country='" + country + '\'' +
                '}';
    }

    public ImportUser() {
    }

    public ImportUser(String userId, String userName, String password, String email, Integer emailVerified, String phone, Integer phoneVerified, String unionId, String openId, String nickName, String company, Integer gender, String address, String city, String province, String country) {
        UserId = userId;
        this.userName = userName;
        this.password = password;
        this.email = email;
        this.emailVerified = emailVerified;
        this.phone = phone;
        this.unionId = unionId;
        this.openId = openId;
        this.nickName = nickName;
        this.company = company;
        this.gender = gender;
        this.address = address;
        this.city = city;
        this.province = province;
        this.country = country;
    }
}

• 这里省略Mapper和Service

Controller（接口）

 @RequestMapping(value = "/importUser",method = RequestMethod.POST)
 @ResponseBody
 public void importUser(MultipartFile excelFile) throws Exception {
     ImportParams importParams = new ImportParams();
     importParams.setHeadRows(1);
     List<ImportUser> list = ExcelImportUtil.importExcel(excelFile.getInputStream(), ImportUser.class, importParams);
     for (ImportUser insertUser: list) {
         insertUser.setUserId(RandomId.createId());
         appUserService.importUser(insertUser);
     }
     System.out.println("-------------------------导入完成------------------------");

 }

• 到这里为止导入操作就OK了

导出 excel 数据

导出实体类

package com.jerry.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


import java.io.Serializable;
import java.util.Date;

@ExcelTarget("users")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AppUser implements Serializable {
    @ExcelIgnore
    private String userId;
    @Excel(name = "用户名",orderNum = "1")
    private String userName;
    @Excel(name = "密码",orderNum = "8")
    private String password;
    @Excel(name = "邮箱",orderNum = "2")
    private String email;
    @Excel(name = "邮箱是否验证",replace = {"是_1","否_0"},orderNum = "3")
    private Integer emailVerified;
    @Excel(name = "手机号",orderNum = "4")
    private String phone;
    @Excel(name = "手机号是否验证",replace = {"是_1","否_0"},orderNum = "5")
    private Integer phoneVerified;
    @Excel(name = "unionid",orderNum = "6")
    private String unionId;
    @Excel(name = "openid",orderNum = "7")
    private String openId;
    @Excel(name = "昵称",orderNum = "0")
    private String nickName;
    private long tokenExpiredAt;
    @Excel(name = "登录次数",orderNum = "10")
    private Integer loginsCount;
    @Excel(name = "上次登录时间",orderNum = "11",format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
    private Date lastLogin;
    @Excel(name = "注册时间",orderNum = "9",format = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
    private Date signUp;
    @Excel(name = "上次登录ip",orderNum = "12")
    private String lastIp;    
    @Excel(name = "公司",orderNum = "13")
    private String company;    
    @Excel(name = "性别",orderNum = "14",replace = {"男_1","女_0","未知_2"})
    private Integer gender;    
    @Excel(name = "地址",orderNum = "18")
    private String address;  
    @Excel(name = "城市",orderNum = "17")
    private String city;
    @Excel(name = "省份",orderNum = "16")
    private String province;
    @Excel(name = "国家",orderNum = "15")
    private String country;     
}

Controller（接口）

• 导出全部用户

    @RequestMapping(value = "/exportAll",method = RequestMethod.POST)
    @ResponseBody
    public void export(HttpServletResponse response) throws IOException {
        List<AppUser> allUser = appUserService.getAllUser();
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), AppUser.class, allUser);
        String fileName = Long.toString(new Date().getTime());
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition", "attachment;fileName="+ URLEncoder.encode(fileName+".xls","UTF-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

• 导出指定（选中）部分

	@RequestMapping(value = "/exportSelected",method = RequestMethod.POST)
    @ResponseBody
    public void exportSelect(@RequestBody SelectUserRequest selectUserRequest, HttpServletResponse response) throws IOException {
        List<AppUser> selectUsers = new ArrayList<>();
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), AppUser.class, selectUsers);
        //将输出流传递到请求
        String fileName = new Date().toString()+".xls";
        response.setHeader("content-disposition", "attachment;fileName=" + new String(fileName.getBytes("utf-8"),"ISO8859-1"));
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }

• Tip：以上是本人在实际项目中的操作过程，能够正常运行，至于Mapper和Service 部分太简单，这里就省略了。如果对内容有不理解的，欢迎留言！