Spring MVC Excel View Example

In this tutorial we demonstrate how to create an Excel view using Spring MVC. By utilizing Spring MVC Content negotiation we can have multiple views of the same resource. This example shows how to create an excel view, either in .xls and .xlsx format. When working with large excel documents it is profitable to use the streaming xlsx view. The streaming view uses less memory and can improve performance of large excel documents.

Maven Dependencies

We use Apache Maven to manage our project. Add the following dependencies to your project and Maven will resolve them automatically. To create excel documents we are using apache POI. For creating .xls excel documents, you need the org.apache.poi:poi dependency. When you want to create .xlsx excel documents, you need the org.apache.poi:poi-ooxml dependency. Make sure these are on the classpath.

<?xml version="1.0" encoding="UTF-8"?>
<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.memorynotfound.spring.mvc</groupId>
    <artifactId>xls-xlsx-content-negotiation</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <name>SPRING-MVC - ${project.artifactId}</name>
    <url>https://memorynotfound.com</url>
    <packaging>war</packaging>

    <properties>
        <encoding>UTF-8</encoding>
        <spring.version>4.2.6.RELEASE</spring.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- xls view -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>

        <!-- xlsx view -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

        <!-- servlet api -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Spring MVC Excel View Configuration

The configureViewResolvers method registers the views we are using in this example. We’ll create these views later in this tutorial. Things to notice is we registered both the XlsxView and XlsxStreamingView, buth only one of these can be active because both are mapped to the same file extension, request parameter and HTTP Accept header.

package com.memorynotfound.config;

import com.memorynotfound.view.XlsView;
import com.memorynotfound.view.XlsxStreamingView;
import com.memorynotfound.view.XlsxView;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.MediaType;
import org.springframework.web.servlet.config.annotation.*;

@EnableWebMvc
@Configuration
@ComponentScan("com.memorynotfound")
public class WebConfig extends WebMvcConfigurerAdapter {

    @Override
    public void configureContentNegotiation(ContentNegotiationConfigurer configurer) {
        configurer
                .defaultContentType(MediaType.TEXT_HTML)
                .parameterName("type")
                .favorParameter(true)
                .ignoreUnknownPathExtensions(false)
                .ignoreAcceptHeader(false)
                .useJaf(true);
    }

    @Override
    public void configureViewResolvers(ViewResolverRegistry registry) {
        registry.jsp("/WEB-INF/views/", ".jsp");
        registry.enableContentNegotiation(
                new XlsView(),
                new XlsxView(),
                new XlsxStreamingView());
    }
}

This is the equivalent Spring XML configuration as above.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       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="com.memorynotfound" />

    <bean id="contentNegotiationManager" class="org.springframework.web.accept.ContentNegotiationManagerFactoryBean">
        <property name="defaultContentType" value="TEXT_HTML"/>
        <property name="parameterName" value="type"/>
        <property name="favorParameter" value="true"/>
        <property name="ignoreUnknownPathExtensions" value="false"/>
        <property name="ignoreAcceptHeader" value="false"/>
        <property name="useJaf" value="true"/>
    </bean>

    <mvc:view-resolvers>
        <mvc:content-negotiation>
            <mvc:default-views>
                <bean class="com.memorynotfound.view.XlsView"/>
                <bean class="com.memorynotfound.view.XlsxView"/>
                <bean class="com.memorynotfound.view.XlsxStreamingView"/>
            </mvc:default-views>
        </mvc:content-negotiation>
        <mvc:jsp prefix="/WEB-INF/views/" suffix=".jsp"/>
    </mvc:view-resolvers>

</beans>

We need to register the DispatcherServlet to serve the request to the correct controller methods.

package com.memorynotfound.config;

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class ServletInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {

    @Override
    protected Class<?>[] getServletConfigClasses() {
        return new Class[] { WebConfig.class };
    }

    @Override
    protected String[] getServletMappings() {
        return new String[] { "/" };
    }

    @Override
    protected Class<?>[] getRootConfigClasses() {
        return null;
    }
}

Create the Controller Endpoint

This Course POJO is used to add data to the Excell document.

package com.memorynotfound.model;

import java.util.Date;

public class Course {

    private Integer id;
    private String name;
    private Date date;

    public Course() {
    }

    public Course(Integer id, String name, Date date) {
        this.id = id;
        this.name = name;
        this.date = date;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Date getDate() {
        return date;
    }
}

The CourseController adds a list of Course instances to the Model, which will be used to display on the Excel document.

package com.memorynotfound.controller;

import com.memorynotfound.model.Course;
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 java.util.Arrays;
import java.util.Date;
import java.util.List;

@Controller
public class CourseController {

    List<Course> documents = Arrays.asList(
            new Course(1, "Spring MVC Xls View", new Date()),
            new Course(2, "Spring MVC Xlsx View", new Date()),
            new Course(3, "Spring MVC XlsxStreaming View", new Date())
    );

    @RequestMapping(method = RequestMethod.GET)
    public String getDocuments(Model model) {
        model.addAttribute("courses", documents);
        return "index";

    }
}

Excel Views

There are 2 file formats in which we can create an Excel document. The .xls is the old format, the .xlsx is the new format which is XML based. We are using apache POI to create excel files, when creating .xls documents make sure the org.apache.poi:poi dependency is on the classpath. When working with .xlsx files, you need the org.apache.poi:poi-ooxml dependency.

Changing the name of the excel document

When you want to change the name of the downloaded excel document, you need to set the Content-Disposition header using the HttpServletResponse#setHeader("Content-Disposition", "attachment; filename=\"filename.xls\""); method.

Xls Excel using AbstractXlsView

The first view we create is the XlsView which extends from AbstractXlsView. You create the excel document by overriding the buildExcelDocument, the rest is self explanatory.

package com.memorynotfound.view;

import com.memorynotfound.model.Course;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.servlet.view.document.AbstractXlsView;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.DateFormat;
import java.util.List;
import java.util.Map;

public class XlsView extends AbstractXlsView {

    private static final DateFormat DATE_FORMAT = DateFormat.getDateInstance(DateFormat.SHORT);

    @Override
    protected void buildExcelDocument(Map<String, Object> model,
                                      Workbook workbook,
                                      HttpServletRequest request,
                                      HttpServletResponse response) throws Exception {

        // change the file name
        response.setHeader("Content-Disposition", "attachment; filename=\"my-xls-file.xls\"");

        @SuppressWarnings("unchecked")
        List<Course> courses = (List<Course>) model.get("courses");

        // create excel xls sheet
        Sheet sheet = workbook.createSheet("Spring MVC AbstractXlsView");

        // create header row
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("Name");
        header.createCell(2).setCellValue("Date");

        // Create data cells
        int rowCount = 1;
        for (Course course : courses){
            Row courseRow = sheet.createRow(rowCount++);
            courseRow.createCell(0).setCellValue(course.getId());
            courseRow.createCell(1).setCellValue(course.getName());
            courseRow.createCell(2).setCellValue(DATE_FORMAT.format(course.getDate()));
        }
    }
}

Xlsx Excel using AbstractXlsxView

The second view is 99% the same code. But instead of extending the AbstractXlsView, we extend from the AbstractXlsxView. This class will take care of the generation of the Excel document in .xlsx format.

package com.memorynotfound.view;

import com.memorynotfound.model.Course;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.servlet.view.document.AbstractXlsxView;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.DateFormat;
import java.util.List;
import java.util.Map;

public class XlsxView extends AbstractXlsxView {

    private static final DateFormat DATE_FORMAT = DateFormat.getDateInstance(DateFormat.SHORT);

    @Override
    protected void buildExcelDocument(Map<String, Object> model,
                                      Workbook workbook,
                                      HttpServletRequest request,
                                      HttpServletResponse response) throws Exception {

        // change the file name
        response.setHeader("Content-Disposition", "attachment; filename=\"my-xlsx-file.xlsx\"");

        @SuppressWarnings("unchecked")
        List<Course> courses = (List<Course>) model.get("courses");

        // create excel xls sheet
        Sheet sheet = workbook.createSheet("Spring MVC AbstractXlsxView");

        // create header row
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("Name");
        header.createCell(2).setCellValue("Date");

        // Create data cells
        int rowCount = 1;
        for (Course course : courses){
            Row courseRow = sheet.createRow(rowCount++);
            courseRow.createCell(0).setCellValue(course.getId());
            courseRow.createCell(1).setCellValue(course.getName());
            courseRow.createCell(2).setCellValue(DATE_FORMAT.format(course.getDate()));
        }

    }
}

XLSX Streaming View

The last Excel view we create is the XlsxStreamingView which extends from the AbstractXlsxStreamingView. This can improve the performance of large excel documents. But when you look at the javadoc of the SXSSFWorkbook, notice that some clients are incompatible with this style of streaming. That being said, when you need to process large excel documents, this is the way to go.

package com.memorynotfound.view;

import com.memorynotfound.model.Course;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.servlet.view.document.AbstractXlsxStreamingView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.DateFormat;
import java.util.List;
import java.util.Map;

public class XlsxStreamingView extends AbstractXlsxStreamingView {

    private static final DateFormat DATE_FORMAT = DateFormat.getDateInstance(DateFormat.SHORT);

    @Override
    protected void buildExcelDocument(Map<String, Object> model,
                                      Workbook workbook, HttpServletRequest request,
                                      HttpServletResponse response) throws Exception {
        // change the file name
        response.setHeader("Content-Disposition", "attachment; filename=\"my-xlsxStreaming-file.xlsx\"");

        @SuppressWarnings("unchecked")
        List<Course> courses = (List<Course>) model.get("courses");

        // create excel xls sheet
        Sheet sheet = workbook.createSheet("Spring MVC AbstractXlsxStreamingView");

        // create header row
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("Name");
        header.createCell(2).setCellValue("Date");

        // Create data cells
        int rowCount = 1;
        for (Course course : courses){
            Row courseRow = sheet.createRow(rowCount++);
            courseRow.createCell(0).setCellValue(course.getId());
            courseRow.createCell(1).setCellValue(course.getName());
            courseRow.createCell(2).setCellValue(DATE_FORMAT.format(course.getDate()));
        }
    }
}

Web View

The default view we registered is the text/html. This view will be displayed using the same URI. Using either HTTP Accept header, a specific request parameter or correct file extension will change the view that’ll be displayed.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"  pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>

<html>
<head>
    <title>Spring MVC Excel View</title>
</head>
<body>

<table>
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Date</th>
    </tr>
    <c:forEach var="c" items="${courses}">
        <tr>
            <td>${c.id}</td>
            <td>${c.name}</td>
            <td><fmt:formatDate value="${c.date}" pattern="MM/dd/yy"/></td>
        </tr>
    </c:forEach>
</table>

</body>
</html>

Demo

URL: http://localhost:8081/spring-mvc-excel-view/courses

URL: http://localhost:8081/spring-mvc-excel-view/courses.xls or http://localhost:8081/spring-mvc-excel-view/courses?type=xls

Mime type of .xls: application/vnd.ms-excel

URL: http://localhost:8081/spring-mvc-excel-view/courses.xlsx or http://localhost:8081/spring-mvc-excel-view/courses?type=xlsx

Mime type of .xlsx: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Download