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;