java生成详细的excel文件

使用对象.属性来自定义要生成的excel内容

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Array;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.gzbugu.action.ActionBase;
import com.gzbugu.domain.BusiObservePlan;
/**
 * @author ylh
 */
public class ExcelAction extends ActionBase{
	
	private List downExcelAttrsList;
	private String fileName;
	
	/**
	 * 导出Excel公共方法
	 * 使用action模板配置文件：
		<action name="自定义" class="自定义" method="自定义">
	  		<result name="success" type="chain">
	  			<param name="actionName">getDownloadExcel</param>	
	  			<param name="downExcelAttrsList">${downExcelAttrsList}</param>	
	  		</result>
	  	</action>
	 * 必须的参数downExcelAttrsList，必须是有setter,getter方法的属性，其包括参数顺序如下：
	 * @param valueList	  必须，通过hql查询数据库后返回的对象List,支持关联查询,在属性前加上对象名:	{"BusiObservePlan.planType,0:个人计划,1:部门月度计划",...}
	 * @param sheetName  必须，Excel的sheet的名字，
	 * @param beanPropertyNames	必须，对象中需要被输出的值,如果是状态值需要被替换的，则如此填写：	{"propertyName,0:个人计划,1:部门月度计划", ...}
	 * @param titleNames 必须，对应上面属性的名字，用来做Excel的表头
	 * @param fileName	   可选，生成的excel名称，如果没有，则默认是sheetName
	 */
	public InputStream getDownloadExcel(){
		final List list = (List)downExcelAttrsList.get(0);
		final String sheetName = (String)downExcelAttrsList.get(1);
		final String[] beanPropertyNames = (String[])downExcelAttrsList.get(2);
		final String[] titleNames = (String[])downExcelAttrsList.get(3);
		if(downExcelAttrsList.size()>=5) {
			fileName = (String)downExcelAttrsList.get(4);
		}else{
			fileName = sheetName;
		}
		if(!fileName.contains(".xls")){
			fileName = fileName + ".xls";
		}
		InputStream is = null;
		try {
			is = this.createExcelFile(list, sheetName, beanPropertyNames, titleNames);
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		try {
			fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		if(null==is) System.out.print("shit...");
		return is;
	}
	
	/**
	 * 生成Excel表
	 */
	private InputStream createExcelFile(List valueList, String sheetName, String[] beanPropertyNames, String[] titleNames) throws Exception{ 
		Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);
        //单元格默认宽度为20
        sheet.setDefaultColumnWidth(20);
        Cell cell;
        
        //表头
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(18f);
        for (int i = 0; i < titleNames.length; i++) {
            cell = headerRow.createCell(i);
            cell.setCellValue(titleNames[i]);
            cell.setCellStyle(this.getHeaderCellStyle(wb));
        }
        //freeze the first row
        sheet.createFreezePane(0, 1);
        Row row;
        int rownum = 1, listSize = valueList.size(), beanPropertyNamesLength = beanPropertyNames.length;
        for (int i = 0; i < listSize; i++, rownum++) {
            row = sheet.createRow(rownum);
            Object currentObj = valueList.get(i);
            for ( int j=0; j < beanPropertyNamesLength; j++ ) {
            	cell = row.createCell(j);
            	cell.setCellStyle(this.getContentCellStyle(wb));
            	Object value = this.getPropertyValue(currentObj, beanPropertyNames[j]);
                this.getCellSetValue(cell, value);
            }
        }
        //将输出流转化为输入流
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        wb.write(out);
      	return new ByteArrayInputStream(out.toByteArray());
	}	
	
	/**
	 * 设置单元格值
	 * @param cell
	 * @param value
	 */
	private void getCellSetValue(Cell cell, Object value){
        String type = value.getClass().toString().toLowerCase();
        if(type.endsWith("integer")){
        	cell.setCellValue((Integer)value);
        }else if(type.endsWith("double")){
        	cell.setCellValue((Double)value);
        }else if(type.endsWith("timestamp")){
        	cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm").format(value).toString());
        }else{
        	String val = (String)value;
        	Pattern pattern = Pattern.compile("<\\w*\\s*/?>");
        	Matcher matcher = pattern.matcher(val);
        	String v = matcher.replaceAll("");
        	//将结束符号替换为：。
        	pattern = Pattern.compile("</\\w*\\s*/?>");
        	matcher = pattern.matcher(v);
        	v = matcher.replaceAll("。");
        	cell.setCellValue(v);
        }       
	}
	
	/**
	 * 获得bean对象中对应属性的值
	 * @param obj
	 * @param propertyName
	 * @return
	 */
	private Object getPropertyValue(Object obj,String beanPropertyName){
		final String[] property = beanPropertyName.split(","); 
		final String[] beanNameAndPropertyName = property[0].split("\\.");
		final String beanName = beanNameAndPropertyName[0].toLowerCase();
		final String propertyName = beanNameAndPropertyName[1];
		Object value = "";
		Method met = null;
        
		//关联查询
		if(obj.getClass().isArray()){
			int objLength = Array.getLength(obj);
			Object[] currentObjectArray = (Object[])obj;
			for(int j=0;j<objLength;j++){
				Object currentObject = currentObjectArray[j];					
				String currentObjectBeanName = currentObject.getClass().getSimpleName().toLowerCase();
				if(currentObjectBeanName.equals(beanName)){
					try {
						met = currentObject.getClass().getMethod(this.getterMethodName(propertyName));
					} catch (SecurityException e) {
						e.printStackTrace();
					} catch (NoSuchMethodException e) {
						e.printStackTrace();
					}
					try {
						value = met.invoke(currentObject);
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						e.printStackTrace();
					} 
				}
			}	              
		}else{
			//属性的形式为：	对象.属性	
			if(beanNameAndPropertyName.length>1){
				try {
					met = obj.getClass().getMethod(this.getterMethodName(propertyName));
				} catch (SecurityException e1) {
					e1.printStackTrace();
				} catch (NoSuchMethodException e1) {
					e1.printStackTrace();
				}
				try {
					value = met.invoke(obj);
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} 
			}else{
				//属性的形式为：	属性
				try {
					met = obj.getClass().getMethod(this.getterMethodName(property[0]));
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				}  
	            try {
					value = met.invoke(obj);
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}
			}				  
		}
		
		//状态值替换
		if(property.length>1){
			value = this.replaceValue(property, value);
		}
		
		return value;
	}
	
	/**
	 * 根据内容来替换对应的状态值
	 * @param propertyContent
	 * @param value
	 * @return
	 */
	private Object replaceValue(String[] propertyContent, Object value){
		int len = propertyContent.length;
		String name = value.getClass().getSimpleName().toLowerCase();
		for(int i=1;i<len;i++){
			String[] statusValueAndReplaceValue = propertyContent[i].split(":");
			if("integer".equals(name)&&Integer.parseInt(statusValueAndReplaceValue[0])==(Integer)value){
				value = statusValueAndReplaceValue[1];
				break;
			}
		}
		return value;
	}
	
	/**
	 * 根据属性名字获得对应的bean对象的getter名字
	 * @param beanPropertyName	bean对象的属性名字
	 * @return
	 */
	private String getterMethodName(String beanPropertyName){
		String name = "get"+beanPropertyName.substring(0, 1).toUpperCase()+beanPropertyName.substring(1);
		return name;
	}
	
	/**
	 * 表头样式
	 * @param wb
	 * @return
	 */
	private CellStyle getHeaderCellStyle(Workbook wb){
        Font headerFont = wb.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        return style;
	}
	
	/**
	 * 单元格边框样式
	 * @param wb
	 * @return
	 */
	private CellStyle createBorderedStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }
	
	/**
	 * 内容部分单元格样式
	 * @param wb
	 * @return
	 */
	private CellStyle getContentCellStyle(Workbook wb){
		CellStyle style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        return style;
	}
	public List getDownExcelAttrsList() {
		return downExcelAttrsList;
	}
	public void setDownExcelAttrsList(List downExcelAttrsList) {
		this.downExcelAttrsList = downExcelAttrsList;
	}
	public String getFileName() {
		return fileName;
	}
	public void setFileName(String fileName) {
		this.fileName = fileName;
	}
}