<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.11-beta2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.11-beta2</version>
</dependency>
@ApiOperation("导入")
@PostMapping(value = "/upload")
@ResponseBody
public ResultData uploadExcel(@RequestHeader("x-access-token")String accessToken,@RequestParam("excelInput") MultipartFile excelInput) throws Exception{
if (excelInput.isEmpty()) {
return ResultData.businessIsFail("文件不能为空");
}
InputStream inputStream = excelInput.getInputStream();
List<List<Object>> list = getBankListByExcel(inputStream, excelInput.getOriginalFilename());
inputStream.close();
for (int i = 0; i < list.size(); i++) {
List<Object> lo = list.get(i);
CabineVip cabineVip = new CabineVip();
cabineVip.setCabineid(Integer.parseInt(lo.get(0).toString()));
cabineVip.setVipid(Integer.parseInt(lo.get(1).toString()));
cabineVipMapper.insertSelective(cabineVip);
}
return ResultData.businessIsOK("上传成功");
}
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;
}
拿走不谢