最近在开发的系统,涉及到很多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这个函数后面可以优化, 实际上不需要传参, 写的时候没注意, 后面懒得调整了