Writing a large Excel sheet with 60+ columns using Apache POI
<p>I am trying to create multiple Excel sheets using the data fetched from a database. Each Excel sheet contains 60+ columns and around 50k records (may vary). The problem is that the system is taking a lot of time (5+ minutes) and then ends up with <code>java.lang.OutOfMemoryError: GC overhead limit exceeded</code> exception.</p>

<p>I tried by reducing the number of columns to only 6 and there was a huge improvement in the turnaround time. </p>

<p>Here's a code that generates byte array of the Excel sheet:</p>

<pre><code>int rowIndex = 0;
while (iterator.hasNext()) {
        List&lt;CustomCellDataBean&gt; cellData = iterator.next();

        // Insert generic data
        Row dataContentRow = sheet.createRow((short) rowIndex);

        for (int counter = 0; counter &lt; cellData.size(); counter++) {
            CustomCellDataBean cd = cellData.get(counter);
            if (cd.getValue() != null) {
            // switch case based on the datatype of the cell
                switch (cd.getType()) {
                }
            }
        }
        rowIndex++;
}
// write to ByteArrayOutputStream and return the array of bytes
</code></pre>

<p>Have referred to several SO questions but unable to figure out anything useful. Would like to know if anything I should try in order to get over this problem.</p>