Excel表格数据导入到数据库

一、问题描述

采用Stringboot+Mybatis+MySQL的框架结构，通过前端实现一键导入Excel表格数据。

二、项目结构参考

点击查看项目创建的目录结构

三、项目具体代码

1、cotroller

/**
 * Excel 导入
 * @param file
 * @return
 * @throws Exception
 */
@RequestMapping("/excel")
public String importExcel(@RequestParam("file") MultipartFile file) throws Exception {
    String name = file.getOriginalFilename();
    if(name.length() < 5 || !name.substring(name.length() - 5).equals(".xlsx")) {
        throw new Exception("文件格式错误");
    }
    //获取Excel中的数据
    List<Excel> detaileds = ExcelUtils.excelToShopIdList(file.getInputStream());
    System.out.println(detaileds.get(0));
    //遍历添加数据库
    for (int i = 0; i < detaileds.size(); i++) {
        Excel detailed = detaileds.get(i);
        partService.addDetailed(detailed);
    }
    //return返回的是我之前的项目页面，你可以自行修改
    return "redirect:/show";
}

2、entity（Excel.java）

/**
 * @Author: 傻猴儿
 * @Description:
 * @Date: Create in 06:06 2022/6/16
 */
public class Excel {
   public int id;
   public String code;
   public String fetch_number;
   public String energy_type;
   public String measuring_unit;
   public String collect;
   public double ratio;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getFetch_number() {
        return fetch_number;
    }

    public void setFetch_number(String fetch_number) {
        this.fetch_number = fetch_number;
    }

    public String getEnergy_type() {
        return energy_type;
    }

    public void setEnergy_type(String energy_type) {
        this.energy_type = energy_type;
    }

    public String getMeasuring_unit() {
        return measuring_unit;
    }

    public void setMeasuring_unit(String measuring_unit) {
        this.measuring_unit = measuring_unit;
    }

    public String getCollect() {
        return collect;
    }

    public void setCollect(String collect) {
        this.collect = collect;
    }

    public double getRatio() {
        return ratio;
    }

    public void setRatio(double ratio) {
        this.ratio = ratio;
    }
}

3、mapper

/**
 * Excel导入
 * @param detailed
 * @return
 */
boolean addDetailed(Excel detailed);

4、server

/**
 * Excel 导入
 * @param detailed
 * @return
 */
public boolean addDetailed(Excel detailed){
    boolean flag = false;
    try {
        partMapper.addDetailed(detailed);
        flag = true;
    }catch (Exception e){
        e.printStackTrace();
    }
    return flag;
}

5、utils

该方法为工具类，建议在xxx里单创建一个文件夹utils，单独存储工具类ExcelUtils

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import com.xxx.entity.Excel;

/**
 * Excel导入工具类
 */
public class ExcelUtils {
    public static List<Excel> excelToShopIdList(InputStream inputStream) throws IOException, InvalidFormatException {
        Workbook workbook = WorkbookFactory.create(inputStream);
        inputStream.close();
        Sheet sheet = workbook.getSheetAt(0);
        //获取Excel表里行数
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
        //创建集合储存行对象
        ArrayList<Excel> detaileds = new ArrayList<>();
        //从第二行开始，遍历表格
        for (int i = 1; i < physicalNumberOfRows; i++) {
            //创建对象储存行数据
            Excel detailed = new Excel();
            //获取当前行数据
            Row row = sheet.getRow(i);
            //获取单元格的值存入对象中
            Cell cell_id=row.getCell(0);
            cell_id.setCellType(CellType.STRING);
            detailed.setId(Integer.valueOf(cell_id.getStringCellValue()));

            detailed.setCode(row.getCell(1).getStringCellValue());
            detailed.setFetch_number(row.getCell(2).getStringCellValue());
            detailed.setEnergy_type(row.getCell(3).getStringCellValue());
            detailed.setMeasuring_unit(row.getCell(4).getStringCellValue());
            detailed.setCollect(row.getCell(5).getStringCellValue());

            Cell cell_radio=row.getCell(6);
            cell_radio.setCellType(CellType.STRING);
            detailed.setRatio(Double.valueOf(cell_radio.getStringCellValue()));
            //将对象放到集合中
            detaileds.add(detailed);
            }
        return detaileds;
    }
}

6、mybatis

在MySQL创建energy_data表格，元素分别为id、code、fetch_number、energy_type、measuring_unit、collect、ratio，元素类型查看实体定义。

<!--excel-->
<insert id="addDetailed" parameterType="com.xxx.entity.Excel">
    insert into energy_data(id,code,fetch_number,energy_type,measuring_unit,collect,ratio)
    values(#{id},#{code},#{fetch_number},#{energy_type},#{measuring_unit},#{collect},#{ratio})
</insert>

7、前端代码

<form enctype="multipart/form-data" method="post" action="/excel">
    <input type="file" name="file" /> <input type="submit" value="上传" />
</form>

8、pom代码

<!--java对象自动映射到关系数据库-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.0.2.RELEASE</version>
</dependency>

<!--使用POI读取文件-->
<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>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.20</version>
</dependency>

9、表格说明

1、表格命名不要包含特殊符号。
2、第一行为标题，后面是数据，第一行标题数量与实体类元素对应。
3、数据或者元素不能为空或者不合法。