欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

POI3.8组件研究(七)--基于XSSF and SAX (Event API)事件的解析

程序员文章站 2022-07-13 13:17:37
...

    针对Event API事件解析仅仅支持excel97~03版本的excel,然而基于XSSF and SAX (Event API)事件的解析仅仅支持excel2007解析方式:同时需要创建SAX处理器继承自SAXHandler对象。

采用XSSF and SAX (Event API) 方式解析Excel2007
        excel2007是一个标准xml所以可以采用sax解析的模式。
         优点:1.效率比较高。
         缺点: 2.只能支持excel2007,针对97~2003格式的excel非标准的xml不支持

 

针对SAX的解析处理器对象:

 

package com.easyway.excel.events.sax;

import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;
/**
 *  创建SAX解析处理器必须继承自
 *  org.xml.sax.helpers.DefaultHandler
 * 实现响应的方法。
 * @Title: 
 * @Description: 实现TODO
 * @Copyright:Copyright (c) 2011
 * @Company:易程科技股份有限公司
 * @Date:2012-6-17
 * @author  longgangbai
 * @version 1.0
 */
public class ExcelSheetSAXHandler extends DefaultHandler {
	private SharedStringsTable sst;
	private String lastContents;
	private boolean nextIsString;
	
	public ExcelSheetSAXHandler(SharedStringsTable sst) {
		this.sst = sst;
	}
	
	public void startElement(String uri, String localName, String name,
			Attributes attributes) throws SAXException {
		// c => cell
		if(name.equals("c")) {
			// Print the cell reference
			System.out.print(attributes.getValue("r") + " - ");
			// Figure out if the value is an index in the SST
			String cellType = attributes.getValue("t");
			if(cellType != null && cellType.equals("s")) {
				nextIsString = true;
			} else {
				nextIsString = false;
			}
		}
		// Clear contents cache
		lastContents = "";
	}
	
	public void endElement(String uri, String localName, String name)
			throws SAXException {
		// Process the last contents as required.
		// Do now, as characters() may be called more than once
		if(nextIsString) {
			int idx = Integer.parseInt(lastContents);
			lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
        nextIsString = false;
		}

		// v => contents of a cell
		// Output after we've seen the string contents
		if(name.equals("v")) {
			System.out.println(lastContents);
		}
	}

	public void characters(char[] ch, int start, int length)
			throws SAXException {
		lastContents += new String(ch, start, length);
	}
}
 

针对HSSF+SAX的类:

package com.easyway.excel.events.sax;

import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.openxml4j.opc.Package;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
/**
 * 采用XSSF and SAX (Event API) 方式解析Excel2007
 *  
 *    excel2007是一个标准xml所以可以采用sax解析的模式。
 *    优点:1.效率比较高。
 *    缺点: 2.只能支持excel2007,针对97~2003格式的excel非标准的xml不支持
 * 
 * @Title: 
 * @Description: 实现TODO
 * @Copyright:Copyright (c) 2011
 * @Company:易程科技股份有限公司
 * @Date:2012-6-17
 * @author  longgangbai
 * @version 1.0
 */
public class SAXHandlerEventUserModel {
	/**
	 * 处理excel中只有一个sheet的方法
	 * @param filename
	 * @throws Exception
	 */
	public void processOneSheet(String filename) throws Exception {
		@SuppressWarnings("deprecation")
		Package pkg = Package.open(filename);
		//创建excel2007的阅读器对象
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
		XMLReader parser = fetchSheetParser(sst);

		// rId2 found by processing the Workbook
		// Seems to either be rId# or rSheet#
		InputStream sheet2 = r.getSheet("rId2");
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
	}

	/**
	 * 处理excel中有多个sheet的格式的 
	 * @param filename
	 * @throws Exception
	 */
	public void processAllSheets(String filename) throws Exception {
		Package pkg = Package.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
		XMLReader parser = fetchSheetParser(sst);
		//获取多个sheet的输入流对象
		Iterator<InputStream> sheets = r.getSheetsData();
		while(sheets.hasNext()) {
			System.out.println("Processing new sheet:\n");
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
			System.out.println("");
		}
	}

	/**
	 * 设置xml阅读器的解析器对象
	 * @param sst
	 * @return
	 * @throws SAXException
	 */
	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		//通过放射方式获取xml阅读器对象
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
					"org.apache.xerces.parsers.SAXParser"
			);
	     //创建相关的xml解析器对象
		ContentHandler handler = new ExcelSheetSAXHandler(sst);
		//设置解析器对象
		parser.setContentHandler(handler);
		return parser;
	}
}
 

测试方法如下:

package com.easyway.excel.events.sax;
/**
 * 
 * 
 * @Title: 测试SAX方式解析Excel 
 * @Description: 实现TODO
 * @Copyright:Copyright (c) 2011
 * @Company:易程科技股份有限公司
 * @Date:2012-6-17
 * @author  longgangbai
 * @version 1.0
 */
public class SAXEventUserModel {
	public static void main(String[] args) throws Exception {
		System.out.println("开始采用SAX解析Excel !");
		SAXHandlerEventUserModel howto = new SAXHandlerEventUserModel();
		howto.processOneSheet("C:\\station2stationone.xlsx");
		System.out.println("单独一个sheet解析完毕!");
		howto.processAllSheets("C:\\station2stationmany.xlsx");
		System.out.println("单独多个个sheet解析完毕!");
		System.out.println("采用SAX解析Excel 完毕!");
	}

}

 

 

测试所需的两个excel文件: