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 🙂