excel通用模板组件

最近在公司实习时，项目中总遇到excel表的导出，就产生写一个组件实现此功能的想法，在此感谢MaximusGet的帮助，基于jdk1.5元数据注解、注释来实现属性名、列名解耦合，模板组件化，或者可以通过LinkedHashMap实现

 

 

package cn.edu.yxy.bean;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


/**
 * @author	杨新彦
 * @ 时间 	2012-2-2 
 * @ 状态 	创建
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {
	public int id() default 0;//excel列id
	public String name();	//excel列名
}

 列名显示顺序比较

package cn.edu.yxy.util;

import java.lang.reflect.Field;
import java.util.Comparator;

import cn.edu.yxy.bean.ExcelAnnotation;


/**
 * @author	杨新彦
 * @ 时间 	2012-2-2 
 * @ 状态 	创建
 */
public class FieldComparator implements Comparator<Object> {

	public int compare(Object arg0, Object arg1) {
        Field fieldOne = (Field)arg0;
        Field fieldTwo = (Field)arg1;
        ExcelAnnotation annoOne = fieldOne.getAnnotation(ExcelAnnotation.class);
        ExcelAnnotation annoTwo = fieldTwo.getAnnotation(ExcelAnnotation.class);
        if(annoOne==null||annoTwo==null){
        	return 0;
        }
        int result = annoOne.id()-annoTwo.id();
        if(result>0){
        	return 1;
        }else if(result<0){
        	return -1;
        }else {
        	return 0;
        }
    }

}

 //通用组件

package cn.edu.yxy.util;

import java.io.File;

/**
 * @author	杨新彦
 * @ 时间 	2012-2-2 
 * @ 状态 	创建
 */
public class DataToExcelModel {
	
	private static int sheetCount = 0;
	private static int max = 0;
	private static List<String> columnArr = null; // 属性名集合
	private static List<Method> methodArr = null; // 待打印属性的get方法
	
	/**
	 * 
	 * @param source	数据源
	 * @param className 实体类类名
	 * @param path		文件存放路径
	 * @return	true：执行成功；false：执行失败
	 * @throws Exception
	 */
	@SuppressWarnings("all")
	public static boolean createExcel(List source,String className,String path,final int pageCount) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		if(null != source){
			max = source.size();    //总记录数
			sheetCount = max / pageCount + (max % pageCount == 0 ? 0 : 1);	//总页数
		}else{
			return createFile(path, wb);
		}
		Class classEntity = null;
		try {
			classEntity = Class.forName(className);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		//排序对应的属性名
		Field[] arrField = sortArributeName(classEntity);
		//生成方法集合并缓存
		createMethod(arrField, classEntity);
		// 生成对应的方法名,要打印的列名
		int nameSize = arrField.length;
		for (int i = 0; i < sheetCount; i++) {
			HSSFSheet sheet = wb.createSheet("sheet" + i);
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = null;
			int begin = pageCount * (i);
			//打印表头
			for (int j = 0; j < nameSize; j++) {
				cell = row.createCell((short) j);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(columnArr.get(j));
			}
			
			int k = 0;
			while (k < pageCount && (begin + k < max)) {
				Object object = source.get(begin + k);
				row = sheet.createRow(k + 1);
				for (int m = 0; m < nameSize; m++) {
					cell = row.createCell((short) m);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					try {
						cell.setCellValue(null == ((methodArr.get(m)).invoke(object)) ? "" : (methodArr.get(m)).invoke(object).toString());
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				k++;
			}
		}
		return createFile(path,wb);
	}
	
	private static  void createMethod(Field[] arrField,Class<?> classEntity){
		String columnName; // 打印的列�
		String beanName; // 属性名
		String methodName; // 方法名
		columnArr = new ArrayList<String>(); // 属性名集合
		methodArr = new ArrayList<Method>();
		for (Field field : arrField) {
			ExcelAnnotation ann = field.getAnnotation(ExcelAnnotation.class);
			columnName = ann.name();
			columnArr.add(columnName);
			beanName = field.getName();
			
			StringBuffer sb = new StringBuffer();
			sb.append("get");
			sb.append(beanName.substring(0, 1).toUpperCase());
			sb.append(beanName.substring(1));
			
			methodName = sb.toString();
			Method method = null;
			try {
				method = classEntity.getMethod(methodName);
			} catch (Exception e) {
				e.printStackTrace();
			}
			methodArr.add(method);
		}
	}
	
	/**
	 * 生成文件，存放在制定的路径
	 * @param path	路径
	 * @param wb	HSSFWorkbook
	 * @return	true 执行成功
	 * @throws IOException
	 */
	private static  boolean createFile(String path,HSSFWorkbook wb){
		boolean flag = false;
		//判断文件是否存在，若存在删除
		File file = new File(path);
		if(file.exists()){
			file.delete();
		}
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(path);
			wb.write(out);
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 排序表头
	 * @param classEntity
	 * @return
	 */
	private static Field[] sortArributeName(Class<?> classEntity){
		// 对需要打印的属性名排序
		Field[] fields = classEntity.getDeclaredFields();
		ArrayList<Field> arrFieldList = new ArrayList<Field>();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelAnnotation.class)) {
				arrFieldList.add(field);
			}
		}
		Field[] arrField = {};
		arrField = arrFieldList.toArray(arrField);
		//排序显示
		Arrays.sort(arrField, new FieldComparator());
		return arrField;
	}
}

 javabean信息

package cn.edu.yxy.bean;


/**
 * @author	杨新彦
 * @ 时间 	2012-2-2 
 * @ 状态 	创建
 */
public class DepartmentBean {
	
	public DepartmentBean() {
		super();
	}
	
	/**
	 * id<br>
	 * 单位id
	 */
	private long id;

	/**
	 * name<br>
	 * 单位名称
	 */
	@ExcelAnnotation(id = 8,name = "单位名称")
	private String name;
	
	/**
	 * code<br>
	 * 单位编码
	 */
	@ExcelAnnotation(id = 2,name = "单位编码")
	private String code;
        // get、set方法

 

参考http://maximusget.iteye.com/blog/734108