Excel工作表POI验证：内存不足的错误 [英] EXcel Sheet POI Validation : Out Of Memory Error

问题描述

我想转储到数据库之前验证使用Java Excel文件。

I am trying to validate an excel file using java before dumping it to database.

下面是我的code片段导致错误。

Here is my code snippet which causes error.

try {
        fis = new FileInputStream(file);
        wb = new XSSFWorkbook(fis);
        XSSFSheet sh = wb.getSheet("Sheet1");
        for(int i = 0 ; i < 44 ; i++){
            XSSFCell a1 = sh.getRow(1).getCell(i);
            printXSSFCellType(a1);
        }

    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

下面是我得到的错误

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at java.util.ArrayList.<init>(Unknown Source)
    at java.util.ArrayList.<init>(Unknown Source)
    at org.apache.xmlbeans.impl.values.NamespaceContext$NamespaceContextStack.<init>(NamespaceContext.java:78)
    at org.apache.xmlbeans.impl.values.NamespaceContext$NamespaceContextStack.<init>(NamespaceContext.java:75)
    at org.apache.xmlbeans.impl.values.NamespaceContext.getNamespaceContextStack(NamespaceContext.java:98)
    at org.apache.xmlbeans.impl.values.NamespaceContext.push(NamespaceContext.java:106)
    at org.apache.xmlbeans.impl.values.XmlObjectBase.check_dated(XmlObjectBase.java:1273)
    at org.apache.xmlbeans.impl.values.XmlObjectBase.stringValue(XmlObjectBase.java:1484)
    at org.apache.xmlbeans.impl.values.XmlObjectBase.getStringValue(XmlObjectBase.java:1492)
    at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTCellImpl.getR(Unknown Source)
    at org.apache.poi.xssf.usermodel.XSSFCell.<init>(XSSFCell.java:105)
    at org.apache.poi.xssf.usermodel.XSSFRow.<init>(XSSFRow.java:70)
    at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(XSSFSheet.java:179)
    at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:143)
    at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:130)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:286)
    at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:159)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:207)
    at com.xls.validate.ExcelValidator.main(ExcelValidator.java:79)

这工作完全正常的时候XLSX文件小于1 MB。

This works perfectly fine when xlsx file is less than 1 MB.

据我了解，这是因为我的XLSX文件的大小约为5-10 MB和POI试图在JVM内存

I understand this is because my xlsx file is around 5-10 MB and POI tries to load the entire sheet at once in JVM Memory

什么可能是一个可能的解决方法？

What can be a possible workaround?

请帮忙。

先谢谢了！

推荐答案

有提供给你两个选择。选项​​1 - 增加JVM堆的大小，从而使Java有向它提供更多的内存。使用的usermodel code在POI处理Excel文件是基于DOM的，所以需要将缓冲到内存中的整个文件（包括解析形式）。试着像如何增加帮助咨询这一块一个问题。

There are two options available to you. Option #1 - increase the size of your JVM Heap, so that Java has more memory available to it. Processing Excel files in POI using the UserModel code is DOM based, so the whole file (including parsed form) needs to be buffered into memory. Try a question like this one for advice on how to increase the help.

选项＃2，这是更多的工作 - 基于（SAX）处理切换到事件。这只是一次处理文件的一部分，所以需要很多很多的内存更少。但是，它需要从你更多的工作，这就是为什么你可能会更好这个问题抛出的记忆几个GB - 内存很便宜，而程序员都没有！该 US preadSheet HOWTO页面对如何做SAX解析说明的的.xlsx文件，并有<一个href=\"https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel\">various通过POI提供的示例文件你可以看看的意见。

Option #2, which is more work - switch to event based (SAX) processing. This only processes part of the file at a time, so needs much much less memory. However, it requires more work from you, which is why you might be better throwing a few more GB of memory at the problem - memory is cheap while programmers aren't! The SpreadSheet howto page has instructions on how to do SAX parsing of .xlsx files, and there are various example files provided by POI you can look at for advice.

此外，另一件事 - 你似乎是通过加载一个流，这是坏的文件，因为它意味着更多的东西需要的缓冲内存。请参阅 POI文档，了解更多关于这个，包括如何一起工作的说明文件直接。

Also, another thing - you seem to be loading a File via a stream, which is bad as it means even more stuff needs buffering into memory. See the POI Documentation for more on this, including instructions on how to work with the File directly.

这篇关于Excel工作表POI验证：内存不足的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！