SpringBoot 集成 easypoi 实现 Excel 导入导出

快来打我* 2021-11-04 12:20 952阅读 0赞

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

**1、项目搭建**

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 1]

**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**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][20190809201536875.]

OK，导入测试成功。

**8、测试导出Excel**

浏览器访问：[http://localhost:8080/expUser][http_localhost_8080_expUser]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 5]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70]: /images/20211104/da2688284de4477a8962254ef032a4e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 1]: /images/20211104/7cbbf2b8c3c84e6583b64c261955d23a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 2]: /images/20211104/e3630dc47aa2495bbda7aced511c250d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 3]: /images/20211104/ce4458390a0b4e8aad39e49f4511a922.png
[20190809201536875.]: /images/20211104/da6593ff70944503b2f5d0c8f0c296cb.png
[http_localhost_8080_expUser]: http://localhost:8080/expUser
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 4]: /images/20211104/cfb3af6b40b14b11ba8f428d9f07a4fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDYyMzczNg_size_16_color_FFFFFF_t_70 5]: /images/20211104/9bff3121a2bb46f1bf17bc595f34ccb2.png