Spring MVC with Excel View Example (Apache POI and JExcelApi)

Details: Written by Nam Ha Minh; Last Updated on 02 September 2020 | Print Email

Spring MVC offers seamless integration with different view technologies, including Excel document view. When configured properly, a Spring’s view resolver can generate an Excel document from model data and send it to the client for downloading or opening by a spreadsheet program like Microsoft Excel. For working with Excel view, Spring supports two popular libraries such as Apache POI and JExcelApi (Both are free and open source).

NOTE: If you're looking for code example exporting Excel in Spring Boot, refer to this article: Spring Boot Export Data to Excel Example

This tutorial is going to help you in understanding how to configure Spring MVC to work with these libraries in order to deliver dynamic content in form of Excel document to the users, by developing a sample Spring MVC application that allows the users to download an Excel document generated on the fly:

Spring MVC Excel View Demo with Apache POI

Clicking on the download link will prompt the users for downloading/opening the document which looks like the following screenshot in Microsoft Excel program:

Open downloaded Excel document in MS Excel

About Apache POI:

Apache POI is a set of pure Java libraries for reading and writing Microsoft Office documents such as Word, Excel, Powerpoint, Outlook, etc. Click the following link to download its latest distribution (which is Apache POI 3.9, as of this writing):

Apache POI Download

The distribution comes with several jar files, but the only the poi-VERSION.jar file is required for typical usage of generating Excel documents (if you want to generate Excel XML format such as *.xlsx files, use the poi-ooxml-VERSION.jar file).

To generate an Excel document using Apache POI within Spring, we need to create a view class that extends from the AbstractExcelView class and override its method buildExcelDocument(). Then using Apache POI’s Excel API to generate the excel document.

About JExcelApi:

JExcelApi is a Java library that is dedicated for reading, writing and modifying Excel spreadsheets. It supports Excel 2003 file format and older versions. You can download JExcelApi from the following link:

JExcelApi Download

To work with JExcelApi, you need to add its only jar file: jxl.jar - to your project’s classpath. And Spring provides an abstract class called AbstractJExcelView which should be extended to generate an Excel document using JExcelApi, similarly to the case of Apache POI.

This tutorial will use Apache POI for the sample application. However, you can also download a JExcelApi version of the project in the Attachments section.

In Eclipse IDE, create a Dynamic Web Project called SpringMvcExcelViewDemo. We will end up with the following project structure:

Spring MVC Excel View Demo Eclipse project structure

The jar files used are:

- Spring framework 3.2.3-RELEASE:

- - spring-beans-3.2.3.RELEASE.jar
  - spring-context-3.2.3.RELEASE.jar
  - spring-context-support-3.2.3.RELEASE.jar
  - spring-core-3.2.3.RELEASE.jar
  - spring-expression-3.2.3.RELEASE.jar
  - spring-web-3.2.3.RELEASE.jar
  - spring-webmvc-3.2.3.RELEASE.jar

- Commons Logging (required by Spring):

- - commons-logging-1.1.1.jar

- Apache POI:

1. - poi-3.9-20121203.jar

1. Creating Java Model Class

We will generate an Excel document that contains a list of Java books, so create the following model class (Book.java):

package net.codejava.spring;

public class Book {
	private String title;
	private String author;
	private String isbn;
	private String publishedDate;
	private float price;

	public Book(String title, String author, String isbn, String publishedDate,
			float price) {
		this.title = title;
		this.author = author;
		this.isbn = isbn;
		this.publishedDate = publishedDate;
		this.price = price;
	}

	// getters and setters

}

2. Coding Entry JSP Page

We need to create a JSP page that displays a hyperlink on which the users will click to download the Excel file. Create a folder called jsp inside WEB-INF directory and create a JSP file called home.jsp under WEB-INF\jsp with the following content:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
	"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Spring MVC Excel View Demo (Apache POI)</title>
</head>
<body>
	<div align="center">
		<h1>Spring MVC Excel View Demo (Apache POI)</h1>
		<h3><a href="/downloadExcel">Download Excel Document</a></h3>
	</div>
</body>
</html>

The hyperlink Download Excel Document points to a relative URL downloadExcel which will be handled by a Spring controller class as described below.

3. Coding Spring Controller

Create a Spring controller class called MainController with the following code:

package net.codejava.spring;

import java.util.ArrayList;
import java.util.List;

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

/**
 * A Spring controller that allows the users to download an Excel document
 * generated by the Apache POI library.
 * 
 * @author www.codejava.net
 * 
 */
@Controller
public class MainController {

	/**
	 * Handle request to the default page
	 */
	@RequestMapping(value = "/", method = RequestMethod.GET)
	public String viewHome() {
		return "home";
	}

	/**
	 * Handle request to download an Excel document 
	 */
	@RequestMapping(value = "/downloadExcel", method = RequestMethod.GET)
	public ModelAndView downloadExcel() {
		// create some sample data
		List<Book> listBooks = new ArrayList<Book>();
		listBooks.add(new Book("Effective Java", "Joshua Bloch", "0321356683",
				"May 28, 2008", 38.11F));
		listBooks.add(new Book("Head First Java", "Kathy Sierra & Bert Bates",
				"0596009208", "February 9, 2005", 30.80F));
		listBooks.add(new Book("Java Generics and Collections",
				"Philip Wadler", "0596527756", "Oct 24, 2006", 29.52F));
		listBooks.add(new Book("Thinking in Java", "Bruce Eckel", "0596527756",
				"February 20, 2006", 43.97F));
		listBooks.add(new Book("Spring in Action", "Craig Walls", "1935182358",
				"June 29, 2011", 31.98F));

		// return a view which will be resolved by an excel view resolver
		return new ModelAndView("excelView", "listBooks", listBooks);
	}
}

As we can see, this controller class implements two request handling methods:

- viewHome(): this method simply returns a logical view name “home” which will be resolved to the home.jsp page (We will configure view resolver for JSP later).
- downloadExcel(): this method creates some dummy data, e.g. creating some books and add them to a list. Finally this method returns a logical view name “excelView” and passes the list of books as the name “listBooks” to the model. We will configure an Excel view class for this view later.

4. Coding Excel View Class

To generate an Excel document from the model data passed by the controller, create a subclass of the AbstractExcelView class as follows:

package net.codejava.spring;

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

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

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.springframework.web.servlet.view.document.AbstractExcelView;

/**
 * This class builds an Excel spreadsheet document using Apache POI library.
 * @author www.codejava.net
 *
 */
public class ExcelBuilder extends AbstractExcelView {

	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		// get data model which is passed by the Spring container
		List<Book> listBooks = (List<Book>) model.get("listBooks");
		
		// create a new Excel sheet
		HSSFSheet sheet = workbook.createSheet("Java Books");
		sheet.setDefaultColumnWidth(30);
		
		// create style for header cells
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setFontName("Arial");
		style.setFillForegroundColor(HSSFColor.BLUE.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setColor(HSSFColor.WHITE.index);
		style.setFont(font);
		
		// create header row
		HSSFRow header = sheet.createRow(0);
		
		header.createCell(0).setCellValue("Book Title");
		header.getCell(0).setCellStyle(style);
		
		header.createCell(1).setCellValue("Author");
		header.getCell(1).setCellStyle(style);
		
		header.createCell(2).setCellValue("ISBN");
		header.getCell(2).setCellStyle(style);
		
		header.createCell(3).setCellValue("Published Date");
		header.getCell(3).setCellStyle(style);
		
		header.createCell(4).setCellValue("Price");
		header.getCell(4).setCellStyle(style);
		
		// create data rows
		int rowCount = 1;
		
		for (Book aBook : listBooks) {
			HSSFRow aRow = sheet.createRow(rowCount++);
			aRow.createCell(0).setCellValue(aBook.getTitle());
			aRow.createCell(1).setCellValue(aBook.getAuthor());
			aRow.createCell(2).setCellValue(aBook.getIsbn());
			aRow.createCell(3).setCellValue(aBook.getPublishedDate());
			aRow.createCell(4).setCellValue(aBook.getPrice());
		}
	}

}

For working with JExcelApi, make the class extends the AbstractJExcelView class like this:

package net.codejava.spring;

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

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

import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.springframework.web.servlet.view.document.AbstractJExcelView;

/**
 * This class builds an Excel spreadsheet document using JExcelApi library.
 * @author www.codejava.net
 *
 */
public class ExcelBuilder extends AbstractJExcelView {

	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			WritableWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		
		// get data model which is passed by the Spring container
		List<Book> listBooks = (List<Book>) model.get("listBooks");
		
		// create a new Excel sheet
		WritableSheet sheet = workbook.createSheet("Java Books", 0);
		
		// create header row
		sheet.addCell(new Label(0, 0, "Book Title"));
		sheet.addCell(new Label(1, 0, "Author"));
		sheet.addCell(new Label(2, 0, "ISBN"));
		sheet.addCell(new Label(3, 0, "Published Date"));
		sheet.addCell(new Label(4, 0, "Price"));
		
		// create data rows
		int rowCount = 1;
		
		for (Book aBook : listBooks) {
			sheet.addCell(new Label(0, rowCount, aBook.getTitle()));
			sheet.addCell(new Label(1, rowCount, aBook.getAuthor()));
			sheet.addCell(new Label(2, rowCount, aBook.getIsbn()));
			sheet.addCell(new Label(3, rowCount, aBook.getPublishedDate()));
			sheet.addCell(new jxl.write.Number(4, rowCount, aBook.getPrice()));
			
			rowCount++;
		}
	}
}

The above code is self-explanatory. As you can see, there are some differences between the Apache POI API and the JExcelApi.

See more:How to Write Excel Files in Java using Apache POI

5. Configuring Excel View Class

Next, we need to tell Spring to use the above ExcelBuilder class as view class for the view name “excelView” returned from the controller’s downloadExcel() method. There are two ways to do this by creating either a .properties file or an XML file.

Using views.properties file:

Create a .properties file called views.properties under the project’s classpath (which is under src directory in the Eclipse project), with the following line:

excelView.(class)=net.codejava.spring.ExcelBuilder

That tells the Spring’s view resolver to use the net.codejava.spring.ExcelBuilder class to process output for the view name “excelView”.

Using views.xml file:

An alternative to the views.properties file is to use XML version. Create views.xml file under WEB-INF directory with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<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-3.0.xsd">
	
	<bean id="excelView" class="net.codejava.spring.ExcelBuilder" />
	
</beans>

Note that the bean’s ID attribute must correspond to the view name “excelView”.

6. Writing Spring Configuration File

Create a Spring configuration file named spring-mvc.xml under WEB-INF directory. In case you are using views.properties file, put the following content:

<?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:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="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="net.codejava.spring" />

   <bean id="viewResolver1" class="org.springframework.web.servlet.view.ResourceBundleViewResolver">
  		<property name="order" value="1"/>
  		<property name="basename" value="views"/>
	</bean>
	
	<bean id="viewResolver2"
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  		<property name="order" value="2"/>
		<property name="prefix" value="/WEB-INF/jsp/" />
		<property name="suffix" value=".jsp" />
	</bean>
	
</beans>

As seen in the above configuration, there are two view resolvers used here:

- ResourceBundleViewResolver: to resolve view names specified in the views.properties file.
- InternalResourceViewResolver: to resolve view names to JSP pages.

The order property does matter here, in which the first resolver has higher priority than the second one.

In case the views.xml is used, configure Spring as follows:

<?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:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="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="net.codejava.spring" />

   <bean id="viewResolver1" class="org.springframework.web.servlet.view.XmlViewResolver">
  		<property name="order" value="1"/>
  		<property name="location" value="/WEB-INF/views.xml"/>
	</bean>
	
	<bean id="viewResolver2"
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  		<property name="order" value="2"/>
		<property name="prefix" value="/WEB-INF/jsp/" />
		<property name="suffix" value=".jsp" />
	</bean>

	
</beans>

7. Configuring Spring MVC in web.xml

The final step is to configure Spring MVC in the web deployment descriptor file as follows:

<?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_3_0.xsd" 
	id="WebApp_ID" version="3.0">
	
  	<display-name>SpringMvcExcelViewDemo</display-name>
  	
	<servlet>
		<servlet-name>SpringController</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/spring-mvc.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>SpringController</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>  
</web-app>

8. Testing the Spring MVC Excel Download Example

Type the following URL into browser to access the application we’ve built:

http://localhost:8080/SpringMvcExcelViewDemo/

The default page (home.jsp) gets displayed (in FireFox):

Test Spring MVC Excel View Demo in Firefox

Click on the “Download Excel Document” link, the browser will ask for opening or saving the file:

Select Open with (Microsoft Office Excel), the document gets opened in Excel as follows:

Download Eclipse project for this application in the Attachments section below.

NOTE: For Excel export in Spring Boot, I recommend you to follow this article: Spring Boot Export Data to Excel Example

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Attachments:
SpringMvcExcelViewDemo.zip	[Eclipse project (using Apache POI library)]	5126 kB
SpringMvcExcelViewDemo2.zip	[Eclipse project (using JExcelApi library)]	4122 kB

Add comment

Comments

1234567

#31Nam2021-03-05 15:48

Hi Reaksmey,
Check this tutorial: codejava.net/.../...

Quote

#30Reaksmey2021-03-05 07:57

How to write data into existing excel file?

Quote

#29saurabh2020-06-30 02:24

when i click on download link,i am getting http 404 error the resource could not found,

Quote

#28sasikanta2018-09-27 02:24

I need to download all (i.e 10L or more) data to download . But it is taking too much time , more than 10 min and sometime it hangs and sometime session out . How to improve performance to max 1 min. Please reply.

Quote

#27Ankesh2018-09-18 14:01

Hi, I have implemented above functionality but i am not able to download excel, I am fetching list from database and trying to create excel of that database table.,

Presently when i call my action page is just getting refresh.

Quote

1234567

Refresh comments list

Spring MVC with Excel View Example (Apache POI and JExcelApi)

About Apache POI:

About JExcelApi:

1. Creating Java Model Class

2. Coding Entry JSP Page

3. Coding Spring Controller

4. Coding Excel View Class

5. Configuring Excel View Class

6. Writing Spring Configuration File

7. Configuring Spring MVC in web.xml

8. Testing the Spring MVC Excel Download Example

Other Spring Tutorials:

About the Author:

Add comment

Comments