SpringBoot解析Excel把数据保存到数据库,亲测可用
转载出处：
一、创建一个spring boot项目
1.1 开发工具 idea
1.3 具体项目搭建流程可以阅读我的另一篇博客（）
二、搭建spring boot开发环境
<?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.0modelVersion>
<parent>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-parentartifactId>
<version>2.3.4.RELEASEversion>
<relativePath/>
parent>
<groupId>com.liyhgroupId>
<artifactId>springboot_excelartifactId>
<version>0.0.1-SNAPSHOTversion>
<packaging>jarpackaging>
<name>springboot_excelname>
<description>study springboot_exceldescription>
<properties>
<project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8project.reporting.outputEncoding>
<java.version>1.8java.version>
properties>
<dependencies>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starterartifactId>
dependency>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-thymeleafartifactId>
dependency>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-webartifactId>
dependency>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-devtoolsartifactId>
<scope>runtimescope>
dependency>
<dependency>
<groupId>mysqlgroupId>
<artifactId>mysql-connector-javaartifactId>
<scope>runtimescope>
dependency>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-jdbcartifactId>
dependency>
<dependency>
<groupId>com.alibabagroupId>
<artifactId>druidartifactId>
<version>1.1.6version>
dependency>
<dependency>
<groupId>org.projectlombokgroupId>
<artifactId>lombokartifactId>
<optional>trueoptional>
dependency>
<dependency>
<groupId>com.baomidougroupId>
<artifactId>mybatis-plus-boot-starterartifactId>
<version>3.2.0version>
dependency>
<dependency>
<groupId>net.sourceforge.nekohtmlgroupId>
<artifactId>nekohtmlartifactId>
<version>1.9.15version>
dependency>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-testartifactId>
<scope>testscope>
<exclusions>
<exclusion>
<groupId>org.junit.vintagegroupId>
<artifactId>junit-vintage-engineartifactId>
exclusion>
exclusions>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxmlartifactId>
<version>4.1.2version>
dependency>
<dependency>
<groupId>org.apache.commonsgroupId>
<artifactId>commons-lang3artifactId>
<version>3.10version>
dependency>
dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-maven-pluginartifactId>
plugin>
plugins>
<resources>
<resource>
<directory>src/main/javadirectory>
<includes>
<include>**/*.xmlinclude>
includes>
resource>
<resource>
<directory>src/main/resourcesdirectory>
resource>
resources>
build>
project>
2.2 配置application.yml文件
DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Titletitle>
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.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;
}
}
FileUtils
2.5 service
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;
}
三、摸板文件
4.1 启动项目，访问结果：