使用POI解析Excel

Excel作为一种常用的数据存储格式,在很多项目中都会有相应的导入导出的功能。这篇文章会介绍如何使用Java操作Excel,以及如何解决大文件读写时内存溢出的问题。

1、OpenXML标准

WordExcelPPT是Office办公套件中最常用的三个组件。早期的Office套件使用二进制格式,这里面包括以.doc.xls.ppt为后缀的文件;直到07这个划时代的版本将基于XML的压缩格式作为默认文件格式,也就是相应以.docx.xlsx.pptx为后缀的文件。

这个结合了XML与Zip压缩技术的新文件格式使用的是OpenXML标准。微软从2000年开始酝酿这项技术标准,到2006年申请成为ECMA-376,然后在Office2007中用作默认的文件格式,再到08年成为了ISO / IEC 29500国际标准,后续每两三年就会发布一个新版本。Office的一路凯歌无不彰显微软雄厚的实力。

所以说三流公司做产品,二流公司做平台,一流公司定标准。

微软的官方文档中详细介绍了WordprocessingML(Word)SpreadsheetML(Excel)PresentationML(PPT)三个标准,这里主要介绍Excel的部分内容。

首先Excel几个最基础的概念:

  • 一个Excel就是一个工作簿(Workbook)
  • 一个Sheet就是一张表格
  • 一个Workbook可以包含多个Sheet
  • 每一行Row的每一列就是一个单元格(Cell)

Exce基础结构

因为07版后的.xlsx本质上就是一个压缩包,我们完全可以用解压工具打开它。

一个基础的Excel解压之后,目录结构大致如下:

Excel文件解压后的结构

更典型的Excel还包括:数字、文本、公式、图表(Chart)、普通列表(Table)、数据透视表(Pivot Table)等内容。

Excel

Excel远比我们想象的复杂

2、使用POI操作Excel

Java领域最常见的两个操作Excel的工具库分别是JXL(Java Excel API)和Apache的POI。JXL有个严重的缺点就是只支持07版本之前的二进制格式Excel,而POI除了能操作Excel,还可以操作Word和PPT以及Office套装中其他的组件,高下立现。

POI全称是Poor Obfuscation Implementation,简洁模糊实现,也有人翻译成糟糕的模糊实现。

POI目前最新版本是4.0,可以将相应maven依赖添加到pom.xml文件中:

1
2
3
4
5
6
7
8
9
10
11
12
<!-- poi:07版之前的二进制格式 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<!-- poi-ooxml:07版之后的OpenXML格式 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>

ComponentMap

POI提供了三种读写Excel的方式

HSSF,XSSF,SXSSF

1、HSSF支持.xls为后缀的二进制格式,并提供了流解析模式的HSSFListener相关API以及基于内存模型的HSSFWorkbook相关API。

2、XSSF支持.xlsx为后缀的OpenXML格式。因为是底层文件是XML所以可以使用SAX解析,POI提供了XSSFReader用来获取压缩包中的各个XML文件相应的输入流;另外提供了基于DOM解析模式的XSSFWorkbook相关API。

3、POI3.8后提供了SXSSF API,它是基于XSSF构建的低内存占用版本(使用滑动窗口机制来实现低内存访问)。但是需要注意的是SXSSFWorkbook默认使用内联字符串而不是共享字符串表(SharedStringsTable),这样可以让保存在内存中的数据尽可能更少(SharedStringsTable需要常驻内存),所以如果是自己写SAX解析要注意兼容性。

POI滑动窗口只窗口范围内的单元格数据加载到内存中,窗口外的数据读写内容会以临时文件的形式保存到磁盘上,同时还支持临时文件的压缩。SXSSF可以通过构造函数中的rowAccessWindowSize参数指定窗口大小,compressTmpFiles指定是否压缩临时文件,useSharedStringsTable指定是否使用共享字符表。

2.1、使用Workbook API

上面说的三种方式都有一个Workbook实现类,用法上基本一致。唯一不同的是SXSSFWorkbook最后需要调用dispose()方法处理磁盘上的临时文件。

Workbook继承图

下面是使用XSSFWorkbook读取.xlsx文件的例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 打开指定位置的Excel文件
FileInputStream file = new FileInputStream(new File(fileLocation));
Workbook workbook = new XSSFWorkbook(file);
// 打开Excel中的第一个Sheet
Sheet sheet = workbook.getSheetAt(0);
// 读取Sheet中的数据
Map<Integer, List<String>> data = new HashMap<>();
int i = 0;
for (Row row : sheet) { // 行
data.put(i, new ArrayList<String>());
for (Cell cell : row) { // 单元格
switch (cell.getCellType()) { // 不同的数据类型
case STRING: ... break; // 字符串类型
case NUMERIC: ... break; // 数值类型
case BOOLEAN: ... break; // 布尔类型
case FORMULA: ... break; // 公式类型
case BLANK: ... break; // 空白类型
}
}
i++;
}

POI有不同的方法来读取每种类型的数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
switch(cell.getCellType()) {
case CellType.STRING:
data.get(i).add(cell.getRichStringCellValue().getString());
break;
case CellType.NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
data.get(i).add(cell.getDateCellValue));
} else {
data.get(i).add(cell.getNumericCellValue());
}
break;
case CellType.BOOLEAN:
data.get(i).add(cell.getBooleanCellValue());
break;
case CellType.FORMULA:
data.get(i).add(cell.getCellFormula());
break;
case CellType.BLANK:
data.get(i).add("")
break;
}

Workbook API也支持Excel的写入:

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
Workbook workbook = new XSSFWorkbook(); // 创建工作簿
Sheet sheet = workbook.createSheet("Persons"); // 创建Sheet
sheet.setColumnWidth(1, 4000);
Row header = sheet.createRow(0); // 创建表头行
CellStyle headerStyle = workbook.createCellStyle(); // 表头单元格样式
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFFont font = ((XSSFWorkbook) workbook).createFont(); // 字体样式
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBold(true);
headerStyle.setFont(font);
Cell headerCell = header.createCell(0); // 创建表头单元格
headerCell.setCellValue("Name");
headerCell.setCellStyle(headerStyle);
headerCell = header.createCell(1); // 创建表头单元格
headerCell.setCellValue("Age");
headerCell.setCellStyle(headerStyle);
CellStyle style = workbook.createCellStyle(); // 普通单元格样式
style.setWrapText(true);
Row row = sheet.createRow(2); // 写入单元格
Cell cell = row.createCell(0);
cell.setCellValue("John Smith");
cell.setCellStyle(style);
cell = row.createCell(1); // 写入单元格
cell.setCellValue(20);
cell.setCellStyle(style);
// 最后写出到文件
FileOutputStream outputStream = Files.newOutputStream("/path/to/excel");
workbook.write(outputStream);
workbook.close();

POI还支持插入图片、形状、数据透视表以及更多的样式,更多详细代码可以参考官方的快速入门指南

2.2、HSSFListener实现流式解析

虽然SXSSFWorkbook通过滑动窗口有效地降低了内存消耗,但是并不支持读的功能,而且写功能也只支持OpenXML格式。而HSSFWorkbook和XSSFWorkbook需要将Excel内容全部读取到内存才能操作,对于二进制Excel大文件的读取必须使用HSSFListener。

不过03版二进制Excel能支持的最大行数为65536

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
// 使用EventAPI读取Excel
public class EventExample implements HSSFListener
{
private SSTRecord sstrec;
// 实现接口方法
public void processRecord(Record record)
{
switch (record.getSid()) {
case BOFRecord.sid: // Beginning Of File
BOFRecord bof = (BOFRecord) record;
if (bof.getType() == bof.TYPE_WORKBOOK) {
System.out.println("Encountered workbook");
} else if (bof.getType() == bof.TYPE_WORKSHEET) {
System.out.println("Encountered sheet reference");
}
break;
case BoundSheetRecord.sid:
BoundSheetRecord bsr = (BoundSheetRecord) record;
System.out.println("New sheet named:" + bsr.getSheetname());
break;
case RowRecord.sid: // 行
RowRecord rowrec = (RowRecord) record;
System.out.println("first column:" + rowrec.getFirstCol() + ","
"last column:" + rowrec.getLastCol());
break;
case NumberRecord.sid: // 数字单元格
NumberRecord numrec = (NumberRecord) record;
System.out.println("Row:"+numrec.getRow() + ","
"Column:" + numrec.getColumn() + ","
"Number value:" + numrec.getValue());
break;
case SSTRecord.sid: // Static String Table Record
sstrec = (SSTRecord) record;
System.out.println("String table value:");
for (int k = 0; k < sstrec.getNumUniqueStrings(); k++) {
System.out.println(k + " = " + sstrec.getString(k));
}
break;
case LabelSSTRecord.sid:
LabelSSTRecord lrec = (LabelSSTRecord) record;
System.out.println("String cell value:" + sstrec.getString(lrec.getSSTIndex()));
break;
}
}
public static void main(String[] args) throws IOException
{
FileInputStream fin = new FileInputStream("/path/to/file");
POIFSFileSystem poifs = new POIFSFileSystem(fin);
InputStream din = poifs.createDocumentInputStream("Workbook");
// 构造 HSSFRequest 对象
HSSFRequest req = new HSSFRequest();
// 监听所有的Record
req.addListenerForAllRecords(new EventExample());
// 创建EventFactory
HSSFEventFactory factory = new HSSFEventFactory();
// 将输入流交给EventFactory解析生成事件
factory.processEvents(req, din);
// 事件处理完后关闭输入流
fin.close();
din.close();
System.out.println("done.");
}
}

Record继承图

2.3、SXSSF API

SXSSF(org.apache.poi.xssf.streaming)是兼容XSSF API的流式扩展,用于生成数据量较大的Excel文件。SXSSF通过限制滑动窗口内行的访问来实现低内存占用,而XSSF API允许访问文档中的所有行。不在窗口中的旧行将不可访问,因为它们已经被写入磁盘。

您可以通过new SXSSFWorkbook(int windowSize)指定窗口大小, 也可以通过SXSSFSheet.setRandomAccessWindowSize(int windowSize)设置每个sheet的窗口大小

当通过createRow()创建新行时,且尚未flush()的数据总量超过指定的窗口大小时,将flush()内存中最前面的行,并且不能再通过getRow()访问该行。

默认窗口大小为100,由SXSSFWorkbook.DEFAULT_WINDOW_SIZE定义。

windowSize为-1表示无限制访问。在这种情况下,所有未调用flushRows()强制刷新的记录都可访问。

请注意,SXSSF会创建临时文件,所以最后必须通过调用dispose()方法来显式清理临时文件

SXSSFWorkbook默认使用内联字符串而不是共享字符串表。因为不需要在内存中保存文档字符内容,所以这种方式能有效地减低内存消耗,但是也导致了生成与某些客户端不兼容的文档。启用共享字符串后,文档中所有的唯一字符串都必须保留在内存中,所以这可能会比禁用共享字符串消耗更多的资源。

另外还需要注意,根据你使用的功能,仍然可能消耗大量内存,例如合并区域,超链接,注释……,这些内容只存储在内存中。

在决定是否启用共享字符串之前,请仔细检查内存预算和兼容性需求。

SXSSF在将sheet的数据刷新到临时文件中(每个sheet一个临时文件),这些临时文件可能会变得非常大。比如,对于20 MB的csv数据,临时xml文件将超过GB字节。如果临时文件的大小是个问题,可以通过setCompressTempFiles(true)让SXSSF使用gzip压缩。

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
// 只在内存中保存100, 超出的行将会flush到磁盘
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sh = wb.createSheet();
// 写入1000行数据
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
// 前900行已经flush到磁盘了,无法访问
for(int rownum = 0; rownum < 900; rownum++){
Assert.assertNull(sh.getRow(rownum));
}
// 最后的100行仍可以访问
for(int rownum = 900; rownum < 1000; rownum++){
Assert.assertNotNull(sh.getRow(rownum));
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// 处理磁盘上的临时文件
wb.dispose();

我们还可以将windowSize置为-1,通过调用SXSSFSheet.flushRows()手动刷新到磁盘

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
// 置为-1,关闭自动flush
SXSSFWorkbook wb = new SXSSFWorkbook(-1);
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
// 手动控制flush
if(rownum % 100 == 0) {
// 保留最后100行数据,其余全部flush到磁盘
((SXSSFSheet)sh).flushRows(100);
// ((SXSSFSheet)sh).flushRows() == ((SXSSFSheet)sh).flushRows(0),
}
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// 处理磁盘上的临时文件
wb.dispose();

2.4、使用SAX解析xlsx文件

虽然大文件的写入有SXSSF的支持,但是读取暂时没有更好的解决方案。POI目前推荐的做法是直接使用SAX API手动解析XML。这要求开发者对Excel的接口有清楚的认识。

Sheet与SharedStrings文件结构

POI也对SAX解析提供了一些支持——XSSFReader

XSSFReader能帮我们轻松地获取.xlsx压缩包中各个部分的输入流,

XSSFReader有一个子类XSSFBReader用于读取.xlsb文件

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
public class ExampleEventUserModel {
public void processOneSheet(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// 根据 Sheet Name / Sheet Order / rID 查找相应的Sheet
// 你只需要使用SAX API处理相应的输入流
// 通常情况使用 rId# 或 rSheet# 形式的id
InputStream sheet2 = r.getSheet("rId2");
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
public void processAllSheets(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
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("");
}
}
public 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;
}
// 使用SAX API解析XML
private static class SheetHandler extends DefaultHandler {
private SharedStringsTable sst; // 共享字符串表
private String lastContents;
private boolean nextIsString;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
if(name.equals("c")) {
// r => reference
System.out.print(attributes.getValue("r") + " - ");
// t => type
String cellType = attributes.getValue("t");
// s => string
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 清空上一次的内容
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
if(nextIsString) {
// sheet中存储了共享字符表的索引
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
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);
}
}
public static void main(String[] args) throws Exception {
ExampleEventUserModel example = new ExampleEventUserModel();
example.processOneSheet(args[0]);
example.processAllSheets(args[0]);
}
}

上面的代码是使用原生SAX API进行XML处理的例子,它要求我们知道sheet.xml文件的内容结构。POI已经将这部分逻辑封装在了XSSFSheetXMLHandler中,我们只要实现它暴露的SheetContentsHandler接口即可。

使用SheetContentsHandler的例子可以参考官方的XLSX2CVS

3、写在最后

Excel本身有很多已知的限制,如最大行数和最大列数(这些限制可以参考SpreadsheetVersion),理论上只要你有足够大的内存,你就能使用Workbook API对任意Excel进行读写。

很多场景需要我们克服内存限制,总结下来有以下方案:

1、大文解析使用SAX

2、大文件写入使用SXSSFWorkbook

还有一种妥协的方案是将数据分别写入到多个Excel中,最后对这些Excel打包。

最近在Github上看到阿里的一位大佬开发的EasyExcel,不过还没实际使用过,处于观望阶段…

参考链接:

https://en.wikipedia.org/wiki/Office_Open_XML

http://poi.apache.org/components/spreadsheet/how-to.html