最近在公司实习时,项目中总遇到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方法