March 2014 Posts

Create and Exporting A Web-Based Excel Report Using JXLS and SpringMVC

Today im trying to create a simple excel exporter report using SpringMVC. Im integrating JXLS library with SpringMVC framework. I prefer using JXLS compared to JasperReport or other java-to-excel-library due to its easy templating, so i dont need to create excel formatting from java code.

I start with my pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.edw</groupId>
    <artifactId>JXLSSpringMVC</artifactId>
    <version>1.0</version>
    <packaging>war</packaging>

    <name>JXLSSpringMVC</name>

    <properties>
        <endorsed.dir>${project.build.directory}/endorsed</endorsed.dir>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-web-api</artifactId>
            <version>6.0</version>
            <scope>provided</scope>
        </dependency>
        
        <!-- Spring 3 dependencies -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>3.1.1.RELEASE</version>
        </dependency> 
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>3.1.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>3.1.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>3.1.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>3.1.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2</version>
        </dependency>
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.9</version>
        </dependency>
        
        <!-- jxls -->
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>0.9.9</version>
        </dependency>
        
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.3.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                    <compilerArguments>
                        <endorseddirs>${endorsed.dir}</endorseddirs>
                    </compilerArguments>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.1.1</version>
                <configuration>
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-dependency-plugin</artifactId>
                <version>2.1</version>
                <executions>
                    <execution>
                        <phase>validate</phase>
                        <goals>
                            <goal>copy</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>${endorsed.dir}</outputDirectory>
                            <silent>true</silent>
                            <artifactItems>
                                <artifactItem>
                                    <groupId>javax</groupId>
                                    <artifactId>javaee-endorsed-api</artifactId>
                                    <version>6.0</version>
                                    <type>jar</type>
                                </artifactItem>
                            </artifactItems>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

And a simple web.xml file

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" 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_3_0.xsd">
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/applicationContext.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>2</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
</web-app>

And 2 SpringMVC configuration file, applicationContext.xml,

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

    <context:annotation-config/>
    
    <tx:annotation-driven/>
    
    <context:component-scan base-package="com.edw.jxlsspringmvc"/>
    
</beans>

and dispatcher-servlet.xml,

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <context:component-scan base-package="com.edw.jxlsspringmvc.controller" />
	
    <mvc:annotation-driven />

    <bean id="viewResolver"
          class="org.springframework.web.servlet.view.InternalResourceViewResolver"
          p:prefix="/WEB-INF/jsp/"
          p:suffix=".jsp" />
</beans>

And a simple excel file,
jxls excel template

A java controller file,

package com.edw.jxlsspringmvc.controller;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

/**
 *
 * @author edwin < edwinkun at gmail dot com >
 */
@Controller
public class IndexController {

    @Autowired
    private ServletContext context;
    
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String index() {
        return "index";
    }
    
    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public String export(HttpServletRequest request, HttpServletResponse response) {
        try {
            // set output header
            ServletOutputStream os = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=\"myexcel.xls\"");
            
            String reportLocation = context.getRealPath("WEB-INF");

            Map beans = new HashMap();
            beans.put("name", "Edwin");
            beans.put("address", "Jakarta, Indonesia");
            XLSTransformer transformer = new XLSTransformer();

            Workbook workbook = transformer.transformXLS(new FileInputStream(reportLocation + "/myexcel.xls"), beans);
            workbook.write(os);
            os.flush();

            return null;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }    
}

And my index.jsp file

<html>
    <head>
        <title>Download Excel</title>
    </head>
    <body>
        <a href="${pageContext.request.contextPath}/export">download excel</a>
    </body>
</html>

My Netbeans project structure is like this,
jxls netbeans project

And this is the result when im clicking on the website’s download url,
jxls download result

Have fun with JXLS :)

Google+
Holycow Sarinah, One of the Best Steak in Jakarta

Holycow Sarinah, One of the Best Steak in Jakarta

Kemaren gw coba iseng-iseng mampir ke Holycow Sarinah, Jakarta. Lokasinya emang agak-agak susah, tapi patokannya adalah dia di gedung sebelah kanan di perempatan Bank Indonesia, kalo mo ke arah Kebon Sirih. Tempatnya memang agak kecil namun cozy dan karena berada di lantai 5, jadi view-nya dapet bgt. Ruangannya juga ada 2 tipe, indoor dan outdoor, jadi yg hobbynya smoking bisa langsung ambil yg outdoor. Dan soal pelayanan menurut gw, udah cukup baik dan ramah.

holycow sabang

Gw coba order Buddy’s Special Medium Rare, dan jujur gw sangat-sangat puas, dagingnya so juicy, pink empuk bgt. And believe me, ini mungkin salah satu steak paling enak yg pernah gw santap. Untuk harga, ya masih sesuai sama budget lah, total makan + minum ice lemon tea, abis sekitar 150k IDR.

Overall, very-very recommended.

nb.
kalo bawa mobil, siap-siap susah parkir-nya yah. Gedungnya rada sempit jadi bisa rebutan lahan parkir, atau silahkan parkir di jalan sabang dan kemudian jalan kaki kesini.

Google+

Fixing WordPress’ Syntaxhighligther and Bootstrap Incompatibility

Today’s i just updated my wordpress’ theme, from the old one to a much better one. I like the newer one because it has more flexibility, and also for its responsive design.

But i had an issue, somehow my syntaxhighlighter gives an extra row space and it happen only in Firefox. Here is the screenshot,

syntax highlighter issue

Somehow my latest wordpress theme shows incompatibility with Sytaxhighlighter css file. After sometime googling, i found out that i need to override a css property so my Syntaxhighlighter would works.

div.syntaxhighlighter .container:before,div.syntaxhighlighter .container:after {
    content:none;
}

Well i hope it helped others, have fun :)

Google+
And Todays’s Quote Would Be..

Life begins at the end of your comfort zone.

Neale Donald Walsch

Binding Date Property to SpringMVC’s @ModelAttribute

Let say i have this java bean,

public class MyBean implements Serializable {
    private Integer id;
    private String userid;       
    private Date contacttime;
	
	// other setter and getter	

And i have a SpringMVC Controller like this, using @ModelAttribute as the method’s parameter,

@RequestMapping(value = "/doSomething", method = RequestMethod.POST)
public @ResponseBody Object doSomething(@ModelAttribute MyBean myBean) {
	// do something
}

Im sending HTTP Post which format is like this

id=2&
userid=3abc&
contacttime=130314101010

But it shows error on my server side, which looks like this,

Failed to convert from type java.lang.String to type java.util.Date for value '130314101010'; 
nested exception is java.lang.IllegalArgumentException

Usually, i have to manually use SimpleDateFormat to parse my “ddMMyyHHmmss” String into Date, but SpringMVC provide a very elegant way of handling with this kind of conversion. Just by using @DateTimeFormat

import org.springframework.format.annotation.DateTimeFormat;

public class MyBean implements Serializable {
    private Integer id;
    private String userid;       
	
	@DateTimeFormat(pattern = "ddMMyyHHmmss")
    private Date contacttime;
	
	// other setter and getter	

Dont forget to include JodaTime on your pom.xml

<dependency>
	<groupId>joda-time</groupId>
	<artifactId>joda-time</artifactId>
	<version>2.3</version>
</dependency>

If not, you will find this kind of error,

org.springframework.core.convert.ConversionFailedException: Failed to convert from type java.lang.String to type @org.springframework.format.annotation.DateTimeFormat java.util.Date for value '130314101010'; nested exception is java.lang.IllegalStateException: JodaTime library not available - @DateTimeFormat not supported

Have fun with SpringMVC :D

Google+