Spring MVC with Excel View Example (Apache POI and JExcelApi)
- Details
- Written by Nam Ha Minh
- Last Updated on 02 September 2020   |   Print Email
About Apache POI:
Apache POI is a set of pure Java libraries for reading and writing Microsoft Office documents such as Word, Excel, Powerpoint, Outlook, etc. Click the following link to download its latest distribution (which is Apache POI 3.9, as of this writing):The distribution comes with several jar files, but the only the poi-VERSION.jar file is required for typical usage of generating Excel documents (if you want to generate Excel XML format such as *.xlsx files, use the poi-ooxml-VERSION.jar file).About JExcelApi:
JExcelApi is a Java library that is dedicated for reading, writing and modifying Excel spreadsheets. It supports Excel 2003 file format and older versions. You can download JExcelApi from the following link: JExcelApi DownloadTo work with JExcelApi, you need to add its only jar file: jxl.jar - to your project’s classpath. And Spring provides an abstract class called AbstractJExcelView which should be extended to generate an Excel document using JExcelApi, similarly to the case of Apache POI.This tutorial will use Apache POI for the sample application. However, you can also download a JExcelApi version of the project in the Attachments section.In Eclipse IDE, create a Dynamic Web Project called SpringMvcExcelViewDemo. We will end up with the following project structure:The jar files used are:- spring-beans-3.2.3.RELEASE.jar
- spring-context-3.2.3.RELEASE.jar
- spring-context-support-3.2.3.RELEASE.jar
- spring-core-3.2.3.RELEASE.jar
- spring-expression-3.2.3.RELEASE.jar
- spring-web-3.2.3.RELEASE.jar
- spring-webmvc-3.2.3.RELEASE.jar
- Commons Logging (required by Spring):
- commons-logging-1.1.1.jar
- Apache POI:
- poi-3.9-20121203.jar
1. Creating Java Model Class
We will generate an Excel document that contains a list of Java books, so create the following model class (Book.java):package net.codejava.spring; public class Book { private String title; private String author; private String isbn; private String publishedDate; private float price; public Book(String title, String author, String isbn, String publishedDate, float price) { this.title = title; this.author = author; this.isbn = isbn; this.publishedDate = publishedDate; this.price = price; } // getters and setters }
2. Coding Entry JSP Page
We need to create a JSP page that displays a hyperlink on which the users will click to download the Excel file. Create a folder called jsp inside WEB-INF directory and create a JSP file called home.jsp under WEB-INF\jsp with the following content:<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!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=UTF-8"> <title>Spring MVC Excel View Demo (Apache POI)</title> </head> <body> <div align="center"> <h1>Spring MVC Excel View Demo (Apache POI)</h1> <h3><a href="/downloadExcel">Download Excel Document</a></h3> </div> </body> </html>The hyperlink Download Excel Document points to a relative URL downloadExcel which will be handled by a Spring controller class as described below.
3. Coding Spring Controller
Create a Spring controller class called MainController with the following code:package net.codejava.spring; 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; /** * A Spring controller that allows the users to download an Excel document * generated by the Apache POI library. * * @author www.codejava.net * */ @Controller public class MainController { /** * Handle request to the default page */ @RequestMapping(value = "/", method = RequestMethod.GET) public String viewHome() { return "home"; } /** * Handle request to download an Excel document */ @RequestMapping(value = "/downloadExcel", method = RequestMethod.GET) public ModelAndView downloadExcel() { // create some sample data List<Book> listBooks = new ArrayList<Book>(); listBooks.add(new Book("Effective Java", "Joshua Bloch", "0321356683", "May 28, 2008", 38.11F)); listBooks.add(new Book("Head First Java", "Kathy Sierra & Bert Bates", "0596009208", "February 9, 2005", 30.80F)); listBooks.add(new Book("Java Generics and Collections", "Philip Wadler", "0596527756", "Oct 24, 2006", 29.52F)); listBooks.add(new Book("Thinking in Java", "Bruce Eckel", "0596527756", "February 20, 2006", 43.97F)); listBooks.add(new Book("Spring in Action", "Craig Walls", "1935182358", "June 29, 2011", 31.98F)); // return a view which will be resolved by an excel view resolver return new ModelAndView("excelView", "listBooks", listBooks); } }As we can see, this controller class implements two request handling methods:
- viewHome(): this method simply returns a logical view name “home” which will be resolved to the home.jsp page (We will configure view resolver for JSP later).
- downloadExcel(): this method creates some dummy data, e.g. creating some books and add them to a list. Finally this method returns a logical view name “excelView” and passes the list of books as the name “listBooks” to the model. We will configure an Excel view class for this view later.
4. Coding Excel View Class
To generate an Excel document from the model data passed by the controller, create a subclass of the AbstractExcelView class as follows:package net.codejava.spring; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFFont; 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.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.springframework.web.servlet.view.document.AbstractExcelView; /** * This class builds an Excel spreadsheet document using Apache POI library. * @author www.codejava.net * */ public class ExcelBuilder extends AbstractExcelView { @Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Book> listBooks = (List<Book>) model.get("listBooks"); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet("Java Books"); sheet.setDefaultColumnWidth(30); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Arial"); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create header row HSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("Book Title"); header.getCell(0).setCellStyle(style); header.createCell(1).setCellValue("Author"); header.getCell(1).setCellStyle(style); header.createCell(2).setCellValue("ISBN"); header.getCell(2).setCellStyle(style); header.createCell(3).setCellValue("Published Date"); header.getCell(3).setCellStyle(style); header.createCell(4).setCellValue("Price"); header.getCell(4).setCellStyle(style); // create data rows int rowCount = 1; for (Book aBook : listBooks) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(aBook.getTitle()); aRow.createCell(1).setCellValue(aBook.getAuthor()); aRow.createCell(2).setCellValue(aBook.getIsbn()); aRow.createCell(3).setCellValue(aBook.getPublishedDate()); aRow.createCell(4).setCellValue(aBook.getPrice()); } } }For working with JExcelApi, make the class extends the AbstractJExcelView class like this:
package net.codejava.spring; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import org.springframework.web.servlet.view.document.AbstractJExcelView; /** * This class builds an Excel spreadsheet document using JExcelApi library. * @author www.codejava.net * */ public class ExcelBuilder extends AbstractJExcelView { @Override protected void buildExcelDocument(Map<String, Object> model, WritableWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Book> listBooks = (List<Book>) model.get("listBooks"); // create a new Excel sheet WritableSheet sheet = workbook.createSheet("Java Books", 0); // create header row sheet.addCell(new Label(0, 0, "Book Title")); sheet.addCell(new Label(1, 0, "Author")); sheet.addCell(new Label(2, 0, "ISBN")); sheet.addCell(new Label(3, 0, "Published Date")); sheet.addCell(new Label(4, 0, "Price")); // create data rows int rowCount = 1; for (Book aBook : listBooks) { sheet.addCell(new Label(0, rowCount, aBook.getTitle())); sheet.addCell(new Label(1, rowCount, aBook.getAuthor())); sheet.addCell(new Label(2, rowCount, aBook.getIsbn())); sheet.addCell(new Label(3, rowCount, aBook.getPublishedDate())); sheet.addCell(new jxl.write.Number(4, rowCount, aBook.getPrice())); rowCount++; } } }The above code is self-explanatory. As you can see, there are some differences between the Apache POI API and the JExcelApi. See more:How to Write Excel Files in Java using Apache POI
5. Configuring Excel View Class
Next, we need to tell Spring to use the above ExcelBuilder class as view class for the view name “excelView” returned from the controller’s downloadExcel() method. There are two ways to do this by creating either a .properties file or an XML file.Using views.properties file:Create a .properties file called views.properties under the project’s classpath (which is under src directory in the Eclipse project), with the following line:excelView.(class)=net.codejava.spring.ExcelBuilderThat tells the Spring’s view resolver to use the net.codejava.spring.ExcelBuilder class to process output for the view name “excelView”. Using views.xml file:An alternative to the views.properties file is to use XML version. Create views.xml file under WEB-INF directory with the following content:
<?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="net.codejava.spring.ExcelBuilder" /> </beans>Note that the bean’s ID attribute must correspond to the view name “excelView”.
6. Writing Spring Configuration File
Create a Spring configuration file named spring-mvc.xml under WEB-INF directory. In case you are using views.properties file, put the following content:<?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="net.codejava.spring" /> <bean id="viewResolver1" class="org.springframework.web.servlet.view.ResourceBundleViewResolver"> <property name="order" value="1"/> <property name="basename" value="views"/> </bean> <bean id="viewResolver2" class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="order" value="2"/> <property name="prefix" value="/WEB-INF/jsp/" /> <property name="suffix" value=".jsp" /> </bean> </beans>As seen in the above configuration, there are two view resolvers used here:
- ResourceBundleViewResolver: to resolve view names specified in the views.properties file.
- InternalResourceViewResolver: to resolve view names to JSP pages.
<?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="net.codejava.spring" /> <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="/WEB-INF/jsp/" /> <property name="suffix" value=".jsp" /> </bean> </beans>
7. Configuring Spring MVC in web.xml
The final step is to configure Spring MVC in the web deployment descriptor file as follows:<?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>SpringMvcExcelViewDemo</display-name> <servlet> <servlet-name>SpringController</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <param-value>/WEB-INF/spring-mvc.xml</param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>SpringController</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> </web-app>
8. Testing the Spring MVC Excel Download Example
Type the following URL into browser to access the application we’ve built:http://localhost:8080/SpringMvcExcelViewDemo/
The default page (home.jsp) gets displayed (in FireFox):Click on the “Download Excel Document” link, the browser will ask for opening or saving the file:Select Open with (Microsoft Office Excel), the document gets opened in Excel as follows: Download Eclipse project for this application in the Attachments section below.NOTE: For Excel export in Spring Boot, I recommend you to follow this article: Spring Boot Export Data to Excel Example Related Spring View Tutorials:- Spring MVC URL-based View Resolution with UrlFilenameViewController Example
- Parameterize View Name with ParameterizableViewController in Spring MVC
- Spring MVC with PDF View Example
- Spring MVC XstlView and XsltViewResolver Example
Other Spring Tutorials:
- Understand the core of Spring framework
- Understand Spring MVC
- Understand Spring AOP
- Spring MVC JdbcTemplate Example
- Spring MVC beginner tutorial with Spring Tool Suite IDE
- Spring MVC Form Handling Tutorial
- Spring MVC Form Validation Tutorial
- 14 Tips for Writing Spring MVC Controller
- Spring Web MVC Security Basic Example (XML Configuration)
- Understand Spring Data JPA with Simple Example
Comments
Check this tutorial: codejava.net/.../...
Presently when i call my action page is just getting refresh.