EXCEL解析之终极方法WorkbookFactory

 EXCEL解析之终极方法WorkbookFactory

Selenium做自动化测试当然不能避免和Excel打交道。

由于Excel版本的关系，文件扩展名分xls和xlsx，

以往的经验都是使用HSSFWorkbook和XSSFWorkbook来分别处理。具体的方式就是先判断文件的类型，然后根据文件扩展名来选择方法。

大概处理方式如下：

String extention= getExtention(path);
             if (!EMPTY.equals(extention)) {
                 if (XLS.equals(extention)) {
                     return readXlsForAllSheets(path);
                 } else if (XLSX.equals(extention)) {
                     return readXlsxForAllSheets(path);
                 }
             } else {
                 System.out.println(path + " is not a excel file.");
             }

 再接着实现readXlsForAllSheets和readXlsxForAllSheets两个方法。

    public Object[][] readXlsxForAllSheets(String path) throws IOException{
         System.out.println(path);
         FileInputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        System.out.println("There are totally "+xssfWorkbook.getNumberOfSheets()+" sheets in the workbook.");
         // Read the Sheet
        List<Object[]> records1=new ArrayList<Object[]>();
         for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
             XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
             int rowCount=xssfSheet.getLastRowNum()-xssfSheet.getFirstRowNum();
             List<Object[]> records=new ArrayList<Object[]>();
             String[] separative={"This is sheet "+xssfWorkbook.getSheetName(numSheet)};
             records.add(separative);
             for(int rowNum =1;rowNum<rowCount+1; rowNum++){
                 XSSFRow xssfRow=xssfSheet.getRow(rowNum);
                 String fields[]=new String[xssfRow.getLastCellNum()];
                 for (int colNum=0;colNum<xssfRow.getLastCellNum();colNum++){
                    XSSFCell xssfCell=xssfRow.getCell(colNum);
                     fields[colNum]=this.getXssfCellValue(xssfCell);
                 }
                 records.add(fields);
             }
             records1.addAll(records);
             }
         Object[][] results=new Object[records1.size()][];
         for(int i=0;i<records1.size();i++){
             results[i]=records1.get(i);
         }
         if (xssfWorkbook!=null){xssfWorkbook.close();}
         return results;
         }

    public Object[][] readXlsForAllSheets(String path) throws IOException{
         System.out.println(PROCESSING + path);
         FileInputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        System.out.println("There are totally "+hssfWorkbook.getNumberOfSheets()+" sheets in the workbook.");
         // Read the Sheet
        List<Object[]> records1=new ArrayList<Object[]>();
         for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
             HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
             int rowCount=hssfSheet.getLastRowNum()-hssfSheet.getFirstRowNum();
             List<Object[]> records=new ArrayList<Object[]>();
             String[] separative={"This is sheet "+hssfWorkbook.getSheetName(numSheet)};
             records.add(separative);
             for(int rowNum =1;rowNum<rowCount+1; rowNum++){
                 HSSFRow xssfRow=hssfSheet.getRow(rowNum);
                 String fields[]=new String[xssfRow.getLastCellNum()];
                 for (int colNum=0;colNum<xssfRow.getLastCellNum();colNum++){
                    HSSFCell xssfCell=xssfRow.getCell(colNum);
                     fields[colNum]=this.getHssfCellValue(xssfCell);
                 }
                 records.add(fields);
             }
             records1.addAll(records);
             }
         Object[][] results=new Object[records1.size()][];
         for(int i=0;i<records1.size();i++){
             results[i]=records1.get(i);
         }
         if (hssfWorkbook!=null){hssfWorkbook.close();}
         return results;
         }

 再实现上两个方法中调用的getXssfCellValue和getHssfCellValue方法。

 private String getXssfCellValue(XSSFCell xssfCell) {
        String cellvalue="";
        DataFormatter formatter = new DataFormatter();
        if (null != xssfCell) {  
            switch (xssfCell.getCellType()) {  
            case XSSFCell.CELL_TYPE_NUMERIC: // 数字  
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(xssfCell)) {
                  cellvalue = formatter.formatCellValue(xssfCell);
              } else {
                  double value = xssfCell.getNumericCellValue();
                  int intValue = (int) value;
                  cellvalue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
              }
                break;
            case XSSFCell.CELL_TYPE_STRING: // 字符串  
                cellvalue=xssfCell.getStringCellValue();
                break;  
            case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean  
                cellvalue=String.valueOf(xssfCell.getBooleanCellValue());
                break;  
            case XSSFCell.CELL_TYPE_FORMULA: // 公式  
                cellvalue=String.valueOf(xssfCell.getCellFormula());  
                break;  
            case XSSFCell.CELL_TYPE_BLANK: // 空值  
                cellvalue="";  
                break;  
            case XSSFCell.CELL_TYPE_ERROR: // 故障  
                cellvalue="";  
                break;  
            default:  
                cellvalue="UNKNOWN TYPE";  
                break;  
            }  
        } else {  
            System.out.print("-");  
        }
        return cellvalue.trim();
    }

 
private String getHssfCellValue(HSSFCell hssfCell) {
        String cellvalue="";
        DataFormatter formatter = new DataFormatter();
        if (null != hssfCell) {  
            switch (hssfCell.getCellType()) {  
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字  
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(hssfCell)) {
                    cellvalue = formatter.formatCellValue(hssfCell);
                } else {
                    double value = hssfCell.getNumericCellValue();
                    int intValue = (int) value;
                    cellvalue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                }
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串  
                cellvalue=hssfCell.getStringCellValue();
                break;  
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean  
                cellvalue=String.valueOf(hssfCell.getBooleanCellValue());
                break;  
            case HSSFCell.CELL_TYPE_FORMULA: // 公式  
                cellvalue=String.valueOf(hssfCell.getCellFormula());  
                break;  
            case HSSFCell.CELL_TYPE_BLANK: // 空值  
                cellvalue="";  
                break;  
            case HSSFCell.CELL_TYPE_ERROR: // 故障  
                cellvalue="";  
                break;  
            default:  
                cellvalue="UNKNOWN TYPE";  
                break;  
            }  
        } else {  
            System.out.print("-");  
        }
        return cellvalue.trim();
    }

 最终整个解析Excel文件的功能才算完成，我们需要实现4个方法readXlsForAllSheets和readXlsxForAllSheets，getXssfCellValue和getHssfCellValue，那么有没有更加简单实用的方法呢？

下面要介绍的是POI jar包提供的WorkbookFactory类。需要加载poi-ooxm-3.15.jar到build path。

只需要两行就可以实例化workbook，而不用管它是xls还是xlsx。
1
2
    
inStream = new FileInputStream(new File(filePath));
Workbook workBook = WorkbookFactory.create(inStream);

 后续可以直接操作sheet，Row,Cell,也不用管文件类型。

目前还没有发现这种方法的缺点。