SpringBoot 集成 easypoi 实现 Excel 导入导出

今天，日月教大家如何实现导入导出excel，话不多说，我们直接上代码。

1、项目搭建

首先创建一个springboot项目，这里就不详细描述项目创建过程了，可以参考我之前发的springboot系列教程。创建一个test数据库，建一张简单的用户表user 。附一张简单的项目结构和数据表图：

2、添加pom依赖

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <!-- 导入导出 -->
         <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

3、配置文件 application.yml

#配置数据源
spring:
  datasource:
     url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
     username: root
     password: 123456
     driver-class-name: com.mysql.jdbc.Driver
     
#指定mybatis映射文件的地址
mybatis:
  mapper-locations: classpath:mapper/*.xml

4、excel工具类

package com.chenqi.springboot.util;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
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;
/**
 * 
* @ClassName: ExcelUtils
* @Description: excle工具类
* @author chenqi
* @date 2018年11月17日
*
 */
public class ExcelUtils {
    /**
     * 
    * @Title: importData
    * @Description: 导入excle 数据
    * @param file  文件
    * @param headerRows  忽略头行数
    * @param pojoClass   转换的实体
    * @return List<User>  返回的集合
     */
    public static <T> List<T> importData(MultipartFile file, Integer headerRows,
            Class<T> pojoClass){
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    /**
     * 
    * @Title: exportExcel
    * @Description: 导出excel
    * @param list  导出的数据
    * @param title  文件标题
    * @param sheetName  sheet名称
    * @param pojoClass  集合的类
    * @param fileName   文件名
    * @param response
    * @return void
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheetName), pojoClass, list);
        if (workbook != null) {
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    
}

5、实体类添加注解

package com.chenqi.springboot.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
 * 
* @ClassName: User
* @Description: 用户表实体
* @author chenqi
* @date 2018年11月17日
*
 */
public class User {
    /** 主键id */
    private Long id;
    
    /** 姓名 */
    @Excel(name = "姓名")
    private String name;
    
    /** 手机号 */
    @Excel(name = "手机号")
    private String mobile;
    
    /** 地址 */ 
    @Excel(name = "地址")
    private String address;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getMobile() {
        return mobile;
    }
    public void setMobile(String mobile) {
        this.mobile = mobile;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    
}

6、编写导入导出测试接口

package com.chenqi.springboot.controller;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.chenqi.springboot.entity.User;
import com.chenqi.springboot.service.UserService;
import com.chenqi.springboot.util.ExcelUtils;
/**
 * 
* @ClassName: UserController
* @Description: 测试
* @author chenqi
* @date 2018年11月17日
*
 */
@RestController
public class UserController {
    @Autowired
    UserService userService;
    
    /**
     * 
    * @Title: impUser
    * @Description: excle导入
    * @param file
    * @return String
     */
    @PostMapping("/impUser")
    public String impUser(MultipartFile file){
        List<User> users = ExcelUtils.importData(file, 1, User.class);
        userService.insertAll(users);
        return "success";
    }
    
    /**
     * 
    * @Title: expUser
    * @Description: 导出excel
    * @param response
    * @return void
     */
    @GetMapping("/expUser")
    public void expUser(HttpServletResponse response){
        List<User> users = userService.select();
        if(users != null && users.size() > 0){
            ExcelUtils.exportExcel(users, null, "用户数据", User.class, "用户20181118.xlsx", response);
        }
    }
    
    
}

7、测试导入excel

OK，导入测试成功。

8、测试导出Excel

浏览器访问：http://localhost:8080/expUser

OK，导出Excel测试成功，至此，excel的导入导出功能就完美实现了。
ps:如果该文章有帮助到您，就点个赞吧！您的支持与肯定是我持续更新最大的动力。