Excel Export in Spring MVC

Export as excel is one of the most wanted feature in an enterprise application. In this tutorial let us learn about export as excel feature using Spring MVC framework. If you are a beginner, go through the Spring MVC tutorial before taking this. We will be using Spring 3 annotation based approach for the web application.

If you have learned Spring MVC, there is not much to discuss, in this excel export tutorial. All you have to know is, Spring provides a class AbstractExcelView which is a view in the MVC. We need to extend this class and write our custom classes. Then configure our custom class as the view in the MVC.

1. Extend AbstractExcelView and write a custom excel view. There is a method buildExcelDocument in this class, which we should override to build the excel.
2. Configure the new custom class, which we have created as the view in the Spring MVC. We should imagine this new java class file as a replacement for the JSP which we generally will have in the view.

Do not call the service layer or DAO directly from the view class. Similarly, it should not contain the business logic of the application. Call to service/business should happen from the controller and the model should be built. Only the model should be sent to the view. This view class, should just construct the excel header, rows and formatting.

Maven is used to manage the dependencies. If you do not have Maven, it is better to download and setup Maven.

Animal.java

Domain class

package com.javapapers.spring.mvc;

public class Animal {
	private int id;
	private String animalName;
	private String animalType;
	private boolean aggressive;
	private int weight;

	public Animal(int id, String animalName, String animalType,
			boolean aggressive, int weight) {
		super();
		this.id = id;
		this.animalName = animalName;
		this.animalType = animalType;
		this.aggressive = aggressive;
		this.weight = weight;
	}

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getAnimalName() {
		return animalName;
	}
	public void setAnimalName(String animalName) {
		this.animalName = animalName;
	}
	public String getAnimalType() {
		return animalType;
	}
	public void setAnimalType(String animalType) {
		this.animalType = animalType;
	}
	public boolean getAggressive() {
		return aggressive;
	}
	public void setAggressive(boolean aggressive) {
		this.aggressive = aggressive;
	}
	public int getWeight() {
		return weight;
	}
	public void setWeight(int weight) {
		this.weight = weight;
	}

}

AnimalListExcelView.java

This is the important class in the excel export tutorial. This serves as the View in Spring MVC by extending the Spring’s AbstractExcelView class.

package com.javapapers.spring.mvc;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

public class AnimalListExcelView extends AbstractExcelView {

	@Override
	protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		HSSFSheet excelSheet = workbook.createSheet("Animal List");
		setExcelHeader(excelSheet);
		
		List animalList = (List) model.get("animalList");
		setExcelRows(excelSheet,animalList);
		
	}

	public void setExcelHeader(HSSFSheet excelSheet) {
		HSSFRow excelHeader = excelSheet.createRow(0);
		excelHeader.createCell(0).setCellValue("Id");
		excelHeader.createCell(1).setCellValue("Name");
		excelHeader.createCell(2).setCellValue("Type");
		excelHeader.createCell(3).setCellValue("Aggressive");
		excelHeader.createCell(4).setCellValue("Weight");
	}
	
	public void setExcelRows(HSSFSheet excelSheet, List animalList){
		int record = 1;
		for (Animal animal : animalList) {
			HSSFRow excelRow = excelSheet.createRow(record++);
			excelRow.createCell(0).setCellValue(animal.getId());
			excelRow.createCell(1).setCellValue(animal.getAnimalName());
			excelRow.createCell(2).setCellValue(animal.getAnimalType());
			excelRow.createCell(3).setCellValue(animal.getAggressive());
			excelRow.createCell(4).setCellValue(animal.getWeight());
		}
	}
}

ZooController.java

Just added couple of methods from the previous MVC hello world tutorial.

package com.javapapers.spring.mvc;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class ZooController {

	protected AnimalService animalService = new AnimalService();

	@RequestMapping(value = "/AnimalList", method = RequestMethod.GET)
	public String getAnimals(Model model) {
		List animalList = animalService.getAnimalList();
		model.addAttribute("animalList", animalList);
		return "AnimalList";
	}

	@RequestMapping(value = "/export", method = RequestMethod.GET)
	public ModelAndView getExcel() {
		List animalList = animalService.getAnimalList();
		return new ModelAndView("AnimalListExcel", "animalList", animalList);
	}

	@RequestMapping("/")
	public String hello() {
		return "hello";
	}
	
	@RequestMapping(value = "/hi", method = RequestMethod.GET)
	public String hi(@RequestParam("name") String name, Model model) {
		String message = "Hi " + name + "!";
		model.addAttribute("message", message);
		return "hi";
	}
}

AnimalService.java

Access DAO via service layer for data.

package com.javapapers.spring.mvc;

import java.util.ArrayList;
import java.util.List;
/*
 
 Our software gurus talk lot about layering
 Generally the layer will be like,
 controller -> Service -> Business -> DAO
 
 Just to demonstrate that we have this class
 */
public class AnimalService {

	private static List animalList = new ArrayList();

	static {
		animalList.add(new Animal(1, "Lion", "Wild", true, 100));
		animalList.add(new Animal(2, "Tiger", "Wild", true, 90));
		animalList.add(new Animal(3, "Goat", "Domestic", true, 20));
		animalList.add(new Animal(4, "Elephant", "Wild", true, 1000));
	}

	public List getAnimalList() {
		//internally we will call business -> DAO
		return animalList;
	}

}

AnimalList.jsp

A JSP view in the Spring MVC

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<html>
<body>
	<h1>Example for Spring MVC Excel Export</h1>

	<h2>Animal List</h2>

	<h3><a href="./export">Export</a></h3>

	<table border="1px" cellpadding="8px">
		<tr>
			<td>Id</td>
			<td>Name</td>
			<td>Type</td>
			<td>Aggressive</td>
			<td>Weight</td>
		</tr>

		<c:forEach items="${animalList}" var="animal">
			<tr>
				<td><c:out value="${animal.id}" /></td>
				<td><c:out value="${animal.animalName}" /></td>
				<td><c:out value="${animal.animalType}" /></td>
				<td><c:out value="${animal.aggressive}" /></td>
				<td><c:out value="${animal.weight}" /></td>
			</tr>
		</c:forEach>
	</table>

</body>
</html>

spring-context.xml

Spring Configuration

<?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:mvc="http://www.springframework.org/schema/mvc"
  xmlns:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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.javapapers.spring.mvc" />
  <mvc:annotation-driven />
 
     <bean class="org.springframework.web.servlet.view.XmlViewResolver">
	   <property name="location">
	       <value>/WEB-INF/spring-excel-views.xml</value>
	   </property>
	   <property name="order" value="0" />
	</bean>
	
  <bean
    class="org.springframework.web.servlet.view.InternalResourceViewResolver">
    <property name="prefix" value="/view/" />
    <property name="suffix" value=".jsp" />
    <property name="order" value="1" />
  </bean>
 
</beans>

spring-excel-views.xml

Excel Export View Spring Configuration

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
 
 	<bean id="AnimalListExcel"
	   	class="com.javapapers.spring.mvc.AnimalListExcelView">
	</bean>
</beans>

web.xml

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

  <display-name>Spring MVC Excel Export</display-name>  
  
  <servlet>
        <servlet-name>springMVCDispatcher</servlet-name>
        <servlet-class>
            org.springframework.web.servlet.DispatcherServlet
        </servlet-class>
      <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>/WEB-INF/config/spring-context.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>springMVCDispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>

  
</web-app>

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/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.javapapers.spring</groupId>
	<artifactId>springexcelexport</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>Spring MVC Excel Export</name>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>3.0.5.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>3.0.5.RELEASE</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>3.0.5.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>3.0.5.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>3.0.5.RELEASE</version>
		</dependency>

		<!-- Excel -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.6</version>
		</dependency>

		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>2.5</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>com.thoughtworks.xstream</groupId>
			<artifactId>xstream</artifactId>
			<version>1.3.1</version>
		</dependency>
		<dependency>
			<groupId>taglibs</groupId>
			<artifactId>standard</artifactId>
			<version>1.1.2</version>
		</dependency>

	</dependencies>

	<build>
		<finalName>springexcelexport</finalName>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-war-plugin</artifactId>
				<version>2.2</version>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-surefire-plugin</artifactId>
				<version>2.16</version>
				<configuration>
					<skipTests>true</skipTests>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.tomcat.maven</groupId>
				<artifactId>tomcat7-maven-plugin</artifactId>
				<version>2.1</version>
				<configuration>
					<url>http://localhost:8080/manager/text</url>
					<server>TomcatAppServer</server>
					<path>/springexcelexport</path>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Output

Download Source Code