POI3.8组件研究（一）---基于User API (HSSF and XSSF)解析Excel2003和2007文件

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

 

评论

4 楼 longgangbai 2012-11-29  

tanghanlin 写道

唯一没有看懂的是： Object cellValue = getCellValue(cell);
这个是怎么来的？ 麻烦告知:[e-mail]

那个根据单元个的类型获取单元格的值。

3 楼 tanghanlin 2012-11-29  

这个对我帮助很大，再留个言，对与上面 Object cellValue = getCellValue(cell);
自己添加了一个方法，测试通过；另外楼主的上面用到了apache的beanutils，需要下载相关jar。添加的getCellValue方法为：

	private Object getCellValue(Cell cell){
		Object value = null;
		int cellType = cell.getCellType();
		switch(cellType){
		case Cell.CELL_TYPE_BLANK:
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_ERROR:
			value = cell.getErrorCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			value = cell.getCellFormula();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			value = cell.getNumericCellValue();
			break;
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		}
		return value;
	}

2 楼 tanghanlin 2012-11-29  

唯一没有看懂的是： Object cellValue = getCellValue(cell);
这个是怎么来的？ 麻烦告知:[e-mail]

1 楼 tanghanlin 2012-11-29  

写的非常好，感谢分享精神！