Resolve OutOfMemoryError With Excel Export in the Apache POI Stream API

Whenever we try to export a huge excel spreadsheet (for example: around 200,000-300,000 records), most of the time we end up with an OutOfMemoryError:JavaHeapSpace. We also consume more time and processing power to export that much of data. The main reason for this kind of problem is that the prior version of Apache POI (prior to 3.8) does not provide a proper solution for this kind of situation, and I also have other issues with the API design in those versions. I've sometimes faced situations where we couldn't support more than 65000 rows of data during excel exports with prior versions of POI. But with version 3.8 and higher, there are solutions for all these problems.

To resolve the memory issues and performance issues of Excel exports, they have utilized a stream API to design to support large data exports. With the stream API we can flush only few rows of data into memory and the reamining rows can be flushed to the hard memory (permanent memory). In this example, you can easily see how it supports larger datasets. I wrote this utility for supporting almost 200,000 records with one of my applications. I hope it will help many who are in search of this kind of solution. I built this solution with Spring MVC.

To solve this problem I have applied the template design pattern to create a utility for excel exporting of any data. This is a generic implementation that you can use wherever you want with a respective implementation. Below we have the first abstract class which is generic class. It needs to be extended to implement the export functionality for our corresponding module.

import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * @author Shidram
 *
 * @param <E>
 */
public abstract class ExcelExportUtility< E extends Object > {

    protected SXSSFWorkbook wb;
    protected Sheet sh;
    protected static final String EMPTY_VALUE = " ";

    /**
     * This method demonstrates how to Auto resize Excel column
     */
    private void autoResizeColumns(int listSize) {

        for (int colIndex = 0; colIndex < listSize; colIndex++) {
            sh.autoSizeColumn(colIndex);
        }
    }

    /**
     * 
     * This method will return Style of Header Cell
     * 
     * @return
     */

    protected CellStyle getHeaderStyle() {
        CellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(CellStyle.ALIGN_CENTER);

        return style;
    }

    /**
     * 
     * This method will return style for Normal Cell
     * 
     * @return
     */

    protected CellStyle getNormalStyle() {
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(CellStyle.ALIGN_CENTER);

        return style;
    }

    /**
     * @param columns
     */
    private void fillHeader(String[] columns) {
        wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        sh = wb.createSheet("Validated Data");
        CellStyle headerStle = getHeaderStyle();

        for (int rownum = 0; rownum < 1; rownum++) {
            Row row = sh.createRow(rownum);

            for (int cellnum = 0; cellnum < columns.length; cellnum++) {
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(columns[cellnum]);
                cell.setCellStyle(headerStle);
            }

        }
    }

    /**
     * @param columns
     * @param dataList
     * @return
     */
    public final SXSSFWorkbook exportExcel(String[] columns, List<E> dataList) {

        fillHeader(columns);
        fillData(dataList);
        autoResizeColumns(columns.length);

        return wb;
    }

    /**
     * @param dataList
     */
    abstract void fillData(List<E> dataList);

}

By extending the class above, we can implement our own excel utility to export the data. In this extended class we have to override the 'fillData()' method to provide the data for export. For example, I have taken one such class below for a demo:

import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

/**
 * @author Shidram
 *
 */
public class ExportRevisionResponseExcel extends ExcelExportUtility<RevisionResponse> {

    /*
     * @see ASTExcelExportUtility#fillData(java.util.List)
     */
    void fillData(List<RevisionResponse> dataList) {

        CellStyle normalStyle = getNormalStyle();
        int rownum = 1;
        SimpleDateFormat dtFormat = new SimpleDateFormat("E MMM dd HH:mm:ss z yyyy");

        for (RevisionResponse rev : dataList) {

            Row row = sh.createRow(rownum);

            Cell cell_0 = row.createCell(0, Cell.CELL_TYPE_STRING);
            cell_0.setCellStyle(normalStyle);
            cell_0.setCellValue(rev.getRevId());

            Cell cell_1 = row.createCell(1, Cell.CELL_TYPE_STRING);
            cell_1.setCellStyle(normalStyle);
            cell_1.setCellValue(rev.getJcrCreated() != null ? dtFormat.format(rev.getJcrCreated()) : " ");

            rownum++;
        }
    }

}

Now the utility is ready. The next step is to call this utility from some Action or Controller code for exporting the data. In this case, I am providing the Spring controller Method code. For the sake of understanding I am just providing only the required code snippet from the controller. For the data being exported I am using ServletContext to get the already available search data to avoid multiple hits to the business methods. For this reason I am using SeverletContext to input the data from teh search method and getting the same data from the Export method of the same controller. Here is the code from the controller:

@Controller
public class RevisionResponseController {

      ......

    @Autowired
    private ServletContext servletContext;

      ......


    @SuppressWarnings("unchecked")
    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public ModelAndView exportRevisionsToExcel(ModelAndView modelAndView) {

        List<RevisionResponse> revList = (List<RevisionResponse>) servletContext.getAttribute("revisionsResponse");
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd_hh_mm_ss");
        String excelFileName = "Revisions_" + formatter.format(LocalDateTime.now()) + ".xlsx";
        SXSSFWorkbook wb = (new ExportRevisionResponseExcel()).exportExcel(new String[] { "REVISION ID",
            "CREATION DATE" }, revList);

        try {
            ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
            wb.write(outByteStream);
            byte[] outArray = outByteStream.toByteArray();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setContentLength(outArray.length);
            response.setHeader("Expires:", "0"); // eliminates browser caching
            response.setHeader("Content-Disposition", "attachment; filename=" + excelFileName);
            OutputStream outStream = response.getOutputStream();
            outStream.write(outArray);
            outStream.flush();
            wb.dispose();
            wb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return modelAndView;

    }


     ......

     ......
}

That's all. The functionality is ready now. The next step is to call this controller method from the UI action.

Kindly post your comments if you like it. And also provide your suggestions if you think you have a better approach than this. I really appreciate such suggestions.

Thank you.

For other posts please visit my blog : Journey Towards Java