针对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文件: