Excel通用导入实现

在web开发过程中，经常会有对Excel操作的功能，导入/导出都是家常便饭。本文主要记录一下自己平时在工作中总结的一些方法。希望大家多多指点交流。

通用导入功能设计

有导入功能的业务场景一般都是需要把业务方提供的业务数据通过程序批量写入到系统中。在做业务建模的时候，一般会设计一张表来存放导入数据。Excel的每一列对应数据库表中的一列。Excel表头的校验就不在此赘述了，针对数据表中的每一列都会有自己的校验规则，比如，长度限制、非空判断、特定格式等。因此可以为每一列定义一个对象来存放这些校验属性，如下：

public class ExcelCellProperty{
    private String propertyName;                //单元格对应对象属性名
    private boolean nullAble;                    //是否可为空值 true:可为空 false:必填
    private long maxLength;                        //最大长度
    private int readType;                        //读取方式：CELL_TYPE_NUMERIC:0;CELL_TYPE_STRING:1 
    private String formatString;                //格式化字符串
}

propertyName对应的是业务对象中的属性，即Excel中的某一列，nullAble、maxLength、readType、formatString为该列的校验规则属性。在解析之前需要将导入文件的列校验对象初始化，比如

学号	姓名
0001	jack
	tom

对应业务对象Student(sno,name);

Map<String,ExcelCellProperty> map = new HashMap<String,ExcelCellProperty>();
ExcelCellProperty property = new ExcelCellProperty();
property.setPropertyName("sno");
property.setNullAble(false);
property.setReadType(HSSFCell.CELL_TYPE_STRING);
map.put("学号", property);
...

最后获取到的map为一个以表头单元格字符串为key，该列数据校验对象为value的map对象。

还需要定义一个校验结果对象来保存每一行的业务数据以及校验的结果和校验不通过的原因来方便记录日志，做后续追踪。对象如下：

public class ExcelReturnObject{
    private int rowId;                    //Excel中的行号
    private Object obj;                    //该行存放的业务对象
    private boolean checkStatus;        //校验状态
    private String remark;                //校验备注
}

为了使导入的excel格式更加灵活，即与列的排列顺序无关，如上述模板中学号和姓名位置可相互调换。可以采用java反射机制将对应的列放入业务对象对应属性中。

通用解析方法定义如下：

public static List<ExcelReturnObject> createRowList(Workbook wb, Map<String,ExcelCellProperty> propertyMap,String classPath);

先解析表头行，将表头放入List<String> tbHeadList中存放。

循环解析Excel行，在循环内通过循环表头list从propertyMap获取单元格的校验规则以及读取方式。

for(int i=1;i<size;i++){
     for(int j=0;j<tbHeadList.size();j++){
          ExcelCellProperty = propertyMap.get(tbHeadList.get(j));
          ...
     }
}

最后返回参数为List<ExcelReturnObject> returnObjectList，可以根据需要对有效的数据进行筛选

List<Student> list = new ArrayList<Student>();
for(ExcelReturnObject obj:returnObjectList){
     if(obj.checkStatus){
          Student student = (Student)obj.getObj();
          ...
          list.add(student);
     }
}

到此Excel中的数据就转变为了List<Student> list。将list写入数据即可~

----------------------------------------------------------------------------------------------

附上解析代码一份，仅供参考

public static List<ExcelReturnObject> createRowList(Workbook wb, Map<String,ExcelCellProperty> propertyMap,String classPath) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SecurityException, NoSuchFieldException{
    List<ExcelReturnObject> returnObjectList = new ArrayList<ExcelReturnObject>();
    Sheet sheet = wb.getSheetAt(0);//获取sheet
    int rows = sheet.getLastRowNum();//获取总行数
    Row row = sheet.getRow(0);//获取表头行
    List<String> tbHeaderList = new ArrayList<String>();
    int index = 0;
    while(row.getCell(index)!=null && StringUtils.isNotBlank(row.getCell(index).toString().trim())){//循环获取表头
        tbHeaderList.add(row.getCell(index).toString().trim());
        index++;
    }
    StringBuffer buffer = new StringBuffer();
    for(int i=1; i<=rows; i++){//解析Excel读取行，验证
        buffer.setLength(0);
        row = sheet.getRow(i);
        Class clazz = Class.forName(classPath);
        Object obj = clazz.newInstance();
        ExcelReturnObject returnObj = new ExcelReturnObject();
        returnObj.setRowId(i+1);
        if(row==null){//空行直接跳过
            continue;
        }else{
            for(int j=0; j<index; j++){//解析行
                Cell cell = row.getCell(j);
                ExcelCellProperty cellProperty = propertyMap.get(tbHeaderList.get(j));
                String propertyName = cellProperty.getPropertyName();
                boolean nullAble = cellProperty.isNullAble();
                long maxLength = cellProperty.getMaxLength();
                int readType = cellProperty.getReadType();
                String formatString = cellProperty.getFormatString();
                Field field = clazz.getDeclaredField(propertyName);//获取当前单元格对应的业务对象属性
                field.setAccessible(true);
                if(!nullAble && (cell==null || StringUtils.isBlank(cell.toString().trim()))){//该单元格非空且当前为空
                    returnObj.setCheckStatus(false);
                    buffer.append("第"+(i+1)+"行"+tbHeaderList.get(j)+"为空;");
                    continue;
                }
                if(cell!=null && StringUtils.isNotBlank(cell.toString().trim())){//单元格不为空时
                    if(readType==HSSFCell.CELL_TYPE_NUMERIC){//读取方式为金额
                        //验证是否为金额类型
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        Pattern pattern = Pattern.compile("(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){1,2})?");
                        Matcher match = pattern.matcher(cell.toString().trim());  
                        if(match.matches()){
                            BigDecimal amount = new BigDecimal(cell.getNumericCellValue());
                            field.set(obj, amount);
                        }else{
                            returnObj.setCheckStatus(false);
                            buffer.append("第"+(i+1)+"行，"+tbHeaderList.get(j)+"金额类型错误;");
                            continue;
                        }
                    }else if(readType==HSSFCell.CELL_TYPE_STRING){//读取方式为字符串
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String content = cell.getStringCellValue();
                        if(maxLength>0 && content.length()>maxLength){//该位设置了最大值且长度超限
                            returnObj.setCheckStatus(false);
                            buffer.append("第"+(i+1)+"行，"+tbHeaderList.get(j)+"长度超过限制"+maxLength+";");
                            continue;
                        }
                        if(StringUtils.isNotBlank(formatString)){//格式化串不为空时，需要格式化（针对时间格式）
                            SimpleDateFormat sf = new SimpleDateFormat(formatString);
                            Date date = null;
                            try{
                                date = sf.parse(content);
                                field.set(obj, date);
                            }catch(ParseException e){
                                returnObj.setCheckStatus(false);
                                buffer.append("第"+(i+1)+"行，"+tbHeaderList.get(j)+"时间类型格式错误;");
                                continue;
                            }
                        }else{
                            field.set(obj, content);
                        }
                    }
                }
            }
            if(StringUtils.isBlank(buffer.toString())){//备注为空，表示该行无异常
                returnObj.setCheckStatus(true);
                returnObj.setRemark("SUCCESS");
            }else{
                returnObj.setRemark(buffer.toString());
            }
            returnObj.setObj(obj);
            returnObjectList.add(returnObj);
        }
    }
    return returnObjectList;
}