poi解析excel

1.解析excel需要的依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.9</version>
</dependency>

 2.controller

 @RequestMapping("loadExcel")
    public Integer importExcel(MultipartFile file) throws IOException {

       return hyGyAnnualReportService.insertAnnualReport(file);
    }

3.service

 @Override
    public Integer insertAnnualReport(MultipartFile file) throws IOException {
        //获得上传的excel文件名
        String fileName = file.getOriginalFilename().toLowerCase();
        //获取上传的excel文件名后缀
        String fileSuffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        List<HyGyAnnualReport> list = new ArrayList<HyGyAnnualReport>();

        if ("xlsx".equals(fileSuffix)) {
            //获取excel工作簿
            XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream());
            int sheetCount = xwb.getNumberOfSheets();
            //获取excel的sheet  sheetCount
            for (int i = 0; i < 10; i++) {
                XSSFSheet xssfSheet = xwb.getSheetAt(i);

                if (xssfSheet == null) {
                    return null;
                }
                //循环获取excel每一行
                for (int rowNum = 1; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    HyGyAnnualReport hyGyAnnualReport = new HyGyAnnualReport();
                    if (xssfRow == null) {
                        continue;
                    }
                    //循环获取excel每一行的每一列
                    for (int cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) {
                        XSSFCell xssCell = xssfRow.getCell(cellNum);
                        if (xssCell == null) {
                            continue;
                        }
 　　　　　　　　　　　　　　list.add(hyGyAnnualReport);
                        System.out.println("");
                    }
                }
            }
        } else if ("xls".equals(fileSuffix)) {
            //获取excel工作簿
            Workbook wb = new HSSFWorkbook(file.getInputStream());
            int sheetCount = wb.getNumberOfSheets();
            //获取excel的sheet
            for (int i = 0; i < sheetCount; i++) {
                //获取excel的sheet
                Sheet sheet = wb.getSheetAt(i);
                if (sheet == null) {
                    return null;
                }
                //循环获取excel每一行
                for (int rowNum = 1; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    Report Report = new Report();
                    if (row == null) {
                        continue;
                    }
                    //循环获取excel每一行的每一列
                    for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        if (cell == null) {
                            continue;
                        }
　　　　　　　　　　　　　　　　
                    list.add(Report);
                    System.out.println("");
                }
            }
        }
        //将list批量添加到数据库
        int count = hyGyAnnualReportMapper.Report(list);
        return count;

    }