SpringBoot解析Excel把数据保存到数据库,亲测可用
转载出处： https://www.cnblogs.com/liyhbk/p/13903528.html
一、创建一个spring boot项目
1.1 开发工具 idea
1.2 jdk 1.8
1.3 具体项目搭建流程可以阅读我的另一篇博客（创建spring boot项目）
1.4 整体结构
二、搭建spring boot开发环境
2.1 添加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.4.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.liyh</groupId> <artifactId>springboot_excel</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>springboot_excel</name> <description>study springboot_excel</description> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <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.1.6</version> </dependency> <!--lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </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> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resources</directory> </resource> </resources> </build> </project>
2.2 配置application.yml文件
# 配置端口 server: port: 8086 spring: # 配置数据源 datasource: url: jdbc:mysql://localhost:3306/db1?useSSL=false&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: debug
2.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 文件工具类
FileUtils2.5 service
package com.liyh.service.impl; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; 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.service.ExcelService; 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 java.io.IOException; import java.text.NumberFormat; import java.util.ArrayList; import java.util.List; import java.util.stream.Collectors; /** * @Author: liyh * @Date: 2020/10/23 17:44 */ @Service public class ExcelServiceImpl extends ServiceImpl<ExcelMapper, ProjectItem> implements ExcelService { private NumberFormat numberFormat = null; @Override public Result importProject(MultipartFile file) { // 解析Excel数据 Result r = readDataFromExcel(file); List list = (List) r.getData(); List<ProjectItem> items = list; if (items == null || items.size() <= 0) { return Result.error("没有数据！！！"); } //查询之前是否存在项目清单项 QueryWrapper wrapper = new QueryWrapper(); wrapper.eq("is_deleted", 0); List<ProjectItem> beforeItems = baseMapper.selectList(wrapper); //如果存在，判断两个集合中是否有相同的项目序号 if (beforeItems != null && beforeItems.size() > 0) { List<String> beforeOrderNumber = beforeItems.stream().map(ProjectItem::getOrderNumber).collect(Collectors.toList()); List<String> afterOrderNumber = items.stream().map(ProjectItem::getOrderNumber).collect(Collectors.toList()); for (String vo : beforeOrderNumber) { if (afterOrderNumber.contains(vo)) { return Result.error(vo + "：该项目序号已经存在"); } } } // 如果没有序号相等，则插入数据表格中的数据，然后重新读取 for (ProjectItem item : items) { // 保存数据 int insert = baseMapper.insertProjectItem(item.getOrderNumber(), item.getName(), item.getContent(), item.getType(), item.getUnit(), item.getPrice(), item.getCount()); if (insert <= 0) { return Result.error("导入失败"); } } return Result.success("导入成功"); } /** * 解析Excel数据 * * @param file 文件 * @return */ public Result readDataFromExcel(MultipartFile file) { 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(0).getRow(0).getPhysicalNumberOfCells() != 7) { return Result.error("请使用类型所对应的模板"); } numberFormat = NumberFormat.getNumberInstance(); List<ProjectItem> list = new ArrayList<>(); // 获取表格第一个sheet的内容 Sheet sheetAt = workbook.getSheetAt(0); // 获得sheet总行数 int lastRowNum = sheetAt.getLastRowNum(); if (lastRowNum < 1) { return Result.error("数据错误"); } // 开始读取,不读取表头所以从第二行开始 for (int i = 1; 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); 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.setOrderNumber(rowList.get(0).trim()); projectItem.setName(rowList.get(1).trim()); projectItem.setContent(rowList.get(2).trim()); if ("直接费".equals(rowList.get(3).trim())) { projectItem.setType(1); } else if ("间接费".equals(rowList.get(3).trim())) { projectItem.setType(2); } else if ("措施费".equals(rowList.get(3).trim())) { projectItem.setType(3); } else { projectItem.setType(null); } 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.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 */ private Integer id; /** * 项目序号 */ private String orderNumber; /** * 项目名称 */ private String name; /** * 项目内容 */ private String content; /** * 费用类型（直接费等） */ private Integer type; /** * 单位 */ private String unit; /** * 单价 */ private String price; /** * 数量 */ private String count; /** * 是否已删除[0-否、1-是] */ private String isDeleted; }
三、摸板文件
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, `order_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` int(16) 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, `is_deleted` int(1) UNSIGNED ZEROFILL NULL DEFAULT 0 COMMENT '是否已删除[0-否、1-是]', 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
