Rendering Excel as ERT from BHUB with Spring POI

Why Excel is not suitable as expected response type (ERT) for all possible BHUB methods is obviously a consequence of the fact that websites render hierarchical data and not just tabular data.

When Excel is needed to present some tabular data like for example when too many columns are to be presented then I provide a specific controller to manage the rendering. One could argue that with so many DHTML data grids components it should not be a big deal to still use HTML instead of Excel and I agree that is the case especially when a front end developer is on board. Still even the best grid component out there will not allow for real post processing, multiple sheets, formulas: Excel sometimes is simply "the tool".

Spring has a View that wraps the POI API. The only extra dependency to include is shown below:

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.6</version>
</dependency>

Here is a Spring Controller using a custom ExcelView. Note that the custom View will acccept simple Map List to contain cells, rows and sheets:

package com.nestorurquiza.spring.web;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
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.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.nestorurquiza.spring.web.ExcelView;


@Controller
public class ExcelController extends RootController {

    @RequestMapping("/excel/sample")
    public ModelAndView welcomeHandler(HttpServletRequest request,
            HttpServletResponse response) {
        //Initialize the context (mandatory)
        ControllerContext ctx = new ControllerContext(request, response);
        init(ctx);
        
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        Map<String, List<Map<String, Object>>> excelWorkbookViewMap = new HashMap<String, List<Map<String, Object>>>();
        try {
            List<Map<String, Object>> excelRows = new ArrayList<Map<String, Object>>();
            Map<String, Object> excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Gregory");
            excelRow.put("Age", 33);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("2/1/1980"));
            excelRow.put("Graduated", false);
            excelRows.add(excelRow);
            excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Mark");
            excelRow.put("Age", 41);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("20/12/1975"));
            excelRow.put("Graduated", true);
            excelRows.add(excelRow);
            excelWorkbookViewMap.put("First Sheet", excelRows);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        return new ModelAndView(new ExcelView(ctx, "sample.xls"), excelWorkbookViewMap);
    }
}

Finally the custom ExcelView:

package com.nestorurquiza.web;

import java.util.Date;
import java.util.Map;
import java.util.List;
import java.util.Set;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.nestorurquiza.utils.Utils;
import com.nestorurquiza.web.ControllerContext;


public class ExcelView extends AbstractExcelView{
    public ExcelView(ControllerContext ctx, String fileName) {
        super();
        this.fileName = fileName;
        this.ctx = ctx;
    }

    private String fileName;
    private ControllerContext ctx; 
 
    /**
     * 
     * model: Map<String, List<Map, Object>>
     * This view returns back an Excel stream
     * The sheets are determined by the amount of parent model map keys
     * The content of the sheets are determined by the value of the model map key (a List of Maps)
     * Each list list entry (the list map) corresponds to a row
     * The headers for each row are the list map keys
     * The content of the cell are the list map values
     * 
     * @author nestor
     *
     */
    @Override
    protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        if(Utils.isEmpty(model)) {
            HSSFSheet sheet = workbook.createSheet(ctx.getString("error.empty.model"));
        } else {
            Set<Map.Entry<String, List<Map<String, Object>>>> set = model.entrySet();
            for (Map.Entry<String, List<Map<String, Object>>> entry : set) {
              String sheetName = entry.getKey();
              HSSFSheet sheet = workbook.createSheet(sheetName);
              List<Map<String, Object>> sheetContent = entry.getValue();
              HSSFRow header = sheet.createRow(0);
              HSSFRow excelRow = header;
              int rowCount = 0;
              for( Map<String, Object> row : sheetContent ) {
                  
                  int i = 0;
                  if( rowCount == 0 ){
                      
                      for( String cellName : row.keySet() ) {
                          Object cellValue = row.get(cellName);
                          header.createCell(i++).setCellValue(cellName);
                      }
                      rowCount++;
                      i = 0;
                  }
                  excelRow = sheet.createRow(rowCount);
                  for( String cellName : row.keySet() ) {
                      Object cellValue = row.get(cellName);
                      HSSFCell cell = excelRow.createCell(i++);
                      
                      //CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN
                      if (cellValue instanceof Integer) {
                          cell.setCellValue((Integer) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if(cellValue instanceof Float) {
                          cell.setCellValue((Float) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if(cellValue instanceof Double) {
                          cell.setCellValue((Double) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if (cellValue instanceof Boolean) {
                          cell.setCellValue((Boolean) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                      } else if (cellValue instanceof Date) {
                          cell.setCellValue((Date) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                          HSSFCellStyle style = workbook.createCellStyle();
                          HSSFDataFormat dataFormat = workbook.createDataFormat();
                          style.setDataFormat(dataFormat.getFormat("dd/MM/yyyy"));
                          cell.setCellStyle(style);
                      } else {
                          cell.setCellValue(cellValue.toString());
                          cell.setCellType(HSSFCell.CELL_TYPE_STRING);   
                      }
                  }
                  rowCount++;
              }
            }
        }
    }
}