Excel and Other Document-Based Views | Professional Java Development with the Spring Framework

We've seen how to use FreeMarker, Velocity, and JavaServer Pages to render HTML-based views. However, web applications oftentimes not only require web pages, but also document-based views, such as PDF documents or reports in Excel format. While designing Spring, it was clear that it should not be necessary to make any changes to a controller to switch between an HTML-based view and, for example, a view that generates PDF files. The solution should be the cleanest possible implementation of the MVC architectural pattern.

Generating an Excel File from Our List of Shows

One of the controllers in our application allows the user to select a show from a list. The controller creates a ModelAndView and adds a list of shows to the model. The following example shows how to create an Excel file that lists all shows, instead of an HTML page. Excel is widely used in many industries, including the financial industry.

The technology used here is Jakarta POI (http://jakarta.apache.org/poi), one of the Java APIs used to generate Excel files. Another API is jExcelApi, available from www.jexcel.org. Both integrate well with Spring, through the org.springframework.web.servlet.view.document.AbstractPoiExcelView and org.springframework.web.servlet.view.document.AbstractJExcelView, respectively. Both view classes are based on the Template pattern and require you only to subclass the Spring class and implement the code that generates the actual Excel content. The view class allows for the loading of templates, and if no template is specified (using the url property), a new and empty workbook is created for you.

public class ListShowsView extends AbstractExcelView {       public void buildExcelDocument(     Map model, HSSFWorkbook wb,      HttpServletRequest request, HttpServletResponse response)   throws Exception {         HSSFSheet sheet = wb.createSheet("shows");     HSSFCell titleCell = getCell(0, 0);     setText(titleCell, "All shows");         List shows = (List)model.get("shows");     Iterator it = shows.iterator();          for (int i = 0; it.hasNext(); i++) {       Show show = (Show)it.next();             HSSFCell nameCell = getCell(sheet, i +2, 0) {       setText(nameCell, show.getName());       setText(nameCell, show.getGenre().getName());     }   } }

As you can see, it's not so difficult to generate an Excel document while reusing the same controller we're using to present the user with an HTML page listing all shows. The only thing left to do is the wiring up of the view and the view resolver. We can, for example, include the view in our views.properties file mentioned alongside a ResourceBundleViewResolver.

### boxoffice-servlet.xml      <bean >     <property name="basename"><value>views</value></property> </bean>     ### /WEB-INF/classes/views.properties  listShows.class=org.springframework.prospring.web.view.excel.ListShowsView

Returning listShows as the view name from your controller is all you need to do to trigger the ListShowsView.

Important

Be careful when deciding on an API to use for your Excel views. Jakarta POI has the most active user community, but when you need to include images in your Excel files, jExcelApi is your only option at the time of this writing. Of course, Spring MVC decouples your controllers and models from the particular view generation library you choose in any case.

Using a Template as a Basis for Your Excel File

Both the AbstractJExcelView and the AbstractExcelView (where the latter uses Jakarta POI and the former jExcelApi) support the loading of templates. Using templates, you can let someone else design an Excel template including formulas and images while you're filling in the live data. By setting the url property on the view class, Spring will load the template using Spring's Resource classes. While including a template in a web application context, Spring will search in your /WEB-INF directory. The template mechanism supports localization, based on the locale, resolved by the LocaleResolver mentioned in your context. Last but not least, Spring will itself append .xls to the URL you're providing. This means that using the following example, users with an English locale will get the template from /WEB-INF/example_en.xls whereas Dutch users will be presented with an Excel file filled from the template located in /WEB-INF/example_nl.xls.

listShows.class=org.springframework.prospring.web.view.excel.ListShowsView listShows.url=example

When using templates, nothing changes in the way you fill your Excel file: The template is loaded and will be passed along with the model and the servlet request and response in the buildExcelDocument() method.

Using a HandlerInterceptor to Distinguish Between HTML and Excel

A neat technique to allow the user to specify what format he likes to have his results displayed in is to use a HandlerInterceptor to change the view name based on an input argument provided by the user. Bear with us while we explain this through the use of an example.

First, we're going to provide the user with a search page where she can enter a search query, based on which a list of shows will be retrieved. We'll also provide the user with two options to choose from, output in Excel or using HTML.

Search for:    <input type="text" name="query"><br> Output in:    <input type="radio name="format" value="xls"/>Excel or    <input type="radio" name="format" value="html"/>Html<br>     <input type="submit"/>

Our controller will search for shows matching the search query somehow and return the ModelAndView.

protected ModelAndView handleRequestInternal(   HttpServletRequest req, HttpServletResponse response {       String query = RequestUtils.getRequiredStringParameter("query");   List shows; // retrieve shows matching the query       ModelAndView mav = new ModelAndView("listshows", "shows", shows);   return mav; }

Important

org.springframework.web.bind.RequestUtils is a convenience class that includes methods to retrieve parameters of different primitive types. The getRequired* methods all throw a ServletRequestBindingException if the parameter does not exist in the request.

As you can see, the view name we're returning is listshows. The next thing we're going to do is attach a HandlerInterceptor to the UrlHandlerMapping and let it postHandle() the request:

public class OutputFormatModificationInterceptor   extends HandlerInterceptorAdapter {       private String parameter;   private String defaultSuffix = "";   private Properties suffices;       // setters and getters omitted        public void postHandler(     HttpServletRequest request, HttpServletResponse response,      Object handler, ModelAndView mav) throws Exception {         String format = request.getParameter("parameter");     String suffix = suffices.getProperty(format);     // modify view name if exists      if (suffix != null) {       mav.setViewName(mav.getViewName() + suffix);     } else {       mav.setViewName(mav.getViewName() + defaultSuffix);   } }

When wiring it up as follows, all requests to the controller with the format parameter set to xls will output using the Excel view we've created. The other requests will result in an HTML page.

<bean >   <property name="interceptors">     <list>       <ref bean="formatInterceptor"/>     </list>   </property> </bean>     <bean      >   <property name="suffices">     <props>       <entry key="xls">-xls</entry>       <entry key="html">-html</entry>     </props>   </property>       <property name="parameter"><value>format</value></property>   <property name="defaultSuffix"><value>-html</value></property> </bean>     <bean name="/listShows.action"    >   ...   </bean>

Of course we still need to define our views. We'll use a ResourceBundleViewResolver and a views.properties (in /WEB-INF/classes) file again:

listShows-html.class=org.springframework.web.servlet.view.JstlView  listShows-html.url=/WEB-INF/jsp/listShows.jsp      listShows-xls.class=org.springframework.prospring.web.view.document.ListShowsView  listShows-xls.url=showListingTemplate

Important

Note that ResourceBundleViewResolver supports localization, so you can even distinguish between views in different languages.