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 🙂

How to Insert Image on XLS Using Apache POI

Hi.. on this article I will give you simple example about how to insert image on xls using apache poi. We need some libraries to help us and insert it to the project. The libraries are apache-commons-io, apache-commons-codec, and ofcourse, apache-poi lib. The result will be look like this
imgxls

I’ve created simple web for this sample. I put the image on WEB-INF folder and start to create web interface and java class just like this.

Interface:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Generate XLS</title>
    </head>
    <body>
        <h1>Hello..!</h1>
        <form action="genreport" target="_blank" name="form1" id="form1" method="post">
            <button value="submit" type="submit">Generate XLS</button>
        </form>
    </body>
</html>

Continue reading “How to Insert Image on XLS Using Apache POI”

Some Useful Method When Working With Apache POI (Excel)

  1. How to add color on background/foreground cell
    public void doSample(){
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheetLapTrx = workbook.createSheet("Sample Sheet");
        row = sheetLapTrx.createRow(0);
        cell = row.createCell((short) 0);
    
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
    
        XSSFColor color = new XSSFColor(Color.GREEN);
        style.setFillForegroundColor(color);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);	
    
        cell.setCellStyle(style);
    }
    
  2. How to merge cell
    public void doSample(){
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheetLapTrx = workbook.createSheet("Sample Sheet");
        row = sheetLapTrx.createRow(0);
        cell = row.createCell((short) 0);
    
        //merge cell A1 until C3
        sheetLapTrx.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
    }
    
  3. How to set font style
    public void doSample(){
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheetLapTrx = workbook.createSheet("Sample Sheet");
        row = sheetLapTrx.createRow(0);
        cell = row.createCell((short) 0);
    
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
    
        cell.setCellStyle(style);
    }
    
  4. How to make border
    public void doSample(){
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheetLapTrx = workbook.createSheet("Sample Sheet");
        row = sheetLapTrx.createRow(0);
        cell = row.createCell((short) 0);
    
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderTop(CellStyle.BORDER_THIN);
    
        cell.setCellStyle(style);
    }
    
  5. How to set text alignment
    public void doSample(){
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheetLapTrx = workbook.createSheet("Sample Sheet");
        row = sheetLapTrx.createRow(0);
        cell = row.createCell((short) 0);
    
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
    
        cell.setCellStyle(style);
    }