使用POI事件模式解析Excel

发表于

POI的两种模式

对于Excel的读取，POI有两种模式，第一种是用户模式，使用比较简单，是将文件一次性读到内存，这种模式在文件比较大的情况下会有OutOfMemory内存溢出的情况。第二种是事件驱动模式，excel的内容是使用XML的格式存储的，处理excel就是解析XML,而目前使用事件驱动模式解析XML的API是SAX(Simple API for XML)，这种模型在读取XML文档时，并没有将整个文档读入内存，而是按顺序将整个文档解析完，在解析过程中，会主动产生事件交给程序中相应的处理函数来处理当前内容。因此这种方式对系统资源要求不高。

事件模式解析步骤

  1. 通过文件路径或者Inputstream调用OPCPackage的open方法生成OPCPackage实例
  2. 通过OPCPackage实例创建XSSFReader实例对象
  3. 通过XSSFReader实例对象获取共享的字符串表
  4. 创建XMLReader实例对象，使用SAX进行解析取共享的字符串表,并设置内容处理器
  5. 开始处理

示例代码

目标

解析一份大数据量的用户名+电话的excel，将解析出来的数据放入List集合中，格式如图:


实体类 AiMobileTemp

1
2
3
4
5
6
7
8
9
10
11
@Data
public class AiMobileTemp {
    /**
     * 用户姓名
     */
    private String name;
    /**
     * 用户手机号
     */
    private String mobile;
}

Excel事件解析类 ExcelXlsxReader类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
package com.yingying.callcenter.component;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections.map.HashedMap;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.Attributes;
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;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class ExcelXlsxReader extends DefaultHandler {
	/**
	 * 共享字符串表
	 */
	private SharedStringsTable sst;
	/**
	 * 上一次的内容
	 */
	private String lastContents;
	/**
	 * 字符串标识
	 */
	private boolean nextIsString;
	/**
	 * 工作表索引
	 */
	private int sheetIndex = -1;
	/**
	 * 行集合
	 */
	private List<String> rowlist = new ArrayList<>();
	/**
	 * 当前行
	 */
	private int curRow = 0;
	/**
	 * 当前列
	 */
	private int curCol = 0;
	private String col = "";
	@SuppressWarnings("rawtypes")
	private Map map = new HashedMap();
	private ExcelReader excelReader;
	public void setExcelRow(ExcelReader excelReader) {
		this.excelReader = excelReader;
	}
	/**
	 * 读取第一个工作簿的入口方法
	 * 
	 * @param path
	 *            文件路径
	 * @param sheetNo
	 *            工作表 从1开始
	 */
	public void readOneSheet(InputStream is, Integer sheetNo) {
		OPCPackage pkg = null;
		InputStream sheet = null;
		try {
			pkg = OPCPackage.open(is);
			XSSFReader r = new XSSFReader(pkg);
			SharedStringsTable sharedStringsTable = r.getSharedStringsTable();
			XMLReader parser = fetchSheetParser(sharedStringsTable);
			sheet = r.getSheet("rId" + sheetNo);
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		} finally {
			try {
				if (sheet != null) {
					sheet.close();
				}
			} catch (IOException e) {
				log.error(e.getMessage(), e);
			}
		}
	}
	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
		this.sst = sst;
		parser.setContentHandler(this);
		return parser;
	}
	@Override
	public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
		// c => 单元格
		if (name.equals("c")) {
			col = attributes.getValue("r");
			// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
			String cellType = attributes.getValue("t");
			if (cellType != null && cellType.equals("s")) {
				nextIsString = true;
			} else {
				nextIsString = false;
			}
		}
		// 置空
		lastContents = "";
	}
	@SuppressWarnings("unchecked")
	@Override
	public void endElement(String uri, String localName, String name) throws SAXException {
		// 根据SST的索引值的到单元格的真正要存储的字符串
		// 这时characters()方法可能会被调用多次
		if (nextIsString) {
			try {
				int idx = Integer.parseInt(lastContents);
				lastContents = sst.getItemAt(idx).toString();
				nextIsString = false;
			} catch (Exception e) {
				log.error(e.getMessage(), e);
			}
		}
		// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
		// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
		if (name.equals("v")) {
			String value = lastContents.trim();
			rowlist.add(curCol, value);
			curCol++;
			map.put(col, value);
		} else {
			// 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
			if (name.equals("row")) {
				// 实际业务逻辑处理
				excelReader.getRow(sheetIndex, curRow, map);
				rowlist.clear();
				curRow++;
				curCol = 0;
			}
		}
	}
	@Override
	public void characters(char[] ch, int start, int length) throws SAXException {
		// 得到单元格内容的值
		lastContents += new String(ch, start, length);
	}
}

事件实现接口 ExcelReader

1
2
3
4
public interface ExcelReader {
	public void getRow(int sheetIndex, int curRow, Map<String, String> map);
}

事件实现具体逻辑类 AiMobileExcelReader

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public class AiMobileExcelReader implements ExcelReader {
	private List<AiMobileTemp> temps = new ArrayList<>();
	@Override
	public void getRow(int sheetIndex, int curRow, Map<String, String> map) {
		if (curRow == 1) {
			return;
		}
		String name = map.get("A" + curRow);
		String mobile = map.get("B" + curRow);
		if (StringUtils.isBlank(name) && StringUtils.isBlank(mobile)) {
			return;
		}
		AiMobileTemp aiMobileTemp = new AiMobileTemp();
		aiMobileTemp.setName(name);
		aiMobileTemp.setMobile(mobile);
		temps.add(aiMobileTemp);
	}
	public List<AiMobileTemp> getAiMobileTemp() {
		return temps;
	}
}

方法调用类 AiTaskServiceImpl

1
2
3
4
5
6
7
8
private List<AiMobileTemp> parseAiCallByExcelUrl(String url) {
		InputStream inputStream = getInputStreamByRemoteUrl(url);
		ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
		AiMobileExcelReader aiMobileExcelReader = new AiMobileExcelReader();
		excelXlsxReader.setExcelRow(aiMobileExcelReader);
		excelXlsxReader.readOneSheet(inputStream, 1);
		return aiMobileExcelReader.getAiMobileTemp();
	}

小结

以上代码基本实现了excel大批量数据导入的需求，不会发生内存溢出的情况了，但还有不少地方可以优化和封装，需要进一步完善。

参考文章

  1. POI事件模式读取Excel 2007
  2. POI解决读入Excel内存溢出
  3. POI 事件模式解析xlsx
  4. POI读写海量Excel
  5. POI事件模式解析Excel 2007（二） - SAX简介