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 🙂

Advertisements

How To Log SOAP Request and Response with Log4j in Axis 1.x

Sometimes we want to log the request that we send to server and the response from server as well. Log4j is one of the library that can help us for this kind of requirement.

First, create class that extend BasicHandler class and implements the invoke method just like this.


package com.ariestania.client.logger;

import org.apache.axis.AxisFault;
import org.apache.axis.MessageContext;
import org.apache.axis.handlers.BasicHandler;
import org.apache.commons.logging.LogFactory;

/**
 * @author ariestania.winda
 */
public class SoapLogHandler extends BasicHandler {

    @Override
    public void invoke(MessageContext mc) throws AxisFault {
        if (mc.getResponseMessage() != null && mc.getResponseMessage().getSOAPPartAsString() != null) {
            String resMsg = mc.getResponseMessage().getSOAPPartAsString();
            //print response on console
            System.out.println("Response: \r\n" + resMsg);
            //print response on log file
            LogFactory.getLog("bohayMessage").info("Response: " + resMsg);
        } else if (mc.getRequestMessage() != null && mc.getRequestMessage().getSOAPPartAsString() != null) {
            String reqMsg = mc.getRequestMessage().getSOAPPartAsString();
            //print request on console
            System.out.println("Request: \r\n" + reqMsg);
            //print request on log file
            LogFactory.getLog("bohayMessage").info("Request: " + reqMsg);
        }
    }
}

Continue reading “How To Log SOAP Request and Response with Log4j in Axis 1.x”

How To Create Simple Web Service Using Netbeans as IDE

This time, I use Netbeans 8.1 and tomcat 8.0.27 as the server. You can choose other version if you want.

Here is step by step to create the web service using Netbeans as the IDE:

Create new java web project. Choose the server and java EE version that you want. Click Finish.

Right click on the project then choose new -> web service. Here my project hierarchy after add new web service.

Add the operation by right click on web service class -> add operation.


package com.ariestania.example;

import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;

/**
 * @author ariestania.winda
 */
@WebService(serviceName = "HelloBohayWebService")
public class HelloBohayWebService {

    @WebMethod(operationName = "hello")
    public String hello(@WebParam(name = "name") String txt) {
        return "Hello " + txt + " !";
    }

    @WebMethod(operationName = "doSummary")
    public int doSummary(@WebParam(name = "valOne") int valOne, @WebParam(name = "valTwo") int valTwo) {
        return valOne + valTwo;
    }

}

Continue reading “How To Create Simple Web Service Using Netbeans as IDE”

How To Solve Unable to find required classes (javax.activation.DataHandler and javax.mail.internet.MimeMultipart). Attachment support is disabled

I tried to create simple web service as server and client as well. But when I tried to run the operation as client, I got error:

Unable to find required classes (javax.activation.DataHandler and javax.mail.internet.MimeMultipart). Attachment support is disabled

After some google, I realized that I need to add 2 libraries on my project javax.mail.jar and activation.jar. So I add those libraries to my client and server project. I also add those libraries to my tomcat lib as well. Booms..!! the error has been solved. Thanks to you who give the advice on stackoverflow.. 🙂

How To Set Default Selection for f:selectItem within h:selectOneMenu

Sometimes we want to set default value for our combo box in the page. On HTML we only need to add attribute “selected” on the options of <select> element that we choose to be the default. But will be different on JSF since we can not add “selected” attribute to the option that generated by the bean objects. Here is my colleague do for this case and might be useful for you and me in the future:

<h:selectOneMenu id="myFormOptions" value="#{myBean.dudu}">
	<!-- make the default using selectItem tag -->                             
	<f:selectItem itemLabel="Default Value" itemValue="theDefaultVal" />
	<!-- this are the other options -->
	<f:selectItems value="#{myBean.lulu}" />
</h:selectOneMenu>

Put the default value into selectItem tag and the other options on selectItems tag just like above code. Hope this 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”

How To Automatically Download Generated XLS File on Client Side (Spring MVC + JXLS)

Sometimes we want to generate report to be downloaded by user without save it on the server side. So here is one of what I did for my web application. The process to create the document are do with the download process on client side. For this example, I create xls file using Jxls library.  But you will also need apache poi libs and apache common too for this process.

Download File Sample

xlsHasil

First of all, we need to prepare xls source file that we will use. Mine is like this

xlssource

Then I create simple form to send the parameter and call the controller. Here my code: Continue reading “How To Automatically Download Generated XLS File on Client Side (Spring MVC + JXLS)”