exceltomodelimpl.java

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

package com.javayjm.excel.file.impl;

/*＊
 * 公　司：
 * 文件名：MessageBean.java
 * 作　者：YJM
 * 版本号：1.1
 * 时　间：2007-11-3上午10:32:03
 */
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;

import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.javayjm.excel.config.RuturnConfig;
import com.javayjm.excel.config.RuturnPropertyParam;
import com.javayjm.excel.file.ExcelToModel;
import com.javayjm.excel.util.DateUtils;
import com.javayjm.excel.util.ValidateColumn;

public class ExcelToModelImpl implements ExcelToModel {

    private File excelFile = null;

    private RuturnConfig excelConfig = null;

    private Map valueMap = null;

    private List fixityList = null;

    // 完整的list
    private List modelList = new ArrayList();

    // 验证成功的List
    private List successModelList = new ArrayList();

    // 验证失败的List
    private List errorList = new ArrayList();

    // 表头验证List
    private List messageList = null;

    // 是否按列读取，当出现有列标题重复时，可以按些方法来操作
    /*
     * isUserColumn = true时，按配制文件中配制的列号读取
     */
    private boolean isUseColumn = false;

    // 读取操作是否执行，执行完成后，会填充modelList,successModelList,errorList数据。

    private boolean isRead = false;

    // 支持带有标题的Excel文件时，保存规则数据开始的行数，及有效列数（就是配制文件中配制的excel列头在实际excel文件出的第几列数）
    private int startTitleRow = 0;

    private List cellColumn = null;
    
    //支持传入sheet 值，指取第几个sheet内的值。　修改时间　2008－1－24
    // intSheet　sheet　在 Excel中第几个
    // strSheet　sheet 在Excel中的名称
    private int intSheet=0;
    
    private String strSheet=null;
    
    //为excel文件中，出现重复列配制
    private List excelTitleList = new ArrayList();
    

    public ExcelToModelImpl(File excelFile, RuturnConfig excelConfig, Map valueMap) {
        this.excelConfig = excelConfig;
        this.excelFile = excelFile;
        this.valueMap = valueMap;
        // this.getExcelToModelList();
    }

    /*
     * 对于配置文件中,指定取固定值的属性列,先取出放到List中.无则为空
     */
    private void setFixity() {
        List fixityList = new ArrayList();
        Map pmap = this.excelConfig.getPropertyMap();
        for (Iterator it = pmap.values().iterator(); it.hasNext();) {
            RuturnPropertyParam propertyBean = (RuturnPropertyParam) it.next();
            if (propertyBean.getFixity().toUpperCase().trim().equals("YES")) {
                fixityList.add(propertyBean);
            }
        }
        this.fixityList = fixityList;
    }

    private void getExcelToModelListByTitle() {

        // List modelList = new ArrayList();
        System.out.println("Excel文件开始读取行数：" + (startTitleRow + 1));
        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());

            // 设置连续空行的变量，如果有连续10个空行，就停止取数
            int intContinueCount = 0;

            for (int i = startTitleRow + 1; i < sheet.getRows(); i++) {

                int intcount = getColumnByValue(sheet.getRow(i));
                if (intcount < 1) {
                    intContinueCount++;
                    if (intContinueCount < 11) {
                        continue;
                    } else {
                        break;
                    }
                } else {
                    intContinueCount = 0;
                }

                Object obj = this.getModelClass(excelConfig.getClassName());

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

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

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

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

                    // 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 + "Cell[" + i + "][" + j + "]:" + 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 + "Cell[" + i + "][" + j + "]:" + excelTitleName + "-日期格式不正确！";
                                    System.out.println("Cell[" + i + "][" + j + "]:" + 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 + "Cell[" + i + "][" + j + "]:" + excelTitleName + "-不是数字！";
                                    System.out.println("Cell[" + i + "][" + j + "]:" + 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(),