Springboot+MybatisPlus整合poi实现导出导入Excle表格

中二的小北

已于 2022-11-29 17:09:47 修改

阅读量2.3k

点赞数 9

文章标签：数据库 mybatis spring boot java mysql

于 2022-11-29 16:57:16 首次发布

本文链接：https://blog.csdn.net/weixin_61307867/article/details/128098325

版权

1.需求

*数据库数据表导出Excle表格

*Excle数据批量新增到数据库

2.工具

工具: idea

数据库: mysql

框架：Springboot

*准备工作*：

创建springboot项目

创建项目的过程就省略了，不会的可以去看我的球员项目超详细

1.导入导出的主要依赖:poi

2.整体依赖：

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.springboot</groupId>
    <artifactId>mybatisplus</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatisplus</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>


        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.15</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </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>4.1.2</version>
        </dependency>
    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <releases>
                <enabled>false</enabled>
            </releases>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <releases>
                <enabled>false</enabled>
            </releases>
        </pluginRepository>
    </pluginRepositories>

</project>

3.yaml配置

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/aac?serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

  thymeleaf:
    cache: false
    suffix=: .html
    prefix: classpath:/templates/
 #   type: com.zaxxer.hikari.HikariDataSource
  jdbc:
    template:
      query-timeout: 3
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3.数据库数据导出Excle表格

1.数据库表结构

2..总体结构

准备实体类goods（我的实体类是Mybatisplus生成的，我就往上面加注解就行了）

可以自动生成很多东西,需要下载mybatisx

连接数据库后

手写也没问题

@TableName(value ="goods")对应的是你的数据库表
@Value属性是匹配Excle表格的标头名称(导入准备的)
导出导入共用一个实体类

3.controller层

 @RequestMapping("/ExcelDownload")
    public void excelDownload(HttpServletResponse response) throws IOException {
        //表头数据
        String[] header = {"id", "数量", "姓名", "单价", "物品类型"};

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        //生成一个表格，设置表格名称为"学生表"
        HSSFSheet sheet = workbook.createSheet("物品表");

        //设置表格列宽度为10个字节
        sheet.setDefaultColumnWidth(10);

        //创建第一行表头
        HSSFRow headrow = sheet.createRow(0);

        //遍历添加表头(下面模拟遍历学生，也是同样的操作过程)
        for (int i = 0; i < header.length; i++) {
            //创建一个单元格
            HSSFCell cell = headrow.createCell(i);

            //创建一个内容对象
            HSSFRichTextString text = new HSSFRichTextString(header[i]);

            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
        }


        //***********************************
        //拿到数据
        List<Goods> goods = goodsService.getAllGoodsList();
        for (int i = 0; i < goods.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            Goods good = goods.get(i);
            HSSFCell cell = null;
            String[] names = {Math.toIntExact(good.getId()) + "",
                    good.getCount() + "",
                    good.getName() + "",
                    good.getPrice() + "",
                    good.getType().getTyname() + ""};
            for (int k = 0; k < names.length; k++) {
                cell = row.createCell(k);
                //拿到属性值
                HSSFRichTextString text = new HSSFRichTextString(names[k]);
                cell.setCellValue(text);
            }
        }
        //*****************************************


        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //这后面可以设置导出Excel的名称，此例中名为student.xls
        response.setHeader("Content-disposition", "attachment;filename=student.xls");

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载
        workbook.write(response.getOutputStream());
    }

//****** 中间的代码就是从数据库拿值出来放到表格中

//拿到数据
List<Goods> goods = goodsService.getAllGoodsList();

如果想让controller层看起来干净点没事我把他封装了一个工具类

两者选其一就行

package com.springboot.mybatisplus.config;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;


public class ExcelUtil {

    /**
     * Excel表格导出
     * @param response HttpServletResponse对象
     * @param excelData Excel表格的数据，封装为List<List<String>>
     * @param sheetName sheet的名字
     * @param fileName 导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<List<String>> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth) throws IOException {

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        //生成一个表格，设置表格名称
        HSSFSheet sheet = workbook.createSheet(sheetName);

        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);

        //写入List<List<String>>中的数据
        int rowIndex = 0;
        for(List<String> data : excelData){
            //创建一个row行，然后自增1
            HSSFRow row = sheet.createRow(rowIndex++);

            //遍历添加本行数据
            for (int i = 0; i < data.size(); i++) {
                //创建一个单元格
                HSSFCell cell = row.createCell(i);

                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(data.get(i));

                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);
            }
        }

        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());

        //关闭workbook
        workbook.close();
    }

}

4.service接口层

5.service实现类

package com.springboot.mybatisplus.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.springboot.mybatisplus.pojo.Goods;
import com.springboot.mybatisplus.service.GoodsService;
import com.springboot.mybatisplus.mapper.GoodsMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 *
 */
@Service
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods>
    implements GoodsService{

    @Autowired
    private GoodsMapper goodsMapper;

    @Override
    public int addAllGoods(List<Goods> list) {
        for (int i = 0; i <list.size() ; i++) {
            if (list.get(i)==null){
                continue;
            }
            System.out.println(getBaseMapper().insert(list.get(i)));
        }
        return 1;


    }

    @Override
    public List<Goods> getAllGoodsList() {
        return goodsMapper.getAll();
    }
}

addAllGoods(List<Goods> list):是导入给数据库批量新增的方法，用的是Mybatisplus自己封装的新增的方法

List<Goods> getAllGoodsList():是自己写的导出从数据库两表查询的方法

6.mapper接口层

7.mapper.xml

getAll写的就是两表查询

然后手动映射(这个也是Mybatisplus自动生成的)

页面：html+thymeleaf

就ok了可以通过地址导出了

4.Excle数据批量新增到数据库

1.实体类是一样的

2.service接口层（和上面一样的）

3.service实现类（和上面一样的）

package com.springboot.mybatisplus.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.springboot.mybatisplus.pojo.Goods;
import com.springboot.mybatisplus.service.GoodsService;
import com.springboot.mybatisplus.mapper.GoodsMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 *
 */
@Service
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods>
    implements GoodsService{

    @Autowired
    private GoodsMapper goodsMapper;

    @Override
    public int addAllGoods(List<Goods> list) {
        for (int i = 0; i <list.size() ; i++) {
            if (list.get(i)==null){
                continue;
            }
            System.out.println(getBaseMapper().insert(list.get(i)));
        }
        return 1;


    }

    @Override
    public List<Goods> getAllGoodsList() {
        return goodsMapper.getAll();
    }
}

4.然后写一个工具类

package com.springboot.mybatisplus.config;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;

import javax.swing.text.DateFormatter;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static java.sql.Types.BOOLEAN;
import static java.sql.Types.NUMERIC;
import static org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType.FORMULA;
import static org.apache.tomcat.util.bcel.classfile.ElementValue.STRING;


public class ExcelImport {
    //上传的是以.xls后缀的
    private final static String excel2003L = ".xls";
    //上传的是以.xlsx后缀的
    private final static String excel2007U = ".xlsx";

    public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
        //创建 list 模拟Excel表结构
        List<List<Object>> list = null;

        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;  //页数
        Row row = null;  //行数
        Cell cell = null;  //列数

        list = new ArrayList<List<Object>>();
        //遍历Excel中所有的sheet页
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }

                //遍历所有的列
                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    //获取第i页第j行第y列的值
                    //Cannot get a STRING value from a NUMERIC cell
                    //poi导入excel表格数据时报java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell异常是因为在读取cell单元格字符串时，有number类型的数据，因此需要把它转化为纯String类型
                    //在number类型转化为String类型的过程中造成了Cannot get a STRING value from a NUMERIC cell这样的问题，因此需要在读取excel单元格数据转化之前设置单元格类型为String
                    //  cell = row.getCell(y);
                    // li.add(getValue(cell));
                   String cellValue=new DataFormatter().formatCellValue(row.getCell(y));
                    li.add(cellValue);
                }
                list.add(li);
            }
        }
        return list;
    }

    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        //获取Excel后缀
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        //跟据后缀创建工作簿
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(inStr);  //2003-
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(inStr);  //2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    public static String getValue(Cell cell) {
        String value = "";
        if (null == cell) {
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = format.format(date);
                    ;
                } else {// 纯数字
                    BigDecimal big = new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                    //解决1234.0  去掉后面的.0
                    if (null != value && !"".equals(value.trim())) {
                        String[] item = value.split("[.]");
                        if (1 < item.length && "0".equals(item[1])) {
                            value = item[0];
                        }
                    }
                }
                break;
            //字符串类型
            case STRING:
                //如果是string类型进行下划线转驼峰处理
                value = cell.getStringCellValue();
                break;
            // 公式类型
            case FORMULA:
                //读公式计算值
                value = String.valueOf(cell.getNumericCellValue());
                if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getStringCellValue();
                }
                break;
            // 布尔类型
            case BOOLEAN:
                value = " " + cell.getBooleanCellValue();
                break;
            default:
                value = cell.getStringCellValue();
        }
        if ("null".endsWith(value.trim())) {
            value = "";
        }
        return value;
    }

    public static void setFileValueByFieldName(Object object, String fieldName, Object val) {
        //获取object类的所有属性
        Field[] fields = object.getClass().getDeclaredFields();
        try {
            //对所有属性进行遍历
            for (int i = 0; i < fields.length; i++) {
                //获取第i个属性
                Field field = fields[i];
                //如果Excel的某一列的列名(fieldName)与
                //在第i个属性上面添加的@Value注解的value一样，进行下面的操作
                if (fieldName.equals(field.getAnnotation(Value.class).value())) {
                    Class type = field.getType();
                    if (fieldName.equals("id")){
                        continue;
                    }
                    //如果field为私有属性，也可以对它进行操作
                    field.setAccessible(true);
                    //获取该属性的数据类型，如果是Integer类型的
                    if (field.getType()==Integer.class) {
                        field.set(object, Integer.valueOf(val.toString()));
                        //如果时LocalDateTime类型的
                    } else  if (field.getType()==double.class) {
                        field.set(object, Double.valueOf(val.toString()));
                        //如果时LocalDateTime类型的
                    } else if (type== int.class) {
                        //把val转成Integer存到该属性里面
                        field.set(object, Integer.valueOf(val.toString()));
                    } else if (field.getType()==long.class) {
                        field.set(object, Long.valueOf(val.toString()));
                        //如果时LocalDateTime类型的
                    } else if (field.getType() == LocalDateTime.class) {
                        //先把他格式化
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                        LocalDateTime time = LocalDateTime.parse(val.toString(), formatter);
                        field.set(object, time);
                        //如果其他类型的直接存
                    } else {
                        field.set(object, val);
                    }
                    return;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

setFileValueByFieldName（实体类Object，Excle表格的标头fileName，要注入的值var）在该方法进行属性类型判定赋值

5.controller层

package com.springboot.mybatisplus.controller;

import com.springboot.mybatisplus.config.ExcelImport;
import com.springboot.mybatisplus.pojo.Goods;
import com.springboot.mybatisplus.service.impl.GoodsServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;


@Controller
public class StudentController {
    @Autowired
    GoodsServiceImpl goodService;

    @RequestMapping("/upload")
    @ResponseBody
    public String uploadFile(@RequestParam("file") MultipartFile file) throws Exception {
        try {
            // 上传文件路径
            String filePath = "E:\\doc\\sdfsdf";
            // 为了防止文件名冲突，获取当前时间+文件原名生成新的文件名
            String fileName = System.currentTimeMillis() + file.getOriginalFilename();
            // 上传文件
            File f = new File(filePath + fileName);
            file.transferTo(f);
            fileName = filePath + fileName;

            //输入流，获取刚刚上传文件转成输入流
            FileInputStream fileInputStream = new FileInputStream(new File(fileName));
            //定义一个list变量，模拟excel结构
            List<List<Object>> list = ExcelImport.getListByExcel(fileInputStream, fileName);
            //定义firstRows变量，用来获取第一行，就是标题，每列名字
            List<Object> firstRows = null;
            //定义studentList变量，用来存储文件内容(学生信息)
            List<Goods> studentList = new ArrayList<>();
            //如果 list 不为空，大小大于0则获取第一行存到firstRows 里面
            if (list != null && list.size() > 0) {
                firstRows = list.get(0);
            } else {
                //否则返回 failure
                return "表格数据为空";
            }


            //对list进行遍历，因为第一行是标题，不用存到数据库，所以从第二行开始遍历
            for (int i = 1; i < list.size(); i++) {
                //获取第i行数据
                List<Object> rows = list.get(i);
                //定义goods遍历，存储第i行数据
                Goods good = new Goods();
                //对第i行数据进行遍历，
                //从1开始是因为新增不能有id
                for (int j = 1; j < rows.size(); j++) {
                    //获取第i行第j列数据，存到cellVal 变量里面
                    String cellVal = (String) rows.get(j);
                    //调用setFileValueByFieldName函数，把数据存到student对应的属性里面
                    ExcelImport.setFileValueByFieldName(good, firstRows.get(j).toString().trim(), cellVal);
                }
                //把student变量加到studentList
                studentList.add(good);
            }
            //调用批量插入方法，把数据存到数据库
            int o= goodService.addAllGoods(studentList);
            if (o != 0) {
                return "成功了";
            } else {
                return "导入失败";
            }
        } catch (Exception e) {
            return "报错了: "+e.getMessage();
        }
    }
}

这边id是自增列，所以不把id新增进去，所以 int =i

6.前端导入页面

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>导入Excel</title>
</head>
<body>
<h1>选择Excel</h1>
<form th:action="@{/upload}" method="post" enctype="multipart/form-data">
    <!-- name 的值与@RequestParam的值相同-->
    <input type="file" name="file"/><br/>
    <input type="submit" value="上传"/><br/>
</form>
 
</body>
</html>