Java实现Excel批量导入数据库

springboot实现Excel批量导入数据库

前言：项目开发中最容易碰到的需求即将Excel数据批量上传到数据库中存储 -> Java实现，Excel数据表，MySQL数据库，具体步骤如下…

---

1.新建excel表，填写测试数据

2.新建项目工程，修改pom依赖以及application.properties如下

pom.xml

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
<!--java对象状态自动映射到关系数据库中数据上-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.0.2.RELEASE</version>
</dependency>
<!--实现类与xml之间的相互转换-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-oxm</artifactId>
    <version>5.0.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.2.12.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-jpamodelgen</artifactId>
    <version>5.2.12.Final</version>
</dependency>
<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
    <version>2.2.3.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--前端页面我这里使用了thymeleaf模板引擎-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.12</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>

application.properties

spring.datasource.username=123456
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/excel?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
#配置通过jpa自动创建表
spring.jpa.hibernate.ddl-auto=create
#打印SQL
spring.jpa.show-sql=true

3.创建实体类，进行关系映射

启动项目就会自动创建实体类中的表，创建完之后会发现数据库里的字段和实体类里的字段顺序是不一样，是乱序状态，是因为hibernate源码中用的是TreeMap存储实体类字段，TreeMap属性是无序的；所以解决办法如下：

• 找到源码文件
  
• 在本项目中创建一个和源码类一样的包结构和一样名字的类，复制源码文件所有代码到新建的类中
  
• 将上图标识的TreeMap 修改为LinkedHashMap修改好之后启动项目，会发现程序走的是新创建的类

package com.wxy.excel.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
@Entity
@Table(name = "excel")
public class Excel implements Serializable {
    //serialVersionUID适用于java序列化机制
    private static final long serialVersionUID = 1L;
    @Id
    @Column(length=36)
    private String id;
    @Column(length=45,nullable=false,unique=true)
    private String username;
    @Column(length=100,nullable=false,unique=true)
    private String email;
    @Column(length=45,nullable=false)
    private String password;
    @Column(length=45)
    private String role;

//省略getter and setter 以及 constructor

4.Excel的DAO类接口，与Excel有关的持久化操作方法

package com.wxy.excel.mapper;
import com.wxy.excel.entity.Excel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ExcelRepository extends JpaRepository<Excel,String> {
}

5.创建service层接口

Excel的Service类接口，与Excel有关的业务逻辑方法

package com.wxy.excel.service;

import org.springframework.web.multipart.MultipartFile;

public interface ExcelService {

boolean getExcel(MultipartFile file) throws Exception;

}

6.service层方法实现

Excel的Service类，与用户信息有关的业务逻辑方法

package com.wxy.excel.service;
import com.wxy.excel.mapper.ExcelRepository;
import com.wxy.excel.entity.Excel;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelServiceImpl implements ExcelService{
@Autowired
private ExcelRepository excelRepository;
@Override
public boolean getExcel(MultipartFile file) throws Exception {
List<Excel> list = new ArrayList<Excel>();
//1.得到上传的表
Workbook workbook2 = WorkbookFactory.create(file.getInputStream());
//2.获取test工作表 注意test就是excel下面的sheet名称
Sheet sheet2 = workbook2.getSheet("test");
//3.获取表的总行数
int num = sheet2.getLastRowNum();
//4.获取表总列数
int col = sheet2.getRow(0).getLastCellNum();
//5.遍历excel每一行
for (int j = 0; j <= num; j++) {
Row row1 = sheet2.getRow(j);
// 如果单元格中有数字或者其他格式的数据，则调用setCellType()转换为string类型
Cell cell1 = row1.getCell(0);
cell1.setCellType(CellType.STRING);
//获取表中第i行，第2列的单元格
Cell cell2 = row1.getCell(1);
cell2.setCellType(CellType.STRING);
//获取excel表的第i行，第3列的单元格
Cell cell3 = row1.getCell(2);
cell3.setCellType(CellType.STRING);
Cell cell4 = row1.getCell(3);
cell4.setCellType(CellType.STRING);
Cell cell5 = row1.getCell(4);
cell5.setCellType(CellType.STRING);
//这里new 一个对象，用来装填从页面上传的Excel数据，字段根据上传的excel决定
Excel excel= new Excel();
excel.setId(cell1.getStringCellValue());
excel.setUsername(cell2.getStringCellValue());
excel.setEmail(cell3.getStringCellValue());
excel.setPassword(cell4.getStringCellValue());
excel.setRole(cell5.getStringCellValue());
list.add(excel);
System.out.println("excel"+excel);
}
excelRepository.saveAll(list);
return true;
}
}

7.Controller层实现

package com.wxy.excel.controller;
import com.wxy.excel.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.multipart.MultipartFile;

@Controller
public class ExcelController {

@Autowired
private ExcelService excelService;

@GetMapping("/upload")
public String goUpload(){
    return "upload";
}
@PostMapping("/excel")
public String upload(MultipartFile file, Model model) throws Exception {
    boolean flag = excelService.getExcel(file);
    if(flag){
        model.addAttribute("Message", "上传成功");
    }else{
        model.addAttribute("Message", "上传失败");
    }
    return "upload";
}}

8.前端html实现

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>批量上传</title>
</head>
<body>
    <form enctype="multipart/form-data" method="post" action="/excel">
        <input type="file" name="file" /> <input type="submit" value="上传" />
    </form>
</body>
</html>

9.项目结构图，以及实现文件上传效果如下

10.项目demo已经上传到github，下载地址

https://github.com/Wangxy9527/ExcelToDatabase

或者直接clone即可：

git@github.Wangxy9527/ExcelToDatabase.git

https://github.com/Wangxy9527/ExcelToDatabase.git

结语：本文主要实现功能为将Excel数据导入到数据库中，不懂不理解或者对以上文章有疑问的小伙伴欢迎留言…