POI3.8组件研究（一）---基于User API (HSSF and XSSF)解析Excel2003和2007文件
?????? 在以前的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);//获取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++) {//创建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);//获取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++) {//创建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;}?