java excel上传--poi

工作中很多批量上传需求不同,每个需求都要写一次批量上传代码,太烦。。决定写一个通用工具,此代码复制即用。需要改进的地方请评论区留言

方法调用传参例子:
上传文件中列的字段对应的是对象的属性

String[] fieldsName = new String[]{"carModelName","salePrice"};  

此参数是上传文件中列必传字段对应对象的属性

String[] fieldsIsNull = new String[]{"carModelName"};  

注:数组属性顺序必须和文件中顺序一样,解析文件时赋值不会赋错字段

 public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2007_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";

/
**

 * 通过反射处理以获得的数据
 * @param
 */
public T reflectDeal(T className, Cell cell, String fieldName){
    Class aClass = null;
    try {
        aClass = className.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        for (Field field:declaredFields) {
            field.setAccessible(true);
            String names = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1, fieldName.length());
            if(field.getName().equals(fieldName) && field.getType().getName().equals("java.math.BigDecimal")){
                aClass.getMethod("set"+names,BigDecimal.class).invoke(className,new BigDecimal(""+cell));
            }else if(field.getName().equals(fieldName) && field.getType().getName().equals("java.lang.String")){
                aClass.getMethod("set"+names,String.class).invoke(className,cell.getStringCellValue().toString());
            }else if(field.getName().equals(fieldName) && field.getType().getName().equals("java.lang.Integer")){
                aClass.getMethod("set"+names,Integer.class).invoke(className,cell);
            }else if(field.getName().equals(fieldName) && field.getType().getName().equals("java.lang.Double")){
                aClass.getMethod("set"+names,Double.class).invoke(className,cell);
            }
        }
    }  catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (NoSuchMethodException e) {
        e.printStackTrace();
    } catch (InvocationTargetException e) {
        e.printStackTrace();
    }
    return className;
}

 /**
 * 本段代码解析xls文件
 * @param
 */
public List<T> analysisXls(InputStream in,Class<?> clazz,String[] fieldsName,String[] fieldsIsNull)  {
    int totalrows = 0;
    int totalCell = 0;

    List<T> list = new ArrayList();
    logger.info("====================  开始解析xls文件  =========================");
    //流读取文件
    //创建文件
    HSSFWorkbook wb = null;
    try {
        wb = new HSSFWorkbook(in);
        //读取页数
        for(int num = 0 ; num < wb.getNumberOfSheets() ; num ++){
            Sheet xs = wb.getSheetAt(num);
            if(xs == null){
                continue;
            }
            totalrows = xs.getLastRowNum()+1;
            for (int rnum = 1 ; rnum < totalrows ; rnum ++){
                boolean flag = false;
                T o = null;
                try {
                    o = (T)clazz.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                Row row = xs.getRow(rnum);
                if(row != null){
                    totalCell = row.getLastCellNum();
                    int nullCellNumb = 0;
                    for (int cnum = 0 ; cnum < totalCell ; cnum ++){
                        Cell cell = row.getCell(cnum);
                        if(null == cell){
                            nullCellNumb++;
                            if(nullCellNumb>=10){
                                break;
                            }
                            continue;
                        }
                        if( cell != null && !"".equals(cell.toString().trim())){
                            this.reflectDeal(o, cell, fieldsName[cnum]);
                        }else {
                            for (int x=0;x<fieldsIsNull.length;x++){
                                if(fieldsIsNull[x].equals(fieldsName[cnum])){
                                    flag = true;
                                    break;
                                }
                            }
                        }
                    }
                    if (flag){
                        continue;
                    }
                    list.add(o);
                }else {
                    return list;
                }
            }
        }
    } catch (IOException e) {
        e.printStackTrace();
    }finally {
        try {
            in.close();
            if(null != wb){
                wb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    return list;
}

/**

 * 本段代码解析xlsx文件
 * @param
 * @param clazz
 * @param fieldsName
 * @return
 */
public List<T> analysisXlsx(InputStream in,Class<?> clazz,String[] fieldsName,String[] fieldsIsNull){
    int totalrows = 0;
    int totalCell = 0;
    List<T> list = new ArrayList();
    logger.info("====================  开始解析xlsx文件  =========================");
    //流读取文件
    //创建文件
    XSSFWorkbook wb = null;
    try {
        wb = new XSSFWorkbook(OPCPackage.open(in));
        //读取页数
        for(int num = 0 ; num < wb.getNumberOfSheets() ; num ++){
            Sheet xs = wb.getSheetAt(num);
            if(xs == null){
                continue;
            }
            totalrows = xs.getLastRowNum()+1;
            for (int rnum = 1 ; rnum < totalrows ; rnum ++){
                boolean flag = false;
                T o = (T)clazz.newInstance();
                Row row = xs.getRow(rnum);
                if(row != null){
                    totalCell = row.getLastCellNum();
                    int nullCellNumb = 0;
                    for (int cnum = 0 ; cnum < totalCell ; cnum ++){
                        Cell cell = row.getCell(cnum);
                        if(null == cell){
                            nullCellNumb++;
                            if(nullCellNumb>=10){
                                break;
                            }
                            continue;
                        }

                        if( cell != null  && !"".equals(cell.toString().trim())){
                            nullCellNumb = 0;
                            this.reflectDeal(o, cell, fieldsName[cnum]);
                        }else {
                            for (int x=0;x<fieldsIsNull.length;x++){
                                if(fieldsIsNull[x].equals(fieldsName[cnum])){
                                    flag = true;
                                    break;
                                }
                            }
                        }
                    }
                    if (flag){
                        continue;
                    }
                    list.add(o);
                }else {
                    return list;
                }
            }
        }
    } catch (IOException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        logger.info("======= XSSF创建文件失败 ====");
        e.printStackTrace();
    }finally {
        try {
            in.close();
            if(null != wb){
                wb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    return list;
} 

/**

 *此段代码区分e'xcel版本,分别调用哪个方法
 * @param
 * @param clazz
 * @param fieldsName
 * @return
 * @throws IOException
 */
public List<T> readxlsAndXlsx(InputStream in,Class<?> clazz,String[] fieldsName,String fileName,String[] fieldsIsNull) {
    if(null != in  ){
        String postfix = ExcelImport.getpostfix(fileName);
        if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
            return this.analysisXls(in,clazz,fieldsName,fieldsIsNull);
        }else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
           return this.analysisXlsx(in,clazz,fieldsName,fieldsIsNull);
        }else {
            return null;
        }
    }
    return null;
} 

/**

 * 获取文件后缀名
 * @param path
 * @return
 */
public static String getpostfix(String path){
    if(path == null || EMPTY.equals(path.trim())){
        return "";
    }
    if(path.contains(POINT)){
       return path.substring(path.lastIndexOf(POINT)+1,path.length());
    }
    return "";
}
上一篇:linux文件目录结构


下一篇:liunx安装nexus私服