Spring Boot使用Poi将Excel导入进数据库

---

0. 前言

每当我使用Java进行编码的时候，就感觉自己像是一个拿着AK进行战斗的黑人🙃

【此处省略一张图】

本以为能够两天完成的毕设Excel文件上传与解析模块，没想到却花了足足四天才实现大半。（菜的程度可见一斑。）

先是找了EasyExcel的相关教程进行学习（例如：教程），CSDN上的各种EasyExcel教程基本都是按照这个教程来写的，可它们要么省略了一些步骤或源码，难以看懂，要么是涵盖了很多类型的Excel操作，同样难以看懂，因此在亲自尝试后没能做出来。

然后发现教程中用到了MyBatis Plus与MyBatis Plus Generator，就去简单学习了一下MPG；可刚学完又发现用到了Swagger和其他的一些依赖…告辞👋🏻

幸运的是后来遇上了POI，在一份大佬教程与一位大佬实体的帮助下终于成功实现了上传Excel文件，解析后将数据插入到了数据库。

---

1. 效果

1.1 Excel文件

1.2 网页页面

1.3 数据库

---

2. 思路

• 使用Poi与Poi-ooxml实现Excel解析

• 使用MySQL与MyBatis实现数据库连接与操作

• 使用Commons-Fileupload与Commons-IO实现文件上传与IO流

---

3. 项目结构

---

4. 项目源码

4.1 pom.xml添加相关依赖

<!-- Excel相关 -->
<!-- 1. Poi:针对Excel2003(后缀为.xls) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>
<!-- 2. Poi-ooxml:针对Excel2007及以后版本(后缀为.xlsx) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>


<!-- 数据库相关 -->
<!-- 1. MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>
<!-- 2. MyBatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>


<!-- 其它 -->
<!-- 1. 文件上传 -->
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.3.1</version>
</dependency>
<!-- 2. io -->
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.4</version>
</dependency>

4.2 application.properties相关配置

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/silk_dormitory?&useUnicode=true&characterEncoding=UTF-8

spring.datasource.username=root
spring.datasource.password=12345678

4.3 Bill.java实体类

public class Bill {

    private Integer id;
    private Integer billYear;
    private Integer billMonth;
    private Integer buildingId;
    private Integer roomId;
    private Double waterUsed;
    private Double waterFee;
    private Double energyUsed;
    private Double energyFee;
    private Double totalFee;
    private Integer paid;

	
    public Bill() {
    }

    public Bill(Integer id, Integer billYear, Integer billMonth, Integer buildingId, Integer roomId, Double waterUsed, Double waterFee, Double energyUsed, Double energyFee, Double totalFee, Integer paid) {
        this.id = id;
        this.billYear = billYear;
        this.billMonth = billMonth;
        this.buildingId = buildingId;
        this.roomId = roomId;
        this.waterUsed = waterUsed;
        this.waterFee = waterFee;
        this.energyUsed = energyUsed;
        this.energyFee = energyFee;
        this.totalFee = totalFee;
        this.paid = paid;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getBillYear() {
        return billYear;
    }

    public void setBillYear(Integer billYear) {
        this.billYear = billYear;
    }

    public Integer getBillMonth() {
        return billMonth;
    }

    public void setBillMonth(Integer billMonth) {
        this.billMonth = billMonth;
    }

    public Integer getBuildingId() {
        return buildingId;
    }

    public void setBuildingId(Integer buildingId) {
        this.buildingId = buildingId;
    }

    public Integer getRoomId() {
        return roomId;
    }

    public void setRoomId(Integer roomId) {
        this.roomId = roomId;
    }

    public Double getWaterUsed() {
        return waterUsed;
    }

    public void setWaterUsed(Double waterUsed) {
        this.waterUsed = waterUsed;
    }

    public Double getWaterFee() {
        return waterFee;
    }

    public void setWaterFee(Double waterFee) {
        this.waterFee = waterFee;
    }

    public Double getEnergyUsed() {
        return energyUsed;
    }

    public void setEnergyUsed(Double energyUsed) {
        this.energyUsed = energyUsed;
    }

    public Double getEnergyFee() {
        return energyFee;
    }

    public void setEnergyFee(Double energyFee) {
        this.energyFee = energyFee;
    }

    public Double getTotalFee() {
        return totalFee;
    }

    public void setTotalFee(Double totalFee) {
        this.totalFee = totalFee;
    }

    public Integer getPaid() {
        return paid;
    }

    public void setPaid(Integer paid) {
        this.paid = paid;
    }
}

4.4 Mapper持久层

• Interface

@Mapper
public interface BillMapper {

    int addBillExcelFileToDatabase(Bill bill);
}

• xml

<mapper namespace="com.silk.excel2database.mapper.BillMapper">
    <insert id="addBillExcelFileToDatabase" parameterType="com.silk.excel2database.entity.Bill" >
        insert into tb_bill
            (id,
             bill_year,
             bill_month,
             building_id,
             room_id,
             water_used,
             water_fee,
             energy_used,
             energy_fee,
             total_fee,
             paid)
        values
            (#{id},
             #{billYear},
             #{billMonth},
             #{buildingId},
             #{roomId},
             #{waterUsed},
             #{waterFee},
             #{energyUsed},
             #{energyFee},
             #{totalFee},
             #{paid})
    </insert>
</mapper>

4.5 Service服务层

@Service
public class BillService {

  @Autowired
  private BillMapper billMapper;


  public int addToDatabase(MultipartFile file) throws Exception{
      int result = 0;
      List<Bill> billList = new ArrayList<>();                    // 创建billList

      String fileName = file.getOriginalFilename();                           // 获取上传的文件名
      String suffix = fileName.substring(fileName.lastIndexOf(".")+1);    // 获取上传文件后缀

      System.out.println("文件后缀为：" + suffix);
      InputStream inputStream = file.getInputStream();            // 输入流读取文件
      Workbook workbook = null;                                   // 1. 新建工作簿

      if(suffix.equals("xlsx")){
          workbook = new XSSFWorkbook(inputStream);               // Excel2007版本及以后 用XSSF(即Poi-ooxml)处理
      } else {
          workbook = new HSSFWorkbook(inputStream);               // Excel2003版本 用HSSF(即Poi)处理
      }

      Sheet sheet = workbook.getSheetAt(0);                     // 2. 获取当前工作表

      if(sheet != null){
          for (int i = 1; i <= sheet.getLastRowNum(); i++){       // 3. 从第二行开始遍历工作表的每一行
              Row row = sheet.getRow(i);
              if (row != null){
                  List<String> list = new ArrayList<>();
                  for (Cell cell : row){                          // 4. 遍历每一个单元格
                      if (cell != null) {
                          cell.setCellType(Cell.CELL_TYPE_STRING);        // 将单元格值转化为字符串
                          String cellValue = cell.getStringCellValue();   // 获取字符串形式的值
                          list.add(cellValue);                            // 将值加入数组
                      }
                  }

                  // 值的转化
                  Integer id = Integer.parseInt(list.get(0));
                  Integer billYear = Integer.parseInt(list.get(1));
                  Integer billMonth = Integer.parseInt(list.get(2));
                  Integer buildingId = Integer.parseInt(list.get(3));
                  Integer roomId = Integer.parseInt(list.get(4));
                  Double waterUsed = Double.parseDouble(list.get(5));
                  Double waterFee = Double.parseDouble(list.get(6));
                  Double energyUsed = Double.parseDouble(list.get(7));
                  Double energyFee = Double.parseDouble(list.get(8));
                  Double totalFee = Double.parseDouble(list.get(9));
                  Integer paid = Integer.parseInt(list.get(10));

                  // 构造一个账单对象，并将从个单元格获取的值赋给它
                  Bill bill = new Bill(id, billYear, billMonth, buildingId, roomId,
                          waterUsed, waterFee, energyUsed, energyFee, totalFee, paid);
                  billList.add(bill);                                 // 将新的一条账单加入billList
              }
          }

          for (Bill bill: billList){
              result = billMapper.addBillExcelFileToDatabase(bill);      // 将每一条账单插入数据库
          }
      }
      return result;
  }
}

4.6 Controller控制层

@Controller
public class BillController {

    @Autowired
    private BillService billService;

    @RequestMapping("/upload")
    @ResponseBody
    public String excelImport(@RequestParam(value = "filename")MultipartFile file, HttpSession session){
        String fileName = file.getOriginalFilename();
        int result = 0;

        try {
            result = billService.addToDatabase(file);
        } catch (Exception e){
            e.printStackTrace();
        }

        if (result > 0){
            return "恭喜，账单导入成功！";
        }else {
            return "害！导入失败了...";
        }

    }
}

4.7 index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>导入Excel数据到MySQL数据库</title>
</head>
<body>
    <h1>导入Excel数据到MySQL数据库</h1><br/>
    <form class="form-horizontal" id="form_table" action="/upload" enctype="multipart/form-data" method="post">
        <button type="submit" class="btn btn-primary">导入</button>
        <input class="form-input" type="file" name="filename"></input>
    </form>
</body>
</html>

---

5. 项目下载

• 项目源码
• Excel测试数据