POI3.8组件研究（一）

? 在以前的Excel解析候，我通常需要Excel解析只能解析一格式03版或者07版。在POI3.5以後可以解析格式。我知道在07的excel是基於xml格式的文件。

??? POI3.5以後的API包括如下方面：

/** * 根文件的路建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;}/** * 以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);//取sheetint 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++) {//建sheetSheet sheet=workbook.getSheetAt(index);//取sheet的名??String sheetName=workbook.getSheetName(index);//取相的??List<Map<String,Object>> sheetData=getExcelMapData(sheet, headTitle);excelData.put(sheetName, sheetData);}}return excelData;}?

/** * 取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的方式存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);//取sheetint 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++) {//建sheetSheet sheet=workbook.getSheetAt(index);//取sheet的名??String sheetName=workbook.getSheetName(index);//取相的??List<T> sheetData=getExcelBeanData(sheet, headTitle,clazz);excelData.put(sheetName, sheetData);}}return excelData;}/** * 取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;}

?