How to Generate Excel in Spring MVC Application using Apache POI

In our earlier tutorial we have already seen How to Create Spring MVC application using Java Based Configuration. In this tutorial we will see some advance concepts of Spring MVC. In this tutorial we will see how to Generate Excel in Spring MVC Application using Apache POI.

How to Generate Excel in Spring MVC Application using Apache POI
How to Generate Excel in Spring MVC Application using Apache POI

I have already a created a tutorial of “How to Create Spring MVC application using Java Bsed Configuration”. This might help you in Creating a Spring Web Application. After creating a Spring MVC application we will be getting a project Structure like this. From that project structure we will add new files/code and generates excel using Spring MVC Application.

How to Generate Excel in Spring MVC Application using Apache POI - Project Structure
How to Generate Excel in Spring MVC Application using Apache POI – Project Structure

Getting Started – How to Generate Excel in Spring MVC Application using Apache POI

To generate Excel Document, we are going to use Apache Poi API. Lets add the below mentioned Dependency in pom.xml file.

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.15</version>
 </dependency>

Java Change – How to Generate Excel in Spring MVC Application using Apache POI

Create a package and add a modal class into it. I have created Student class as mentioned below.

package com.learnfromexamples.springmvcjavaconfig.modal;

public class Student {
     private String studentId;
     private String studentName;
     private String studentMobileNum;
     
     public Student() {
          
     }
     public Student(String studentId, String studentName, String studentMobileNum) {
          super();
          this.studentId = studentId;
          this.studentName = studentName;
          this.studentMobileNum = studentMobileNum;
     }

     // getters and setters method
     //overriden toString() method
}

To build an Excel Document Spring provides an Abstract Class called “AbstractXlsView”. By Extending that, i have created a new class called “ExcelReportView” and overridden the buildExcelDocument() method. Inside that method we have provided our own implementation to build Excel Document.

package com.learnfromexamples.springmvcjavaconfig.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.AbstractXlsView;

import com.learnfromexamples.springmvcjavaconfig.modal.Student;


public class ExcelReportView extends AbstractXlsView{

 @Override
 protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
 HttpServletResponse response) throws Exception {
 
 response.setHeader("Content-Disposition", "attachment;filename=\"student.xls\"");
 List<Student> studentList = (List<Student>) model.get("studentList");
 Sheet sheet = workbook.createSheet("Student Data");
 Row header = sheet.createRow(0);
 header.createCell(0).setCellValue("Student ID");
 header.createCell(1).setCellValue("Student Name");
 header.createCell(2).setCellValue("Student Mobile");
 
 int rowNum = 1;
 for(Student student:studentList){
 Row row = sheet.createRow(rowNum++);
 row.createCell(0).setCellValue(student.getStudentId());
 row.createCell(1).setCellValue(student.getStudentName());
 row.createCell(2).setCellValue(student.getStudentMobileNum());
 }
 }
}

We already have a Controller for this project but for Segregation i have created a new one called “ReportController” with a method getExcel() which returns ModelAndView.

package com.learnfromexamples.springmvcjavaconfig.controller;

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

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;

import com.learnfromexamples.springmvcjavaconfig.modal.Student;
import com.learnfromexamples.springmvcjavaconfig.view.ExcelReportView;

@Controller
@RequestMapping("/report")
public class ReportController {
       @RequestMapping(method=RequestMethod.GET)
       public ModelAndView getExcel(){
              List<Student> studentList = new ArrayList<Student>(); 
              studentList.add(new Student("S01", "Sri", "12344"));
              studentList.add(new Student("S02", "Dharan", "658"));
              return new ModelAndView(new ExcelReportView(), "studentList", studentList);
       }
}

Lets add hyperlink in welcome.jsp with a associated url to RequestMapping.

<%@ 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="spring" uri="http://www.springframework.org/tags"%>
<!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>How to Generate Excel using Spring and Apache POI</title>
</head>
<body>
 <h1>Greeting : ${greetMsg}</h1>
 <spring:url value="/report" var="xlsURL"></spring:url>
 <a href="${xlsURL}">Excel Document</a>
</html>

Project Structure – Generate Excel in Spring MVC Application using Apache POI

Finally Our Project Structure will be like this.

How to Generate Excel in Spring MVC Application using Apache POI - Project Structure Final
How to Generate Excel in Spring MVC Application using Apache POI – Project Structure Final

Lets add the server and hit the URL. http://localhost:7171/SpringMVCJavaConfig/

How to Generate Excel in Spring MVC Application using Apache POI - Output
How to Generate Excel in Spring MVC Application using Apache POI – Output

We will get Excel Document Like This

How to Generate Excel in Spring MVC Application using Apache POI - Output Excel
How to Generate Excel in Spring MVC Application using Apache POI – Output Excel

If you have any questions on Generate Excel in Spring MVC Application using Apache POI, please post your questions in Comment box. If you this tutorial please share it with your friends.

 

 

5 Comments

  1. Manohar Paswan June 6, 2018 Reply
  2. Cris June 7, 2018 Reply
  3. Ahmed Woye March 7, 2019 Reply
  4. Akshay Lonkar March 27, 2020 Reply

Add a Comment

Your email address will not be published. Required fields are marked *