exceltomodelimpl.java

J2EE项目开发Excel数据导入导出操作组件源代码（附带说明文档）

                            // value);
                            this.setPropertyValue(obj, propertyBean.getName(), value);
                            // System.out.println(propertyBean.getName()+ " = "+
                            // bw.getPropertyValue(propertyBean.getName()));
                        }
                    }

                }
                /*
                 * 设置错误标志，及错误消息
                 */
                if (flag) {
                    String propertyFlag = (String) excelConfig.getFlagMap().get("name");
                    String propertyMessage = (String) excelConfig.getMessageMap().get("name");
                    // 调整支持Map
                    // bw.setPropertyValue(propertyFlag, "1");
                    if (StringUtils.isNotBlank(propertyFlag)) {
                        this.setPropertyValue(obj, propertyFlag, "1");
                    }

                    // 调整支持Map
                    // bw.setPropertyValue(propertyMessage, strMessage);
                    if (StringUtils.isNotBlank(propertyMessage)) {
                        this.setPropertyValue(obj, propertyMessage, strMessage);
                    }

                } else {
                    String propertyFlag = (String) excelConfig.getFlagMap().get("name");
                    String propertyMessage = (String) excelConfig.getMessageMap().get("name");
                    // 调整支持Map
                    // bw.setPropertyValue(propertyFlag, "0");

                    if (StringUtils.isNotBlank(propertyFlag)) {
                        this.setPropertyValue(obj, propertyFlag, "0");
                    }

                    // 支持Map时，保持Map中每个属性都存在。
                    if (StringUtils.isNotBlank(propertyMessage)) {
                        this.setPropertyValue(obj, propertyMessage, "");
                    }

                }
                /*
                 * 对在配制文件中指定需要设置为固定值的属性，设置属性中的固定值，根据传入的Map中取出，无则取其默认值
                 */
                for (Iterator it = this.fixityList.iterator(); it.hasNext();) {
                    RuturnPropertyParam propertyBean = (RuturnPropertyParam) it.next();
                    Object value = this.valueMap.get(propertyBean.getName());
                    if (value == null || value.toString().length() < 1) {
                        value = propertyBean.getDefaultValue();
                    }
                    // 调整支持Map
                    // bw.setPropertyValue(propertyBean.getName(), value);
                    this.setPropertyValue(obj, propertyBean.getName(), value);
                }
                this.modelList.add(obj);
                if (flag) {
                    this.errorList.add(obj);
                } else {
                    this.successModelList.add(obj);
                }

            }
            book.close();
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private void setPropertyValue(Object obj, String property, Object value) {
        if (obj instanceof Map) {
            ((Map) obj).put(property, value);
        } else {
            BeanWrapper bw = new BeanWrapperImpl(obj);
            bw.setPropertyValue(property, value);
        }
    }

 
    private Object getModelClass(String className) {
        Object obj = null;
        try {
            obj = Class.forName(className).newInstance();
            System.out.println("init Class = " + className);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return obj;
    }

    /*
     * 验证Excel表头格式是否正确 (non-Javadoc)
     * 
     * @see com.javayjm.excel.file.ExcelToModel#validateExcelFormat()
     */
    public boolean validateExcelFormat() {
        // TODO Auto-generated method stub
        boolean boolResult = true;
        List titleList = this.getExcelTitle();
        
        /*
         * 对excel列做处理，把出现相同的列加编号 如果出现两个　姓名 做成 姓名，姓名_1
         */
        List tempList = new ArrayList();
        for(int i=0;i<titleList.size();i++){
        	String temp = ValidateColumn.columnValidate(tempList,titleList.get(i).toString());
        	tempList.add(temp);
        }
        
        this.messageList = new ArrayList();
        Map pmap = this.excelConfig.getPropertyMap();
        for (Iterator it = pmap.keySet().iterator(); it.hasNext();) {
            String title = (String) it.next();
            // System.out.println("tilte = " + title);
            if (!tempList.contains(title)) {
                boolResult = false;
                this.messageList.add("Excel中不存在 " + title + " 列！");
                System.out.println("Excel中不存在 " + title + " 列!");
            }
        }
        /*
         * 保存验证通过的，已经被处理的excel标题
         */
        setExcelTitleList(tempList);
        
        return boolResult;
    }

    private List getExcelTitle() {
        this.setFixity();
        List titleList = new ArrayList();
        try {
            Workbook book;

            book = Workbook.getWorkbook(this.excelFile);
            //Sheet sheet = book.getSheet(0);
//          edit 2008-1-24            
            Sheet sheet;
            if(StringUtils.isNotBlank(this.getStrSheet())){
                sheet = book.getSheet(this.getStrSheet());
            }else{
                sheet = book.getSheet(this.getIntSheet());
            }    
            // 验证列头开始的行数，并设置列头对应的有效列数

            for (int i = 0; i < sheet.getRows(); i++) {
                // 验证一行有值的列数
                int intcount = this.getColumnByValue(sheet.getRow(i));
                // 属性列 - 固定值列数 = 必须配制的列数
                int intNotNullColumn = this.excelConfig.getPropertyMap().size() - fixityList.size();
                if (intNotNullColumn <= intcount) {
                    // boolean bool = this.validateColumnTitle(sheet.getRow(i),
                    // this.excelConfig.getPropertyMap());
                    // //如果 bool = true 这一行是列头,保存有列头行数行数，及有效列
                    // if(bool){
                    // startTitleRow = i;
                    // this.setValidateCellColumn(sheet.getRow(i),this.excelConfig.getPropertyMap());
                    // break;
                    // }
                    // 验证时，只要出现的第一行的有值列数大于配制文件中配制的列数，就认为是列头
                    startTitleRow = i;
                    break;
                }
            }

            for (int j = 0; j < sheet.getColumns(); j++) {
                String title = sheet.getCell(j, startTitleRow).getContents().trim();
                titleList.add(title);
            }
            book.close();
        } catch (BiffException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return titleList;
    }

    public Map getConfigTitle() {
        // TODO Auto-generated method stub
        Map titleMap = new HashMap();
        Map pmap = this.excelConfig.getPropertyMap();
        for (Iterator it = pmap.keySet().iterator(); it.hasNext();) {
            String configTitle = (String) it.next();
            RuturnPropertyParam propertyBean = (RuturnPropertyParam) excelConfig.getPropertyMap().get(configTitle);
            titleMap.put(propertyBean.getName(), configTitle);
        }
        return titleMap;
    }

    public List getModelList() {
        return this.getExcelToList(0);
    }

    public List getErrorModelList() {
        // TODO Auto-generated method stub
        return this.getExcelToList(2);
    }

    public List getSuccessModelList() {
        // TODO Auto-generated method stub
        return this.getExcelToList(1);
    }

    private List getExcelToList(int intFlag) {
        if (!isRead) {
            this.isRead = true;
            getExcelToModelList();
        }
        if (intFlag == 0) {
            return this.modelList;
        } else if (intFlag == 1) {
            return this.successModelList;
        } else if (intFlag == 2) {
            return this.errorList;
        } else {
            return new ArrayList();
        }
    }

    private void getExcelToModelList() {
        if (isUseColumn) {
            getExcelToModelListByColumn();
        } else {
            getExcelToModelListByTitle();
        }
    }

    private void getExcelToModelListByColumn() {
        this.setFixity();
        // List modelList = new ArrayList();
        try {
            Workbook book;

            book = Workbook.getWorkbook(this.excelFile);
            //Sheet sheet = book.getSheet(0);
//          edit 2008-1-24
            Sheet sheet;
            if(StringUtils.isNotBlank(this.getStrSheet())){
                sheet = book.getSheet(this.getStrSheet());
            }else{
                sheet = book.getSheet(this.getIntSheet());
            }    
            
            System.out.println("JXL version : " + book.getVersion());
            // editBook = Workbook.createWorkbook(file, book);

            // System.out.println("rows = " + sheet.getRows() + " cols ="+
            // sheet.getColumns());
            for (int i = 1; i < sheet.getRows(); i++) {
                Object obj = this.getModelClass(excelConfig.getClassName());

                // 调整支持Map,写入属性值做一个私有方法来处理。
                // BeanWrapper bw = new BeanWrapperImpl(obj);

                // 错误标志
                boolean flag = false;
                // 错误消息
                String strMessage = "";