准备数据库:
CREATE TABLE `tb_user` (
`id` int NOT NULL,
`user_name` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
`sex` int DEFAULT NULL,
`birthday` date DEFAULT NULL,
`created` date DEFAULT NULL,
`updated` date DEFAULT NULL,
`note` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
准备Excel表:
id | user_name | password | name | age | sex | birthday | created | updated | note |
1 | tom | 123456 | tom | 20 | 1 | 1999-11-28 | 2018-11-28 | 2018-11-28 | |
2 | lucy | 123456 | lucy | 20 | 2 | 2018-11-28 | 2018-11-28 | 2018-11-28 |
创建springboot项目,链接数据库
server.port=9020
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/MYJPA?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=12345678
spring.jpa.show-sql=true
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=validate
导入相应的pom
<?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.3.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.kismet</groupId>
<artifactId>excelupload</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>excelupload</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>4.3.1</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>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.10</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
编写Controller层
package com.kismet.excelupload.controller;
import com.kismet.excelupload.pojo.User;
import com.kismet.excelupload.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
/**
* @author ganqizhi
* @date 2020/9/1 10:47 上午
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/findAll")
public List<User> findAll(){
return userService.findAll();
}
@GetMapping("/excel/upload")
public void importExcel(@RequestParam("excelFile")MultipartFile multipartFile){
userService.importExcel(multipartFile);
}
}
编写server层
package com.kismet.excelupload.service;
import com.kismet.excelupload.pojo.User;
import com.kismet.excelupload.repository.UserRepository;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @author ganqizhi
* @date 2020/9/1 10:48 上午
*/
@Slf4j
@Service
public class UserService {
@Autowired
private UserRepository repository;
public List<User> findAll(){
return repository.findAll();
}
public void importExcel(MultipartFile excelFile){
if (excelFile==null|| excelFile.getSize()==0){
log.error("文件上传错误,重新上传");
}
String filename = excelFile.getOriginalFilename();
if (!(filename.endsWith(".xls")|| filename.endsWith(".xlsx"))){
log.error("文件上传格式错误,请重新上传");
}
List<User>list = null;
try {
if (filename.endsWith(".xls")){
list = readXLS(excelFile);
}else {
list=readXLSX(excelFile);
}
}catch (IOException e) {
e.printStackTrace();
log.error("文件内容读取失败,请重试");
}
repository.saveAll(list);
}
public List<User> readXLS(MultipartFile file) throws IOException {
List<User> list =new ArrayList<>();
InputStream inputStream = file.getInputStream();
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//读取第一张sheet
HSSFSheet sheet = workbook.getSheetAt(0);
String errorMsg="";
//遍历每一行Excel获取内容
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
HSSFRow row = sheet.getRow(rowNum);
if (row!=null){
User user = new User();
//使用了getStringCellValue()方法来获取值,POI会判断单元格的类型,如果非字符串类型就会抛出上面的异常。
//所以先使用setCellType()方法先将该单元格的类型设置为STRING
//然后poi会根据字符串读取它
row.getCell(0).setCellType(CellType.STRING);
row.getCell(4).setCellType(CellType.STRING);
row.getCell(5).setCellType(CellType.STRING);
user.setId(Integer.parseInt(row.getCell(0).getStringCellValue()));
user.setUserName(row.getCell(1).getStringCellValue());
user.setPassword(row.getCell(2).getStringCellValue());
user.setName(row.getCell(3).getStringCellValue());
user.setAge(Integer.parseInt(row.getCell(4).getStringCellValue()));
user.setSex(Integer.parseInt(row.getCell(5).getStringCellValue()));
user.setBirthday(row.getCell(6).getDateCellValue());
user.setCreated(row.getCell(7).getDateCellValue());
user.setUpdated(row.getCell(8).getDateCellValue());
HSSFCell cell = row.getCell(9);
if (cell==null){
log.info("第"+rowNum+"行,第九列数据为null");
}else {
user.setNote(row.getCell(9).getStringCellValue());
}
list.add(user);
}
}
return list;
}
public List<User> readXLSX(MultipartFile file) throws IOException {
ArrayList<User> list = new ArrayList<>();
InputStream inputStream = file.getInputStream();
XSSFWorkbook Workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheet = Workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
XSSFRow row = sheet.getRow(rowNum);
if (row!=null){
User user = new User();
//使用了getStringCellValue()方法来获取值,POI会判断单元格的类型,如果非字符串类型就会抛出上面的异常。
//所以先使用setCellType()方法先将该单元格的类型设置为STRING
//然后poi会根据字符串读取它
row.getCell(0).setCellType(CellType.STRING);
row.getCell(4).setCellType(CellType.STRING);
row.getCell(5).setCellType(CellType.STRING);
user.setId(Integer.parseInt(row.getCell(0).getStringCellValue()));
user.setUserName(row.getCell(1).getStringCellValue());
user.setPassword(row.getCell(2).getStringCellValue());
user.setName(row.getCell(3).getStringCellValue());
user.setAge(Integer.parseInt(row.getCell(4).getStringCellValue()));
user.setSex(Integer.parseInt(row.getCell(5).getStringCellValue()));
user.setBirthday(row.getCell(6).getDateCellValue());
user.setCreated(row.getCell(7).getDateCellValue());
user.setUpdated(row.getCell(8).getDateCellValue());
XSSFCell cell = row.getCell(9);
if (cell==null){
log.info("第"+rowNum+"行,第九列数据为null");
}else {
user.setNote(row.getCell(9).getStringCellValue());
}
list.add(user);
}
}
return list;
}
}
其余代码请下载资源包!