利用springboot 将数据库中的数据导出为excle。还实现将excle里面的数据上传到数据库里面

一写代码就开心

发布于 2020-11-20 15:09:49

4.1K10

代码可运行

文章被收录于专栏：java和pythonjava和python

运行总次数：0

代码可运行

利用springboot 将数据库中的数据导出为excle

写一个接口，浏览器一输入这个接口，那么就可以导出数据库里面的数据到excle表里面了。要实现这个功能。我们使用springboot

1 创建一个简单的springboot项目

2 导入操作excle的依赖，和数据库里面的依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com</groupId>
    <artifactId>03_springboot_login</artifactId>
    <version>1.0-SNAPSHOT</version>
    <!--配置继承坐标-->
    <!--配置项目继承-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.10.RELEASE</version>
    </parent>
    <!--配置依赖-->
    <dependencies>
        <!--web启动器依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mybatis启动器依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!--mysql的依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <!--配置Driud启动器-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--配置Thymeleaf的启动器-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!--配置PageHelper启动器-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>

        <!-- easyexcel相关依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>


        <!--@Getter @Setter-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>
    </dependencies>

</project>

以上是全部的依赖，最主要的是两个，一个操作excle，一个操作数据库

   <!-- easyexcel相关依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

   <!--mybatis启动器依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!--mysql的依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

3 配置数据库的来源，也就是用户名和密码

##配置数据库连接池
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://127.0.0.1:3306/springboot_login?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: '123456'
    driver-class-name: com.mysql.jdbc.Driver

4 controller层

//导出数据到excle表里面
    @GetMapping("/user/excel")
    public void excelExport(HttpServletResponse response) throws IOException {
        userService.excelExport(response);
}

5service层

public interface UserService {


    void excelExport(HttpServletResponse response) throws IOException;

    void excelImport(MultipartFile file) throws IOException;
}

 @Override
    public void excelExport(HttpServletResponse response) throws IOException {
        List<User> list = userMapper.selUserMapper();
        String fileName = "用户名单";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ) + ".xls");
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS,true);
        Sheet sheet = new Sheet(1,0,User.class);
        //设置自适应宽度
        sheet.setAutoWidth(Boolean.TRUE);
        sheet.setSheetName("用户名单");
        writer.write(list,sheet);
        writer.finish();
        out.flush();
        response.getOutputStream().close();
        out.close();
    }

mapper层

    //查询所有的用户信息
    @Select("select * from t_user")
    List<User> selUserMapper();

pojo实体类层

import java.util.Objects;
@Data
public class User extends BaseRowModel {
    @ExcelProperty(value = "ID", index = 0)
    private String uid;
    @ExcelProperty(value = "姓名", index = 1)
    private String uname;
    @ExcelProperty(value = "密码", index = 2)
    private Integer pwd;
}

导出 Excel 时，若需要表头，那么相应的实体类需要继承 BaseRowModel，并加入 @ExcelProperty(value = “id”, index = 0) 注解。其中 value 代表在导出 Excel 时，该字段对应的表头名称；index 代表该字段对应的表头位置（从0开始）。如下图：

以上就写好了后台的接口，现在浏览器数据接口，那么就弹出下载框了

实现将excle里面的数据上传到数据库里面

controller

//    将excle表里面的数据保存到数据库
    @PostMapping("/user/excel2")
    public String excelImport(@RequestParam("file") MultipartFile file) throws IOException {
        userService.excelImport(file);
        return "main";
    }

service

    @Override
    public void excelImport(MultipartFile file) throws IOException {
        if(!file.getOriginalFilename().equals("用户名单.xls") && !file.getOriginalFilename().equals("用户名单.xlsx") ){
            return;
        }
        InputStream inputStream = new BufferedInputStream(file.getInputStream());
        //实例化实现了AnalysisEventListener接口的类
        ExcelListener excelListener = new ExcelListener(userMapper);
        ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
        //读取信息
        reader.read(new Sheet(1,1,User.class));
    }

mapper

    @Insert("insert into t_user (uid ,uname,pwd) values (#{uid},#{uname},#{pwd}) ")
    int addUser(User user);

工具类

package com.controller;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.mapper.UserMapper;
import com.pojo.User;

import java.util.ArrayList;
import java.util.List;

/**
 * @author 
 * @date 2020/11/14--17:15
 */
public class ExcelListener extends AnalysisEventListener<User> {
    private List<User> datas = new ArrayList<>();
    private static final int BATCH_COUNT = 3000;
    private UserMapper userDao;

    public ExcelListener(UserMapper userDao){
        this.userDao = userDao;
    }

    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(user);
        //达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(datas.size() >= BATCH_COUNT){
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }

    private void saveData() {
        for(User user : datas){
            userDao.addUser(user);
        }
    }

    public List<User> getDatas() {
        return datas;
    }

    public void setDatas(List<User> datas) {
        this.datas = datas;
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
    }
}

excle里面的主键的id值不能和数据库一样，那么这样才可以上传

新准备的excle，excle的名字要和后台的固定