excel数据的导出

一、需求

读取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;
    }