February 2013 Posts

[Java] Membuat Laporan Excel di Aplikasi Web Based

Excel adalah salah satu format output yang disukai oleh enduser, selain dari pdf. Karena di excel pengguna bisa melakukan perhitungan-perhitungan, modifikasi ataupun kustomisasi yang lebih bebas dibandingkan dari pdf.

Di tutorial ini, akan dibahas bagaimana membuat export laporan aplikasi web-based ke format xls. Gw menggunakan library jxls (http://jxls.sourceforge.net/) karena lebih mudah dalam pembuatan format template excel jika dibandingkan dengan jasper. Konsepnya sebenarnya sederhana, sebuah servlet yang jika diinvoke dari method GET, akan meng-generate file excel dan mengirimkan ke pengguna langsung via browser.

Dimulai dengan membuat template excel yang akan kita isi dengan data (perhatikan baris ke-5), kemudian diletakkan dibawah folder WEB-INF.

Untuk sourcecodenya seperti biasa, sebuah java bean sederhana sebagai tempat untuk menampung data

package com.edw.bean;

public class Mahasiswa {

    private String nama;
    private String alamat;
    private int usia;

	// jangan lupa setter dan getter-nya    
}

Berikut adalah servlet yang digunakan untuk meng-generate laporan berformat xcel

package com.edw.servlet;

import com.edw.bean.Mahasiswa;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;

public class ReportServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        // set output header
        ServletOutputStream os = response.getOutputStream();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=\"report." + new SimpleDateFormat("yyyyMMdd.hhmm").format(new Date()) + ".xls\"");

        // ambil file excelnya
        ServletContext context = getServletContext();
        String reportLocation = context.getRealPath("WEB-INF");

        // buat data
        List<Mahasiswa> mahasiswas = new ArrayList<Mahasiswa>();
        for (int i = 0; i < 10; i++) {
            Mahasiswa mahasiswa = new Mahasiswa();

            mahasiswa.setNama("Edwin " + new Random().nextInt());
            mahasiswa.setAlamat("Jakarta " + new Random().nextInt());
            mahasiswa.setUsia(new Random().nextInt());

            mahasiswas.add(mahasiswa);
        }

        // kirim ke excel
        Map beans = new HashMap();
        beans.put("x", mahasiswas);

        try {
            XLSTransformer transformer = new XLSTransformer();
            Workbook workbook = transformer.transformXLS(new FileInputStream(reportLocation + "/report.xls"), beans);
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            os.flush();
        }
    }
}

Jangan lupa daftarkan servletnya di web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app  version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
          xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

    <display-name>Reporting Excel</display-name>

    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>  
    
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
	
    <servlet>
        <servlet-name>reportServlet</servlet-name>
        <servlet-class>com.edw.servlet.ReportServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>reportServlet</servlet-name>
        <url-pattern>/reportServlet</url-pattern>
    </servlet-mapping>	  

</web-app>

Kemudian file jsp yang akan dijadikan sebagai halaman landing page

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Download</h1>
        <a href="reportServlet">here</a>        
    </body>
</html>

Jika dijalankan, akan menghasilkan aplikasi web seperti ini,

Ini adalah isi file excel hasil download ketika dibuka

Berikut adalah konfigurasi dan library yang gw gunakan di Netbeans gw,

Ga terlalu sulit kan bikin laporan pakek JXLS ;)

Google+

[Java] Algoritma Bilangan Romawi

Berawal dari ngobrol iseng-iseng dengan temen gw ( Kamplenk SKom ) sepulang dari ngopi bareng, beliau kebetulan nanya soal algoritma bilangan romawi untuk digunakan sebagai nomer surat. Kebetulan karena waktu gw agak kosong, iseng-iseng bikin kayak ginian ( setelah dibantu om google juga sih :p ).

package com.edw.main;

public class RomanNumber {
    
    private static final char[] ROMAWI = {'M', 'D', 'C', 'L', 'X', 'V', 'I'};
    private static final int MAX = 1000; 
    private static final int[][] DIGITS = {
        {}, {0}, {0, 0}, {0, 0, 0}, {0, 1}, {1},
        {1, 0}, {1, 0, 0}, {1, 0, 0, 0}, {0, 2}}; 
        // konstanta digit modulus 10, 
        // array ke 4(0,1) maksudnya IV, array ke 9(0,2) --> IX

    public static String int2roman(int number) {
        if (number <= 0) {
            return "N";
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0, m = MAX; m > 0; m /= 10, i += 2) {
            int[] d = DIGITS[(number / m) % 10];
            for (int n : d) {
                sb.append(ROMAWI[i - n]);
            }
        }
        return sb.toString();
    }
    
    public static void main(String[] args) {
        System.out.println(int2roman(1990));
        System.out.println(int2roman(2012));
        System.out.println(int2roman(231));
    }
}

Oi plenk, udah jadi nih wkkwkwk….

Google+

A Simple Blowfish Encryption / Decryption using Java

This is a simple encryption using Blowfish Algorithm that i use to encrypt several properties on my application. On this example im using username appended with password as salt to encrypt password variables.

package com.edw.main;

import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

public class BlowfishTest {

    public static void main(String[] args) throws Exception {
        encrypt("edwin","password");
        decrypt("6VsVtA/nhHKUZuWWmod/BQ==");
    }

    private static void encrypt(String username, String password) throws Exception {
        byte[] keyData = (username+password).getBytes();
        SecretKeySpec secretKeySpec = new SecretKeySpec(keyData, "Blowfish");
        Cipher cipher = Cipher.getInstance("Blowfish");
        cipher.init(Cipher.ENCRYPT_MODE, secretKeySpec);
        byte[] hasil = cipher.doFinal(password.getBytes());
        System.out.println(new BASE64Encoder().encode(hasil));
    }
    
    private static void decrypt(String string) throws Exception {
        byte[] keyData = ("edwin"+"password").getBytes();
        SecretKeySpec secretKeySpec = new SecretKeySpec(keyData, "Blowfish");
        Cipher cipher = Cipher.getInstance("Blowfish");
        cipher.init(Cipher.DECRYPT_MODE, secretKeySpec);
        byte[] hasil = cipher.doFinal(new BASE64Decoder().decodeBuffer(string));
        System.out.println(new String(hasil));
    }
}
Google+

A Weird Hibernate Exception : org.hibernate.ObjectNotFoundException: No row with the given identifier exists

Today i met a weird hibernate exception, somehow it show that my User object does not exists. A little bit weird because i’m querying Comment tables, but the exceptions show “object user does not exist”.

	A Weird Hibernate Error : org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.edw.entity.User#anonymousUser]
    org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.edw.entity.User#anonymousUser]
	at org.hibernate.impl.SessionFactoryImpl$1.handleEntityNotFound(SessionFactoryImpl.java:377)
	at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:145)
	at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:195)
	at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:103)
	at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
	at org.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:846)
	at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:557)
	at org.hibernate.type.EntityType.resolve(EntityType.java:379)
	at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:120)

After some minutes digging on my sourcecode, i found out that javabean Comment has a @ManyToOne (Hibernate annotation for many to one relationship) to javabean User. Too bad one Comment object, still has a foreign key to a deleted User object.

This is my previous User class and Comment class, the main culprit is on line 30.

package com.edw.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

@Entity
@Table(name="tbl_comment")
public class Comment implements Serializable {

	@Id
	@GeneratedValue
	private Long id;
	
	@Temporal(TemporalType.TIMESTAMP)
	private Date timestamp;
	
	private String content;
		
	@ManyToOne
	private User user;
		
	@ManyToOne
	private News news;

	public Long getId() {
		return id;
	}
	// other setter and getter
}
package com.edw.entity;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="tbl_user")
public class User implements Serializable {

	@Id
    private String username;
    private String password;
    private Boolean status;
    
    private String email;
    private String name;
    private String address;
    private String city;
    private String job;
    private String about;
    private String relationship;
    private String privacy;
    
    private String role;
    
    private String tokenRegistration;
    private String tokenForgotPassword;
    
    public User() {}
    public User(String username) {
    	this.username = username;
    }   	
}

The workaround is by adding @NotFound annotation to ignore the condition when Hibernate unable to find the User class.

package com.edw.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

@Entity
@Table(name="tbl_comment")
public class Comment implements Serializable {

	@Id
	@GeneratedValue
	private Long id;
	
	@Temporal(TemporalType.TIMESTAMP)
	private Date timestamp;
	
	private String content;
	
	@NotFound(action=NotFoundAction.IGNORE)
	@ManyToOne
	private User user;
	
	@NotFound(action=NotFoundAction.IGNORE)
	@ManyToOne
	private News news;

	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
}

Hope it helped others, have fun ;-)

Google+