POI 3.8 处理Excel03 和Excel07

原文：http://topmanopensource.iteye.com/blog/1560593

在以前的Excel解析时候，我们通常需要编写Excel解析只能解析一种格式03版或者07版。现在POI3.5以后可以解析两种格式。我们知道在07的excel是基于xml格式的文件。

POI3.5以后的API包括如下几个方面：

             Excel (SS=HSSF+XSSF)

             Word (HWPF+XWPF)

             PowerPoint (HSLF+XSLF)

             OpenXML4J (OOXML)

             OLE2 Filesystem (POIFS)

             OLE2 Document Props (HPSF)

              Outlook (HSMF)

             Visio (HDGF)

             TNEF (HMEF)

             Publisher (HPBF)

 

本文重点代码讲述一下：

针对读取03和07版本的excel的公共方法如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

/**         * 根据文件的路径创建Workbook对象         * @param filePath         */      private Workbook getExcelWorkBook(String filePath) {            InputStream ins = null ;            Workbook book = null ;            try {                ins= new FileInputStream( new File(filePath));                //ins= ExcelService.class.getClassLoader().getResourceAsStream(filePath);                book = WorkbookFactory.create(ins);                ins.close();                return book;            } catch (FileNotFoundException e1) {                e1.printStackTrace();            } catch (InvalidFormatException e) {                e.printStackTrace();            } catch (IOException e) {                e.printStackTrace();            } finally {                if (ins != null ) {                    try {                        ins.close();                    } catch (IOException e) {                        e.printStackTrace();                    }                }            }            return null ;        }

将文件转换为FileInputStream，然后再转换为Workbook，最后读取相关的信息。

 

在读取excel的文件时候以Map形式读取相关的excel相关的信息：

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

/**    * 以Map的格式存储数据    * 读取Excel文件的数据   * @param filePath   excel 文件的    * @param headTitle       * @return    */ public Map<String,List<Map<String,Object>>> readEXCELMap(String filePath,String[] headTitle){       //获取workbook对象       Workbook workbook=getExcelWorkBook(filePath);       //获取sheet页数       int sheetNum=workbook.getNumberOfSheets();       //存储excel相关的数�?       Map<String,List<Map<String,Object>>> excelData= new HashMap<String,List<Map<String,Object>>>();       //遍历相关sheet页面获取相关的数�?       if (sheetNum>0){           for (int index = 0; index < sheetNum; index++) {               //创建sheet               Sheet sheet=workbook.getSheetAt(index);               //获取sheet的名               String sheetName=workbook.getSheetName(index);               //获取相关的数�              List<Map<String,Object>> sheetData=getExcelMapData(sheet, headTitle);               excelData.put(sheetName, sheetData);           }       }       return excelData;  }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

/**        * 获取sheet表中的数�?        * @param sheet        * @return�?eadTitle 格式�?.1.2....列标做为key        */      private List<Map<String,Object>> getExcelMapData(Sheet sheet,String[] headTitle){           //获取�?��和结束行           int startRow=sheet.getFirstRowNum();           int lastRow=sheet.getLastRowNum();           List<Map<String,Object>> allRowMapData= new ArrayList<Map<String,Object>>();           if (startRow!=lastRow){               //忽略第一行数�?               startRow=startRow+1;               //获取行数�?               for (int indexRow=startRow;indexRow<lastRow;indexRow++){                   Row row=sheet.getRow(indexRow);                   if (row== null ){                       continue ;                   }                   int firstCellNum=row.getFirstCellNum();                   int lastCellNum=row.getLastCellNum();                   Map<String,Object> RowDataMap= new HashMap<String,Object>();                   //遍历相关的列数据                   for (int indexCol = firstCellNum; indexCol <lastCellNum; indexCol++) {                       Cell cell=row.getCell(indexCol);                       String cellKey=headTitle[indexCol-firstCellNum];                       if (cell== null ){                           continue ;                       }                       //获取列的数据的信�?                       Object cellValue = getCellValue(cell);                       RowDataMap.put(cellKey, cellValue);                   }                   allRowMapData.add(RowDataMap);               }           }                         return allRowMapData;       }

 

以Bean形式保存excel多个sheet中的每行记录为一个对象。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

/**   *    * 以Bean的方式存储bean对象   * 读取Excel文件的数�?   * @param filePath   excel 文件的路   * @param headTitle      * @param clazz   * @return   */ public Map<String,List<T>> readEXCELBean(String filePath,String[] headTitle,Class<T> clazz){      //获取workbook对象      Workbook workbook=getExcelWorkBook(filePath);      //获取sheet页数      int sheetNum=workbook.getNumberOfSheets();      //存储excel相关的数�?      Map<String,List<T>> excelData= new HashMap<String,List<T>>();      //遍历相关sheet页面获取相关的数�?      if (sheetNum>0){          for (int index = 0; index < sheetNum; index++) {              //创建sheet              Sheet sheet=workbook.getSheetAt(index);              //获取sheet的名�?              String sheetName=workbook.getSheetName(index);              //获取相关的数�?              List<T> sheetData=getExcelBeanData(sheet, headTitle,clazz);              excelData.put(sheetName, sheetData);          }      }      return excelData; }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

/**       * 获取sheet表中的数�?       * @param sheet       * @param sheet�?eadTitle bean每列对应的属性数�?       * @param clazz   bean对应的类       * @throws InstantiationException        */      @SuppressWarnings( "unused" )      private List<T> getExcelBeanData(Sheet sheet,String[] headTitle,Class<T> clazz) {          //获取�?��和结束行          int startRow=sheet.getFirstRowNum();          int lastRow=sheet.getLastRowNum();          List<T> allRowMapData= new ArrayList<T>();          if (startRow!=lastRow){              //忽略第一行数�?              startRow=startRow+1;              //获取行数�?              for (int indexRow=startRow;indexRow<lastRow;indexRow++){                  Row row=sheet.getRow(indexRow);                  if (row== null ){                      continue ;                  }                  int firstCellNum=row.getFirstCellNum();                  int lastCellNum=row.getLastCellNum();                  T bean= null ;                  try {                      bean = clazz.newInstance();                      //遍历相关的列数据                      for (int indexCol = firstCellNum; indexCol <lastCellNum; indexCol++) {                          Cell cell=row.getCell(indexCol);                          //indexCol=11 firstCellNum 0 lastCellNum=11                          //System.out.println("indexCol="+indexCol+"firstCellNum "+firstCellNum+" lastCellNum="+lastCellNum+" headTitle.length"+headTitle.length);                          String cellKey=headTitle[indexCol-firstCellNum];                          if (cell== null ){                              continue ;                          }                          //获取列的数据的信�?                          Object cellValue = getCellValue(cell);                          try {                              BeanUtils.setProperty(bean, cellKey, cellValue);                          } catch (InvocationTargetException e) {                              e.printStackTrace();                          }                      }                      allRowMapData.add(bean);                  } catch (InstantiationException e1) {                      e1.printStackTrace();                  } catch (IllegalAccessException e1) {                      e1.printStackTrace();                  }              }          }                      return allRowMapData;