1200字范文 > Java实现Excel批量导入数据库

Java实现Excel批量导入数据库

时间：2020-12-03 19:26:02

相关推荐

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><dependency><groupId>org.springframework</groupId><artifactId>spring-orm</artifactId><version>5.0.2.RELEASE</version></dependency><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><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><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=123456spring.datasource.password=123456spring.datasource.url=jdbc:mysql://localhost:3306/excel?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC#配置通过jpa自动创建表spring.jpa.hibernate.ddl-auto=create#打印SQLspring.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;@Repositorypublic 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;@Servicepublic class ExcelServiceImpl implements ExcelService{@Autowiredprivate ExcelRepository excelRepository;@Overridepublic 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;@Controllerpublic class ExcelController {@Autowiredprivate 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>