首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 网站开发 > Web前端 >

POI3.8组件研究(1)

2012-07-16 
POI3.8组件研究(一)? 在以前的Excel解析時候,我們通常需要編寫Excel解析只能解析一種格式03版或者07版。現

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);//獲取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;}

?

热点排行