Spring MVC And Excel File

In this Post we will see step by step use of AbstractExcelView class in Spring MVC application to export data to Excel file for download.

In this sample we are going to export data to Excel file via Apache POI library.

Final Project Structure Look Like below:

Below is the Maven file with dependencies required:

<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.mahesh.common</groupId>
  <artifactId>SpringMVCExcel</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>SpringMVC Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>

<!-- Spring framework -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring</artifactId>
<version>2.5.6</version>
</dependency>

    <!-- Spring MVC framework -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>2.5.6</version>
</dependency>

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

<!-- Pdf library -->
<dependency>
<groupId>com.lowagie</groupId>
<artifactId>itext</artifactId>
<version>1.4.8</version>
</dependency>

<!-- JSTL -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.1.2</version>
</dependency>

      <dependency>
          <groupId>javax.servlet</groupId>
          <artifactId>servlet-api</artifactId>
          <version>2.5</version>
          <scope>provided</scope>
      </dependency>

<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>

  </dependencies>
  <build>
    <finalName>SpringMVCExcel</finalName>
      <plugins>
          <plugin>
              <groupId>org.apache.maven.plugins</groupId>
              <artifactId>maven-compiler-plugin</artifactId>
              <version>2.0.2</version>
              <configuration>
                  <source>1.6</source>
                  <target>1.6</target>
              </configuration>
          </plugin>
      </plugins>
  </build>
</project>

Below is the Code for Controller EmployeeController.java :

A controller class, generate dummy data for demonstration, and get the request parameter to determine which view to return. If the request parameter is equal to “EXCEL”, then return an Excel view (AbstractExcelView).

package com.mahesh.common.controller;

import java.util.HashMap;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.springframework.web.bind.ServletRequestUtils;

import org.springframework.web.servlet.ModelAndView;

import org.springframework.web.servlet.mvc.AbstractController;

public class EmployeeController extends AbstractController{

protected ModelAndView handleRequestInternal(HttpServletRequest request,

HttpServletResponse response) throws Exception {

String output =

ServletRequestUtils.getStringParameter(request, "output");

//dummy data

Map<String,String> employeeData = new HashMap<String,String>();

        employeeData.put("242511", "Mahesh");

        employeeData.put("242512", "Kumar");

        employeeData.put("242513", "Chegu");

        employeeData.put("242514", "Rithvik");

        employeeData.put("242515", "vaisnav");

if(output ==null || "".equals(output)){

//return normal view

return new ModelAndView("EmployeeSummary","employeeData",employeeData);

}else if("EXCEL".equals(output.toUpperCase())){

//return excel view

return new ModelAndView("ExcelEmployeeSummary","employeeData",employeeData);

}else{

//return normal view

return new ModelAndView("EmployeeSummary","employeeData",employeeData);

}

}

}

Below is the code for AbstractExcelView:

Create an Excel view by extends the AbstractExcelView class, and override the buildExcelDocument() method to populate the data to Excel file. The AbstractExcelView is using the Apache POI API to create the Excel file detail.

package com.mahesh.common.view;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

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.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.IndexedColors;

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

public class ExcelEmployeeView extends AbstractExcelView{

protected void buildExcelDocument(Map model, HSSFWorkbook workbook,

HttpServletRequest request, HttpServletResponse response)

throws Exception {

Map<String,String> revenueData = (Map<String,String>) model.get("employeeData");

//create a wordsheet

HSSFSheet sheet = workbook.createSheet("Employee Report");

CellStyle headerRowStyle = workbook.createCellStyle();

        headerRowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        headerRowStyle.setAlignment(CellStyle.ALIGN_CENTER);

        headerRowStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());

        headerRowStyle.setBorderBottom(CellStyle.BORDER_THIN);

        headerRowStyle.setBorderTop(CellStyle.BORDER_THIN);

        headerRowStyle.setBorderRight(CellStyle.BORDER_THIN);

        headerRowStyle.setBorderLeft(CellStyle.BORDER_THIN);

        CellStyle allCellStyle = workbook.createCellStyle();

        allCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        allCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

        allCellStyle.setBorderTop(CellStyle.BORDER_THIN);

        allCellStyle.setBorderRight(CellStyle.BORDER_THIN);

        allCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

        HSSFRow header = sheet.createRow(8);

header.createCell(3).setCellValue("Employee Id");

        header.getCell(3).setCellStyle(headerRowStyle);

header.createCell(4).setCellValue("Employee Name");

        header.getCell(4).setCellStyle(headerRowStyle);

int rowNum = 9;

for (Map.Entry<String, String> entry : revenueData.entrySet()) {

//create the row data

HSSFRow row = sheet.createRow(rowNum++);

row.createCell(3).setCellValue(entry.getKey());

            row.getCell(3).setCellStyle(allCellStyle);

row.createCell(4).setCellValue(entry.getValue());

            row.getCell(4).setCellStyle(allCellStyle);

        }

}

}

Below is the code for Spring Configuration:

mvc-dispatcher-servlet.xml

<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 class="org.springframework.web.servlet.mvc.support.ControllerClassNameHandlerMapping" />

    

    <bean class="com.mahesh.common.controller.EmployeeController" />

    

    <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 id="viewResolver"

      class="org.springframework.web.servlet.view.InternalResourceViewResolver" >

        <property name="prefix">

           <value>/WEB-INF/pages/</value>

         </property>

        <property name="suffix">

           <value>.jsp</value>

        </property>

        <property name="order" value="1" />

    </bean>

</beans>

spring-excel-views.xml

<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="ExcelEmployeeSummary"

  class="com.mahesh.common.view.ExcelEmployeeView">

</bean>

</beans>

Below is the Content for web.xml:

<web-app id="WebApp_ID" version="2.4" 

xmlns="http://java.sun.com/xml/ns/j2ee" 

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee 

http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

  <display-name>Spring Web MVC Application</display-name>

  

  <servlet>

  <servlet-name>mvc-dispatcher</servlet-name>

    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

    <load-on-startup>1</load-on-startup>

  </servlet>

  

  <servlet-mapping>

  <servlet-name>mvc-dispatcher</servlet-name>

    <url-pattern>*.htm</url-pattern>

  </servlet-mapping>

  <context-param>

<param-name>contextConfigLocation</param-name>

<param-value>/WEB-INF/mvc-dispatcher-servlet.xml</param-value>

  </context-param>

  

  <listener>

    <listener-class>

      org.springframework.web.context.ContextLoaderListener

    </listener-class>

  </listener>

  

</web-app>

Below is the Content for EmployeeSummary.jsp :

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

<html>

<body>

<h2>Spring MVC</h2>

<h3>Employee Report</h3>

<table border="1px" cellpadding="8px">

<tr>

<td>Employee Id</td><td>Employee Name</td>

</tr>

<c:forEach items="${employeeData}" var="current">

<tr>

   <td><c:out value="${current.key}" /></td>

   <td><c:out value="${current.value}" /></td>

</tr>

</c:forEach>

</table>

</body>

</html>

OutPut in XLSheet:

http://localhost:8080/SpringMVCExcel/employee.htm?output=EXCEL

OutPut in JSP:

http://localhost:8080/SpringMVCExcel/employee.htm