使用POI解析Excel
Excel作为一种常用的数据存储格式，在很多项目中都会有相应的导入导出的功能。这篇文章会介绍如何使用Java操作Excel，以及如何解决大文件读写时内存溢出的问题。
1、OpenXML标准
Word、Excel、PPT是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)
因为07版后的.xlsx本质上就是一个压缩包，我们完全可以用解压工具打开它。
一个基础的Excel解压之后
更典型的Excel还包括：数字、文本、公式、图表(Chart)、普通列表(Table)、数据透视表(Pivot Table)等内容。
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文件中：
<!-- 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>
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()方法处理磁盘上的临时文件。
下面是使用XSSFWorkbook读取.xlsx文件的例子：
// 打开指定位置的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有不同的方法来读取每种类型的数据：
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的写入：
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
// 使用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."); } }
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压缩。
// 只在内存中保存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，关闭自动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的接口有清楚的认识。
POI也对SAX解析提供了一些支持——XSSFReader。
XSSFReader能帮我们轻松地获取.xlsx压缩包中各个部分的输入流，
XSSFReader有一个子类XSSFBReader用于读取.xlsb文件。
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://blog.csdn.net/holmofy/article/details/82532311
