java基于xml配置的通用excel单表数据导入组件(四、DAO主处理类)

package XXXXX.manage.importexcel;

import java.beans.IntrospectionException;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.logging.Logger;

import org.apache.commons.betwixt.io.BeanReader;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException;

import XXXXX.cache.UserSession;
import xxxxxxxx.manage.importerror.BN_ImportError;
import xxxxxxxx.manage.importerror.DAO_ImportError;
import xxxxxxxx.supp.BN_Row;
import xxxxxxxx.supp.DAORunner;
import xxxxxxxx.supp.TransactionHandler;
import xxxxxxxx.util.ObjectUtil;
import xxxxxxxx.util.PathUtil;
import xxxxxxxx.util.StringUtil;

public class DAO_ImportExcel {

    private String tableImport;  //导入的临时表名
    private String tableTarget;  //导入的目标表名
    private File excelFile;      //excel数据文件
    private int rows;            //总行数
    private int columns;         //总列数
    private Map<String, BN_Column> columnMap = new HashMap();// 列序号、字段定义映射
    private String[] columnName; // excel列名

    private BN_ImportExcel xmlBean;
    private Map<String, BN_Column> excelColumn; // 列名称、字段定义映射
    private Map<String, BN_Column> systemColumn;// 系统内置字段定义
    
    Map<String, Object> resultMap = new HashMap<String, Object>();// 定义返回结果map

    private static Logger logger = Logger.getLogger(DAO_ImportExcel.class
            .getName());

    public DAO_ImportExcel(String tableTarget, File excelFile) {
        this.tableTarget = tableTarget;
        this.tableImport = tableTarget + "_import";  //导入临时表，固定以"_import"结尾
        this.excelFile = excelFile;
        loadXmlBean();
    }

    private void loadXmlBean() {
        BeanReader beanReader = new BeanReader();
        // 配置BeanReader实例
        beanReader.getXMLIntrospector().getConfiguration()
                .setAttributesForPrimitives(false);
        beanReader.getBindingConfiguration().setMapIDs(false); // 不自动生成ID
        // 注册要转换对象的类，并指定根节点名称
        BufferedReader br = null;
        try {
            beanReader.registerBeanClass("importExcel", BN_ImportExcel.class);
            br = new BufferedReader(new InputStreamReader(
                    new FileInputStream(new File(
                            PathUtil.getPath("/WEB-INF/excel/" + tableImport + ".xml"))), "GBK"));
            // 将XML解析Java Object
            xmlBean = (BN_ImportExcel) beanReader.parse(br);
            excelColumn = xmlBean.getExcelColumn();
            systemColumn = xmlBean.getSystemColumn();
        } catch (UnsupportedEncodingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IntrospectionException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SAXException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (br != null){
                try {
                    br.close();
                } catch (IOException e) {
                }
            }
        }
    }

    public Map doImport() {
        Workbook workBook = null;
        try {
            workBook = new XSSFWorkbook(new FileInputStream(excelFile));
        } catch (Exception ex) {
            try {
                workBook = new HSSFWorkbook(new FileInputStream(excelFile));
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (workBook == null){
            resultMap.put("result", false);
            resultMap.put("message", "导入失败!无法加载该数据文件，请检查文件格式是否正确.");
            return resultMap;
        }
        Sheet sheet = workBook.getSheetAt(0);
        rows = sheet.getPhysicalNumberOfRows();
        columns = sheet.getRow(0).getPhysicalNumberOfCells();
        logger.info("rows=" + rows + "  columns=" + columns);
        if (rows <= 1 || columns == 0 ){
            resultMap.put("result", true);
            resultMap.put("error", false);
            resultMap.put("message", "系统没有检测到需要导入的数据内容!");
            return resultMap;
        }
        columnName = new String[columns];
        
        //执行数据操作之前的初始化脚本initSQL
        boolean initResult = runXMLSQL(xmlBean.getInitSQL());
        if (!initResult){
            resultMap.put("result", false);
            resultMap.put("message", "系统初始化失败，请检查配置文件'"+tableImport+".xm'的initSQL是否正确!");
            return resultMap;
        }
        
        // 解析第一行表头列名
        Iterator cellIterator = sheet.getRow(0).cellIterator();
        StringBuffer memo = new StringBuffer();
        while (cellIterator.hasNext()) {
            Cell cell = (Cell) cellIterator.next();
            // 读取当前单元格的值
            String cellValue = cell.getStringCellValue();
            logger.info("cell[0," + cell.getColumnIndex() + "]=" + cellValue);
            columnName[cell.getColumnIndex()] = cellValue;
            if (excelColumn.containsKey(cellValue)) {
                columnMap.put(cell.getColumnIndex() + "",
                        (BN_Column) excelColumn.get(cellValue));
            } else {
                memo.append("列名[").append(cellValue).append("] ");
                logger.info("cell[0," + cell.getColumnIndex() + "]列名["
                        + cellValue + "]" + "未在xml的<excelColumn>节点内定义!无法解析导入");
            }
        }
        if (memo.length() > 0) {// 有未在xml的<excelColumn>节点内定义的列
            BN_ImportError errorBean = new BN_ImportError();
            errorBean.setOp_id(StringUtil.getUUID());
            errorBean.setOp_name(tableTarget);
            errorBean.setRow_no("1");
            errorBean.setMemo(memo.toString()+" 未在xml配置文件中定义，无法解析导入!");
            errorBean.setCreate_id(UserSession.getOpId());
            errorBean.setCreate_name(UserSession.getOpName());
            errorBean.setCreate_time(BN_Row.getNowInput());
            DAO_ImportError.doInsert(errorBean, null);
        }

        // 解析数据行(从第2行开始)
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            cellIterator = sheet.getRow(rowNum).cellIterator();
            memo = new StringBuffer();
            BN_Row rowBean = new BN_Row();
            // 处理表格数据
            while (cellIterator.hasNext()) {
                Cell cell = (Cell) cellIterator.next();
                String cellValue = getCellValue(cell);
                logger.info("cell[" + rowNum + "," + cell.getColumnIndex() + "]=" + cellValue);
                if (columnMap.containsKey(cell.getColumnIndex() + "")) {// 列名有在xml中有定义
                    BN_Column columnBean = columnMap.get(cell.getColumnIndex() + "");
                    Object[] parseResult = parseCell(columnBean, rowNum,
                            columnName[cell.getColumnIndex()], cellValue);
                    if (parseResult[0] != null) {
                        rowBean.setColumnValue(columnBean.getColumn(), parseResult[0]);
                    } else {
                        if (parseResult[1] != null) {
                            memo.append(parseResult[1]);
                        }
                    }
                }
            }
            if (memo.length() > 0) {// 有不符合内容格式要求的字段
                logger.info("第["+(rowNum + 1)+"行] "+memo.toString());
                BN_ImportError errorBean = new BN_ImportError();
                errorBean.setOp_id(StringUtil.getUUID());
                errorBean.setOp_name(tableTarget);
                errorBean.setRow_no((rowNum + 1) + "");
                errorBean.setMemo(memo.toString());
                errorBean.setCreate_id(UserSession.getOpId());
                errorBean.setCreate_name(UserSession.getOpName());
                errorBean.setCreate_time(BN_Row.getNowInput());
                DAO_ImportError.doInsert(errorBean, null);
            } else {
                // 系统级字段
                Iterator iterator = systemColumn.keySet().iterator();
                while (iterator.hasNext()) {
                    String key = iterator.next().toString();
                    BN_Column sysColumnBean = systemColumn.get(key);
                    Object result = transfer(sysColumnBean.getDataType(), "",
                            sysColumnBean.getClassName(),
                            sysColumnBean.getMethod(), sysColumnBean.getParam());
                    if (result != null) {
                        rowBean.setColumnValue(sysColumnBean.getColumn(),
                                result);
                    }
                }
                int result = DAORunner.insertRow(tableImport, rowBean);
                if (result == -1){//保存临时表失败，登记错误信息
                    BN_ImportError errorBean = new BN_ImportError();
                    errorBean.setOp_id(StringUtil.getUUID());
                    errorBean.setOp_name(tableTarget);
                    errorBean.setRow_no((rowNum + 1) + "");
                    errorBean.setMemo("数据保存失败");
                    errorBean.setCreate_id(UserSession.getOpId());
                    errorBean.setCreate_name(UserSession.getOpName());
                    errorBean.setCreate_time(BN_Row.getNowInput());
                    DAO_ImportError.doInsert(errorBean, null);
                }
            }
        }
        //计算自己导入成功的数据条数
        int dataSuccNum = DAORunner.count("select count(*) from "+tableImport+" where create_id=?", 
                new Object[]{UserSession.getOpId()});
        //计算自己文件解析失败的数据条数，排除首行列名解析问题
        int dataFailNum = DAORunner.count("select count(*) from t_import_error where create_id=? and row_no != ?",  
                new Object[]{UserSession.getOpId(), 1});
        int columnFailNum = DAORunner.count("select count(*) from t_import_error where create_id=? and row_no = ?",  
                new Object[]{UserSession.getOpId(), 1});
        boolean result = DAORunner.runTransaction(new TransactionHandler() {
            public void process(Connection connections) throws SQLException {
                //执行数据转移前的脚本　beforeSaveSQL
                if (!ObjectUtil.isNull(xmlBean.getBeforeSaveSQL())){
                    String[] sqlArr = xmlBean.getBeforeSaveSQL().split(";");
                    for (int i = 0 ; i < sqlArr.length ; i ++){
                        DAORunner.runUpdate(sqlArr[i].replaceAll("\$\{userId}", UserSession.getOpId()),
                                new Object[]{}, connections);
                    }
                }
                
                //转移数据至tableTarget
                DAORunner.runUpdate("insert into "+tableTarget+" select * from "+tableImport+" where create_id=?",
                        new Object[]{UserSession.getOpId()}, connections);
                
                //执行数据转移后的脚本　afterSaveSQL
                if (!ObjectUtil.isNull(xmlBean.getBeforeSaveSQL())){
                    String[] sqlArr = xmlBean.getAfterSaveSQL().split(";");
                    for (int i = 0 ; i < sqlArr.length ; i ++){
                        DAORunner.runUpdate(sqlArr[i].replaceAll("\$\{userId}", UserSession.getOpId()),
                                new Object[]{}, connections);
                    }
                }
            }
        });
        if (result){
            resultMap.put("result", true);
            if ((dataFailNum + columnFailNum) > 0){//excel出现问题数据
                resultMap.put("error", true);
            }
            StringBuffer message = new StringBuffer();
            message.append("已成功导入").append(dataSuccNum).append("条数据!");
            if (dataFailNum > 0){
                message.append("有").append(dataFailNum).append("条数据因格式内容有问题无法导入!");
            }
            if (columnFailNum > 0){
                message.append("个别列名未在xml配置文件中定义无法导入!");
            }
            resultMap.put("message", message.toString());
            return resultMap;
        }else{
            resultMap.put("result", false);
            resultMap.put("message", "数据导入失败，请重新尝试!");
        }
        return resultMap;
    }

    private String getCellValue(Cell cell) {
        int type = cell.getCellType();
        switch (type) {
        case Cell.CELL_TYPE_NUMERIC:// 数值、日期类型
            double d = cell.getNumericCellValue();
            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
                Date date = HSSFDateUtil.getJavaDate(d);
                return new SimpleDateFormat("yyyyMMdd").format(date);
            } else {// 数值类型
                return d + "";
            }
        case Cell.CELL_TYPE_BLANK:// 空白单元格
            return null;
        case Cell.CELL_TYPE_STRING:// 字符类型
            return cell.getStringCellValue();
        case Cell.CELL_TYPE_BOOLEAN:// 布尔类型
            return cell.getBooleanCellValue() + "";
        default:
            logger.info("未处理类型["+type+"]");
            break;
        }
        return null;
    }

    private Object transfer(String dataType, String cellValue,
            String className, String method, String param) {
        try {
            Class[] classArr = null;
            Object[] valueArr = null;
            if (ObjectUtil.isNull(param)) {
                classArr = new Class[] {};
                valueArr = new Object[] {};
            } else {
                String[] paramArr = param.split(",");
                classArr = new Class[paramArr.length];
                valueArr = new Object[paramArr.length];
                for (int i = 0; i < paramArr.length; i++) {
                    if ("${self}".equals(paramArr[i])) {
                        if ("varchar".equals(dataType)
                                || "date".equals(dataType)) {
                            classArr[i] = String.class;
                            valueArr[i] = cellValue;
                        } else if ("int".equals(param)) {
                            classArr[i] = int.class;
                            valueArr[i] = new Integer(cellValue);
                        } else if ("float".equals(param)) {
                            classArr[i] = float.class;
                            valueArr[i] = new Float(cellValue);
                        }
                    } else {
                        String typeStr = paramArr[i].split(":")[0];
                        String valueStr = paramArr[i].split(":")[1];
                        if ("varchar".equals(typeStr)) {
                            classArr[i] = String.class;
                            valueArr[i] = valueStr;
                        } else if ("int".equals(typeStr)) {
                            classArr[i] = int.class;
                            valueArr[i] = new Integer(valueStr);
                        } else if ("float".equals(typeStr)) {
                            classArr[i] = float.class;
                            valueArr[i] = new Float(valueStr);
                        }
                    }
                }
            }
            Class tClass = Class.forName(className);
            Object classInstance = tClass.newInstance();
            Method classMethod = tClass.getMethod(method, classArr);
            Object result = classMethod.invoke(classInstance, valueArr);
            return result;
        } catch (SecurityException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 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();
        } catch (NoSuchMethodException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    private Object[] parseCell(BN_Column columnBean, int rowNum,
            String columnName, String cellValue) {
        Object[] objectArr = new Object[2];
        String dataType = columnBean.getDataType();
        int length = columnBean.getLength();
        boolean required = columnBean.isRequired();
        boolean transfer = columnBean.isTransfer();

        if (ObjectUtil.isNull(cellValue)) {
            if (required) {
                objectArr[0] = null;
                objectArr[1] = """ + columnName + ""不能为空! ";
                return objectArr;
            } else {
                objectArr[0] = null;
                objectArr[1] = null;
                return objectArr;
            }
        }

        if (transfer) {// 需要转换内容
            String className = columnBean.getClassName();
            String method = columnBean.getMethod();
            String param = columnBean.getParam();
            Object result = transfer(dataType, cellValue, className, method,
                    param);
            logger.info("[" + rowNum + "," + columnName + "]转换结果=" + result);
            objectArr[0] = result;
            objectArr[1] = null;
        } else {// 不需要转换内容
                // 根据数据类型检查长度和内容是否符合字段要求
            if ("varchar".equals(dataType) || "date".equals(dataType)) {
                if (length != 0 && cellValue.length() > length) {
                    objectArr[0] = null;
                    objectArr[1] = """ + columnName + ""内容超过最大长度" + length + "的限制! ";
                } else {
                    objectArr[0] = cellValue;
                    objectArr[1] = null;
                }
            } else if ("int".equals(dataType)) {
                if (!StringUtil.isInteger(cellValue)) {
                    objectArr[0] = null;
                    objectArr[1] = """ + columnName + ""内容应为整数! ";
                } else {
                    objectArr[0] = new Integer(cellValue);
                    objectArr[1] = null;
                }
            } else if ("float".equals(dataType)) {
                if (!StringUtil.isDouble(cellValue)) {
                    objectArr[0] = null;
                    objectArr[1] = """ + columnName + ""内容应为浮点数! ";
                } else {
                    objectArr[0] = new Float(cellValue);
                    objectArr[1] = null;
                }
            }
        }

        return objectArr;
    }
    
    private boolean runXMLSQL(final String sqlStr){
        if (ObjectUtil.isNull(sqlStr)){
            return true;
        }
        return DAORunner.runTransaction(new TransactionHandler() {
            public void process(Connection connections) throws SQLException {
                String[] sqlArr = sqlStr.split(";");
                for (int i = 0 ; i < sqlArr.length ; i ++){
                    DAORunner.runUpdate(sqlArr[i].replaceAll("\$\{userId}", UserSession.getOpId()),
                            new Object[]{}, connections);
                }
            }
        });
    }

    public static void main(String[] args) throws Exception {
    }
}