Springboot 最简单的结合MYSQL数据实现EXCEL表格导出及数据导入

忘是亡心i 2022-02-25 15:20 345阅读 0赞

嗯，今天如题，给大家介绍下最最最简单的实现excel导入导出的功能，功能简单叙述：

导入：读取本地的excel表格，将里面的内容都插入对应的数据库表（批量插入）

导出：读取数据库表内容，将其导出到excel文件

进入正题前，还是啰嗦一下，为啥要做一个这样的简单实战介绍呢，因为现在网上很多结合poi实现excel导入导出的教程不是太花了，就是各种版本老旧，所以在此，我也是等于提炼一下可用的，等于结合实战给大家一个最新的，最简单的教程例子。

OK,开始贴码实战（至于连接mybatis的整合以及查询MYSQL这些就不做介绍了，这是前提，我们不用模拟数据，当然你可以用也就是模拟个List而已嘛）：

先上pom.xml中需要用到关于excel导入导出的依赖：

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

OK，创建个实体类，User.java：

package com.example.tdemo.pojo;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    
    public class User {
        @Excel(name = "学号", orderNum = "0")
        private Integer id;
        @Excel(name = "姓名", orderNum = "1")
        private String  userName;
        @Excel(name = "年龄", orderNum = "2")
        private String  userAge;
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", userName='" + userName + '\'' +
                    ", userAge='" + userAge + '\'' +
                    '}';
        }
    
        public User() {
        }
    
        public User(Integer id, String userName, String userAge) {
            this.id = id;
            this.userName = userName;
            this.userAge = userAge;
        }
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUserName() {
            return userName;
        }
    
        public void setUserName(String userName) {
            this.userName = userName;
        }
    
        public String getUserAge() {
            return userAge;
        }
    
        public void setUserAge(String userAge) {
            this.userAge = userAge;
        }
    }

然后是 excel导入导出的工具类,ExcelUtil：

package com.example.tdemo.util;
    
    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;
    import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    
    import java.util.Map;
    import java.util.NoSuchElementException;
    
    public class ExcelUtil {
    
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
            ExportParams exportParams = new ExportParams(title, sheetName);
            exportParams.setCreateHeadRows(isCreateHeader);
            defaultExport(list, pojoClass, fileName, response, exportParams);
    
        }
        //导出
        public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
            defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        }
        public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
            defaultExport(list, fileName, response);
        }
    
        private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
            if (workbook != null);
            downLoadExcel(fileName, response, workbook);
        }
    
        private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage());
            }
        }
        private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
            Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
            if (workbook != null);
            downLoadExcel(fileName, response, workbook);
        }
    
        //导入
        public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
            if (StringUtils.isBlank(filePath)){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            }catch (NoSuchElementException e){
                throw new RuntimeException("模板不能为空");
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
            return list;
        }
        public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
            if (file == null){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            }catch (NoSuchElementException e){
                throw new RuntimeException("excel文件不能为空");
            } catch (Exception e) {
                throw new RuntimeException(e.getMessage());
            }
            return list;
        }
    
    
    
    }

OK，接下来玩导出导入，

既然是结和MYSQL数据库，那么我们简单看看，表，毕竟刚刚也创建了实体类：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70][]

好了，然后UserMapper简单贴一下：

@Mapper
    public interface UserMapper {
    
    
        //查询所有
        List<User> queryUserInfo();
    
    
        //插入所有
        void addUserInfo(List<User> list);
    }

相关的 userMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.example.tdemo.mapper.UserMapper">
    
        
        <select id="queryUserInfo" resultType="com.example.tdemo.pojo.User">
            select *
            from user
        </select>
    
    
        
        <insert id="addUserInfo" parameterType="java.util.List">
            insert into user(
            user_name,
            user_age
            )
            values
            <foreach collection="list" item="item" index= "index" separator =",">
                (
                #{item.userName},
                #{item.userAge}
                )
            </foreach>
        </insert>
    
       
    
    </mapper>

然后是UserService：

public interface UserService {
    
    
    
        //查询所有
        List<User> queryUserInfo();
        //插入所有
        void addUserInfo(List<User> list);
    }

对应的实现类：

@Service
    public class UserServiceImpl implements UserService {
    
    
        @Autowired
        UserMapper userMapper;
        @Override
        public List<User> queryUserInfo() {
            return userMapper.queryUserInfo();
        }
    
        @Override
        public void addUserInfo(List<User> list) {
    
           userMapper.addUserInfo(list);
        }
    }

好了，开始导出导入了！

我们直接看代码，创建一个TestController，

package com.example.tdemo.controller;
    
    import com.example.tdemo.pojo.User;
    import com.example.tdemo.service.impl.UserServiceImpl;
    import com.example.tdemo.util.ExcelUtil;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.http.HttpServletResponse;
    import java.util.List;
    
    @RestController
    public class TestController {
    
        @Autowired
        UserServiceImpl userService;
    
    
        @RequestMapping("exportExcel")
        public void export(HttpServletResponse response){
    
    
            List<User> userList = userService.queryUserInfo();
            //导出操作
            ExcelUtil.exportExcel(userList,"用户信息","sheet1",User.class,"testDATA.xls",response);
        }
    
        @RequestMapping("importExcel")
        public String importExcel(){
            String filePath = "C:\\testInfo.xls";
            //解析excel，
            List<User> userList = ExcelUtil.importExcel(filePath,1,1,User.class);
            //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
            System.out.println("导入数据一共【"+userList.size()+"】行");
    
            userService.addUserInfo(userList);
            List<User> userList2 = userService.queryUserInfo();
            return userList2.toString();
    
        }
    
    
    
    }

好像结束了，我们来简单调下接口，验证下吧：

导出前数据表数据是这样的：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1][]

调用导出接口（谷歌浏览器默认下载到对应路径，IE\\360等浏览器可以选择导出下载地址）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 2][]

OK，EXCEL表格已经导出，我们看看打开看看：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 3][]

OK的，没有什么问题。

那我们继续调下导入吧（记得看下对应的接口，导入选择的路径我们是写死的，文件名也是，后期结合前端交互可以改为路径选择传入），

先看看需要导入数据的EXCEL表格内容，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 4][]

好，开始调用导入接口（将EXCEL表格内容转化为List，再将List数据批量插入数据库）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 5][]

OK，我们导入数据，插入数据库后打印出来当前表信息，其实已经可以确定，肯定是无误了，最后看看数据库：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 6][]

好了，就到此结束吧，是不是很简单？

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70]: /images/20220225/814e930e9b324f65b777e2458aa23220.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1]: /images/20220225/fe8af9c73c754b70b9bbab054d186446.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 2]: /images/20220225/d7aa46e021634aaea8e81194b0a2c6f0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 3]: /images/20220225/15b02d8e0d2b40c1b1f6dc7b2de553d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 4]: /images/20220225/84c805753f3b4ea390d3b94d0ce85aab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 5]: /images/20220225/5fc18dfc0aa1404c80eeb6712a174c15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 6]: /images/20220225/1eab201f2cad450683fa84d2aafc9600.png