exceltomodelimpl.java

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

                // 对excel每一列的值进行取值.
                for (int j = 0; j < sheet.getColumns(); j++) {

                    // System.out.println("i = " + i + " j =" + j);
                    // 取得Excel表头
                    String excelTitleName = sheet.getCell(j, 0).getContents().trim();
                    // 取得Excel值
                    String value = sheet.getCell(j, i).getContents().trim();
                    //          
                    // 取得配置属性
                    // RuturnPropertyParam propertyBean = (RuturnPropertyParam)
                    // excelConfig.getPropertyMap().get(excelTitleName);

                    /*
                     * 按配制列号读取Excel文件，只修改了下面的几行代码，注释上面一行代码。
                     */
                    String keycolumn = String.valueOf(j + 1);
                    RuturnPropertyParam propertyBean = (RuturnPropertyParam) excelConfig.getColumnMap().get(keycolumn);
                    System.out.println("column = " + keycolumn);
                    // System.out.println("i = " + i + " j =" + j + " value ="+
                    // value + " title = " + excelTitleName);

                    // 列错误标志
                    boolean columnFlag = false;
                    if (propertyBean != null) {
                        // System.out.println("propertyName = " +
                        // propertyBean.getName());
                        // 判断是否非空，非空标志 ,取值为空时设置为false
                        boolean nullFlag = true;

                        if (propertyBean.getIsNull().equals("N")) {
                            if (value.length() < 1) {
                                // 非空标志
                                nullFlag = false;
                                flag = true;
                                strMessage = strMessage + ";列：" + excelTitleName + "-为空！";
                            }
                        }
                        // 如果取值不空
                        if (nullFlag) {
                            // 长度判断
                            int intLength = 0;
                            if (StringUtils.isNotBlank(propertyBean.getMaxLength()) && (NumberUtils.isNumber(propertyBean.getMaxLength()))) {
                                intLength = Integer.parseInt(propertyBean.getMaxLength());
                            }
                            // 如果设置了最大长度，从右边截取指定的字符长度
                            if (intLength > 0 && value.length() > intLength) {
                                value = value.substring(0, intLength);
                            }
                            // 判断数据类型 支持 String ,Integer,Date
                            if (propertyBean.getDataType().indexOf("Date") > -1) {
                                String[] strTemp = propertyBean.getDataType().split(" ");
                                String pattern = "";

                                if (strTemp.length > 1) {
                                    pattern = strTemp[1];
                                }
                                // boolean isDate = this.isValidDate(value,
                                // pattern);
                                Cell strCell = sheet.getCell(j, i);
                                if (strCell.getType() == CellType.DATE) {
                                    DateCell dateCell = (DateCell) strCell;
                                    Date date = dateCell.getDate();
                                    value = DateUtils.getFormatDate(date, pattern);
                                    System.out.println("DateFormat = " + dateCell.getDateFormat());
                                    System.out.println("date = " + DateUtils.getFormatDate(date, ""));
                                } else {
                                    value = null;
                                }
                                if (value == null) {
                                    flag = true;
                                    columnFlag = true;
                                    strMessage = strMessage + "列：" + excelTitleName + "-日期格式不正确！";
                                    System.out.println("列：" + excelTitleName + "-日期格式不正确！");
                                }
                                // boolean isDate = DateUtils.isValidDate(value,
                                // pattern);
                                // if (!isDate) {
                                // flag = true;
                                // columnFlag = true;
                                // strMessage = strMessage + "列："
                                // + excelTitleName + "-日期格式不正确！";
                                // System.out.println("列：" + excelTitleName
                                // + "-日期格式不正确！");
                                // }
                                // value = DateUtils.getFormatDate(value,
                                // pattern);
                            }
                            if (propertyBean.getDataType().equals("Integer")) {
                                if (!NumberUtils.isNumber(value)) {
                                    flag = true;
                                    columnFlag = true;
                                    strMessage = strMessage + "列：" + excelTitleName + "-不是数字！";
                                    System.out.println("列：" + excelTitleName + "-不是数字！");
                                }
                            }
                        }

                        // //做出判断,是否需要 Text/Value 值转换.
                        if (propertyBean.getCodeTableName().length() > 1) {

                            String valueKey = propertyBean.getCodeTableName().trim() + value;
                            // System.out.println("valueKey = " + valueKey);
                            Object obj1 = this.valueMap.get(valueKey);
                            if (obj1 == null) {
                                value = "";
                            } else {
                                value = obj1.toString();
                            }
                        }
                        // 设置默认值 value 先是取值，再判断是否有码表对换值
                        if (value == null || value.length() < 1) {
                            value = propertyBean.getDefaultValue();
                        }
                        if (!columnFlag) {
                            // 调整支持Map
                            // bw.setPropertyValue(propertyBean.getName(),
                            // 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");
                    this.setPropertyValue(obj, propertyFlag, "1");

                    System.out.println(propertyMessage + " = " + strMessage);
                    // 调整支持Map
                    // bw.setPropertyValue(propertyMessage, strMessage);
                    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");
                    this.setPropertyValue(obj, propertyFlag, "0");
                    // 支持Map时，保持Map中每个属性都存在。
                    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();
        }
    }

    public boolean isUseColumn() {
        return isUseColumn;
    }

    public void setUseColumn(boolean isUseColumn) {
        this.isUseColumn = isUseColumn;
    }

    // 取得当前行有值的列数
    private int getColumnByValue(Cell[] rowCell) {
        int column = rowCell.length;
        int intResult = column;
        for (int i = 0; i < column; i++) {
            String str = rowCell[i].getContents();
            if (StringUtils.isBlank(str)) {
                intResult--;
            }
        }
        return intResult;
    }

    // 验证当前有值的列大于等于配制文件所需要的列表的值是否是列头。
    private boolean validateColumnTitle(Cell[] rowCell, Map columnMap) {
        int column = rowCell.length;
        List list = this.getColumnTitle(rowCell);
        boolean bool = true;
        for (Iterator it = columnMap.keySet().iterator(); it.hasNext();) {
            String str = (String) it.next();
            if (!list.contains(str)) {
                bool = false;
            }
        }
        return bool;
    }

    private List getColumnTitle(Cell[] rowCell) {
        List list = new ArrayList();
        int column = rowCell.length;
        for (int i = 0; i < column; i++) {
            String str = rowCell[i].getContents().toString().trim();
            if (StringUtils.isNotBlank(str)) {
                list.add(str);
            }
        }
        return list;
    }

    // 设置配制文件中，指定的列头在实际Excel文件中出现的列数
    private void setValidateCellColumn(Cell[] rowCell, Map columnMap) {
        this.cellColumn = new ArrayList();
        int column = rowCell.length;
        for (int i = 0; i < column; i++) {
            String str = rowCell[i].getContents().toString();
            if (StringUtils.isNotBlank(str)) {
                if (columnMap.get(str) != null) {
                    this.cellColumn.add(String.valueOf(i));
                }
            }
        }
    }

    public int getStartTitleRow() {
        // TODO Auto-generated method stub
        return startTitleRow;
    }

    public int getIntSheet() {
        return intSheet;
    }

    public void setSheet(int intSheet) {
        this.strSheet = null;
        this.intSheet = intSheet;
    }

    public String getStrSheet() {        
        return strSheet;
    }

    public void setSheet(String strSheet) {
        this.intSheet = 0;
        this.strSheet = strSheet;
    }

	private void setExcelTitleList(List excelTitleList) {
		this.excelTitleList = excelTitleList;
	}
    
    
    
}