【怎么查看excel内存】Apache POI Excel读取和写入大量数据

通过Excel导入导出的场景在软件系统开发中很常见，Java开发人员通常使用Apache POI库处理Excel。这是非常受欢迎的Java API for Microsoft Documents。

阅读XSSFWorkbook Excel

最常用的读取代码如下：

workbook workbook=new xssfworkbook([输入流])；

In (0，workbook.getnumberofsheets())。forearch(

图纸索引-{

sheet sheet=workbook . getsheet at(sheet index)；

//数据处理

}

)如果导入的数据量少，通常可以正常工作，但如果导入的Excel数据量非常大，操作环境内存不足，则会出现内存溢出错误。为了进行测试，我们构建了包含8000行数据的Excel，设置了JVM参数“-Xms32M -Xmx32M”，出现了以下错误：

Exception in thread 'main '

Java . lang . out of memory error : Java heapspace此文件仅占用279K的磁盘大小。由于xlsx默认为压缩的XML文件堆，并且是XML，因此POI处理Excel文件时使用的内存远大于279K。如果将此XML放入未压缩内存中，内存消耗将增加10倍，计算出添加

XSSFReader读取Excel

为了解决内存使用问题，XSSF可以导入基本XML数据并自行处理。需要学习一些xlsx文件的结构，但可以使用相对较小的安装内存读取大量数据的XLSX文件。

XML解析也分为DOM解析和SAX解析。使用DOM很简单，但占用大量内存，而SAX解析将XML文件作为流读取，并通过事件触发器通知代码。对于XLSX文件格式，未填充内容的单元格在XML中不存在。也就是说，并非每一行都有相同数量的单元格Cell元素，因此必须通过代码解决这些问题。

解压缩的Excel文件

要在Excel中查看XML信息，请将Excel后缀设置为“.zip”，然后打开“XL workSheets”以查看该图纸的标签节点信息。

如前面的屏幕快照所示，sheet标签包含row(行标签)、c(单元格标签)、r(Cell的Excel编号属性)、t(Cell的Type属性)等节点，您可以访问Microsoft以了解XML的结构

接下来，创建XSSFReader实例，并使用SAX解析读取数据。示例代码如下：

Public class XSSFReaderExample {

public static void main(string[]args){

Opc软件包pkg=OPC软件包。open(文件路径)；

XSS freader r=new XSS freader(pkg)；

sharedstringstable SST=r . getsharedstringstable()；

XML reader parser=XML hel()；

content handler handler=new sheet handler(SST)；

(处理程序)；

从//XML取得Id，通常是rId# or rSheet#

inputstream sheet=r . get sheet(' rid 1 ')；

input source sheet source=new input source(sheet)；

(图纸来源)；

()；

}

private static class sheet handler extends default handler {

私有final sharedstringstable SST

私有字符串lastContents

专用布尔扩展；

//Row

数据,使用TreeMap保持顺序 private Map<String, String> rowData = new TreeMap<>(); private int cellSeq = 0; // Cell序列,处理Cell为空的情况 private int rowSeq = 0; // Row序列 private OrderSheetHandler(SharedStringsTable sst) { = sst; } @Override public void startElement(String uri, String localName, String qName, Attributes attributes) { if ("row")) { cellSeq = 0; rowSeq = In("r")); } // c => cell if ("c")) { String cellSeqStr = CellReference .convertNumToColString(cellSeq++) + rowSeq; String readCellStr = a("r"); while (!cellSeqS(readCellStr)) { rowDa(cellSeqStr, ""); cellSeqStr = CellRe(cellSeq++) + rowSeq; } String cellType = a("t"); if (cellType != null && cellTy("s")) { nextIsString = true; } else { nextIsString = false; } } // Clear contents cache lastContents = ""; } @Override public void endElement(String uri, String localName, String qName) { if ("row")) { // 处理行数据rowData rowDa(); } if (nextIsString) { // sheet xml中存储的是文字索引 int idx = In(lastContents); lastContents = (idx).getString(); nextIsString = false; } // v => 单元格的内容 if ("v")) { rowDa( CellRe(cellSeq - 1) + rowSeq, lastContents); } } // 接收元素内字符数据 @Override public void characters(char[] ch, int start, int length) { lastContents += new String(ch, start, length); } } }

采用XSSFWorkbook案例相同的内存配置，使用上述代码读取大数据量Excel，能轻松处理，不会出现内存溢出的现象。

XSSFWorkbook写入Excel

XSSF是POI提供的常用Excel写入工具，示例代码如下：

XSSFWorkbook wb = new XSSFWorkbook(); Sheet sh = wb.createSheet("order"); AtomicInteger rownum = new AtomicInteger(0); Row headerRow = ()); (0).setCellValue("Title-1"); (1).setCellValue("Title-2"); (2).setCellValue("Title-3"); (3).setCellValue("Title-4"); InClosed(), 8000) .forEach( idx -> { Row row = ()); row.createCell(0).setCellValue("value-column-1" + idx); row.createCell(1).setCellValue("value-column-2" + idx); row.createCell(2).setCellValue("value-column-3" + idx); row.createCell(3).setCellValue("value-column-4" + idx); }); wb.write(new FileOutputStream(filePath));

设置JVM启动参数“-Xms16M -Xmx16M”运行上述代码，遇到如下错误：

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space at org.a$Tex(Saver.java:1701) ......

原因依然是由于XSSF在内存中组织数据，需要消耗大量的内存。这类错误笔者曾在给某金融公司做顾问时听到他们的抱怨，他们的生产环境在导出几次大量数据的Excel之后，内存占用开始上升，直至遇到内存溢出的错误。

SXSSFWorkbook写入Excel

好消息是Apache POI 引入了SXSSF，用于在电子表格中流式传输非常大量的数据，具有非常好的性能和低内存使用率。示例代码如下：

// .... 与上面的代码一致 SXSSFWorkbook wb = new SXSSFWorkbook); Sheet sh = wb.createSheet("order"); // .... 与上面的代码一致

与上面的代码的区别在于使用SXSSFWorkbook来写入，它的构造函数的参数是允许在内存中的数据行数，当达到行数后row的索引值会被刷新，临时数据会被写入磁盘，通过以下代码可以设置POI使用的临时目录：

Tem( new DefaultTempFileCreationStrategy(new File(poiTempDir)));

此时运行代码可以观察到POI处理数据时所生成的临时文件，如下图：

POI Temp File

这两种处理Excel写入的方式内存占用差异见下图，前部分是SXSSF方式的内存占用，非常稳定，后部分是XSSF方式，内存占用非常大。

XSSF与SXSSF内存占用对比

此外大数据量的写入不都是像示例代码中这样通过逻辑写入数据，通常是将数据库中的数据读取出来组织后写入Excel，要注意设置好JDBC featchSize参数，流式读取以防止内存占用过大造成OOM。

本文总结

1. POI读取的2种方式：XSSF与SAX解析，读取大数据量用SAX解析防止OOM；
2. POI写入的2种方式：XSSF与SXSSF，写入大数据量用SXSSF方式OOM；
3. JDBC读取大数据量设置featchSize参数，流失读取防止大量内存占用；

---

参考资料

另：示例代码POI版本：4.1.2