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

淡淡的烟草味﹌ 2022-07-15 11:09 193阅读 0赞

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;
        }   
    }