Spring MVC Excel View Resolver

Exploring Kotlin

Less than 100 pages covering Kotlin syntax and features in straight and to the point explanation.

spring-mvc-view The V in MVC is about viewing the data, or "model", requested through the controller. Spring MVC can render retrieved data into views of different format such as PDF, JSP or Excel. Spring framework view resolvers e.g. InternalResourceViewResolver, XmlViewResolver, ResourceBundleViewResolver and a few others enable you to render model into views. This post shows you how to render model into Excel view using XmlViewResolver.

Overview

We will build a REST service using Spring MVC that returns Excel “.xlsx” document. In order to build the service we need the following Java classes and XML files:

  • Controller.java: Java class that receives mapped HTTP request.
  • Link.java: Simple Java class to be used as model object holding links data.
  • ExcelXlsxView: Java class extending AbstractXlsxView and generating the actual Excel document.
  • view.xml: XML file containing Excel view class configuration.
  • rest-servlet.xml: Spring XML configuration file containing configuration of controllers and view resolvers.

spring-mvc-excel-view-structure

Dependencies

To develop and run the service you need the following libraries:

  • Spring core, context, beans, expression, webmvc, web.
  • Apache common logging
  • Apache POI
  • Apache POI OOXML

Complete Maven dependencies is included in pom.xml

Project Structure

spring-mvc-excel-view-project

Controller.java

package com.hmkcode.controllers;

import java.util.LinkedList;
import java.util.List;

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

import com.hmkcode.model.Link;

@RestController
public class Controller {
	
	@RequestMapping(value = "/viewExcel", method = RequestMethod.GET)
	public ModelAndView getExcel(){
		System.out.println("getExcel!");
		
		List<Link> links = new LinkedList<Link>();
		links.add(new Link("Android", "android.com"));
		links.add(new Link("Spring", "spring.io"));
		links.add(new Link("Firebase", "firebase.com"));
		
		return new ModelAndView("ExcelXlsxView", "model", links);
	}

}

ExcelXlsxView.java

package com.hmkcode.view;

import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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.AbstractXlsxView;
import com.hmkcode.model.Link;

public class ExcelXlsxView extends AbstractXlsxView {

	@Override
	protected void buildExcelDocument(Map<String, Object> model, 
			Workbook workbook, 
			HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		
		@SuppressWarnings("unchecked")
		List<Link> links = (List<Link>) model.get("model");
	         
        // create a new Excel sheet
        Sheet sheet = workbook.createSheet("Sheet");

        int i = 0;
        Row row = null;
        for(Link link:links){
        	row = sheet.createRow(i++);
        	row.createCell(0).setCellValue(link.getTitle());
        	row.createCell(1).setCellValue(link.getUrl());
        }
	}
}

Link.java

package com.hmkcode.model;

public class Link {

	private String title;
	private String url;
	
	public Link(String title, String url){
		this.title = title;
		this.url = url;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getUrl() {
		return url;
	}
	public void setUrl(String url) {
		this.url = url;
	}
	@Override
	public String toString() {
		return "Link [title=" + title + ", url=" + url + "]";
	}
}

views.xml

<?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="ExcelXlsxView" class="com.hmkcode.view.ExcelXlsxView" />
     
</beans>

rest-servlet.xml


<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-3.0.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">

	<context:component-scan base-package="com.hmkcode.controllers" />


    <bean id="ExcelXlsxView" class="org.springframework.web.servlet.view.XmlViewResolver">
        <property name="order" value="1"/>
        <property name="location" value="/WEB-INF/views.xml"/>
    </bean>
    
</beans>

Running the Service

pom.xml includes jetty server as a plug-in. To run the service type the following command

>mvn jetty:run

spring-mvc-excel-view-running

Output

The service response will contain an Excel document “.xlsx”. The content of the document will be as shown below.

spring-mvc-excel-view-output

Source Code @ GitHub

Exploring Kotlin

Less than 100 pages covering Kotlin syntax and features in straight and to the point explanation.