Code4java: AbstractExcelView Using Spring MVC

AbstractExcelView Using Spring MVC

Hello Friends Hope you all are fine.today i am going to demonstrate how we can work with Excel using Spring mvc.i am using AbstractExcelView using which we can add/display excel data. i am putting sample code here:

Model :
package org.demo.model;

public class Student
{
    private int id;
    private String name;
    private String college;


    public Student() { }

    public Student(int id,String name,String college)
    {
          this.id=id;
          this.name=name;
          this.college=college;
    }
    public int getId() {
       return id;
    }

    public void setId(int id) {
       this.id = id;
    }

    public String getName() {
       return name;
    }

    public void setName(String name) {
       this.name = name;
    }

    public String getCollege() {
       return college;
    }

    public void setCollege(String college) {
       this.college = college;
    }
}

Controller Class:

package org.demo.controller;

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

import org.demo.model.Student;
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;

@Controller
public class MainController
{
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String viewHome()
    {
        return "home";
    }

    @RequestMapping(value = "/downloadExcel", method = RequestMethod.GET)
    public ModelAndView downloadExcel()
    {
        // create some sample data
        List<Student> listStudents= new ArrayList<Student>();
        listStudents.add(new Student(1,"Nisarg","Semcom"));
        listStudents.add(new Student(2,"Raj","GLS"));
        listStudents.add(new Student(3,"Kinjal","Semcom"));


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

}

ExcelBuilder.java which prepares view to be resolved

package org.demo.view;

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

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

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.demo.model.Student;
import org.springframework.web.servlet.view.document.AbstractExcelView;

public class ExcelBuilder extends AbstractExcelView
{

    @Override
    protected void buildExcelDocument(Map<String, Object> model,HSSFWorkbook workbook, HttpServletRequest arg2, HttpServletResponse arg3)
            throws Exception
    {
    List<Student> listStudents=(List<Student>) model.get("listStudents");

        HSSFSheet sheet=workbook.createSheet("listStudents");
        sheet.setDefaultColumnWidth(30);

        HSSFRow header=sheet.createRow(0);
        header.createCell(0).setCellValue("id");
        header.createCell(1).setCellValue("Name");
        header.createCell(2).setCellValue("College");

        int r=1;

        for(Student s: listStudents)
        {
            HSSFRow arow=sheet.createRow(r++);

            arow.createCell(0).setCellValue(s.getId());
            arow.createCell(1).setCellValue(s.getName());
            arow.createCell(2).setCellValue(s.getCollege());

        }

    }

}

home.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<h3><a href="/downloadExcel">Download Excel Document</a></h3>
</body>
</html>

web.xml

<?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>SpringExcelView</display-name>

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

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

View.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="excelView" class="org.demo.view.ExcelBuilder" />

</beans>

dispatcherservlet-servlet.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"
    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="org.demo.controller" />

   <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="/" />
        <property name="suffix" value=".jsp" />
    </bean>


</beans>

the output will look like as given below: