一、需求
读取Excel表格数据
二、添加依赖
<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>
三、具体实现
@Override
public List<Role> readExcelData(String excelPath,String sheetName) throws Exception {
log.info("开始读取位置{}为的Excel文件,工作簿名称为{}",excelPath,sheetName);
InputStream io = new FileInputStream(excelPath);
Workbook workbook ;
if(StringUtils.endsWith(excelPath,"xlsx")){
workbook = new XSSFWorkbook(io);
}else if(StringUtils.endsWith(excelPath,"xls")){
workbook = new HSSFWorkbook(io);
}else {
throw new MissingResourceException("选择的文件后缀只支持xls、xlsx两种格式",ExcelServiceImpl.class.getName(),"excelPath");
}
if(org.springframework.util.StringUtils.isEmpty(workbook)){
throw new Exception("excel文件不存在");
}
List<Role> roleList = new ArrayList<>();
Sheet sheet = workbook.getSheet(sheetName);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int k = 1; k < sheet.getLastRowNum()+1; k++) {
Row row = sheet.getRow(k);
Role role = Role.builder().roleName(row.getCell(0).getStringCellValue())
.description(row.getCell(1).getStringCellValue())
.createTime(sdf.format(row.getCell(2).getDateCellValue()))
.updateTime(sdf.format(row.getCell(3).getDateCellValue())).build();
roleList.add(role);
}
return roleList;
}