1、新建一个springBoot项目
pom.xml依赖配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.2.2</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</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
2、数据库配置
#mysql配置
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://47.92.245.9:3306/peo-test?&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=Btkx1234!
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.show-sql=true
spring.jpa.show-sql=true
#druid 数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#编码格式设置
spring.thymeleaf.encoding=UTF-8
3、配置静态页面
在项目的resources/templates/excelImport.html下
页面内容：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Excel导入</title></head>
<body>
<form enctype="multipart/form-data" method="post" action="/excelImport">选择Excel表格：<input type="file" name="file"/><br/>
<input type="submit" value="上传"/></form>
</body>
</html>
4、DAO层
import com.java.excel.Entity.Excel;
import org.springframework.data.jpa.repository.JpaRepository;
public interface ExcelDaoextends JpaRepository {
}
5、service层
@Service
//事务控制
@Transactional
public class ExcelService {
@Autowired
private ExcelDaoexcelDao;
public void saveExcel(MultipartFile file)throws IOException {
//根据路径获取这个操作excel的实例
// HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
XSSFWorkbook work =new XSSFWorkbook(file.getInputStream());
//根据页面index 获取sheet页
// HSSFSheet sheet = wb.getSheetAt(0);
XSSFSheet sheet = work.getSheetAt(0);
// HSSFSheet 和XSSFSheet 是有区别的，自行百度
XSSFRow row =null;//接受每一行的数据
for (int i =1; i < sheet.getPhysicalNumberOfRows(); i++) {
//获取每一行数据
row = sheet.getRow(i);
//要判断每一行数据是否为空，此处没写，自行百度
row.getCell(0);//拿到每一列
//拿到每一列后放入对应的实体中，新增
}
}
6、controller层
@Controller
public class ExcelController {
@Autowired
private ExcelServiceexcelService;
@GetMapping("/toHtml")
public String test(HttpServletRequest request) {
return "excelImport";
}
//处理文件上传
@ResponseBody//返回json数据
@RequestMapping(value ="/excelImport", method = RequestMethod.POST)
public String uploadImg(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
String contentType = file.getContentType();
String fileName = file.getOriginalFilename();
if (file.isEmpty()) {
return "文件为空！";
}
try {
excelService.saveExcel(file);
}catch (Exception e) {
e.printStackTrace();
}
return "导入成功!";
}
}
