POI事件驱动读取Excel分析

前言
前一篇文章http://codingo.xyz/index.php/2017/06/29/poi_excel/介绍了实际项目中读取Excel导致内存溢出的问题,最后总结了在读取大Excel情况下,优先使用事件驱动模式读取,但是文件并没有对事件驱动模式做过多分析,本文将对事件驱动模式做简单分析。

xml解析器
jdk本身提供了两种XMl解析器:
1.像文档对象模型(Document Object Model,DOM)解析器这样的树型解析器(tree parser),它们将读入的XML文档转换成树结构;
2.像用于XML的简单API(Simple API for XML,SAX)解析器这样的流机制解析器(streaming parser),它们在读入XML文档时生成
相应的事件;

当文档很大,并且处理算法非常简单,可以在运行时解析节点,而不必要看到所有的树型结构时,DOM可能就会显得效率低下了,在这种情况下,应该使用流机制解析器(streaming parser);
SAX解析器使用的事件回调(event callback),SAX解析器在解析XML输入的构件时就报告事件,但不会以任何方式存储文档。由事件处理器决定是否要建立数据结构实际上,DOM解析器是在SAX解析器的基础上建立起来的,它在接受到解析器事件时就建立DOM树;

在使用SAX解析器时,需要一个处理器来定义不同的解析器事件的事件动作。ContentHandler接口定义了若干个回调方法:
1.startElement和endElement在每当遇到起始或终止标签时调用;
2.characters每当遇到字符数据时调用;
3.startDocument和endDocument分别在文档开始和结束时各调用一次。

可以发现poi读取Excel的事件驱动模式api正是使用的SAX解析器,为什么SAX可以读取Excel,主要还是因为Excel2007以后,其内容采用XML的格式来存储,所以处理excel就是解析XML;可以改变Excel的后缀为.zip,就可以查看里面的xml文件了。

准备Excel
准备一个test.xlsx用来做分析,内容如下:
QQ图片20170709211310

修改test.xlsx后缀为zip,打开zip文件,目录如下:
QQ截图20170709211510

xl文件夹下的worksheets和sharedStrings.xml是我们比较关心的;worksheets存放了每个sheet存储的数据,但是里面的string类型数据都存放在了sharedStrings.xml文件中,部分文件内容如下;
sheet1中的sheetData如下:

  <sheetData>
    <row r="1" spans="1:5" x14ac:dyDescent="0.15">
      <c r="A1" s="1" t="s">
        <v>2</v>
      </c>
      <c r="B1" s="1" t="s">
        <v>3</v>
      </c>
      <c r="C1" s="1" t="s">
        <v>4</v>
      </c>
      <c r="D1" s="1" t="s">
        <v>5</v>
      </c>
      <c r="E1" s="1" t="s">
        <v>6</v>
      </c>
    </row>
    <row r="2" spans="1:5" x14ac:dyDescent="0.15">
      <c r="A2" s="1" t="s">
        <v>1</v>
      </c>
      <c r="B2" s="2" t="s">
        <v>0</v>
      </c>
      <c r="C2" s="1">
        <v>99999</v>
      </c>
      <c r="D2" s="1"/>
      <c r="E2" s="1">
        <v>11.11</v>
      </c>
    </row>
  </sheetData>

row标签表示每一行,c标签表示cell,v标签表示内容;c标签属性t用来表示类型,t=‘s’表示value是字符串,而在sheet文件中存放的是value的索引,真正的值在sharedStrings.xml,内容如下:

<?xml version="1.0" encoding="utf-8"?>

<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="7" uniqueCount="7">
  <si>
    <t>12345</t>
    <phoneticPr fontId="1" type="noConversion"/>
  </si>
  <si>
    <t>zhaohui</t>
    <phoneticPr fontId="1" type="noConversion"/>
  </si>
  <si>
    <t>col1</t>
    <phoneticPr fontId="1" type="noConversion"/>
  </si>
  <si>
    <t>col2</t>
    <phoneticPr fontId="1" type="noConversion"/>
  </si>
  <si>
    <t>col3</t>
    <phoneticPr fontId="1" type="noConversion"/>
  </si>
  <si>
    <t>col4</t>
    <phoneticPr fontId="1" type="noConversion"/>
  </si>
  <si>
    <t>col5</t>
    <phoneticPr fontId="1" type="noConversion"/>
  </si>
</sst>

每个si标签表示的就是一个具体的值,第一个值的索引是0,往后就是1,2,3,4….

事件驱动模式过程
对上文中的SheetHandler做如下修改,方便查看过程:

private static class SheetHandler extends DefaultHandler {
		private SharedStringsTable sst;
		private String lastContents;

		private SheetHandler(SharedStringsTable sst) {
			this.sst = sst;
		}

		public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
			lastContents="";
			System.out.println("startElement:uri=" + uri + ",localName=" + localName + ",name=" + name + ",type="
					+ attributes.getValue("t"));
		}

		public void endElement(String uri, String localName, String name) throws SAXException {
			System.out.println("endElement:uri=" + uri + ",localName=" + localName + ",name=" + name);
		}

		public void characters(char[] ch, int start, int length) throws SAXException {
			lastContents += new String(ch, start, length);
			System.out.println("characters:lastContents=" + lastContents);
		}
	}

读取test.xlsx文件,部分输出结果如下:

startElement:localName=sheetData,name=sheetData,type=null
startElement:localName=row,name=row,type=null
startElement:localName=c,name=c,type=s
startElement:localName=v,name=v,type=null
characters:lastContents=0
endElement:localName=v,name=v
endElement:localName=c,name=c
startElement:localName=c,name=c,type=s
startElement:localName=v,name=v,type=null
characters:lastContents=1
endElement:localName=v,name=v
endElement:localName=c,name=c
startElement:localName=c,name=c,type=s
startElement:localName=v,name=v,type=null
characters:lastContents=2
endElement:localName=v,name=v
endElement:localName=c,name=c
startElement:localName=c,name=c,type=s
startElement:localName=v,name=v,type=null
characters:lastContents=3
endElement:localName=v,name=v
endElement:localName=c,name=c
startElement:localName=c,name=c,type=s
startElement:localName=v,name=v,type=null
characters:lastContents=4
endElement:localName=v,name=v
endElement:localName=c,name=c
endElement:localName=row,name=row
startElement:localName=row,name=row,type=null
startElement:localName=c,name=c,type=s
startElement:localName=v,name=v,type=null
characters:lastContents=5
endElement:localName=v,name=v
endElement:localName=c,name=c
startElement:localName=c,name=c,type=s
startElement:localName=v,name=v,type=null
characters:lastContents=6
endElement:localName=v,name=v
endElement:localName=c,name=c
startElement:localName=c,name=c,type=null
startElement:localName=v,name=v,type=null
characters:lastContents=99999
endElement:localName=v,name=v
endElement:localName=c,name=c
startElement:localName=c,name=c,type=null
endElement:localName=c,name=c
startElement:localName=c,name=c,type=null
startElement:localName=v,name=v,type=null
characters:lastContents=11.11
endElement:localName=v,name=v
endElement:localName=c,name=c
endElement:localName=row,name=row
endElement:localName=sheetData,name=sheetData

从输出的内容可以发现正是读取的worksheets/sheet1.xml文件;触发的过程是startElement,其实是characters,最后是endElement;元素包括sheetData,row,c,v都将经历这三个过程,除非单元格内没有内容,将只能解析出startElement:c和endElement:c,最后将提供一个完整的实例。

实例代码
以下将提供一个完整的实例代码,可以指定开始读的行数,将返回List>类型的所有数据:

package zh.excelTest;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
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.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

public class EventModel {

	// 开始读数据的行数
	private int beginRow;
	// 结束读数据的行数
	private int endRow;
	// 所有值列表
	private List<List<Object>> allValueList = new ArrayList<>();

	public EventModel(int beginRow) {
		this.beginRow = beginRow;
	}

	public EventModel(int beginRow, int rows) {
		this.beginRow = beginRow;
		this.endRow = this.beginRow + rows - 1;
	}

	public void processOneSheet(String filename) throws Exception {
		InputStream sheet = null;
		try {
			OPCPackage pkg = OPCPackage.open(filename);
			XSSFReader r = new XSSFReader(pkg);
			SharedStringsTable sst = r.getSharedStringsTable();

			XMLReader parser = fetchSheetParser(sst);
			sheet = r.getSheet("rId1");
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
		} catch (Exception e) {
			throw e;
		} finally {
			if (sheet != null) {
				sheet.close();
			}
		}
	}

	private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	public List<List<Object>> getAllValueList() {
		return allValueList;
	}

	private class SheetHandler extends DefaultHandler {
		private SharedStringsTable sst;
		private String lastContents;
		private boolean isString;
		private boolean validRow;
		// 一行的所有数据
		private List<Object> rowValueList;

		private SheetHandler(SharedStringsTable sst) {
			this.sst = sst;
		}

		public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
			if (name.equals("row") || name.equals("c")) {
				int column = getColumn(attributes);
				if (column < beginRow || (endRow > 0 && column > endRow)) {
					validRow = false;
				} else {
					validRow = true;
					if (name.equals("row")) {
						rowValueList = new ArrayList<>();
						allValueList.add(rowValueList);
					}
					String cellType = attributes.getValue("t");
					if (cellType != null && cellType.equals("s")) {
						isString = true;
					} else {
						isString = false;
					}
				}
			}
			lastContents = "";
		}

		public void endElement(String uri, String localName, String name) throws SAXException {
			if (validRow) {
				if (isString) {
					int idx = Integer.parseInt(lastContents);
					lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
					isString = false;
					validRow = false;
					if (name.equals("v")) {
						rowValueList.add(lastContents);
					}
				} else {
					if (name.equals("c")) {
						rowValueList.add(lastContents);
					}
				}
			}
		}

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

		private int getColumn(Attributes attributes) {
			String row = attributes.getValue("r");
			int firstDigit = -1;
			for (int c = 0; c < row.length(); ++c) {
				if (Character.isDigit(row.charAt(c))) {
					firstDigit = c;
					break;
				}
			}
			return Integer.valueOf(row.substring(firstDigit, row.length()));
		}
	}
}

总结
本文只针对excel2007以后的版本读取,对excel的读取间接转化为对xml的解析,这其中主要依靠OpenXML4J,OpenXML4J是一个Java类库用于创建和操作基于Office Open XML(ECMA-376)与OPC规范的文档。