java excel上传--poi

悠悠 2022-02-14 16:23 240阅读 0赞

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

方法调用传参例子：  
上传文件中列的字段对应的是对象的属性  
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 "";
    }