By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,143 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,143 IT Pros & Developers. It's quick & easy.

Attempt to write an excel file with Apache POI causing OutOfMemoryError

blazedaces
100+
P: 284
So I have a program below which writes an excel file with multiple sheets based on inputs of sheet names, data, cell types, etc. It uses Apache POI, which is currently the only thing I found (correct me if I'm wrong about this) that can write excel 2007 files. The program works. I've tested it out on very small files (I will include the commented out main method that you can use to test this out yourself if you'd like, that is assuming you download apache poi and all it's required .jar files too, mostly bean stuff).

Now I'm trying to implement it, but obviously with a bit more data than when I tested it out. It runs out of memory it seems. Here's the stack trace below:

Expand|Select|Wrap|Line Numbers
  1. Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
  2.     at org.apache.xmlbeans.impl.store.Xobj$ElementXobj.newNode(Xobj.java:2710)
  3.     at org.apache.xmlbeans.impl.store.Xobj.copyNode(Xobj.java:1099)
  4.     at org.apache.xmlbeans.impl.store.Xobj.copy_contents_from(Xobj.java:2319)
  5.     at org.apache.xmlbeans.impl.values.XmlObjectBase.setterHelper(XmlObjectBase.java:1907)
  6.     at org.apache.xmlbeans.impl.values.XmlObjectBase.set(XmlObjectBase.java:1954)
  7.     at org.apache.xmlbeans.impl.values.XmlComplexContentImpl.arraySetterHelper(XmlComplexContentImpl.java:1162)
  8.     at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.setCArray(Unknown Source)
  9.     at org.apache.poi.xssf.usermodel.XSSFRow.onDocumentWrite(XSSFRow.java:392)
  10.     at org.apache.poi.xssf.usermodel.XSSFSheet.write(XSSFSheet.java:1736)
  11.     at org.apache.poi.xssf.usermodel.XSSFSheet.commit(XSSFSheet.java:1701)
  12.     at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:171)
  13.     at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:173)
  14.     at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:199)
  15.     at Utils.Utilities.createExcelFile(Utilities.java:660)
  16.     at ReadAndSpitOutSpecificExcelData.makeExcelSheetWithAllTheControlGroupData(ReadAndSpitOutSpecificExcelData.java:400)
  17.     at ReadAndSpitOutSpecificExcelData.main(ReadAndSpitOutSpecificExcelData.java:150)
The error occurs (I'll point out where the error occurs when I post the code further below) at the line where I write "workbook.write(fileOut)" where fileOut is a FileOutputStream. This seems so odd to me. That implies that there's obviously enough memory for ALL of the java objects to store the excel file, but for some reason as it's writing to the hard disk it must grab a whole lot more memory.

Just to tell you, I have attempted to increase the java heap size up until 1 gig (by adding -Xms128m -Xmx1024m) but that still doesn't seem to do the trick. And the thing is, this file when it's finished will probably barely be 1 mb, maybe 2... So I just don't understand what's going on here.

Now, my solution to this problem unless someone can help me figure it out, will be to just write separate excel files and then combine the sheets manually. I assume that'll work, though I could be mistaken.

Anyway, here's the code below:

Expand|Select|Wrap|Line Numbers
  1.     //The following method makes an excel file with a bunch of sheets, each sheet containing some data, sort of all like the
  2.     //method above but for multiple sheets
  3.     public static void createExcelFile(ArrayList<ArrayList<ArrayList>> allSheetsData, ArrayList<ArrayList<String>> allSheetsHeaders, int[][] allSheetsCellTypes, ArrayList<String> sheetNames,String filePath) throws IndexOutOfBoundsException {
  4.         if(allSheetsData.size() != allSheetsHeaders.size() && allSheetsHeaders.size() != allSheetsCellTypes.length && allSheetsCellTypes.length != sheetNames.size())
  5.             throw new IndexOutOfBoundsException("The number of sheets does not match the number of sheet names?");
  6.  
  7.         ArrayList<ArrayList> data;
  8.         ArrayList<String> headers;
  9.         int[] cellTypes;
  10.         String sheetName;
  11.  
  12.         File file = new File(filePath);
  13.         XSSFWorkbook workbook = new XSSFWorkbook();
  14.         XSSFSheet sheet;
  15.         XSSFRow currentRow = null;
  16.         XSSFCell currentCell = null;
  17.  
  18.         try {
  19.             FileOutputStream fileOut = new FileOutputStream(file);
  20.             try {
  21.                 for (int currentSheetNum = 0; currentSheetNum < allSheetsData.size(); currentSheetNum++) {
  22.                     data = allSheetsData.get(currentSheetNum);
  23.                     headers = allSheetsHeaders.get(currentSheetNum);
  24.                     cellTypes = allSheetsCellTypes[currentSheetNum];
  25.                     sheetName = sheetNames.get(currentSheetNum);    
  26.  
  27.                     if(data.size() != headers.size() && headers.size() != cellTypes.length)
  28.                         throw new IndexOutOfBoundsException("The number of data ArrayLists is not equal to the number of headers in sheet " + sheetName);
  29.  
  30.                     sheet = workbook.createSheet(sheetName);
  31.  
  32.                     currentRow = sheet.createRow(0);
  33.                     for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
  34.                         currentCell = currentRow.createCell(cellNum, XSSFCell.CELL_TYPE_STRING);
  35.                         currentCell.setCellValue(headers.get(cellNum));
  36.                     }
  37.  
  38.                     for(int rowNum = 0; rowNum < getSizeOfLongestArrayList(data); rowNum++) {
  39.                         currentRow = sheet.createRow(rowNum + 1);
  40.  
  41.                         for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
  42.                             if (rowNum < data.get(cellNum).size()) {
  43.                                 if(cellTypes[cellNum] == XSSFCell.CELL_TYPE_NUMERIC) {
  44.                                     currentCell = currentRow.createCell(cellNum, cellTypes[cellNum]);
  45.                                     currentCell.setCellValue(((Double) data.get(cellNum).get(rowNum)).doubleValue());
  46.                                 } else if (cellTypes[cellNum] ==XSSFCell.CELL_TYPE_STRING) {
  47.                                     currentCell = currentRow.createCell(cellNum, cellTypes[cellNum]);
  48.                                     currentCell.setCellValue((String) data.get(cellNum).get(rowNum));
  49.                                 }
  50.                             }
  51.                         }
  52.                     }
  53.                 }
  54.  
  55.                 workbook.write(fileOut);  //ERROR HAPPENS  HERE!
  56.             } finally {
  57.                 if (fileOut != null)
  58.                     fileOut.close();
  59.             }
  60.         } catch (FileNotFoundException fnfex) {
  61.             fnfex.printStackTrace(); 
  62.         } catch (IOException ioex) {
  63.             ioex.printStackTrace();
  64.         } 
  65.     }
  66.  
And here's the main method I used to test it out:

Expand|Select|Wrap|Line Numbers
  1. //    public static void main(String args[]) {
  2. //        ArrayList<ArrayList<ArrayList>> twoSheetsData = new ArrayList<ArrayList<ArrayList>>(2);
  3. //        ArrayList<ArrayList<String>> allSheetsHeaders = new ArrayList<ArrayList<String>>(2);
  4. //        int[][] allSheetsCellTypes = new int[2][];
  5. //        ArrayList<String> sheetNames = new ArrayList<String>(2);
  6. //        sheetNames.add("Sheet One");
  7. //        sheetNames.add("Sheet Two");
  8. //        String filePath = "C:/Documents and Settings/Asaf/Desktop/testExcelFile.xlsx";
  9. //        
  10. //        ArrayList<ArrayList> testListForSheet1 = new ArrayList<ArrayList>(3);
  11. //        
  12. //        ArrayList<String> stringList1ForSheet1 = new ArrayList<String>(4);
  13. //        stringList1ForSheet1.add("one");
  14. //        stringList1ForSheet1.add("two");
  15. //        stringList1ForSheet1.add("three");
  16. //        stringList1ForSheet1.add("four");
  17. //                
  18. //        ArrayList<Double> doubleListForSheet1 = new ArrayList<Double>(3);
  19. //        doubleListForSheet1.add(new Double(1));
  20. //        doubleListForSheet1.add(new Double(2));
  21. //        doubleListForSheet1.add(new Double(3));
  22. //        
  23. //        ArrayList<String> stringList2ForSheet1 = new ArrayList<String>(5);
  24. //        stringList2ForSheet1.add("second one");
  25. //        stringList2ForSheet1.add("second two");
  26. //        stringList2ForSheet1.add("second three");
  27. //        stringList2ForSheet1.add("second four");
  28. //        stringList2ForSheet1.add("second five");
  29. //
  30. //        ArrayList<String> headerListForSheet1 = new ArrayList<String>(3);
  31. //        headerListForSheet1.add("First strings");
  32. //        headerListForSheet1.add("Doubles");
  33. //        headerListForSheet1.add("Second strings");
  34. //        
  35. //        testListForSheet1.add(stringList1ForSheet1);
  36. //        testListForSheet1.add(doubleListForSheet1);
  37. //        testListForSheet1.add(stringList2ForSheet1);
  38. //        
  39. //        int[] cellTypesForSheet1 = { XSSFCell.CELL_TYPE_STRING, XSSFCell.CELL_TYPE_NUMERIC, XSSFCell.CELL_TYPE_STRING };
  40. //        
  41. //        ArrayList<ArrayList> testListForSheet2 = new ArrayList<ArrayList>(3);
  42. //        
  43. //        ArrayList<String> stringList1ForSheet2 = new ArrayList<String>(4);
  44. //        stringList1ForSheet2.add("five");
  45. //        stringList1ForSheet2.add("six");
  46. //        stringList1ForSheet2.add("seven");
  47. //        stringList1ForSheet2.add("eight");
  48. //                
  49. //        ArrayList<Double> doubleListForSheet2 = new ArrayList<Double>(3);
  50. //        doubleListForSheet2.add(new Double(4));
  51. //        doubleListForSheet2.add(new Double(5));
  52. //        doubleListForSheet2.add(new Double(6));
  53. //        
  54. //        ArrayList<String> stringList2ForSheet2 = new ArrayList<String>(5);
  55. //        stringList2ForSheet2.add("second six");
  56. //        stringList2ForSheet2.add("second seven");
  57. //        stringList2ForSheet2.add("second eight");
  58. //        stringList2ForSheet2.add("second nine");
  59. //        stringList2ForSheet2.add("second ten");
  60. //
  61. //        ArrayList<String> headerListForSheet2 = new ArrayList<String>(3);
  62. //        headerListForSheet2.add("First strings");
  63. //        headerListForSheet2.add("Doubles");
  64. //        headerListForSheet2.add("Second strings");
  65. //        
  66. //        testListForSheet2.add(stringList1ForSheet2);
  67. //        testListForSheet2.add(doubleListForSheet2);
  68. //        testListForSheet2.add(stringList2ForSheet2);
  69. //        
  70. //        int[] cellTypesForSheet2 = { XSSFCell.CELL_TYPE_STRING, XSSFCell.CELL_TYPE_NUMERIC, XSSFCell.CELL_TYPE_STRING };
  71. //        
  72. //        twoSheetsData.add(testListForSheet1);
  73. //        twoSheetsData.add(testListForSheet2);
  74. //        
  75. //        allSheetsHeaders.add(headerListForSheet1);
  76. //        allSheetsHeaders.add(headerListForSheet2);
  77. //        
  78. //        allSheetsCellTypes[0] = cellTypesForSheet1;
  79. //        allSheetsCellTypes[1] = cellTypesForSheet2;
  80. //        
  81. //        createExcelFile(twoSheetsData, allSheetsHeaders, allSheetsCellTypes, sheetNames, filePath);
  82. //    }
  83.  
Anyway, if you can help me out with this and explain to me either what I'm doing wrong, or what is going on, I would appreciate it. Thank you very much for your time and help.

-blazed
Apr 27 '09 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 392
@blazedaces
That is the only option I know of for Java to create the acutal files. Some other options are to export the data in CSV format and import it to Excel, or for Office 2003 or newer you can save the in an XHTML table and Excel can open that just like a normal Excel file. For this option to work the XHTML has to be well formed, and there is some meta data tweaking that has to be done. To see an example just save a normal Excel file as HTML and look at the file it generates.




@blazedaces
I would bet that it is holding your data in collection API objects, and does not start to add all of the Excel overhead until it starts to write out the file. So at that point it is holding all of the data, and then has to open a buffer to hold all of the data with the file overhead basically more than doubling the RAM being used.




@blazedaces
Are you completely sure this setting is actually being made? I have seen it happen several times where scripts are bing used to start and stop the app server that do not pass the attributes correctly so the extra config options (like Xmx) are never being set in the JVM. You might want to use JStat to verify the memory settings are actually being made.

Also how many rows and data cells are you adding to these files? 128MB should be enough unless there is a massive amount of data... i.e.m More than you can really put in a spreadsheet. Just a note Office 2003 could only handle 65k rows, and earlier version could only do 16k. So if you are going over that the files created will be un-readable any way.

The only other idea that comes to mind would be to try using something like a BufferedOutputStream or BufferedWriter which might use memory more effectively when creating the file.
Apr 29 '09 #2

Expert 100+
P: 392
P.S. I would also try adding the line below just before the write() method call to make sure your heap size settings are actually being made. You might also try using a capital 'M' instead of lower case for setting the Xmx value. I see to remember seeing it that way in examples some where.
Apr 29 '09 #3

blazedaces
100+
P: 284
I ended up doing what I suggested earlier, which is instead of creating one file with multiple sheets just create a few files for each sheet separately. This was successful. Each file was approximately 16kb in size.

The largest file (which would be the largest sheet) was 20kb, used 745 rows, and up to the S column. That's what's so odd. If I was creating an excel sheet with a ridiculous amount of data that would be one thing, but that's simply not the case.

I will take your tip and run this thing with JStat to check that the xmx option is functioning as it should. You're correct in that I frustratingly realized I didn't really know if this option was doing anything.

I would run it right now, but like I said I went ahead without it and I'm in the middle of changing a lot of code to include more data (now that I can create the excel sheets correctly). I'll post as soon as I can again.

Thank you so much for your help,
-blazed
Apr 29 '09 #4

N002213F
P: 39
I have an app that opens an excelsheet about 4MB and does some nice business logic and save into another excel sheet. Checking from file sizes you mention, creating individual files is simply running away from the actual problem. I think you code needs major reworking to improve on efficient use of memory.
May 5 '09 #5

blazedaces
100+
P: 284
@N002213F
I'm not saying the problem isn't in my code... but I'm showing you my code. Where is the problem?

-blazed
May 5 '09 #6

10K+
P: 13,264
Shouldn't you be using HSSFWorkbook instead?
May 5 '09 #7

blazedaces
100+
P: 284
@r035198x
HSSFWorkbook is for excel files with the .xls extension (Excel 97-2003 documents). XSSFWorkbook supposedly acts the same way for Excel 2007 documents that use the .xlsx extension.

No, I don't know enough about it to explain it thoroughly as the current examples on XSSFWorkbooks is minor at best. I had to basically go back and forth looking at examples of work done using HSSFWorkbook and HSSFRows, cells, etc. and looking at the XSSFWorkbook and other XSSF API to figure out how to write my code... And some trial and error as well.

Let me tell you though that obtaining data from excel was the harder part of this "project" if you would call it that. I can give you more details if you want, but I don't think it's very relevant.

Again, thank you guys for your help.

-blazed
May 5 '09 #8

P: 1
Hello guys, I have registered at this forum only for post this link that I've found.

http://svn.apache.org/repos/asf/poi/...gGridDemo.java


Regards,

Dalton Camargo
Aug 17 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.