POI3.8组件研究（一）

  在以前的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)

摘錄自官方的針對2003和2007的解釋如下：

 

              The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.

               OLE2 files include most Microsoft Office files such as XLS, DOC, and PPT as well as MFC serialization API based file formats. The project provides APIs for the OLE2 Filesystem (POIFS) and OLE2 Document Properties (HPSF) .

              Office OpenXML Format is the new standards based XML file format found in Microsoft Office 2007 and 2008. This includes XLSX, DOCX and PPTX. The project provides a low level API to support the Open Packaging Conventions using openxml4j .

                 For each MS Office application there exists a component module that attempts to provide a common high level Java api to both OLE2 and OOXML document formats. This is most developed for Excel workbooks (SS=HSSF+XSSF ) . Work is progressing for Word documents (HWPF+XWPF ) and PowerPoint presentations (HSLF+XSLF) .

 

 

 

       HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

 

          HSSF and XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets. They provide:

• low level structures for those with special needs
• an eventmodel api for efficient read-only access
• a full usermodel api for creating, reading and modifying XLS files

 

 

使用前提條件：

   1.POI的版本必須高於等於3.5.

   2.JDK的版本必須高於等於1.5.

 

 

本文重點代碼講述一下：

針對讀取03和07版本的excel的公共方法如下：

/**
	 * 根據文件的路徑創建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相關的信息：

	/**
	 * 以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;
	}

 

/**
	 * 獲取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中的每行記錄为一個對象。

	/**
	 * 
	 * 以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;
	}

 

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