Excel Generation With Spring And POI

To generate an Excel document for the Spring MVC View, simply extend AbstractExcelView.

public class WidgetListExcelView extends AbstractExcelView
{
  protected void buildExcelDocument(Map model,
                                    HSSFWorkbook workbook,
                                    HttpServletRequest request,
                                    HttpServletResponse response)
  {
    ...
  }
}

AbstractExcelView implements the View’s render(Map model, HttpServletRequest request, HttpServletResponse response) for you, makes a new POI HSSFWorkbook, and makes you implement protected void buildExcelDocument(Map model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response). From there, you can simply work on the workbook as you would in POI. Spring will handle the messy work of setting the response content type, streaming the workbook to the browser, etc. For more information on working with Excel documents via POI see Busy Developers’ Guide to HSSF Features at the official POI website.

Here is a full example to generate an Excel document of a list of Widgets:

/**
 * View to generate an Excel document of a list of Widgets 
 * @author rlambert
 */
public class WidgetListExcelView extends AbstractExcelView
{
  public static final String WIDGET_LIST_KEY = "widgetList";
  protected static final short WIDGET_NAME_COLUMN = 0;
  protected static final short WIDGET_SIZE_COLUMN = 1;

  protected void buildExcelDocument(Map model,
                                    HSSFWorkbook workbook,
                                    HttpServletRequest request, 
                                    HttpServletResponse response)
  {
    //CREATE THE SHEET
    HSSFSheet sheet = workbook.createSheet("Widget List");
    sheet.setDefaultColumnWidth((short) 12);

    //GETCELL: getCell(SHEET, ROW, COLUMN);
    short currentRow = 0;

    //WRITE ROW FOR HEADER
    HSSFCell header0 = getCell(sheet, 
                               currentRow, 
                               WIDGET_NAME_COLUMN);
    setText(header0, "NAME");

    HSSFCell header1 = getCell(sheet, 
                               currentRow, 
                               WIDGET_SIZE_COLUMN);
    setText(header1, "SIZE");

    List widgetList = (List) model.get(WIDGET_LIST_KEY);
    Iterator widgetListIterator = widgetList.iterator();

    while (widgetListIterator.hasNext())
    {
      currentRow++;
      Widget widget = (Widget) widgetListIterator.next();
      HSSFRow row = sheet.createRow(currentRow);
      row.createCell(WIDGET_NAME_COLUMN)
         .setCellValue(widget.getName());
      row.createCell(WIDGET_SIZE_COLUMN)
         .setCellValue(widget.getSize());
    }
  }
}

Here’s a simple Controller that uses the above WidgetListExcelView as the View:

package com.zabada.springbook.excel;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

import com.zabada.springbook.dao.WidgetDAO;

/**
 * Controller to show a Widget List in an Excel document
 *
 * @author rlambert
 */
public class WidgetListExcelController implements Controller
{
  private WidgetDAO dao;
  
  public void setWidgetDAO(WidgetDAO dao)
  {
    this.dao = dao;
  }

  /* (non-Javadoc)
   * @see org.springframework.web.servlet.mvc.Controller
   * #handleRequest(javax.servlet.http.HttpServletRequest,
   *                javax.servlet.http.HttpServletResponse)
   */
  public ModelAndView handleRequest(HttpServletRequest arg0,
                                    HttpServletResponse arg1)
                                   throws Exception
  {
    Map model = new HashMap();
    List list = dao.getWidgets();
    model.put(WidgetListExcelView.WIDGET_LIST_KEY, list);
    return new ModelAndView(new WidgetListExcelView(), model);
  }
}

Further Reading

Next: Generating PDFs Using Spring and iText