Attempt to write an excel file with Apache POI causing OutOfMemoryError

[blazedaces]

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:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at org.apache.xmlbeans.impl.store.Xobj$ElementXobj.newNode(Xobj.java:2710)
    at org.apache.xmlbeans.impl.store.Xobj.copyNode(Xobj.java:1099)
    at org.apache.xmlbeans.impl.store.Xobj.copy_contents_from(Xobj.java:2319)
    at org.apache.xmlbeans.impl.values.XmlObjectBase.setterHelper(XmlObjectBase.java:1907)
    at org.apache.xmlbeans.impl.values.XmlObjectBase.set(XmlObjectBase.java:1954)
    at org.apache.xmlbeans.impl.values.XmlComplexContentImpl.arraySetterHelper(XmlComplexContentImpl.java:1162)
    at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.setCArray(Unknown Source)
    at org.apache.poi.xssf.usermodel.XSSFRow.onDocumentWrite(XSSFRow.java:392)
    at org.apache.poi.xssf.usermodel.XSSFSheet.write(XSSFSheet.java:1736)
    at org.apache.poi.xssf.usermodel.XSSFSheet.commit(XSSFSheet.java:1701)
    at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:171)
    at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:173)
    at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:199)
    at Utils.Utilities.createExcelFile(Utilities.java:660)
    at ReadAndSpitOutSpecificExcelData.makeExcelSheetWithAllTheControlGroupData(ReadAndSpitOutSpecificExcelData.java:400)
    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:

    //The following method makes an excel file with a bunch of sheets, each sheet containing some data, sort of all like the
    //method above but for multiple sheets
    public static void createExcelFile(ArrayList<ArrayList<ArrayList>> allSheetsData, ArrayList<ArrayList<String>> allSheetsHeaders, int[][] allSheetsCellTypes, ArrayList<String> sheetNames,String filePath) throws IndexOutOfBoundsException {
        if(allSheetsData.size() != allSheetsHeaders.size() && allSheetsHeaders.size() != allSheetsCellTypes.length && allSheetsCellTypes.length != sheetNames.size())
            throw new IndexOutOfBoundsException("The number of sheets does not match the number of sheet names?");
 
        ArrayList<ArrayList> data;
        ArrayList<String> headers;
        int[] cellTypes;
        String sheetName;
 
        File file = new File(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet;
        XSSFRow currentRow = null;
        XSSFCell currentCell = null;
 
        try {
            FileOutputStream fileOut = new FileOutputStream(file);
            try {
                for (int currentSheetNum = 0; currentSheetNum < allSheetsData.size(); currentSheetNum++) {
                    data = allSheetsData.get(currentSheetNum);
                    headers = allSheetsHeaders.get(currentSheetNum);
                    cellTypes = allSheetsCellTypes[currentSheetNum];
                    sheetName = sheetNames.get(currentSheetNum);    
 
                    if(data.size() != headers.size() && headers.size() != cellTypes.length)
                        throw new IndexOutOfBoundsException("The number of data ArrayLists is not equal to the number of headers in sheet " + sheetName);
 
                    sheet = workbook.createSheet(sheetName);
 
                    currentRow = sheet.createRow(0);
                    for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
                        currentCell = currentRow.createCell(cellNum, XSSFCell.CELL_TYPE_STRING);
                        currentCell.setCellValue(headers.get(cellNum));
                    }
 
                    for(int rowNum = 0; rowNum < getSizeOfLongestArrayList(data); rowNum++) {
                        currentRow = sheet.createRow(rowNum + 1);
 
                        for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
                            if (rowNum < data.get(cellNum).size()) {
                                if(cellTypes[cellNum] == XSSFCell.CELL_TYPE_NUMERIC) {
                                    currentCell = currentRow.createCell(cellNum, cellTypes[cellNum]);
                                    currentCell.setCellValue(((Double) data.get(cellNum).get(rowNum)).doubleValue());
                                } else if (cellTypes[cellNum] ==XSSFCell.CELL_TYPE_STRING) {
                                    currentCell = currentRow.createCell(cellNum, cellTypes[cellNum]);
                                    currentCell.setCellValue((String) data.get(cellNum).get(rowNum));
                                }
                            }
                        }
                    }
                }
 
                workbook.write(fileOut);  //ERROR HAPPENS  HERE!
            } finally {
                if (fileOut != null)
                    fileOut.close();
            }
        } catch (FileNotFoundException fnfex) {
            fnfex.printStackTrace(); 
        } catch (IOException ioex) {
            ioex.printStackTrace();
        } 
    }
 

And here's the main method I used to test it out:

//    public static void main(String args[]) {
//        ArrayList<ArrayList<ArrayList>> twoSheetsData = new ArrayList<ArrayList<ArrayList>>(2);
//        ArrayList<ArrayList<String>> allSheetsHeaders = new ArrayList<ArrayList<String>>(2);
//        int[][] allSheetsCellTypes = new int[2][];
//        ArrayList<String> sheetNames = new ArrayList<String>(2);
//        sheetNames.add("Sheet One");
//        sheetNames.add("Sheet Two");
//        String filePath = "C:/Documents and Settings/Asaf/Desktop/testExcelFile.xlsx";
//        
//        ArrayList<ArrayList> testListForSheet1 = new ArrayList<ArrayList>(3);
//        
//        ArrayList<String> stringList1ForSheet1 = new ArrayList<String>(4);
//        stringList1ForSheet1.add("one");
//        stringList1ForSheet1.add("two");
//        stringList1ForSheet1.add("three");
//        stringList1ForSheet1.add("four");
//                
//        ArrayList<Double> doubleListForSheet1 = new ArrayList<Double>(3);
//        doubleListForSheet1.add(new Double(1));
//        doubleListForSheet1.add(new Double(2));
//        doubleListForSheet1.add(new Double(3));
//        
//        ArrayList<String> stringList2ForSheet1 = new ArrayList<String>(5);
//        stringList2ForSheet1.add("second one");
//        stringList2ForSheet1.add("second two");
//        stringList2ForSheet1.add("second three");
//        stringList2ForSheet1.add("second four");
//        stringList2ForSheet1.add("second five");
//
//        ArrayList<String> headerListForSheet1 = new ArrayList<String>(3);
//        headerListForSheet1.add("First strings");
//        headerListForSheet1.add("Doubles");
//        headerListForSheet1.add("Second strings");
//        
//        testListForSheet1.add(stringList1ForSheet1);
//        testListForSheet1.add(doubleListForSheet1);
//        testListForSheet1.add(stringList2ForSheet1);
//        
//        int[] cellTypesForSheet1 = { XSSFCell.CELL_TYPE_STRING, XSSFCell.CELL_TYPE_NUMERIC, XSSFCell.CELL_TYPE_STRING };
//        
//        ArrayList<ArrayList> testListForSheet2 = new ArrayList<ArrayList>(3);
//        
//        ArrayList<String> stringList1ForSheet2 = new ArrayList<String>(4);
//        stringList1ForSheet2.add("five");
//        stringList1ForSheet2.add("six");
//        stringList1ForSheet2.add("seven");
//        stringList1ForSheet2.add("eight");
//                
//        ArrayList<Double> doubleListForSheet2 = new ArrayList<Double>(3);
//        doubleListForSheet2.add(new Double(4));
//        doubleListForSheet2.add(new Double(5));
//        doubleListForSheet2.add(new Double(6));
//        
//        ArrayList<String> stringList2ForSheet2 = new ArrayList<String>(5);
//        stringList2ForSheet2.add("second six");
//        stringList2ForSheet2.add("second seven");
//        stringList2ForSheet2.add("second eight");
//        stringList2ForSheet2.add("second nine");
//        stringList2ForSheet2.add("second ten");
//
//        ArrayList<String> headerListForSheet2 = new ArrayList<String>(3);
//        headerListForSheet2.add("First strings");
//        headerListForSheet2.add("Doubles");
//        headerListForSheet2.add("Second strings");
//        
//        testListForSheet2.add(stringList1ForSheet2);
//        testListForSheet2.add(doubleListForSheet2);
//        testListForSheet2.add(stringList2ForSheet2);
//        
//        int[] cellTypesForSheet2 = { XSSFCell.CELL_TYPE_STRING, XSSFCell.CELL_TYPE_NUMERIC, XSSFCell.CELL_TYPE_STRING };
//        
//        twoSheetsData.add(testListForSheet1);
//        twoSheetsData.add(testListForSheet2);
//        
//        allSheetsHeaders.add(headerListForSheet1);
//        allSheetsHeaders.add(headerListForSheet2);
//        
//        allSheetsCellTypes[0] = cellTypesForSheet1;
//        allSheetsCellTypes[1] = cellTypesForSheet2;
//        
//        createExcelFile(twoSheetsData, allSheetsHeaders, allSheetsCellTypes, sheetNames, filePath);
//    }
 

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

[pronerd]

@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.

[pronerd]

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.

[blazedaces]

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

[N002213F]

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.

[blazedaces]

@N002213F
I'm not saying the problem isn't in my code... but I'm showing you my code. Where is the problem?

-blazed

[r035198x]

Shouldn't you be using HSSFWorkbook instead?

[blazedaces]

@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

[daltondecamargo]

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