POI导入excel数据

1、导入jar包

       <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>

2、代码

    public static List<ActivityParticipateInfo> getList(String xlsPath) throws Exception {
        //验证是否为excel文件
        validateExcel(xlsPath);

        List<ActivityParticipateInfo> participateInfoList = new ArrayList<>();

        //创建文件输入流
        FileInputStream fis = new FileInputStream(xlsPath);
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook workbook = null;
        //excel版本为2003
        if (isExcel2003(xlsPath)) {
            workbook = new HSSFWorkbook(fis);
        }
        //excel版本为2007
        if (isExcel2007(xlsPath)) {
            workbook = new XSSFWorkbook(fis);
        }

        Sheet sheet = null;
        //获取sheet数量
        int number = workbook.getNumberOfSheets();
        for (int i = 0; i < number; i++) {
            sheet = workbook.getSheetAt(i);
            for (Row row : sheet) {
                if(row.getRowNum() < 1){
                    continue;
                }
                //创建实体类
                ActivityParticipateInfo participateInfo = new ActivityParticipateInfo();
                participateInfo.setUserName(row.getCell(0).getStringCellValue());
                //设置cell的数据类型，避免数字电话号码报错
                row.getCell(1).setCellType(CellType.STRING);
                participateInfo.setPhoneNumber(row.getCell(1).getStringCellValue());
                participateInfo.setName(row.getCell(2).getStringCellValue());
                participateInfo.setDescribe(row.getCell(3).getStringCellValue());
                participateInfo.setHospital(row.getCell(4).getStringCellValue());
                participateInfo.setKeshi(row.getCell(5).getStringCellValue());
                participateInfoList.add(participateInfo);
            }
        }
        fis.close();
        return participateInfoList;
    }


    /**
     * 验证excel文件
     * @param filePath
     * @return boolean
     * @throws Exception
     */
    public static boolean validateExcel(String filePath) throws Exception {
        /** 检查文件名是否为空或者是否是Excel格式的文件 */
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            throw new Exception("文件名不是excel格式");
        }
        /** 检查文件是否存在 */
        File file = new File(filePath);
        if (file == null || !file.exists()) {
            throw new Exception("文件不存在");
        }
        return true;
    }


    /**
     * 是否是2003的excel
     * @param filePath
     * @return boolean
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 是否是2007的excel
     * @param filePath
     * @return boolean
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }