注:
artifactID为poi,表示适配旧版office/excel (2003及之前版本版本, .xls)*
artifactID为poi-ooxml,表示新版office/excel (2007及之后版本, .xlsx)*
建议根据情况导入依赖,若不确定输入excel版本,两者同时引入即可。
此处确定输入excel为2007后版本,所以只引入poi-ooxml
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency>
Thymeleaf+Spring boot+mybatis+lombok+mysql
添加依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency>
templates页面添加index.html
<form action="/upload" method="post" enctype="multipart/form-data"> <input type="file" name="file" > <button type="submit">上传</button> </form>
后端设置controller - 设定传入参数类型为 MultipartFile
@PostMapping("upload") public List<PriceInfoEntity> importEmp(@RequestParam("file") MultipartFile file) throws IOException
excel解析
2003版本(包含2003)以前的扩展名为.xls需要用HSSFWorkbook类操作
2007版本(包含2007)以后的扩展名为.xlsx需要用XSSFWorkbook类操作
获得sheet
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); XSSFSheet sheet = workbook.getSheetAt(0);
根据sheet的row number,获得row
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { XSSFRow row = sheet.getRow(i);
获得row中的cell
row.getCell(0).getStringCellValue().trim(),
处理结束后关闭workbook
workbook.close();