利用Java反射机制和POI实现导出Excel功能

1.Apache POI简介
Apache poi工具包是一个著名的操作Microsoft文档的Java工具库，里面提供大量的对word,excel,ppt操作的方法。最近由于项目需要一个将数据库数据导出到excel的功能，所以学习了这个工具包的用法。下面简要介绍一下操作excel的几个重要的类和方法。

1.HSSF、XSSF和SXSSF
HSSF和XSSF包都是poi中操作excel的包，他们的区别在于hssf包操作的是Excel ‘97(-2007)格式的文档，而xssf操作的是 Excel 2007 以上版本格式的文档。换言之，hssf生成的excel文件后缀名是.xls,xssf生成的文件后缀是.xlsx。如果要同时支持这两种格式的文档，poi也提供了SXSSF包来实现其功能，

具体代码如下：

public class CreateExcelByReflect {
    private static Sheet sheet;    //表格对象   
    private static SXSSFWorkbook wb;    //工作簿
    private static LinkedHashMap<String,String> header;   //表头   


    //初始化
    public static void init(String sheetName) {
        wb = new SXSSFWorkbook();
        sheet = wb.createSheet(sheetName);
        header = new LinkedHashMap<String,String>();
        header.put("recordTime", "时间");
        header.put("businessLine", "业务线");
        header.put("resourceName", "名称");

    }   

    /**
     * 创建各列表头
     * @param headString
     */
    public static void createHeadRow(){        
        Row head = sheet.createRow(0);   //创建表格第一行对象，为表头行
        Iterator<Entry<String,String>> headTitle = header.entrySet().iterator();  //循环输出表头
        for(int i=0;headTitle.hasNext();i++){
            Cell cell = head.createCell(i);
            cell.setCellValue(headTitle.next().getValue());
        }
    }

    /**
     * 创建数据行
     * @param data
     * @param cols
     */
    public static void createRows(List<?> data){
        int rowCount = data.size();     //根据数据集设置行数
        for(int i=0;i<rowCount;i++){
            Row row = sheet.createRow(i+1);  //创建行，表头是第0行
           //转换数据，将每一个DO映射为属性名与FieldsEntity的Map
            Map<String,FieldsEntity> map = DataConvertUtil.convertObjectToMap(data.get(i));            
            Iterator<Entry<String,String>> head = header.entrySet().iterator();
            //创建每行的单元格并填充值
            for(int col = 0;col < header.size() && head.hasNext();col++){
                Cell cell = row.createCell(col);
                //设置表头的迭代器
                Map.Entry<String,String> enty = (Map.Entry<String,String>)head.next();
                String name = enty.getKey();
                cell.setCellValue(map.get(name).getValue().toString());        //填充属性值      
            }
        }        
    }


    private static void flashoutFile(OutputStream out, SXSSFWorkbook book) {
        try {
            book.write(out);
            out.close();
//            book.dispose();
//            book.
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            if(out!=null){
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

  public static void main(String[] args) {
      init("测试表格");        
      Date date = new Date();
      DataObject data1 = new DataObject();
      data1.setRecordTime(date);
      data1.setBusinessLine("业务1");
      data1.setResourceName("资源1");

      DataObject data2 = new DataObject();
      data2.setRecordTime(date);
      data2.setBusinessLine("业务2");
      data2.setResourceName("资源2");

      List<DataObject> data = new ArrayList<DataObject>();
      data.add(data1);
      data.add(data2);

      createHeadRow();   //创建表头
      createRows(data);           //创建数据行
      try{
          FileOutputStream out = new FileOutputStream("D:\\测试2.xlsx");
          flashoutFile(out,wb);
      }catch(Exception e){
          e.printStackTrace();
      }       
 }
}

DataConvertUtil类代码如下：

public class DataConvertUtil {
    /**
     * 将对象的属性名称与值映射为MAP
     * @param o 对象
     * @return  Map<key,value> key为属性名称，value为名称、类型和值组成的对象 
     */
    public static Map<String,FieldsEntity> convertObjectToMap(Object o){
        Class oClass = o.getClass();
        Field[] fields = oClass.getDeclaredFields();   //获取类中的所有声明的属性
        Map<String,FieldsEntity> map = new HashMap<String, FieldsEntity>();
        try{
            for(int i=0;i<fields.length;i++){
//                不对序列化ID进行映射
                if(fields[i].getName().equals("serialVersionUID")){   
                    continue;
                }
                Object valueObject = getFieldValue(o,fields[i].getName());
                map.put(fields[i].getName(), new FieldsEntity(fields[i].getName(), valueObject, fields[i].getType()));
            }
            return map; 
        }catch(Exception e){
           e.printStackTrace();           
        }
        return map;     
    }   

    /**
     * 通过对象的getter方法获取属性值
     * @param o 对象
     * @param name 属性名称
     * @return 相应属性的值 
     */
public static Object getFieldValue(Object o,String name) throws SecurityException, NoSuchMethodException,IllegalArgumentException, IllegalAccessException, InvocationTargetException {
        Class owner = o.getClass();
        Method mothed = owner.getMethod(createGetter(name));
        Object object = mothed.invoke(o);
        return object;
    }

/**
 * 通过属性名称拼凑getter方法
 * @param fieldName
 * @return
 */
public static String createGetter(String fieldName){
    if(fieldName == null || fieldName.length() == 0 ){
        return null;
    }        
    StringBuffer sb = new StringBuffer("get");
    sb.append(fieldName.substring(0, 1).toUpperCase()).append(fieldName.substring(1));
    return sb.toString();
}
}

FieldsEntity代码如下：

public class FieldsEntity {
    private String attributeName;     //属性变量名称    
    private Object value;                //属性变量值    
    private Class classType;          //属性类型  

    public String getAttributeName() {
        return attributeName;
    }    
    public void setAttributeName(String attributeName) {
        this.attributeName = attributeName;
    }   
    public Object getValue() {
        return value;
    }    
    public void setValue(Object value) {
        this.value = value;
    }     
    public Class getClassType() {
        return classType;
    } 
    public void setClassType(Class classType) {
        this.classType = classType;
    }    
    public FieldsEntity(String fieldName, Object o, Class classType){
        this.attributeName = fieldName;
        this.value = o;
        this.classType = classType;
    }
}

DataObject代码如下：

public class DataObject {
    private Date recordTime;
    private String businessLine;
    private String resourceName;
    public Date getRecordTime() {
        return recordTime;
    }
    public void setRecordTime(Date recordTime) {
        this.recordTime = recordTime;
    }
    public String getBusinessLine() {
        return businessLine;
    }
    public void setBusinessLine(String businessLine) {
        this.businessLine = businessLine;
    }
    public String getResourceName() {
        return resourceName;
    }
    public void setResourceName(String resourceName) {
        this.resourceName = resourceName;
    }   
}