Bug 55769

Summary: Out of Memory Error while reading .Xlsx file
Product: POI Reporter: CTcreations <ctvipin007>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: blocker    
Priority: P2    
Version: 3.9-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description CTcreations 2013-11-12 12:11:07 UTC
Hi,

I am using Apache POI for reading .xlsx files, using POI 3.9 version jar files.
While reading the excel file I am getting out of memory error, and also it creates some .txt,.phd files in the folder.
Data in Excel file  is only 100 rows. Error is given below.

<<
JVMDUMP006I Processing dump event "systhrow", detail "java/lang/OutOfMemoryError" - please wait.
JVMDUMP032I JVM requested Heap dump using '/dsProjects/Scripts/C_Analysis/heapdump.20131112.064446.57043.0001.phd' in response to an event
JVMDUMP010I Heap dump written to /dsProjects/Scripts/C_Analysis/heapdump.20131112.064446.57043.0001.phd
>>

Also I am using XSSF Workbook. Is there any idea to resolve this issue? 
Is there any function available like Dispose() for resolving this issue?
Comment 1 Nick Burch 2013-11-12 12:40:40 UTC
The default JVM heap size is very small. You either need to increase it to a more sensible size, or if you really are constrained to a tiny heap, switch to SAX based parsing (as detailed on the website)
Comment 2 CTcreations 2013-11-12 14:39:16 UTC
But when I use the SAX parser , it can't take null valued cell..
Comment 3 Nick Burch 2013-11-12 15:16:57 UTC
If you're not sure how to use the SAX parser, ask on the user list. If you've found a bug in the SAX parser, please raise a new bugzilla entry for that. As it stands, you just need to either increase your heap size to something sensible, or use SAX
Comment 4 CTcreations 2013-11-13 07:22:38 UTC
Am using this code...But it cannot[Neglect] empty cell... Is there any idea to get null cell in the following code ???

....void parseExcel(File file) throws IOException 
	{
		
  OPCPackage container;
        try 
		{
            container = OPCPackage.open(file.getAbsolutePath());
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(container);
            XSSFReader xssfReader = new XSSFReader(container);
            StylesTable styles = xssfReader.getStylesTable();
            XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
			
            while (iter.hasNext()) 
			{
				
                InputStream stream = iter.next();
				processSheet(styles, strings, stream);
                stream.close();
				
            }


	protected static void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, SAXException 
	{
				
        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        try 
		{
            SAXParser saxParser = saxFactory.newSAXParser();
            XMLReader sheetParser = saxParser.getXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, new SheetContentsHandler() 
			{
				
				@Override
                public void startRow(int rowNum) 
				{
					//System.out.println("")
					
				}
                @Override
                public void endRow() 
				{
                	stringList.add("Row");
                }
                @Override
                public void cell(String cellReference, String formattedValue) 
				{
					System.out.println(formattedValue);
				}
                @Override
                public void headerFooter(String text, boolean isHeader, String tagName) 
				{
                	System.out.println(isHeader?"Header " +text:"Footer "+text );
                }

            }, 
            false//means result instead of formula
            );
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
        } 
		catch 
		{
Comment 5 Nick Burch 2013-11-13 22:02:27 UTC
My previous comment still stands:

If you're not sure how to use the SAX parser, ask on the user list. If you've found a bug in the SAX parser, please raise a new bugzilla entry for that.For *this* bug, you just need to either increase your heap size to something sensible, or use SAX properly