How To Generate Large Excel File in Java Using POI

On my last project, I have to create report on excel format with huge data. The data that I need to print on report can be up to 4 million rows. I tried using HSSF and XSSF class but always got heap space problem even though I already set up my JVM. After some google, I found out that I can use SXSSF class for my case. You can check about it on apache page.

For my code, I changed my query to split the resultset so the memory will not keep all data in one time. Then I divide the rows into some sheets too. Here is some of my code.

package main.xls;

/* import libraries */

/**
 * @author ariestania.winda
 */
public class XLSReportGenerator {

 /**
  * SOME CODE
  */

 public String generate() {
  Connection conn = null;
  try {
   conn = DriverManager.getConnection(Var.CONN_REPORT);

   int maxRow = 60000;
   int rowMaxMemory = 200;
   int defaultStartRownum = 1;
   int defaultEndRownum = 6000;

   String fileName = "mytestrep.xlsx";
   String pathSeparator = BohayUtils.getPathSeparator();
   String path = configuration.getReportPath() + pathSeparator + sdfYear.format(dtTo) + pathSeparator + sdfMonth.format(dtTo) + pathSeparator + fileName;

   File repFile = new File(path);

   if (!repFile.exists()) {
    try (SXSSFWorkbook workbook = new SXSSFWorkbook(rowMaxMemory)) {
     CellStyle boldCenterStyle = ReportGlobalFunction.getBoldFontStyle(workbook, true);
     CellStyle currencyStyle = ReportGlobalFunction.getCellCurrency(workbook);

     SXSSFSheet mySheetSample = workbook.createSheet("My Detail Report");

     //track column to set autoSizeColumn
     mySheetSample.trackAllColumnsForAutoSizing();

     //get my data
     List < MyDatas > jmlMyData = myDBHandler.getMyDatas(startRownum, endRownum);

     int startRownum = defaultStartRownum;
     int endRownum = defaultEndRownum;
     int curColNum = 0;
     int curRowNum = 1;
     int sheetNo = 1;

     //title
     row = mySheetSample.createRow(curRowNum);
     cell = row.createCell(curColNum);
     cell.setCellValue("JUMLAH BOHAY MESIN");
     cell.setCellStyle(boldCenterStyle);
     mySheetSample.addMergedRegion(new CellRangeAddress(curRowNum, curRowNum, 0, 6));
     curRowNum = curRowNum + 2;

     //header table
     row = mySheetSample.createRow(curRowNum);
     cell = row.createCell(curColNum);
     cell.setCellValue("  NO  ");
     cell.setCellStyle(boldCenterStyle);
     mySheetSample.autoSizeColumn(curColNum);
     curColNum++;

     cell = row.createCell(curColNum);
     cell.setCellValue("     BRANCHID     ");
     cell.setCellStyle(boldCenterStyle);
     mySheetSample.autoSizeColumn(curColNum);
     curColNum++;

     cell = row.createCell(curColNum);
     cell.setCellValue("       DESCR       ");
     cell.setCellStyle(boldCenterStyle);
     mySheetSample.autoSizeColumn(curColNum);
     curColNum++;

     cell = row.createCell(curColNum);
     cell.setCellValue("       CASHIN_AMT       ");
     cell.setCellStyle(boldCenterStyle);
     mySheetSample.autoSizeColumn(curColNum);
     curColNum++;
     curRowNum++;

     //release track
     mySheetSample.untrackAllColumnsForAutoSizing();

     curColNum = 0;
     noData = 0;
     if (jmlmyDt.size() > 0) {
      boolean runLoop = true;
      while (runLoop) {
       for (MyDatas myDt: jmlMyData) {
        if (noData == maxRow) {
         sheetTotalTrxTunai = workbook.createSheet(judulSheet + sheetNo);
         noData = 0;
         sheetNo++;
         curRowNum = 0;
         curColNum = 0;
        }
        noData++;

        row = mySheetSample.createRow(curRowNum);

        cell = row.createCell(curColNum);
        cell.setCellValue(myDt.getNO());
        curColNum++;

        cell = row.createCell(curColNum);
        cell.setCellValue(myDt.getBRANCH_ID());
        curColNum++;

        cell = row.createCell(curColNum);
        cell.setCellValue(myDt.getDESCR());
        curColNum++;

        cell = row.createCell(curColNum);
        cell.setCellValue(myDt.getCASHIN_AMT());
        cell.setCellStyle(currencyStyle);
        curColNum++;

        curRowNum++;
        curColNum = 0;
       }
       startRownum = startRownum + defaultEndRownum;
       endRownum = endRownum + defaultEndRownum;
       jmlMyData = myDBHandler.getMyDatas(startRownum, endRownum);
       runLoop = jmlMyData.size() > 0;
      }
     } else {
      row = mySheetSample.createRow(curRowNum);
      cell = row.createCell(curColNum);
      cell.setCellValue("NO DATA");
     }

     jmlmyDt = null;

     repFile.getParentFile().mkdirs();
     repFile.createNewFile();

     try (FileOutputStream fos = new FileOutputStream(repFile)) {
      workbook.write(fos); //write file
      fos.flush();
     }

     //dont forget to dispose the workbook
     workbook.dispose();
    }
   }
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException ex) {
   ex.printStackTrace();
  } catch (ParseException | SQLException ex) {
   ex.printStackTrace();
  } finally {
   if (conn != null && !conn.isClose()) {
    try {
     conn.close();
    } catch (SQLException ex) {}
   }
  }

  return "SUCCESSFUL";

 }

}

Hope it can be help 🙂