473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Attempt to write an excel file with Apache POI causing OutOfMemoryError

blazedaces
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?");
  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
8 28253
pronerd
392 Expert 256MB
@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
pronerd
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
blazedaces
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,
-blazed
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
blazedaces
284 100+
@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
r035198x
13,262 8TB
Shouldn't you be using HSSFWorkbook instead?
May 5 '09 #7
blazedaces
284 100+
@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
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

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

Similar topics

9
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...
1
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...
2
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...
4
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"; ...
27
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?
19
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...
8
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...
15
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. ...
17
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.