Workbook导入excel
1.需要用到的依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
- 工具类
mport org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtil {
//XXX为excel列对应的实体类
public static List<XXX> readExcel(MultipartFile file) throws IOException {
List<XXX> list = new ArrayList<>();
InputStream inputStream = file.getInputStream(); // 获取上传文件的输入流
Workbook workbook = WorkbookFactory.create(inputStream); // 使用工厂方法创建 Workbook 对象
Sheet sheet = workbook.getSheetAt(0); // 获取工作簿中的第一个表格
DataFormatter dataFormatter = new DataFormatter();
// 获取表格中的每一行
// 获取当前行
boolean isOk = true;
for (Row currentRow : sheet) {
// 跳过第一行
if(isOk) {
isOk = false;
continue;
}
XXX sx = new XXX();
//获取列赋值给对象,这里就举例,具体的按自己需求定
sx.setCycle(dataFormatter.formatCellValue(currentRow.getCell(0)));
sx.setCode(dataFormatter.formatCellValue(currentRow.getCell(1)));
list.add(sx);
return list;
}
}
3.controller层
@PostMapping("/uploadExcel")
@ResponseBody
public ResponseEntity<String> uploadExcel(@RequestParam("file") MultipartFile file) {
try {
LOGGER.info("接收到上传 excel: {}, 文件大小: {}", file.getOriginalFilename(), file.getSize());
List<XXX> list = ExcelUtil.readExcel(file);
LOGGER.info("读取到 excel 行数: {}", list.size());
for (XXX sx: list) {
XXXService.save(sx);
}
return new ResponseEntity<>("excel 内容入库", 200);
} catch (IOException e){
e.printStackTrace();
return new ResponseEntity<>("excel 内容读取失败", 2001);
}
}
简单页面调用
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>excel导入</title>
</head>
<body>
<h1>引入 excel 数据源</h1>
//action指向接口相对路径
<form method="post" enctype="multipart/form-data" action="/uploadExcel">
<input type="file" name="file"/>
<br />
<br />
<input type="submit" value="上传"/>
</form>
</body>
</html>