Jeesite-导入导出源码跟踪分析（导入）

在使用Excel导入的时候，我们的思想基本上和导出是一样的，但是要先读取Excel中的数据，然后遍历cell，并且判断类型，最终导入我们的数据

导入工具类

` public class ImportExcel {

private static Logger log = LoggerFactory.getLogger(ImportExcel.class);/** * 工作薄对象 */private Workbook wb;/** * 工作表对象 */private Sheet sheet;/** * 标题行号 */private int headerNum;/** * 构造函数 * @param path 导入文件，读取第一个工作表 * @param headerNum 标题行号，数据行号=标题行号+1 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(String fileName, int headerNum)         throws InvalidFormatException, IOException {    this(new File(fileName), headerNum);}/** * 构造函数 * @param path 导入文件对象，读取第一个工作表 * @param headerNum 标题行号，数据行号=标题行号+1 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(File file, int headerNum)         throws InvalidFormatException, IOException {    this(file, headerNum, 0);}/** * 构造函数 * @param path 导入文件 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(String fileName, int headerNum, int sheetIndex)         throws InvalidFormatException, IOException {    this(new File(fileName), headerNum, sheetIndex);}/** * 构造函数 * @param path 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(File file, int headerNum, int sheetIndex)         throws InvalidFormatException, IOException {    this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);}/** * 构造函数 * @param file 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)         throws InvalidFormatException, IOException {    this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);}/** * 构造函数 * @param path 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)         throws InvalidFormatException, IOException {    if (StringUtils.isBlank(fileName)){        throw new RuntimeException("导入文档为空!");    }else if(fileName.toLowerCase().endsWith("xls")){            this.wb = new HSSFWorkbook(is);        }else if(fileName.toLowerCase().endsWith("xlsx")){          this.wb = new XSSFWorkbook(is);    }else{          throw new RuntimeException("文档格式不正确!");    }      if (this.wb.getNumberOfSheets()<sheetIndex){        throw new RuntimeException("文档中没有工作表!");    }    this.sheet = this.wb.getSheetAt(sheetIndex);    this.headerNum = headerNum;    log.debug("Initialize success.");}/** * 获取行对象 * @param rownum * @return */public Row getRow(int rownum){    return this.sheet.getRow(rownum);}/** * 获取数据行号 * @return */public int getDataRowNum(){    return headerNum+1;}/** * 获取最后一个数据行号 * @return */public int getLastDataRowNum(){    return this.sheet.getLastRowNum()+headerNum;}/** * 获取最后一个列号 * @return */public int getLastCellNum(){    return this.getRow(headerNum).getLastCellNum();}/** * 获取单元格值 * @param row 获取的行 * @param column 获取单元格列号 * @return 单元格值 */public Object getCellValue(Row row, int column){    Object val = "";    try{        Cell cell = row.getCell(column);        if (cell != null){            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){                val = cell.getNumericCellValue();            }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){                val = cell.getStringCellValue();            }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){                val = cell.getCellFormula();            }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){                val = cell.getBooleanCellValue();            }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){                val = cell.getErrorCellValue();            }        }    }catch (Exception e) {        return val;    }    return val;}/** * 获取导入数据列表 * @param cls 导入对象类型 * @param groups 导入分组 */public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException,RuntimeException{    List<Object[]> annotationList = Lists.newArrayList();    // Get annotation field     Field[] fs = cls.getDeclaredFields();    for (Field f : fs){        ExcelField ef = f.getAnnotation(ExcelField.class);        if (ef != null && (ef.type()==0 || ef.type()==2)){            if (groups!=null && groups.length>0){                boolean inGroup = false;                for (int g : groups){                    if (inGroup){                        break;                    }                    for (int efg : ef.groups()){                        if (g == efg){                            inGroup = true;                            annotationList.add(new Object[]{ef, f});                            break;                        }                    }                }            }else{                annotationList.add(new Object[]{ef, f});            }        }    }    // Get annotation method    Method[] ms = cls.getDeclaredMethods();    for (Method m : ms){        ExcelField ef = m.getAnnotation(ExcelField.class);        if (ef != null && (ef.type()==0 || ef.type()==2)){            if (groups!=null && groups.length>0){                boolean inGroup = false;                for (int g : groups){                    if (inGroup){                        break;                    }                    for (int efg : ef.groups()){                        if (g == efg){                            inGroup = true;                            annotationList.add(new Object[]{ef, m});                            break;                        }                    }                }            }else{                annotationList.add(new Object[]{ef, m});            }        }    }    // Field sorting    Collections.sort(annotationList, new Comparator<Object[]>() {        public int compare(Object[] o1, Object[] o2) {            return new Integer(((ExcelField)o1[0]).sort()).compareTo(                    new Integer(((ExcelField)o2[0]).sort()));        };    });    Row checkrow = this.getRow(1);    int checkcolumn = 0;    for (Object obj[] : annotationList) {        Object val = this.getCellValue(checkrow, checkcolumn++);        if(val==null){            val = "";        }        ExcelField ef = (ExcelField) obj[0];        if(!val.toString().equals(ef.title())){            if(StringUtils.isEmpty(val.toString())){                log.info("excel异常:"+val.toString()+":"+ef.title());                throw new RuntimeException(ef.title()+"列不存在,请重新下载模板!");            }            if(StringUtils.isNotEmpty(val.toString())&&StringUtils.isNotEmpty(ef.title())){                log.info("excel异常:"+val.toString()+":"+ef.title());                throw new RuntimeException("excel表格列对应不一致,请重新下载模板!");            }        }    }    //log.debug("Import column count:"+annotationList.size());    // Get excel data    List<E> dataList = Lists.newArrayList();    for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {        E e = (E)cls.newInstance();        int column = 0;        Row row = this.getRow(i);        StringBuilder sb = new StringBuilder();        for (Object[] os : annotationList){            Object val = this.getCellValue(row, column++);            if (val != null){                ExcelField ef = (ExcelField)os[0];                // If is dict type, get dict value                if (StringUtils.isNotBlank(ef.dictType())){                    String oldVal = val.toString();                    val = DictUtils.getDictValue(val.toString().trim(), ef.dictType(), val.toString());                    if(StringUtils.isEmpty(val.toString())){                        val = com.cyou.seal.modules.network.utils.DictUtils.getDictValue(val.toString().trim(), ef.dictType(), val.toString());                    }                    if(e.getClass().getName().equals("com.cyou.seal.modules.application.VO.AssetQueryImportVO")){                        if("[empty]".equals(val)){                            val = "[empty]";                        }                    }else if(!oldVal.equals("") && val.toString().equals("")) {                        throw new RuntimeException(ef.title()+",没有找到【"+oldVal+"】的数据字典值");                    }                    //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);                }                // Get param type and type cast                Class<?> valType = Class.class;                if (os[1] instanceof Field){                    valType = ((Field)os[1]).getType();                }else if (os[1] instanceof Method){                    Method method = ((Method)os[1]);                    if ("get".equals(method.getName().substring(0, 3))){                        valType = method.getReturnType();                    }else if("set".equals(method.getName().substring(0, 3))){                        valType = ((Method)os[1]).getParameterTypes()[0];                    }                }                //log.debug("Import value type: ["+i+","+column+"] " + valType);                try {                    if (valType == String.class){                        String s = String.valueOf(val.toString());                        if(StringUtils.endsWith(s, ".0")){                            val = StringUtils.substringBefore(s, ".0");                        }else{                            val = String.valueOf(val.toString());                        }                    }else if (valType == Integer.class){                        val = Double.valueOf(val.toString()).intValue();                    }else if (valType == Long.class){                        val = Double.valueOf(val.toString()).longValue();                    }else if (valType == Double.class){                        val = Double.valueOf(val.toString());                    }else if (valType == Float.class){                        val = Float.valueOf(val.toString());                    }else if (valType == Date.class){                        val = DateUtil.getJavaDate((Double)val);                    }else{                        if (ef.fieldType() != Class.class){                            val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());                        }else{                            val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),                                     "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());                        }                    }                } catch (Exception ex) {                    log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());                    val = null;                }                // set entity value                if (os[1] instanceof Field){                    Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);                }else if (os[1] instanceof Method){                    String mthodName = ((Method)os[1]).getName();                    if ("get".equals(mthodName.substring(0, 3))){                        mthodName = "set"+StringUtils.substringAfter(mthodName, "get");                    }                    Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});                }            }            sb.append(val+", ");        }        dataList.add(e);        log.debug("Read success: ["+i+"] "+sb.toString());    }    return dataList;}

} `

如何使用这个工具类

1.初始化:ImportExcel

ImportExcel ei = new ImportExcel(file, 1, 0);

我们跟踪初始化代码：获取文档的文件名称，判断是否为空，是否是.xls或者.xlsx,判断文档中是否有工作表，获取到对应的工作表，和标题行

2.调用getDataList获取Excel中映射得到的实体List 在这个方法里面，跟导出类型，就不多做解释。获取带有ExcelField注解的字段，在获取带有ExcelField的方法。将遍历得到的数据放到List。排序。我们最终得到的这个List我们还需要进行遍历，判断取出的cell的字符和我们实体类中的字符是否相符。遍历得到的list,得到Excel中的cell中的值Object val = this.getCellValue(row, column++); 判断值的类型，从而转换成对应的实体类的字段的类型。 3.遍历导入即可