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

How To Resolve "Java compiler level does not match the version of the installed Java project facet." Issue in Eclipse, STS tool or Eclipse Based Tools

Image
                  I have faced this issue during the Java 7 upgrade from Java 6 into one of our application. You will face this issue suppose if your application or project built on lower versions of Java (in my case it is Java 1.6) and you are trying to upgrade it to the Higher versions of Java (in My case it is Java 1.7), then Eclipse will throw this error in the tool. You will face this error even after changing the compiler version from build path. To fix this issue Follow the Below Mentioned Steps. 1.  Open the ' Markers ' Tab at the bottom of the eclipse or STS tool beside the ' Properties ' Tab. (as shown in the below figure) 2. Right Click on the Error as mentioned in the below figure. in The context menu click on the ' Quick Fix ' item as shown below in the figure. 3. Select the correct Java Project Facet from the ' Quick Fix ' Window. and click on the ' Finish ' Button. 4.  After Clicking on '
Read more

Be-Aware of the Performance of the String Concatenation...

                  The string concatenation operator (+) is a convenient way to combine a few strings into one. It is fine for generating a single line of output or for constructing the string representation of a small, fixed-size object, but it does not scale. Using the string concatenation operator repeatedly to concatenate n strings requires time quadratic in n. It is an unfortunate consequence of the fact that strings are immutable. When two strings are concatenated, the contents of both are copied. For example, consider the following method that constructs a string representation of a billing statement by repeatedly concatenating a line for each item: // In-appropriate use of string concatenation - Hits Performance drastically! public String statement() {      String res = "";      for (int i = 0; i < numOfItems(); i++)       res += lineOfItem(i); // String concatenation       return res; }                   This method performs very slow if the number of
Read more