SpringBoot(十三)_springboot上传Excel并读取excel中的数据

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

今天工作中，发现同事在整理数据，通过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; }}