Spring Web MVC offers seamless integration with different view technologies, including Excel document view. When configured properly, a Spring’s view resolver can generate an Excel document from model data and send it to the client for downloading or opening by a spreadsheet program like Microsoft Excel. For working with Excel view, Spring supports two popular libraries such as Apache POI and JExcelApi (Both are free and open source). This tutorial is going to help you in understanding how to configure Spring MVC to work with these libraries in order to deliver dynamic content in form of Excel document to the users, by developing a sample Spring MVC application that allows the users to download an Excel document generated on the fly:

Spring MVC Excel View Demo with Apache POI

Clicking on the download link will prompt the users for downloading/opening the document which looks like the following screenshot in Microsoft Excel program:

Open downloaded Excel document in MS Excel

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):

Apache POI Download

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.jarfile).

To generate an Excel document using Apache POI within Spring, we need to create a view class that extends from theAbstractExcelView class and override its method buildExcelDocument(). Then using Apache POI’s Excel API to generate the excel document.

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:

Spring MVC Excel View Demo Eclipse 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-1.1.1.jar
    • Apache POI:
      • poi-3.9-20121203.jar
 

This book: Getting started with Spring Framework  helps you master all major concepts like Spring core modules, dependency injection, Spring AOP, annotation-driven development, and more.

 

1. Creating Model Class

We will generate an Excel document that contains a list of Java books, so create the following model class (Book.java):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<%@ 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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.  

This book: Spring in Action  helps you learn the latest features, tools, and practices including Spring MVC, REST, Security, Web Flow, and more.


4. Coding Excel View Class

To generate an Excel document from the model data passed by the controller, create a subclass of the AbstractExcelViewclass as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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

 

This book: Spring Integration in Action help you learn more about enterprise integration and messaging using the Spring Integration framework.


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:

1
excelView.(class)=net.codejava.spring.ExcelBuilder
That 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:

1
2
3
4
5
6
7
8
9
<?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 usingviews.properties file, put the following content:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?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.
The order property does matter here, in which the first resolver has higher priority than the second one.

 

In case the views.xml is used, configure Spring as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?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>
 

Discover how to write efficient batch applications with Spring Batch in Action


7. Configuring Spring MVC in web.xml

The final step is to configure Spring MVC in the web deployment descriptor file as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?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 application

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):

Test Spring MVC Excel View Demo in Firefox

Click on the “Download Excel Document” link, the browser will ask for opening or saving the file:

Opening downloadExcel dialog

Select Open with (Microsoft Office Excel), the document gets opened in Excel as follows:

Open downloaded Excel document in MS Excel

 

Download Eclipse project for this application in the Attachments section below





출처 : http://www.codejava.net/frameworks/spring/spring-mvc-with-excel-view-example-apache-poi-and-jexcelapi


저작자표시

'Server Enterprise > Spring' 카테고리의 다른 글

[build] spring pom.xml com.microsoft.sqlserver package  (0) 2016.10.25
[STS] Spring Boot Restful 서버 구축 (Hello World)  (0) 2016.09.07
[web.xml] 세션시간, 에러페이지, http-method 설정  (0) 2015.03.10
<스프링과 메이븐을 활용한 실전 프레임워크 설계와 구축> 소스코드  (0) 2014.12.31
[Multi] 다중 삭제  (0) 2014.11.03

+ Recent posts

티스토리툴바