FRAMEWORK/SPRING

Spring MVC with Excel View Example (Apache POI and JExcelApi)

적외선 2014. 11. 19. 17:29

SpringMvcExcelViewDemo.zip

SpringMvcExcelViewDemo2.zip

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.jar file).

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 Download

To work with JExcelApi, you need to add its only jar file: jxl.jar - to your project’s classpath. And Spring provides an abstract class calledAbstractJExcelView 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 calledSpringMvcExcelViewDemo. We will end up with the following project structure:

The jar files used are:

- Spring framework 3.2.3-RELEASE:

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

1. - poi-3.9-20121203.jar

Recommended Book: Getting started with Spring Framework

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

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.

Recommended Book: Spring in Action

4. Coding Excel View Class

To generate an Excel document from the model data passed by the controller, create a subclass of theAbstractExcelView 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.

Recommended Book: Spring Integration in Action

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:

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

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

<?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>

Recommended Book: 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:

<?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/

Download Eclipse project for this application in the Attachments section below.

Recommended Book: Pro Spring 3

Related Course: The Java Spring Tutorial

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

저작자표시 비영리