需求：Excel批量导入数据
实现Excel批量导入的难点是如何获取Excel文档中的数据，只要能获取到数据，至于用于数据封装或者往数据库里增数据只需要根据不同业务进行处理即可。
代码Demo
1、poi依赖
<dependency>
<groupId>com.deepoove</groupId>
<artifactId>poi-tl</artifactId>
<version>1.9.0</version>
</dependency>
2、前端页面
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta charset="UTF-8">
<title>测试</title>
</head>
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
<body>
<h1>测试文件上传</h1>
<form method="post" action="/excel/import">
<input type="file" id="uploadFile" name="uploadWord">
<input type="button" value="上传" onclick="upload()">
</form>
</body>
<script>
function upload() {
let file = document.getElementById("uploadFile").files[0];
let formData = new FormData();
formData.append("file",file);
$.ajax({
type: "post",
async: false,
data: formData,
cache: false,
processData : false,
contentType : false,
url: "/excel/import",
success: function (res) {
alert(res);
}
})
}
</script>
</html>
3、controller（Slf4j日志记录，不需要可以删除）
package com.bootdemo.myspringboot.controller; import com.bootdemo.myspringboot.service.ExcelService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; 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; /** * @Classname ExcelController * @Description TODO * @Date 2022/4/27 14:15 * @Created by BG */ @RestController @RequestMapping("/excel") @Slf4j public class ExcelController { @Autowired public ExcelService excelService; @RequestMapping("import") public String excelImport(@RequestParam("file") MultipartFile file) { return excelService.excelImport(file); } }
4、Service
package com.bootdemo.myspringboot.service.impl; import com.bootdemo.myspringboot.service.ExcelService; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.io.InputStream; /** * @Classname ExcelServiceImpl * @Description TODO * @Date 2022/4/27 14:16 * @Created by BG */ @Service @Slf4j public class ExcelServiceImpl implements ExcelService { @Override public String excelImport(MultipartFile file) { //excel.XLS文件 //HSSFWorkbook hssfWorkbook = null; //excel.XLSX文件 XSSFWorkbook xssfWorkbook = null; try { InputStream inputStream = file.getInputStream(); xssfWorkbook = new XSSFWorkbook(inputStream); } catch (IOException e) { log.info("创建文件输入流失败：" + e.getMessage()); return "创建文件输入流失败"; } // 获取Excel的第一个sheet XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0); //获取行数 int columnNum = sheetAt.getPhysicalNumberOfRows(); for (int i = 0; i < columnNum; i++) { //获取每行 Row row = sheetAt.getRow(i); //获取列数 int lastRowNum = row.getPhysicalNumberOfCells(); for (int j = 0; j < lastRowNum; j++) { //获取每列 Cell cell = row.getCell(j); //第i行第j列的值(模板用string数值，如果用其他类型则用其他方法获取值) String cellValue = cell.getStringCellValue(); System.out.println("第" + i + "行第" + j + "列数值为:" + cellValue); } } return "success"; } }
结果展示
1、Excel模板
2、页面
3、控制台输出
