Wednesday, December 30, 2015

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<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, 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<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. 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.

Reading a file while file being written at the same time

Below code describes how to read a file when a particular file is actively being written. Here is a full example. For the below example the mentioned file should be existed in the mentioned path else it will throw FileNotFoundException.

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class ReadingFileWhileWrite extends Thread {

    boolean running = true;
    BufferedInputStream reader = null;

    public static void main(String[] args) throws FileNotFoundException {
        ReadingFileWhileWrite tw = new ReadingFileWhileWrite();
        tw.reader = new BufferedInputStream(new FileInputStream("TestFile.txt"));
        tw.start();
    }

    public void run() {
        while (running) {
            try {
                if (reader.available() > 0) {
                    System.out.print((char) reader.read());
                } else {
                    try {
                        sleep(500);
                    } catch (InterruptedException ex) {
                        running = false;
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}