Excel数据导入至数据库

最新推荐文章于 2022-05-11 15:43:04 发布
原创最新推荐文章于 2022-05-11 15:43:04 发布 · 262 阅读
0 ·
CC 4.0 BY-SA版权
文章标签：
#Excel导入 #单元格验证
本文详细介绍了Excel数据导入功能的实现过程，包括XML配置、数据验证和导入服务的实现。主要内容涵盖导入方法、参数类型、数据行描述、导入流程及结果反馈。
Excel数据导入功能分为这么几个模块：
一、XML配置
1、Excel数据导入方法对应的类的全名和方法名以及导入（数据新增方法）对应的参数（实体）类型；
2、数据行（Excel每行）的描述（XML文件）,包括Excel的列名和数据实体对应的字段、每个字段对应的数据类型、是否强制为空、是否关联验证
二、Excel数据导入对应的服务实现类如下：
方法的返回值为导入结果信息，结果信息包括Excel导入数据的总行数、导入成功行数、导入失败行数、未通过验证的Excel单元格的验证信息等
主要代码如下，乱的一B 啊
package com.topinfo.service.impl;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.stereotype.Service;

import com.topinfo.constant.Constant_EXCEL_IMPORT;
import com.topinfo.libs.ExcelUtil;
import com.topinfo.libs.FileUtils;
import com.topinfo.libs.ImportLogFactory;
import com.topinfo.libs.ReflectObject;
import com.topinfo.libs.Validator;
import com.topinfo.service.ImportExcelService;
import com.topinfo.vo.FailInfo;

@Service
public class ImportExcelServiceImpl implements ImportExcelService {

    private Logger log = Logger.getLogger (this.getClass ());

    @Override
    public String importData(File dataFile,String xmlName){
        ImportLogFactory.clean ();// 初始化导入的辅助参数
        String resultInfo = "";// 导入结果信息（包含导入成功条数，失败条数、总条数，验证未通过的单元格的定位和未通过的提示信息和快速解决方案）
        FileUtils fileUtils = new FileUtils ();
        String proRoot = this.getClass ().getClassLoader ().getResource ("/").getPath ();
        try {
            // ---------------获取EXCEL文件和XML配置文件的信息并做整体验证---------------//
            File inputDataFile = fileUtils.importCopyFile (dataFile);// 根据Excel文件全路径复制文件到临时文件目录下，这一步骤可以自行调整，默认采用FileUtils中的方案(详细见FileUtils 的importCopyFile方法)
            // --------------------Dom4J初始化-----------------------------//
            SAXReader reader = new SAXReader ();
            Document document = reader.read (new File (proRoot + "dataimport" + File.separator + xmlName));
            if (document == null) {
                FailInfo failInfo = new FailInfo (Constant_EXCEL_IMPORT.XML_CONFIG,Constant_EXCEL_IMPORT.WRONG_XML_CONFIG,
                        Constant_EXCEL_IMPORT.MEASURE_WRONG_XML_CONFIG);
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                resultInfo = ImportLogFactory.buildImportResult (ImportLogFactory.buildImportInfo ());// 最终生成导入结果信息
                return resultInfo;
            }
            Element root = document.getRootElement ();
            if (root == null) {
                FailInfo failInfo = new FailInfo (Constant_EXCEL_IMPORT.XML_CONFIG,Constant_EXCEL_IMPORT.WRONG_XML_CONFIG,
                        Constant_EXCEL_IMPORT.MEASURE_WRONG_XML_CONFIG);//
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                resultInfo = ImportLogFactory.buildImportResult (ImportLogFactory.buildImportInfo ());// 最终生成导入结果信息
                return resultInfo;
            }
            Workbook workbook = Workbook.getWorkbook (dataFile);
            Sheet sheet = workbook.getSheet (0);

            // ----------------------验证excel文件是否复合XML的配置规则-----------------//
            String[] excelHeaderData = ExcelUtil.readHeaderData (sheet);// 获取表头/列头数组
            boolean isCorrectExcel = validateCorrectnessOfExcel (root, excelHeaderData);
            if (!isCorrectExcel) {// excel与xml配置不符合
                FailInfo failInfo = new FailInfo (Constant_EXCEL_IMPORT.EXCEL + dataFile.getName (),Constant_EXCEL_IMPORT.WRONG_EXCEL,
                        Constant_EXCEL_IMPORT.MEASURE_WRONG_EXCEL);
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                resultInfo = ImportLogFactory.buildImportResult (ImportLogFactory.buildImportInfo ());// 最终生成导入结果信息
                return resultInfo;
            } else {// excel与xml配置相符
                extractAndValidateData (inputDataFile, workbook, root);// 执行导入方法
                resultInfo = ImportLogFactory.buildImportResult (ImportLogFactory.buildImportInfo ());// 最终生成导入结果信息
            }
        } catch (Exception e) {
            RuntimeException exception = new RuntimeException ("在导入数据文件时出错" + e.getMessage ());
            log.error (exception.getMessage (), e);
            throw exception;
        }
        return resultInfo;
    }

    /**
     *@Description: 抽取EXCEL中的数据并根据XML配置来做验证 
     *@Author: 朱德峰
     *@Since: 2012-12-14下午4:18:54
     *@param dataFile
     *@param workbook
     *@param root
     *@return
     * @throws ClassNotFoundException 
     */
    private void extractAndValidateData(File dataFile,Workbook workbook,Element root) throws DocumentException,IOException,BiffException,SecurityException,
            IllegalArgumentException,NoSuchMethodException,IllegalAccessException,InvocationTargetException,ClassNotFoundException{
        ReflectObject reflect = new ReflectObject ();// 实例化反射帮助类
        List<Object[]> cellValueList = new ArrayList<Object[]> ();// 单元格值集合
        List<Object> list_validateSucc = new ArrayList<Object> ();// 验证成功的单元格值集合（泛型类型为验证成功的行对象,对应一个业务实体）
        String columnName = "";// 列头/表头名称
        String cellValue = "";// 表格数据
        CellType ctype = null;// 单元格类型
        Sheet sheet = workbook.getSheet (0);
        String[] excelHeaderData = ExcelUtil.readHeaderData (sheet);
        // ------循环单元格值并逐一作验证---------//
        cellValueList = ExcelUtil.readDateExcel (sheet);
        for ( int i = 0 ; i < cellValueList.size () ; i++ ) {
            Object isValidatetRow = false;
            Object[] rowObject = cellValueList.get (i);
            Object entity = reflect.createObject (root.attributeValue ("class"));
            ImportLogFactory.currentRowValFlag = true;
            for ( int j = 0 ; j < root.elements ().size () ; j++ ) {
                Element columnElement = (Element) root.elements ().get (j);
                columnName = columnElement.element ("associate").elementText ("columns");
                ctype = ExcelUtil.getCellType (sheet, excelHeaderData, columnName, i + 1);
                ImportLogFactory.currentRow = i + 2;
                if (ctype != CellType.DATE) {
                    cellValue = ExcelUtil.getCellValueByColNameAndRowIndex (sheet, ExcelUtil.readHeaderData (sheet), columnName, i + 1);
                } else {
                    DateCell dc = (DateCell) ExcelUtil.getCell (sheet, excelHeaderData, columnName, i + 1);
                    SimpleDateFormat df = new SimpleDateFormat ("yyyy-MM-dd");
                    log.info (dc.getDate () + "");
                    cellValue = df.format (dc.getDate ());
                }
                isValidatetRow = validateColumn (reflect, columnElement, cellValue, entity);
                if ("false".equals (isValidatetRow)) {
                    // break;
                } else {
                    if (isValidatetRow == null) continue;
                    Class<?> refFieldClass = Class.forName (columnElement.element ("field").elementText ("type"));
                    reflect.set (entity, columnElement.element ("field").elementText ("name"), refFieldClass, reflect.get (isValidatetRow, "cellValue"));
                }
            }
            if (ImportLogFactory.currentRowValFlag) {
                list_validateSucc.add (entity);
            } else {
                ImportLogFactory.failCount++;
            }
        }
        int failRows = insert2DBFromExcelRow (reflect, root, list_validateSucc);
        ImportLogFactory.buildImportInfo ().setSuccessCount (list_validateSucc.size () - failRows);// 导入成功的数据条数
        ImportLogFactory.buildImportInfo ().setFailCount (ImportLogFactory.failCount + failRows);// 导入失败的行数
        // 返回错误文档
        workbook.close ();
    }

    /**
     *@Description: 验证需要导入的EXCEL文件是否与XML配置相匹配
     *@Author: 朱德峰
     *@Since: 2012-12-14下午3:07:08
     *@param root 导入XML根节点
     *@param excelHeaderData 表头数据
     *@return 当前excel文件是否符合XML的配置
     */
    private boolean validateCorrectnessOfExcel(Element root,String[] excelHeaderData){
        List<Element> listElement = root.elements ("data");
        boolean isSame = false;
        for ( Element element : listElement ) {
            String columnName = element.element ("associate").elementText ("columns");
            boolean isSameTemp = false;
            if (StringUtils.isNotBlank (columnName)) {
                for ( String colName : excelHeaderData ) {
                    if (StringUtils.equalsIgnoreCase (columnName, colName)) isSameTemp = true;
                }
            }
            isSame = isSameTemp;
        }
        return isSame;
    }

    /**
     *@Description: 常规验证
     *@Author: 朱德峰
     *@Since: 2012-6-25上午9:59:33
     *@param columnElement:xml中关于列配置的节点元素
     *@param cellValue:excel中的列值(跟XML中配置的相对应)
     *@param isValidate:是否验证成功的标识字段
     *@return:如果返回false字符代表验证失败,否则返
     */
    protected Validator validateRoutine(ReflectObject reflect,Element columnElement,String cellValue){
        Validator validator = new Validator ();
        Element fieldElement = columnElement.element ("field");// 获取字段描述XML节点
        String fieldType = fieldElement.elementText ("type");// 字段类型
        String doubleSize = null;// 字符长度
        int fieldSize = 0;// 字段长度
        if (fieldElement.elementText ("size").split (",").length > 1) {
            doubleSize = fieldElement.elementText ("size");
        } else {
            fieldSize = Integer.parseInt (fieldElement.elementText ("size"));// 字段长度大小
        }

        // -------------------------非空验证-------------------------------//
        Boolean mandatory = Boolean.parseBoolean (fieldElement.elementText ("mandatory"));// 是否强制不能为空
        if (mandatory && StringUtils.isBlank (cellValue)) {
            FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                    Constant_EXCEL_IMPORT.WRONG_NOTNULL,Constant_EXCEL_IMPORT.MEASURE_WRONG_NOTNULL);// （不能为空）验证失败的失败信息
            ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
            ImportLogFactory.currentRowValFlag = false;
        } else if (!mandatory && StringUtils.isBlank (cellValue)) {
            validator.setSucces (true);
        }
        // ----------------------------时间验证------------------------------------//
        if (StringUtils.equalsIgnoreCase (fieldType, "java.util.Date")) {
            SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd");
            try {
                Date fieldDate = sdf.parse (cellValue);
                validator.setSucces (true);
                validator.setCellValue (fieldDate);
            } catch (ParseException e) {
                FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                        Constant_EXCEL_IMPORT.WRONG_DATE,Constant_EXCEL_IMPORT.MEASURE_WRONG_DATE);// （错误的时间格式）验证失败的失败信息
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                ImportLogFactory.currentRowValFlag = false;
                validator.setSucces (false);
            }
            // --------------------------字符验证-------------------------------------//
        } else if (StringUtils.equalsIgnoreCase (fieldType, "java.lang.String")) {
            if (fieldSize > 0 && fieldSize < cellValue.length ()) {// 验证字符长度
                validator.setSucces (false);
                FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                        Constant_EXCEL_IMPORT.WRONG_EXCEED_MAX,Constant_EXCEL_IMPORT.MEASURE_WRONG_EXCEED_MAX + fieldSize);// （最大长度限制错误）验证失败的失败信息
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                ImportLogFactory.currentRowValFlag = false;
            } else {
                validator.setSucces (true);
                validator.setCellValue (cellValue);
            }
            // -------------------double类型验证-----------------//
        } else if (StringUtils.equalsIgnoreCase (fieldType, "java.lang.Double")) {
            String[] doubleStr = null;
            if (cellValue.indexOf (".") > 0) {
                doubleStr = cellValue.split (".");
            } else {
                doubleStr = new String[] { cellValue };
            }
            if (doubleStr[0].length () > Integer.parseInt (doubleSize.split (",")[0])
                    || (doubleStr.length > 1 && doubleStr[1].length () > Integer.parseInt (doubleSize.split (",")[1]))) {
                validator.setSucces (false);
            } else {
                try {
                    Double fieldDouble = Double.parseDouble (cellValue);
                    validator.setSucces (true);
                    validator.setCellValue (fieldDouble);
                } catch (Exception e) {
                    validator.setSucces (false);
                    FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                            Constant_EXCEL_IMPORT.WRONG_DOUBLE,Constant_EXCEL_IMPORT.MEASURE_WRONG_DOUBLE);// （小数转型失败）验证失败的失败信息
                    ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                    ImportLogFactory.currentRowValFlag = false;
                }
            }
        } else if (StringUtils.equalsIgnoreCase (fieldType, "java.lang.Long")) {
            if (cellValue.length () > fieldSize) {
                validator.setSucces (false);
                FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                        Constant_EXCEL_IMPORT.WRONG_MAX_LONG,Constant_EXCEL_IMPORT.MEASURE_WRONG_MAX_LONG + fieldSize);// （整型数据转型失败）验证失败的失败信息
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                ImportLogFactory.currentRowValFlag = false;
            } else {
                try {
                    Long fieldLong = null;
                    if (StringUtils.equals (cellValue, "null")) {
                        fieldLong = null;
                    } else {
                        fieldLong = Long.parseLong (cellValue);
                    }
                    validator.setSucces (true);
                    validator.setCellValue (fieldLong);
                } catch (Exception e) {
                    FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                            Constant_EXCEL_IMPORT.WRONG_LONG,Constant_EXCEL_IMPORT.MEASURE_WRONG_LONG);// （整型数据转型失败）验证失败的失败信息
                    ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                    ImportLogFactory.currentRowValFlag = false;
                    validator.setSucces (false);
                }
            }
        } else if (StringUtils.equalsIgnoreCase (fieldType, "java.math.BigDecimal")) {
            if (cellValue.length () > fieldSize) {
                validator.setSucces (false);
                FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                        Constant_EXCEL_IMPORT.WRONG_MAX_BIGDECIMAL,Constant_EXCEL_IMPORT.MEASURE_WRONG_MAX_BIGDECIMAL + fieldSize);
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                ImportLogFactory.currentRowValFlag = false;
            } else {
                try {
                    BigDecimal bigDecimal = new BigDecimal (cellValue);
                    validator.setSucces (true);
                    validator.setCellValue (bigDecimal);
                } catch (Exception e) {
                    FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                            Constant_EXCEL_IMPORT.WRONG__BIGDECIMAL,Constant_EXCEL_IMPORT.MEASURE_WRONG_BIGDECIMAL);
                    ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                    ImportLogFactory.currentRowValFlag = false;
                    validator.setSucces (false);
                }
            }
        } else if (StringUtils.equalsIgnoreCase (fieldType.toUpperCase (), "REGULAR")) {
            // 正则验证，待继续

        } else {
            // isValidate = "false";
        }
        return validator;
    }

    /**
     *@Description: 关联验证
     *@Author: 朱德峰
     *@Since: 2012-6-25上午9:59:33
     *@param refFieldElement
     *@param cellValue
     *@param isValidate
     *@return:如果返回false字符代表验证失败,否则返
     * @throws InvocationTargetException 
     * @throws IllegalAccessException 
     * @throws NoSuchMethodException 
     * @throws IllegalArgumentException 
     * @throws SecurityException 
     */
    private Validator validateRelevance(ReflectObject reflect,Element refFieldElement,String cellValue,Object entity) throws SecurityException,
            IllegalArgumentException,NoSuchMethodException,IllegalAccessException,InvocationTargetException{

        String serviceName = refFieldElement.element ("associate").elementText ("class");
        String methodName = refFieldElement.element ("associate").elementText ("method");
        String argValue = refFieldElement.element ("associate").elementText ("value");
        // 获取关联对象.
        String refField = null;
        // 获取关联字段的类型
        String refFeildType = null;
        int argsCount = refFieldElement.element ("associate").element ("args").elements ().size ();
        Class<?>[] arg_classArr = new Class<?>[argsCount];// 参数类型数组
        Object[] arg_obj = new Object[argsCount];// 参数数组
        for ( int i = 0 ; i < argsCount ; i++ ) {
            Element argElement = (Element) refFieldElement.element ("associate").element ("args").elements ().get (i);
            refField = argElement.elementText ("dataid");
            arg_classArr[i] = String.class;
            Element e = (Element) argElement.elements ().get (0);
            if (StringUtils.equalsIgnoreCase ("dataid", e.getName ())) {
                arg_obj[i] = reflect.get (entity, refField);
            } else if (StringUtils.equalsIgnoreCase ("const", e.getName ())) {
                arg_obj[i] = "NULL".equals (e.getText ().toUpperCase ()) ? null : e.getText ();
            }
        }
        Object ref_result = null;
        try {
            ref_result = reflect.reflectMethodByInstance (reflect.getApplicationBean (serviceName), methodName, arg_classArr, arg_obj);
        } catch (Exception e) {
            log.error ("调用关联查询方法时遇到异常:" + e.getMessage (), e);
            FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                    Constant_EXCEL_IMPORT.WRONG_RELATED,Constant_EXCEL_IMPORT.MEASURE_WRONG_RELATED);
            ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
            ImportLogFactory.currentRowValFlag = false;
            return null;
        }
        if (StringUtils.isBlank (argValue)) return validateRoutine (reflect, refFieldElement, "" + ref_result);
        else {
            if (null == validateRoutine (reflect, refFieldElement, "" + reflect.get (ref_result, argValue))) {
                FailInfo failInfo = new FailInfo (ExcelUtil.getColMarkByCurrentColNum (ImportLogFactory.currentCol) + ImportLogFactory.currentRow,
                        Constant_EXCEL_IMPORT.WRONG_RELATED,Constant_EXCEL_IMPORT.MEASURE_WRONG_RELATED);
                ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
                ImportLogFactory.currentRowValFlag = false;
            }
            return validateRoutine (reflect, refFieldElement, "" + reflect.get (ref_result, argValue));
        }
    }

    /**
     *@Description:解析当前列的数据规则
     *@Author: 朱德峰
     *@Since: 2012-6-20下午4:32:00
     *@param columnElement:XML中列的相关信息
     *@param cellValue:excel中抽取出来的值
     *@param entity :根据XML中配置的实体类名获取到的实体对象
     *@return excel中对应的列的某行的值是否跟数据库字段匹配成功
     * @throws InvocationTargetException 
     * @throws IllegalAccessException 
     * @throws NoSuchMethodException 
     * @throws IllegalArgumentException 
     * @throws SecurityException 
     */
    protected Validator validateColumn(ReflectObject reflect,Element columnElement,String cellValue,Object entity) throws SecurityException,
            IllegalArgumentException,NoSuchMethodException,IllegalAccessException,InvocationTargetException{
        Validator validator = null;
        String columnData = columnElement.element ("associate").elementText ("columns");// 获取XML中配置的列名
        if (!(StringUtils.isBlank (columnData))) {
            validator = validateRoutine (reflect, columnElement, cellValue);// 常规验证
        } else {
            validator = validateRelevance (reflect, columnElement, cellValue, entity);// 关联验证
        }
        return validator;
    }

    /**
     *@Description: 插入单条记录
     *@Author: 朱德峰
     *@Since: 2012-7-4上午8:47:50
     *@param root:Excel数据导入XML辅助文件的根节点
     *@param entity:需要插入的实体类
     *@return:是否插入成功
     */
    private Boolean insertSingleObj2DB(ReflectObject reflect,Element root,Object entity){
        String serviceName = root.attributeValue ("service");
        String methodName = root.attributeValue ("servicemethod");
        Boolean isInsert2DBSucc = false;
        try {
            reflect.executeData (serviceName, methodName, entity);
            isInsert2DBSucc = true;
        } catch (Exception e) {
            log.error ("在插入数据时出现异常：" + e.getMessage (), e);
            FailInfo failInfo = new FailInfo (Constant_EXCEL_IMPORT.XML_CONFIG_SERVICE,Constant_EXCEL_IMPORT.WRONG_XML_CONFIG,
                    Constant_EXCEL_IMPORT.MEASURE_WRONG_XML_CONFIG);// （不能为空）验证失败的失败信息
            ImportLogFactory.buildImportInfo ().addFailRow2list (failInfo);
            isInsert2DBSucc = false;
        }
        return isInsert2DBSucc;
    }

    /**
     *@Description: 插入数据库
     *@Author: 朱德峰
     *@Since: 2012-6-20下午8:47:30
     *@param root:数据导出xml的根节点
     *@param entity:实体类
     *@return:插入是否成功！
     */
    protected int insert2DBFromExcelRow(ReflectObject reflect,Element root,List list_validateSuc){
        Object entity = null;
        Boolean isInsert2DBSucc = false;
        int failInsertCount = 0;

        for ( int i = 0 ; i < list_validateSuc.size () ; i++ ) {
            entity = list_validateSuc.get (i);
            isInsert2DBSucc = insertSingleObj2DB (reflect, root, entity);
            if (!isInsert2DBSucc) {
                failInsertCount++;
            }
        }
        return failInsertCount;
    }



}