SpringBoot解析excel把数据保存到数据库
一、创建一个spring boot项目
1.1 开发工具 idea
1.2 jdk 1.8
1.3 具体项目搭建流程可以阅读我的另一篇博客（创建spring boot项目）
1.4 整体结构
二、搭建spring boot开发环境
2.1 添加pom依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--jdbc 数据库连接-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 引入阿里数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.16</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.26</version>
</dependency>
<!-- mybatisPlus 核心库 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>net.sourceforge.nekohtml</groupId>
<artifactId>nekohtml</artifactId>
<version>1.9.15</version>
</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.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.10</version>
</dependency>
</dependencies>2.2 配置application.yml文件
# 配置端口
server:
port: 8086
spring:
# 配置数据源
datasource:
url: jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
thymeleaf:
mode: LEGACYHTML5
# 取消模板文件缓存
cache: false
#设定thymeleaf文件路径 默认为src/main/resources/templates
freemarker:
template-loader-path: classpath:/templates
#设定静态文件路径，js,css等
mvc:
static-path-pattern: /static/**
servlet:
multipart:
# 设置单个文件大小
max-file-size: 200MB
# 设置单次请求文件的总大小
max-request-size: 200MB
# mybatis-plus相关配置
mybatis-plus:
# xml扫描，多个目录用逗号或者分号分隔（告诉 Mapper 所对应的 XML 文件位置）
mapper-locations: classpath*:com/liyh/mapper/xml/*.xml
configuration:
# 是否开启自动驼峰命名规则映射:从数据库列名到Java属性驼峰命名的类似映射
map-underscore-to-camel-case: true
#打印sql，保存到文件
logging:
level:
com.liyh.mapper: debug2.3 编写摸板文件
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<hr/>
<p>摸板下载</p>
<a href="/excel/download">下载摸板</a>
<hr/>
<p>文件上传</p>
<form action="/excel/import" method="POST" enctype="multipart/form-data">
文件：<input type="file" name="file"/>
<input type="submit"/>
</form>
<hr/>
</body>
</html>2.4 创建IndexController，FileController
package com.liyh.controller;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
/**
* @Author: liyh
* @Date: 2020/10/23 17:33
*/
@Controller
public class IndexController {
@RequestMapping("/")
public String index()
{
return "index";
}
}
package com.liyh.controller;
import com.liyh.entity.Result;
import com.liyh.service.ExcelService;
import com.liyh.utils.ExcelTool;
import com.liyh.utils.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* 接口
*
* @Author: liyh
* @Date: 2020/10/23 17:05
*/
@RestController
@RequestMapping("/excel")
public class ExcelController {
Logger logger = LoggerFactory.getLogger(ExcelController.class);
@Autowired
private ExcelService excelService;
@PostMapping("/import")
public Result importProject(MultipartFile file) {
String postfix = ExcelTool.getPostfix(file.getOriginalFilename());
if (!"xlsx".equals(postfix) && !"xls".equals(postfix)) {
return Result.error("导入失败，请选择正确的文件格式支持xlsx或xls");
}
return excelService.importProject(file);
}
@GetMapping("/download")
public String downloadFile(HttpServletRequest request, HttpServletResponse response) {
String fileName = "template.xlsx";
String result = FileUtils.downloadFiles(request, response, fileName);
if (request == null) {
return null;
}
return result;
}
}2.4 文件工具类
package com.liyh.utils;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
/**
* @Author: liyh
* @Date: 2020/11/4 16:10
*/
public class FileUtils {
/**
* 下载文件
* @param request
* @param response
* @param fileName
* @return
* @throws IOException
*/
public static String downloadFiles(HttpServletRequest request, HttpServletResponse response, String fileName){
if (StringUtils.isEmpty(fileName)) {
return "文件名称为空";
}
//设置文件路径
ClassPathResource classPathResource = new ClassPathResource("templates/" + fileName);
File file = null;
try {
file = classPathResource.getFile();
} catch (IOException e) {
e.printStackTrace();
return "文件不存在";
}
response.setHeader("content-type", "application/octet-stream");
// 设置强制下载不打开
response.setContentType("application/force-download");
// 设置文件名
response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
byte[] buffer = new byte[1024];
InputStream fis = null;
BufferedInputStream bis = null;
try {
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer, 0, i);
i = bis.read(buffer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return "文件下载成功";
}
/**
* 判断文件大小
*
* @param file 文件
* @param size 限制大小
* @param unit 限制单位（B,K,M,G）
* @return
*/
public static boolean checkFileSize(MultipartFile file, int size, String unit) {
if (file.isEmpty() || StringUtils.isEmpty(size) || StringUtils.isEmpty(unit)) {
return false;
}
long len = file.getSize();
double fileSize = 0;
if ("B".equals(unit.toUpperCase())) {
fileSize = (double) len;
} else if ("K".equals(unit.toUpperCase())) {
fileSize = (double) len / 1024;
} else if ("M".equals(unit.toUpperCase())) {
fileSize = (double) len / 1048576;
} else if ("G".equals(unit.toUpperCase())) {
fileSize = (double) len / 1073741824;
}
if (fileSize > size) {
return false;
}
return true;
}
}
package com.liyh.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
/**
* 解析Excel文件单元格内容
*
* @Author: liyh
* @Date: 2020/10/24 17:05
*/
public class ExcelTool {
public static final String EMPTY = "";
private static final String POINT = ".";
/**
* 获得path的后缀名
*
* @param path 文件路径
* @return 路径的后缀名
*/
public static String getPostfix(String path) {
if (path == null || EMPTY.equals(path.trim())) {
return EMPTY;
}
if (path.contains(POINT)) {
return path.substring(path.lastIndexOf(POINT) + 1, path.length());
}
return EMPTY;
}
/**
* 解析xls和xlsx不兼容问题
*
* @param pfs
* @param workbook
* @param file
* @return
*/
public static Workbook getWorkBook(POIFSFileSystem pfs, Workbook workbook, MultipartFile file) throws IOException {
String filename = file.getOriginalFilename();
if (filename.endsWith("xls")) {
pfs = new POIFSFileSystem(file.getInputStream());
workbook = new HSSFWorkbook(pfs);
return workbook;
} else if (filename.endsWith("xlsx")) {
try {
workbook = new XSSFWorkbook(file.getInputStream());
return workbook;
} catch (IOException e) {
return null;
}
} else {
return null;
}
}
}2.5 service
package com.liyh.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.liyh.entity.ProjectItem;
import com.liyh.entity.Result;
import org.springframework.web.multipart.MultipartFile;
/**
* @Author: liyh
* @Date: 2020/10/23 17:44
*/
public interface ExcelService extends IService<ProjectItem> {
Result importProject(MultipartFile file);
}
package com.liyh.service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.liyh.entity.ProjectItem;
import com.liyh.entity.Result;
import com.liyh.mapper.ExcelMapper;
import com.liyh.utils.ExcelTool;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import java.io.IOException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: liyh
* @Date: 2020/10/23 17:44
*/
@Service
public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, ProjectItem> implements ExcelService {
@Resource
private ExcelMapper excelMapper;
@Override
public Result importProject(MultipartFile file) {
// 解析Excel数据
Result result = readDataFromExcel(file, 0, 1);
if (result.isFlag()) {
List<ProjectItem> items = (List<ProjectItem>) result.getData();
if (items == null || items.size() <= 0) {
return Result.error("没有数据！！！");
}
// 插入数据表格中的数据
this.saveBatch(items);
// for (ProjectItem item : items) {
// // 保存数据
// int insert = baseMapper.insertProjectItem(item.getOrder(), item.getName(), item.getContent(), item.getType(), item.getUnit(), item.getPrice(), item.getCount());
// this.saveBatch(items);
// if (insert <= 0) {
// return Result.error("导入失败");
// }
// }
return Result.success("导入成功");
} else return result;
}
/**
* 解析Excel数据
*
* @param file 文件
* @param sheetNum 第几个sheet
* @param startRow 从第几行开始读
* @return
*/
public Result readDataFromExcel(MultipartFile file, int sheetNum, int startRow) {
POIFSFileSystem pfs = null;
Workbook workbook = null;
try {
// 解析xls和xlsx不兼容问题
workbook = ExcelTool.getWorkBook(pfs, workbook, file);
} catch (IOException e) {
e.printStackTrace();
return Result.error("模板保存异常。");
}
if (workbook == null) {
return Result.error("请使用模板上传文件");
}
// 判断有记录的列数（表格的列数和表的列一样）
if (workbook.getSheetAt(sheetNum).getRow(0).getPhysicalNumberOfCells() != 7) {
return Result.error("请使用类型所对应的模板");
}
List<ProjectItem> list = new ArrayList<>();
// 获取表格sheet的内容
Sheet sheetAt = workbook.getSheetAt(sheetNum);
// 获得sheet总行数
int lastRowNum = sheetAt.getLastRowNum();
if (lastRowNum < 1) {
return Result.error("数据错误");
}
// （注意！！！！！！！！！！！！！）开始读取,不读取表头所以从第二行开始
for (int i = startRow; i <= lastRowNum; i++) {
// 获取每一行
Row row = sheetAt.getRow(i);
// 行为空不读取
if (row == null) continue;
Cell cell = row.getCell(0);
// 列为空不读取
if (cell == null || StringUtils.isEmpty(convertData(cell))) continue;
// 创建对象封装行数据
ProjectItem projectItem = new ProjectItem();
// 创建一个集合根据下标来确定每个单元格对应对象的什么属性
List<String> rowList = new ArrayList<>();
// 添加数据
for (int j = 0; j < 7; j++) {
Cell cellOne = row.getCell(j);
if (cellOne == null) {
rowList.add("");
} else {
try {
String item = convertData(cellOne);
rowList.add(item);
} catch (Exception e) {
System.out.println("-------------------Err-----------------------");
System.out.println(i + "行" + j + "列数据转换出现异常");
rowList.add("");
}
}
}
// 规避行数数据后几行为空
if (rowList.size() < 7) {
for (int k = 0; k < 7 - rowList.size(); k++) {
rowList.add("");
}
}
// 添加数据
projectItem.setNumber(rowList.get(0).trim());
projectItem.setName(rowList.get(1).trim());
projectItem.setContent(rowList.get(2).trim());
projectItem.setType(rowList.get(3).trim());
projectItem.setUnit(rowList.get(4).trim());
projectItem.setPrice(rowList.get(5).trim());
projectItem.setCount(rowList.get(6).trim());
list.add(projectItem);
}
return Result.success("解析成功", list);
}
/**
* 表格数据转换
*
* @param cell
* @return
*/
public String convertData(Cell cell) {
String str = "";
switch (cell.getCellTypeEnum()) {
case NUMERIC:
// 判断是否是整数
str = NumberFormat.getNumberInstance().format(cell.getNumericCellValue());
break;
case STRING:
str = cell.getStringCellValue();
break;
case _NONE:
str = "";
break;
case BLANK:
str = "";
break;
case FORMULA:
try {
str = String.valueOf(cell.getNumericCellValue());
} catch (IllegalArgumentException e) {
str = String.valueOf(cell.getRichStringCellValue());
}
break;
default:
str = "";
}
return str;
}
}2.6 entity
package com.liyh.entity;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.io.Serializable;
/**
* 项目清单表实体类
*
* @Author: liyh
* @Date: 2020/10/23 17:05
*/
@Data
@TableName("project_item")
public class ProjectItem implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键uuid
*/
@TableId(type = IdType.AUTO)
private Integer id;
/**
* 项目序号
*/
private String number;
/**
* 项目名称
*/
private String name;
/**
* 项目内容
*/
private String content;
/**
* 费用类型（直接费等）
*/
private String type;
/**
* 单位
*/
private String unit;
/**
* 单价
*/
private String price;
/**
* 数量
*/
private String count;
}2.7 mapper文件
package com.liyh.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.liyh.entity.ProjectItem;
import org.apache.ibatis.annotations.Param;
/**
* @Author: liyh
* @Date: 2020/10/23 17:46
*/
public interface ExcelMapper extends BaseMapper<ProjectItem> {
int insertProjectItem(@Param("order") String order, @Param("name") String name, @Param("content") String content,
@Param("type") String type, @Param("unit") String unit, @Param("price") String price, @Param("count") String count);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.liyh.mapper.ExcelMapper">
<!-- 通用查询映射结果 -->
<resultMap id="projectItemResultMap" type="com.liyh.entity.ProjectItem">
<id column="id" property="id"/>
<result column="number" property="number"/>
<result column="name" property="name"/>
<result column="content" property="content"/>
<result column="type" property="type"/>
<result column="unit" property="unit"/>
<result column="price" property="price"/>
<result column="count" property="count"/>
</resultMap>
<insert id="insertProjectItem">
insert into project_item (number, name, content, type, unit, price, count)
values(#{number}, #{name}, #{content}, #{type}, #{unit}, #{price}, #{count})
</insert>
</mapper>三、sql文件和摸板文件
SET NAMES utf8mb4;
SET
FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for project_item
-- ----------------------------
DROP TABLE IF EXISTS `project_item`;
CREATE TABLE `project_item`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`content` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`unit` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`price` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`count` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;
SET
FOREIGN_KEY_CHECKS = 1;
四、启动项目，进行测试
4.1 启动项目，访问结果：
4.2 测试文件上传
4.3 测试摸板下载
4.4 完整项目地址：https://gitee.com/liyhGitee/springboot/tree/master/springboot_excel
浙公网安备 33010602011771号