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++; } } } } }
No comments:
Post a Comment