今天工作中, 发现同事在整理数据, 通过 excel 上传到数据库. 所以现在写了篇利用 springboot 读取 excel 中的数据的 demo. 至于数据的进一步处理, 大家肯定有不同的应用场景, 自行修改
pom 文件
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</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-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
</dependencies>
controller 代码
- @Controller
- public class ImportController {
- @Autowired
- private ImportService importService;
- @PostMapping(value = "/upload")
- @ResponseBody
- public String uploadExcel(HttpServletRequest request) throws Exception {
- MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
- MultipartFile file = multipartRequest.getFile("filename");
- if (file.isEmpty()) {
- return "文件不能为空";
- }
- InputStream inputStream = file.getInputStream();
- List<List<Object>> list = importService.getBankListByExcel(inputStream, file.getOriginalFilename());
- inputStream.close();
- for (int i = 0; i <list.size(); i++) {
- List<Object> lo = list.get(i);
- //TODO 随意发挥
- System.out.println(lo);
- }
- return "上传成功";
- }
- }
service 代码
- @Service
- public class ImportService {
- /**
- * 处理上传的文件
- *
- * @param in
- * @param fileName
- * @return
- * @throws Exception
- */
- public List getBankListByExcel(InputStream in, String fileName) throws Exception {
- List list = new ArrayList<>();
- // 创建 Excel 工作薄
- Workbook work = this.getWorkbook(in, fileName);
- if (null == work) {
- throw new Exception("创建 Excel 工作薄为空!");
- }
- Sheet sheet = null;
- Row row = null;
- Cell cell = null;
- for (int i = 0; i <work.getNumberOfSheets(); i++) {
- sheet = work.getSheetAt(i);
- if (sheet == null) {
- continue;
- }
- for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
- row = sheet.getRow(j);
- if (row == null || row.getFirstCellNum() == j) {
- continue;
- }
- List<Object> li = new ArrayList<>();
- for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
- cell = row.getCell(y);
- li.add(cell);
- }
- list.add(li);
- }
- }
- work.close();
- return list;
- }
- /**
- * 判断文件格式
- *
- * @param inStr
- * @param fileName
- * @return
- * @throws Exception
- */
- public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
- Workbook workbook = null;
- String fileType = fileName.substring(fileName.lastIndexOf("."));
- if (".xls".equals(fileType)) {
- workbook = new HSSFWorkbook(inStr);
- } else if (".xlsx".equals(fileType)) {
- workbook = new XSSFWorkbook(inStr);
- } else {
- throw new Exception("请上传 excel 文件!");
- }
- return workbook;
- }
- }
代码下载
完整代码下载: https://github.com/runzhenghengbin/SpringBoot/tree/master/springboot(十一)
来源: https://www.cnblogs.com/zhenghengbin/p/9490511.html