While writting complex Excel files getting GC Overhead limit Exceeded Error

Question

In Java I am trying to write the Excel workbook which contain 27 sheets and in each sheet near about 500 to 600 columns are there, But when i run the program, it is giving me GC Overhead error.

This is the Exception I got.

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
  at org.apache.xmlbeans.impl.store.Cur$Locations.<init>(Cur.java:495)
  at org.apache.xmlbeans.impl.store.Locale.<init>(Locale.java:168)
  at org.apache.xmlbeans.impl.store.Locale.getLocale(Locale.java:235)
  at org.apache.xmlbeans.impl.store.Locale.newInstance(Locale.java:586)
  at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.newInstance(SchemaTypeLoaderBase.java:198)
  at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell$Factory.newInstance(Unknown Source)
  at org.apache.poi.xssf.usermodel.XSSFCell.setBlank(XSSFCell.java:696)
  at org.apache.poi.xssf.usermodel.XSSFCell.setCellType(XSSFCell.java:737)
  at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:328)
  at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:315)

Possible duplicate of [java.lang.OutOfMemoryError: GC overhead limit exceeded](http://stackoverflow.com/questions/5839359/java-lang-outofmemoryerror-gc-overhead-limit-exceeded) — Ward, Dec 08 '15 at 13:57

Sándor Juhos · Answer 1 · 2015-12-08T13:36:03.980

1

You should use SXSSFWorkbook. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.

public static void main(String[] args) throws Throwable {
    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Sheet sh = wb.createSheet();
    for(int rownum = 0; rownum < 1000; rownum++){
        Row row = sh.createRow(rownum);
        for(int cellnum = 0; cellnum < 10; cellnum++){
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
        }

    }

    // Rows with rownum < 900 are flushed and not accessible
    for(int rownum = 0; rownum < 900; rownum++){
      Assert.assertNull(sh.getRow(rownum));
    }

    // ther last 100 rows are still in memory
    for(int rownum = 900; rownum < 1000; rownum++){
        Assert.assertNotNull(sh.getRow(rownum));
    }

    FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
    wb.write(out);
    out.close();

    // dispose of temporary files backing this workbook on disk
    wb.dispose();
}

Example: https://poi.apache.org/spreadsheet/how-to.html#sxssf

edited Dec 08 '15 at 13:36

answered Dec 08 '15 at 13:23

Sándor Juhos

1,143
9
18

Link only answers are discouraged on SO, as links tend to break. If there is an example that solves OP's problem, please include it (the solution, not just the link) in your answer. – Tgsmith61591 Dec 08 '15 at 13:26
okay i will try this and let you know about it. thanx in advance – Swapnil Chidrawar Dec 08 '15 at 13:27
Hey Sándor Juhos i used SXSSF instead XSSF but now it is taking too much time to write the excel sheets to write 27 sheets. could you please help me to sort out the same – Swapnil Chidrawar Dec 08 '15 at 14:53
It is slower because of io operations, you should find the best rowAccessWindowSize for creating workbook(new SXSSFWorkbook(rowAccessWindowSize)). If you increase this parameter your workbook operations will be faster, but you will use more memory. If you still have unacceptable performance, you should try increase the heap size of the jvm and the rowAccessWindowSize. – Sándor Juhos Dec 08 '15 at 16:04
hey Sándor Juhos i am using 64 bit java and i increased the heap size to maximum limit that is 4 GB still it is giving problem. – Swapnil Chidrawar Dec 09 '15 at 07:45
Did you increase the rowAccessWindowSize also? – Sándor Juhos Dec 09 '15 at 07:48
Independently from the great advice Sandor is giving, in my experience reading large Excel files with SXSSFWorkbook can require quite a bit of memory... Also, this library creates a lot of temporary objects. I'd take a look at your JVM with VisualGC and see if you're having a lot of spill over to the Old generation. Changing the NewSize to be a large proportion of your total memory can be a big help in this case. – matthew p. Dec 10 '15 at 17:22

While writting complex Excel files getting GC Overhead limit Exceeded Error

1 Answers1