Java tutorial
/* * Copyright 2005-2013 compass.com. All rights reserved. * Support: http://www.compass.com * License: http://www.compass.com/license */ package com.util.poi; import java.net.URLEncoder; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import net.sf.json.JSONObject; import org.apache.commons.beanutils.BeanUtils; import org.apache.commons.beanutils.ConvertUtils; import org.apache.commons.beanutils.Converter; import org.apache.commons.beanutils.PropertyUtils; import org.apache.commons.beanutils.converters.DateConverter; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFComment; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; 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.springframework.util.Assert; import org.springframework.web.servlet.view.document.AbstractExcelView; /** * Excel * * @author CreativeWises Team * @version 3.0 */ public class ExcelView extends AbstractExcelView { /** ?? */ private static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss"; /** ?? */ private String filename; /** ?? */ private String sheetName; /** */ private String[] properties; /** */ private String[] titles; /** */ private Integer[] widths; /** ? */ private Converter[] converters; /** ? */ private Collection<?> data; /** */ private String[] contents; static { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } /** * @param filename * ?? * @param sheetName * ?? * @param properties * * @param titles * * @param widths * * @param converters * ? * @param data * ? * @param contents * */ public ExcelView(String filename, String sheetName, String[] properties, String[] titles, Integer[] widths, Converter[] converters, Collection<?> data, String[] contents) { this.filename = filename; this.sheetName = sheetName; this.properties = properties; this.titles = titles; this.widths = widths; this.converters = converters; this.data = data; this.contents = contents; } /** * @param properties * * @param titles * * @param data * ? * @param contents * */ public ExcelView(String[] properties, String[] titles, Collection<?> data, String[] contents) { this.properties = properties; this.titles = titles; this.data = data; this.contents = contents; } /** * @param properties * * @param titles * * @param data * ? */ public ExcelView(String[] properties, String[] titles, Collection<?> data) { this.properties = properties; this.titles = titles; this.data = data; } /** * @param properties * * @param data * ? */ public ExcelView(String[] properties, Collection<?> data) { this.properties = properties; this.data = data; } /** * ?Excel * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); //comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); /* Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { /*Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } } @SuppressWarnings("unused") private HashMap<String, Object> toHashMap(Object object) { HashMap<String, Object> data = new HashMap<String, Object>(); JSONObject jsonObject = toJSONObject(object); Iterator<?> it = jsonObject.keys(); while (it.hasNext()) { String key = String.valueOf(it.next()); Object value = jsonObject.get(key); data.put(key, value); } return data; } private JSONObject toJSONObject(Object object) { return JSONObject.fromObject(object); } /** * ??? * * @return ?? */ public String getFileName() { return filename; } /** * ?? * * @param filename * ?? */ public void setFileName(String filename) { this.filename = filename; } /** * ??? * * @return ?? */ public String getSheetName() { return sheetName; } /** * ?? * * @param sheetName * ?? */ public void setSheetName(String sheetName) { this.sheetName = sheetName; } /** * ? * * @return */ public String[] getProperties() { return properties; } /** * * * @param properties * */ public void setProperties(String[] properties) { this.properties = properties; } /** * ? * * @return */ public String[] getTitles() { return titles; } /** * * * @param titles * */ public void setTitles(String[] titles) { this.titles = titles; } /** * ? * * @return */ public Integer[] getWidths() { return widths; } /** * * * @param widths * */ public void setWidths(Integer[] widths) { this.widths = widths; } /** * ?? * * @return ? */ public Converter[] getConverters() { return converters; } /** * ? * * @param converters * ? */ public void setConverters(Converter[] converters) { this.converters = converters; } /** * ?? * * @return ? */ public Collection<?> getData() { return data; } /** * ? * * @param data * ? */ public void setData(Collection<?> data) { this.data = data; } /** * ? * * @return */ public String[] getContents() { return contents; } /** * * * @param contents * */ public void setContents(String[] contents) { this.contents = contents; } }