472,973 Members | 2,367 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,973 software developers and data experts.

Attempt to write an excel file with Apache POI causing OutOfMemoryError

284 100+
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?");
  7.         ArrayList<ArrayList> data;
  8.         ArrayList<String> headers;
  9.         int[] cellTypes;
  10.         String sheetName;
  12.         File file = new File(filePath);
  13.         XSSFWorkbook workbook = new XSSFWorkbook();
  14.         XSSFSheet sheet;
  15.         XSSFRow currentRow = null;
  16.         XSSFCell currentCell = null;
  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);    
  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);
  30.                     sheet = workbook.createSheet(sheetName);
  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.                     }
  38.                     for(int rowNum = 0; rowNum < getSizeOfLongestArrayList(data); rowNum++) {
  39.                         currentRow = sheet.createRow(rowNum + 1);
  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.                 }
  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.     }
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. //    }
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.

Apr 27 '09 #1
8 28084
392 Expert 256MB
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.

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.

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
392 Expert 256MB
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
284 100+
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,
Apr 29 '09 #4
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
284 100+
I'm not saying the problem isn't in my code... but I'm showing you my code. Where is the problem?

May 5 '09 #6
13,262 8TB
Shouldn't you be using HSSFWorkbook instead?
May 5 '09 #7
284 100+
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.

May 5 '09 #8
Hello guys, I have registered at this forum only for post this link that I've found.



Dalton Camargo
Aug 17 '10 #9

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

Similar topics

by: Rich | last post by:
Hi, I have a bunch of Excel reports that I would like to display on my company's intranet. The reports contain priviledged information, however. My plan was to have a page with a dropdown box...
by: Andre Ranieri | last post by:
I'm having trouble programatically inserting an Excel file into an Image column in our CRM package's SQL 2000 database. The function appears to work ok, but when I attempt to access the file through...
by: Ryan Riddell | last post by:
I wrote an application to pull data from certain ranges of an Excel workbook. The application works great for the first 2 of 4 sections I created. I created the third section of code and the...
by: Kevin R. | last post by:
Greetings, I have the following code that attempts to download an excel file: System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; ...
by: jeniffer | last post by:
I need to create an excel file through a C program and then to populate it.How can it be done?
by: wreckingcru | last post by:
I'm trying to output a SQL query that is constructed thru my VB.net GUI into an excel file. Here is the code I'm using: 'Sqlstmt is the SQL query statement 'Conn is the SQL Connection object...
by: shenkel55 | last post by:
I'm using Access and Excel 2003. Using either the import wizard or code, I have the same problem. This problem only happens with Excel files automatically generated by Corp IT. If I try to do an...
by: patf | last post by:
Hi - experienced programmer but this is my first Python program. This URL will retrieve an excel spreadsheet containing (that day's) msci stock index returns. ...
by: timleonard | last post by:
I seem to be over my head and can use some help... I have pieced together some code from several internet searched that is supposed to export a query from access to an existing excel file. I also...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.