Spring MVC Excel Generation Example

In this post we’ll see how to generate an Excel sheet in Spring MVC using the fields from a view page (JSP).

Technologies used

Following is the list of tools used for the Spring MVC Excel generation example.

Spring 5.0.8 Release (Spring core, spring web, spring webmvc).
Java 10
Tomcat server V 9.0.10
Eclipse IDE
Apache POI 4.0.0 (Required for generating excel)

Table of contents

Spring framework support for Apache POI
Spring MVC Excel generation example using Apache POI
Maven Dependencies
Spring MVC Excel generation example flow
Spring MVC Excel generation example – Model classes
Spring MVC Excel generation – Views
Spring MVC View for Excel sheet
Spring MVC Excel generation – Controller Class
Spring MVC Excel generation – Configuration
Deploying and testing the application

Spring framework support for Apache POI

Apache POI is an open source library using which you can read and write Excel files from your Java program.

Spring framework provides support for Excel document views using two abstract classes-

AbstractXlsView- You will use AbstractXlsView as superclass for Excel document views in traditional XLS format.
AbstractXlsxView- You will use AbstractXlsxView as superclass for Excel document views in the Office 2007 XLSX format.

Both of these classes are compatible with Apache POI 3.5 and higher.

With in Apache POI also there are two implementations for these two types of sheets-

HSSF- It is the POI Project's pure Java implementation of the Excel '97(-2007) (.xls) file format.
XSSF- It is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

Note that there is a component module that attempts to provide a common high level Java API to both OLE2 and OOXML document formats which is SS for Excel workbooks. So we’ll try to use SS package as much as possible so that one implementation can be replaced by another seamlessly.

Spring MVC Excel generation example using Apache POI

In this Spring MVC Excel generation example we’ll generate a .xlsx file available for downloading/openeing as an xlsx format sheet so the class that needs to be extended is AbstractXlsxView.

Spring MVC Project structure using Maven

Please refer Spring Web MVC Example With Annotations for getting the project structure using Spring XML configuration.
Please refer Spring Web MVC Java Configuration Example for getting the project structure using Spring Java configuration.

Maven Dependencies

Apart from Spring dependencies following dependencies are also to be added to the pom.xml for generating excel using Apache POI library.

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.0.0</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.0.0</version>
</dependency>

Here the first dependency is required for working with older excel format having .xls extension.

Second dependency is required for working with the OOXML based file format having .xlsx extension.

These dependencies add the following jars-

poi-4.0.0.jar
commons-codec-1.10.jar
commons-collections4-4.2.jar

poi-ooxml-4.0.0.jar
poi-ooxml-schemas-4.0.0.jar
xmlbeans-3.0.1.jar
commons-compress-1.18.jar
curvesapi-1.04.jar

Spring MVC Excel generation example flow

The model class used in the example is User with fields firstName, lastName, email and dob. In the example there is a JSP that shows a list of Users and there is a button “View Excel”. In the JSP that list of users is bound to a Model.

When the button is clicked, the request is mapped to the appropriate controller method and from there the logical view name and Model where the list of users is set as attribute is transferred to the view which creates an Excel sheet. To resolve a view to a Excel another view resolver has to be added.

Spring MVC Excel generation example – Model classes

User class is the bean class in this example. For field of type java.util.Date pattern is also specified using the @DateTimeFormat annotation.

import java.util.Date;
import org.springframework.format.annotation.DateTimeFormat;

public class User {

 private String firstName;
 private String lastName;
 private String email;
 @DateTimeFormat(pattern = "dd/MM/yyyy")
 private Date dob;
 public User() {
  
 }
 public User(String firstName, String lastName, String email, Date dob) {
  this.firstName = firstName;
  this.lastName = lastName;
  this.email = email;
  this.dob = dob;
 }
 
 public String getFirstName() {
  return firstName;
 }
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }
 public String getLastName() {
  return lastName;
 }
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
 public String getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
 public Date getDob() {
  return dob;
 }
 public void setDob(Date dob) {
  this.dob = dob;
 }
}

Following class acts a container for the List of User objects.

public class UserListContainer {
  private List<User> users;

  public List<User> getUsers() {
    return users;
  }

  public void setUsers(List<User> users) {
    this.users = users;
  }
}

Spring MVC Excel generation – Views

showUsers.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Spring MVC Excel Generation Example</title>
</head>
<body>
<form:form method="POST" action="viewExcel" modelAttribute="Users">
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>DOB</th>
</tr>
<c:forEach items="${Users.users}" var="user" varStatus="tagStatus">
  <tr>
    <td><form:input path="users[${tagStatus.index}].firstName" value="${user.firstName}" readonly="true"/></td>
    <td><form:input path="users[${tagStatus.index}].lastName" value="${user.lastName}" readonly="true"/></td>
    <td><form:input path="users[${tagStatus.index}].email" value="${user.email}" readonly="true"/></td>
    <td><form:input path="users[${tagStatus.index}].dob"  readonly="true"/></td>
  </tr>
</c:forEach>
</table>
<input type="submit" value="View Excel" />
</form:form>
</body>
</html>

This JSP displays the users in the List by iterating the List and binding list again to the Model. Clicking “View Excel” button generates the PDF using the List bound to the Model.

Spring MVC View for Excel sheet

For generating .xlsx sheet you need to extend the Abstract class AbstractXlsxView and provide implementation for the buildExcelDocument() method to generate excel sheet as per your requirement.

import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.netjs.model.User;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

public class ExcelView extends AbstractXlsxView {
  private static final String[] header= {"First Name", "Last Name", "Email", "DOB"};
  @Override
  protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
         HttpServletRequest request, HttpServletResponse response) throws Exception {
    // List of users that will be displayed in the Excel
    List<User> users = (List<User>)model.get("Users");
    int rowNum = 1;
    // Creating sheet with in the workbook
    Sheet sheet = workbook.createSheet("Users");
    /** for header **/
    Font font = workbook.createFont();
    font.setFontName("HELVETICA");
    font.setColor(IndexedColors.WHITE.getIndex());
    font.setBold(true);
            
    CellStyle style = workbook.createCellStyle();
    style.setFont(font);
    style.setWrapText(true);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    Row row = sheet.createRow(0);
    for(int i = 0; i < header.length; i++) {
      // each column 12 characters wide
      sheet.setColumnWidth(i, 12*256);
      Cell cell = row.createCell(i);
      cell.setCellValue(header[i]);
      cell.setCellStyle(style);
    }
    /** header ends **/
    /** Rows in the sheet **/
    CellStyle dateStyle = workbook.createCellStyle();
    // Setting format For the date column
    dateStyle.setDataFormat(workbook.getCreationHelper().createDataFormat()
          .getFormat("dd/MM/yyyy"));
    for(User user : users) {
      row = sheet.createRow(rowNum++);
      row.createCell(0).setCellValue(user.getFirstName());
      row.createCell(1).setCellValue(user.getLastName());
      row.createCell(2).setCellValue(user.getEmail());
      Cell cell = row.createCell(3);
      cell.setCellValue(user.getDob());
      cell.setCellStyle(dateStyle);
    }
  }
}

Spring MVC Excel generation – Controller Class

@Controller
public class UserController {
  @RequestMapping(value = "/getUsers", method = RequestMethod.GET)
  public String getUsers(Model model) throws Exception{
    List<User> users = getListOfUsers();
    UserListContainer userList = new UserListContainer();
    userList.setUsers(users);
    model.addAttribute("Users", userList);
    return "showUsers";
  }
    
  @RequestMapping(value = "/viewExcel", method = RequestMethod.POST)
  public ModelAndView viewExcel(@ModelAttribute("Users") UserListContainer userList) throws Exception{        
    List<User> users = userList.getUsers();
    return new ModelAndView("viewExcel", "Users", users);
  }
    
  // Dummy method for adding List of Users
  private List<User> getListOfUsers() throws ParseException {
    List<User> users = new ArrayList<User>();
    Calendar dob = Calendar.getInstance();
    dob.set(1975,6,12);
    users.add(new User("Jack", "Reacher", "abc@xyz.com", dob.getTime()));
    // Using LocalDate from new time&date API 
    LocalDate date = LocalDate.of(2016, Month.APRIL, 28);
    users.add(new User("Remington", "Steele", "rs@cbd.com",
       Date.from(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant())));
    dob.set(1965,12,6);
    users.add(new User("Jonathan", "Raven", "jr@sn.com", dob.getTime()));
    return users;
  }
}

In the Controller class there are two handler methods. Method getUsers() displays the list of users in a JSP page (showUsers.jsp). In that JSP there is a “View Excel” button, the request created on clicking that button is handled by the handler method viewExcel() which passes the list of users and the logical view name to be resolved to an excel view.

Spring MVC Excel generation – Configuration

The Spring configuration file is as follows.

mvcexample-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:context="http://www.springframework.org/schema/context"
    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.xsd
        http://www.springframework.org/schema/mvc 
        http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd">
        
  <mvc:annotation-driven />
  <context:component-scan base-package="org.netjs.controller" />
  <bean id="ExcelResolver" class=
      "org.springframework.web.servlet.view.ResourceBundleViewResolver">
    <property name="order" value="1"/>
    <property name="basename" value="excel-view"/>
  </bean>
  <bean id="JSPViewResolver" class=
    "org.springframework.web.servlet.view.InternalResourceViewResolver">
    <property name="order" value="2"/>
    <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
    <property name="prefix" value="/WEB-INF/jsp/" />
    <property name="suffix" value=".jsp" />
  </bean>
</beans>

As you can see two view resolver classes are configured here.

ResourceBundleViewResolver resolves the views from the properties file. ResourceBundleViewResolver is the Implementation of ViewResolver that uses bean definitions in a ResourceBundle, specified by the bundle base name, and for each view it is supposed to resolve, it uses the value of the property [viewname].(class) as the view class and the value of the property [viewname].url as the view url.

Here “basename” property has the value excel-view which means properties file is named excel-view.properties file.

excel-view.properties

viewExcel.(class)=org.netjs.config.ExcelView

Controller class handler method viewExcel() returns logical view name as “viewExcel” which is used to resolve the view class using this properties file.

Another view resolver InternalResourceViewResolver is used to resolve the view name to JSPs.

Here note that both the Resolvers have a property order too which decides the priority. Lower order value means higher priority. Here ResourceBundleViewResolver has order set as 1 which means Spring framework will first try to resolve view using this class.

As a rule InternalResourceViewResolver should always have higher order value because it will always be resolved to view irrespective of value returned giving no chance to any other Resolver class.

Deploying and testing the application

Once the application is deployed to Tomcat server it can be accessed using the URL- http://localhost:8080/spring-mvc/getUsers

Generated Excel for download

On clicking the View Excel button Excel sheet is generated and there is a prompt for saving it.

Generated Excel sheet

Generated excel sheet with user details.

That's all for this topic Spring MVC Excel Generation Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Spring Tutorial Page

Related Topics

You may also like-

Tech Tutorials

Wednesday, January 27, 2021