excel导入，用反射匹配字段名

最近在开发的系统，涉及到很多xls导入， 开发太忙，没时间做比较通用的模块，我反正闲着， 就试着帮他们写了个比较通用的xls导入模块，自我感觉良好， 就写个文章记录下

（demo代码，轻喷）

首先缩小需求：

1. 按行导入，不支持合并行，和并列，合并的单元格处理太累

2. 第一行是表头, 导入xls需要通过表头的字符串来匹配列

这样相对就简单了, 首先是建立表头和列索引的关系, 通过类反射建立索引关系

类反射定义

```java

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface FieldDescInject {

        String value() default "";

}

类反射的使用

public class Order extends XlsObject {
    @FieldDescInject("订单号")
    String thirdOrderId;
    @FieldDescInject("收件人")
    String receiveName;
    @FieldDescInject("省")
    String province;
    @FieldDescInject("市")
    String city;
    @FieldDescInject("区")
    String zone;
    @FieldDescInject("地址")
    String detailAddress;
    @FieldDescInject("电话")
    String mobile;

}

把类成员变量和xls对应列关联起来

XlsObject定义

package com.digitcore.readxls;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by pan on 2016/12/1.
 */

public abstract class XlsObject {



    //for example
//    @FieldDescInject("价格")
//    public int price;

    public void setFieldValue(String desc, String value){
        String fieldStr = fieldDescMap.get(desc);
        if(fieldStr == null){
            setFieldValueNotFound(desc, value);
            return;
        }

        try {
            Field field = this.getClass().getDeclaredField(fieldStr);
            field.setAccessible(true);
            Class clzz = field.getType();
            if(clzz == String.class){
                field.set(this, value);
            }
            else if(clzz == int.class || clzz == Integer.class){
                field.setInt(this, Integer.parseInt(value));
            }
            else if(clzz == float.class || clzz == Float.class){
                field.setFloat(this, Float.parseFloat(value));
            }
            else {
                throw new IllegalArgumentException("联系程序员补充类型支持!");
            }

        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    public void setFieldValueNotFound(String field, String value){

    }

    public XlsObject() {
        inject();
    }

    private HashMap<String, String> fieldDescMap = new HashMap<String, String>();
    public List<String> getDescList(){
        return new ArrayList<String>(fieldDescMap.keySet());
    }

    public void inject(){
        Field[] fields = this.getClass().getDeclaredFields();
        if (fields != null && fields.length > 0) {
            for (Field field : fields) {

//                Class<?> fieldType = field.getType();

                FieldDescInject ed = field.getAnnotation(FieldDescInject.class);
                if (ed != null) {
//                        field.setAccessible(true);
                    fieldDescMap.put(ed.value(), field.getName());

                }
            }
        }
    }


}

再增加一个导入的辅助工具类

package com.digitcore.readxls;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;

import static android.R.attr.order;

/**
 * Created by pan on 2016/11/29.
 * 导入xls表, 通过变量描述对应表字段, 或者手动选择(外部界面配合)列和对象变量对应
 */

public class ImportXls {
    private String mFile;
    private int mStartRow = 0;//head开始行, 默认从0开始
    private Class<? extends XlsObject> mClazz;
    private HashMap<String, Integer> mIndexMap;

    public ImportXls(String file, int startRow, Class<? extends XlsObject> clazz){
        mFile = file;
        mStartRow = startRow;
        mClazz = clazz;
    }

    /**
     * 获取xls表头的head列表
     * @return
     */
    public List<String> getXlsHead(){
        List<String> heads = new ArrayList<String>();

        InputStream is = null;
        Workbook book = null;
        try {
            is = new FileInputStream(mFile);
            book = Workbook.getWorkbook(is);

//            int num = book.getNumberOfSheets();
//            txt.setText("the num of sheets is " + num+ "\n");
//            // 获得第一个工作表对象
            Sheet sheet = book.getSheet(0);
            int Rows = sheet.getRows();
            int Cols = sheet.getColumns();
            if(Rows >= mStartRow && Cols > 0){
                for(int i = 0; i < Cols; i++){
                    heads.add(sheet.getCell(i, mStartRow).getContents());
                }
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        finally {
            if(book != null){
                book.close();
            }
            if(is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return heads;
    }

    public void setIndexMap(HashMap<String, Integer> indexMap){
        mIndexMap = indexMap;
    }

    /**
     * 自动生成xls文件中字段索引表, xlsObject对象中注解的描述要和xls头对应
     * @param importHead xlsObject中注解的描述
     */
    public void generateIndexMap(List<String> importHead){
        List<String> xlsHead = getXlsHead();
        mIndexMap = new HashMap<String, Integer>();

        for(String head : importHead){
            int index = xlsHead.lastIndexOf(head);
            if(index >= 0) {
                mIndexMap.put(head, Integer.valueOf(index));
            }

        }
    }
    public List<XlsObject> importXls(){
        ArrayList<XlsObject> list = new ArrayList<XlsObject>();

        InputStream is = null;
        Workbook book = null;
        try {
            is = new FileInputStream(mFile);
            book = Workbook.getWorkbook(is);

            Sheet sheet = book.getSheet(0);
            int Rows = sheet.getRows();
//            int Cols = sheet.getColumns();

            for (int i = 1; i < Rows; ++i) {
                XlsObject o = mClazz.newInstance();

                Iterator iter = mIndexMap.entrySet().iterator();
                while (iter.hasNext()) {
                    Map.Entry entry = (Map.Entry) iter.next();
                    String key = (String) entry.getKey();
                    Integer val = (Integer) entry.getValue();
                    o.setFieldValue(key, sheet.getCell(val, i).getContents());
                }

                list.add(o);

            }
        } catch (Exception e) {
            System.out.println(e);
        }
        finally {
            if(book != null){
                book.close();
            }
            if(is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }


}

使用示例:

ImportXls importXls = new ImportXls("/buhebing.xls", 0, Order.class);

Order order = new Order();
List<String> imprtHead = order.getDescList();

importXls.generateIndexMap(imprtHead);
importXls.importXls();

优化:

generateIndexMap这个函数后面可以优化, 实际上不需要传参, 写的时候没注意, 后面懒得调整了