Skip to main content

Resolve OutOfMemoryError With ExcelExport : Export Excel Utility with Apache POI Stream API (SXSSF)




                                 Whenever we try to export excel of huge data (for ex: around 200000-300000 records), most of the time we end up with OutOfMemoryError:JavaHeapSpace. And also consuming more time to process or export that much of data. Main reason to this kind of problem is that, the prior version of Apache POI (prior to 3.8) will not provide proper solution for this kind of situations and also we have other issues with the API designed with those versions. Even I had faced issues of not supporting more than 65000 rows of data during exporting excel with prior versions of POI. But with the version 3.8 and higher they come with solutions for all these problems. To resolve Memory issue and performance issue of Excel Export they have utilized stream API to design their API to support huge data export and performance issues. With stream API we can flush only few rows of data into the Memory and reamining we can flush to the hard memory (permanent Memory). In this example you can esily identify how it supports for larger data. I wrote this utility for supporting almost more than 200000 lakhs of records with one of my application. I hope it will help many who are in search of this kind of solution. This solution I have applied with Spring MVC application.

                  To solve this problem I have applied Template design pattern to create utility for excel Export of any data. This is a generic implementation which you can use wherever you want with respective implementation. Please find below the First Abstract class which is generic class which we need to extend to implement 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 above class 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 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&lt;RevisionResponse&gt; {

    /*
     * @see ASTExcelExportUtility#fillData(java.util.List)
     */
    void fillData(List&lt;RevisionResponse&gt; 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, 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 which is to be 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 put the data from search method and getting the same data from Export method of the same controller. Please find below 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&lt;RevisionResponse&gt; revList = (List&lt;RevisionResponse&gt;) 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. Next step is to call this controller method from the UI action.

Kindly place your comments if you like it. And also provide your suggestions if you feel still you can write better approach than this. I really appreciate such suggestions. Because I feel still I need to know the depth of the same which is a day to day long run process. If you like it kindly share the same with your friends whoever in need of the same.

Thank you.



Popular posts from this blog

Consider static factory methods instead of constructors

The normal way for a class to allow a client to obtain an instance of itself is to provide a public constructor. There is another technique that should be a part of every programmer’s toolkit. A class can provide a public static factory method, which is simply a static method that returns an instance of the class. Here’s a simple example from Boolean (the boxed primitive class for the primitive type boolean). This method translates a boolean primitive value into a Boolean object reference:

public static Boolean valueOf(boolean b) {
return b ? Boolean.TRUE : Boolean.FALSE;
}

          Note that a static factory method is not the same as the Factory Method pattern from Design Patterns . The static factory method described in this item has no direct equivalent in Design Patterns. A class can provide its clients with static factory methods instead of, or in addition to, constructors. Providing a static factory method instead of a public constructor has both advantages and disadvant…

Java Performance Checklist

Specify the required performance. Ensure performance objectives are clear.Specify target response times for as much of the system as possible.Specify all variations in benchmarks, including expected response ranges (e.g., 80% of responses for X must fall within 3 seconds).Include benchmarks for the full range of scaling expected (e.g., low to high numbers of users, data, files, file sizes, objects, etc.).Specify and use a benchmark suite based on real user behavior. This is particularly important for multi-user benchmarks.Agree on all target times with users, customers, managers, etc., before tuning.

Make your benchmarks long enough: over five seconds is a good target. Use elapsed time (wall-clock time) for the primary time measurements.Ensure the benchmark harness does not interfere with the performance of the application.Run benchmarks before starting tuning, and again after each tuning exercise.Take care that you are not measuring artificial situations, such as full caches contain…