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 }
<%@ 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.
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:
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
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”.
<?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:
<?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>
<?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>
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: