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 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 🙂

Februari-ku

Hujan masih mengguyur malam terakhir bulan Februari. Mengantarkan sisa gelombang panas sore tadi untuk beranjak dari singgananya. Hawa dingin mulai menyeruak. Mengusikku untuk menenggelamkan diri ku ke dalam selimut.

Ku pandangi atap plafon kamar tidurku. Nampak bercak-bercak dan lingkaran coklat di beberapa tempat. “Ini kalo gw benerin rumah, bisa setara ama catering 600 orang kali ya.. atau lebih.. heeemmm.. resepsi gw ntar apa kabar.. heemm..” gurauku dalam hati. “Mungkin hati manusia bisa kayak si plafon ini. Yang lama-lama bisa mulai ada noda-nodanya dan butuh pihak lain buat bikin kece lagi”.

Aku melayangkan pikiran ku lebih jauh lagi. Mulai merunut kembali kejadian bulan ini. Dalam 28 hari yang kulalui, aku memaksakan diri untuk belajar membuat keputusan terbijak yang bisa ku ambil. Meski ku sadari tidak akan pernah bisa aku membuat semua pihak senang. Tidak akan bisa aku menuruti keegoan setiap orang. Tidak akan bisa aku menang dalam pertempuran menghadapi hati yang telah mengeras karena iri dengki dan sakit hati.

Dalam 28 hari bulan Februari yang telah kulalui, aku semakin menyadari bahwa manusia hanya bisa berserah diri kepada Allah SWT setelah kelelahan menggapainya dalam usaha yang dijalankannya. Hanya ada sujud untuk menyembah Sang Maha Kuasa dan meminta pertolongan-NYA ketika air mata tak lagi bisa dicegah. Layaknya batu karang yang tetap berdiri meski terus terhempas gelombang pasang lautan, aku harus tetap tegar meski banyak hujatan yang kuterima.

Dalam 28 hari bulan Februari yang telah kulalui, aku semakin menyadari bahwa hati manusia memiliki kedalaman yang tidak bisa terukur dengan ilmu pasti. Meski pepatah mengatakan batu dapat hancur dengan tetesan air yang terus menerus mengenainya, tidak demikian dengan hati manusia. Hanya dengan izin Allah SWT semua dapat terlaksana.

“Apakah manusia itu mengira bahwa mereka dibiarkan (saja) mengatakan, “Kami telah beriman”, sedang mereka tidak diuji lagi? Dan sesungguhnya Kami telah menguji orang-orang yang sebelum mereka, maka sesungguhnya Allah mengetahui orang-orang yang benar dan sesungguhnya Dia mengetahui orang-orang yang dusta.” (Qs. Al-Ankabut [29]:2-3)

Pengalaman Operasi Gigi Geraham Bungsu (Part 4)

“Nek, mau liat gigi lw yang mahal itu nggak?” Mas bertanya iseng ke saya.

“Mane? mau donk liat”

Mas memberikan sebuah cup tertutup yang berisi si Sombong beserta kawannya. Ternyata si sombong itu gede banget. Nggak kebayang bolong di gusi saya seberapa lama bakal keisi lagi buat nutupin kekosongan karena si sombong dan kawan-kawan diambil.

img-20161127-wa0015

“Itu ada formalinnya ya. Entar pas sampe rumah kubur aja”, lanjut Mas menerangkan.

Setelah saya merasa efek bius sudah hilang, saya memutuskan untuk pulang. Saya dijadwalkan kontrol seminggu berikutnya untuk mencabut jahitan. Suster dengan senyum manisnya yang mempesona, memberikan beberapa obat yang harus saya minum. Diantara obat itu ada obat anti nyeri dan antibiotik yang mestinya diminum dengan teratur. Mestinya. Continue reading “Pengalaman Operasi Gigi Geraham Bungsu (Part 4)”