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

Excel Export Spring MVC

Download Source Code

This Spring tutorial was added on 09/10/2013.

Comments on "Excel Export in Spring MVC" Tutorial:

  1. Praveen says:

    Good One.. Thanks for explaining in both Spring and JSP

  2. Archi says:

    thanks for this tutorial

  3. Pratik says:

    Thanks Joe , due to your articles only I am in touch with Java.

  4. Rady RIN says:

    Thank you so much!!
    but it download export.do
    How to change this file name to export.xls?

    Best Regards.

  5. Swati says:

    Thank you so much. Its really very helpful to me.

  6. shahrzad says:

    For downloading xls format, add ‘.xls’ to ‘./export’ => ‘./export.xls’ in
    Export

    and also

    here :
    @RequestMapping(value = “/export.xls”, method = RequestMethod.GET)

    Merci bcp

  7. Aleksandr says:

    Thx, so much!

  8. lazyguy786 says:

    Hi Joe,

    I had done the export to excel through ajax request

    $ajax({
    url: “http://localhost:8080/spring/downloadExcel.htm?myid=”+”myid+”;

    });

    controller:
    @RequestMapping(value = “/downloadExcel”, method = RequestMethod.GET)
    public ModelAndView downloadExcel(@RequestParam(“myid”) int myid, HttpReq…, HttpResp..){

    Map map = new HashMap();
    map.put(“result”, result); //result is a list
    return new ModelAndView(“exportView”, map);

    }

    public class ExportView extends AbstractExcelView{ //your excel logic}

    applicationcontext.xml :

    It’s going inside the controller But not able to hit the ExportView class
    plz help me

  9. Kiran says:

    How to group and subtotal in excel sheet using POI?

  10. Kiran says:

    create view bean and then return new ModelAndView(“beanId” , “model” , output) from your controller .

  11. michael says:

    Thanks for your example.
    I have a question,how can I change the file name in every request?
    like excel1.xls,excel2.xls
    Best Regards,

  12. Dev says:

    What if the service class is an interface ? How to get the values from DB through it ?

  13. Ankit Agarwal says:

    Thank you so much. This guidance is rally very helpful for me. Thanks

Comments are closed for this "Excel Export in Spring MVC" tutorial.