【项目】-Excle导入数据库

分手后的思念是犯贱 2022-06-14 02:00 138阅读 0赞

小编最近在做的一个项目，项目中有一个需求是把excle中的数据导入到数据库中对应的题库表,业务逻辑层最主要的代码如下：

public Dictionary<int, DataTable> GetErrorTable()
            {
                return dicErrorTable;
            }
    
            /// <summary>
            /// 得到解析、验证过程中生成的第三张表数据
            /// </summary>
            /// <returns></returns>
            public Dictionary<int, DataTable> GetThirdTable()
            {
                return dicThirdTable;
            }
    
            
            public Dictionary<int, DataTable> ImportExcel(string strPath, string strXMLName, Dictionary<string, string> dicDefaultColumn, string strDBKey, String TableName)
            {
                //得到导入目标表的DataTable
                Dictionary<int, DataTable> dicTargetTable = this.GetImportTable(strPath, strXMLName, dicDefaultColumn, strDBKey, TableName);
                //得到导入第三张表的DataTable
                Dictionary<int, DataTable> dicThirdTable = this.GetThirdTable();
                //得到过程中出现的问题表
                Dictionary<int, DataTable> dicErrorTable = this.GetErrorTable();
                //执行隐式事务
                // try
                // {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    for (int intTableIndex = 0; intTableIndex < dicTargetTable.Count; intTableIndex++)
                    {
                        if (dicTargetTable[intTableIndex].Rows.Count > 0)
                        {
                            DataTable dtTarget = dicTargetTable[intTableIndex];
                            //TODO:从这里调用导入数据库的方法，在sqlhelper中
                            //   sqlHelper.InsertTable(strPath, strDBKey, dtTarget, dtTarget.TableName, dtTarget.Columns);
                            //MySqlDBHelper mysqlhelper = new MySqlDBHelper();
    
                            //mysqlhelper.BulkInsert(strDBKey,dtTarget);
                            //string strConnValue = ConfigHelper.ReadAppSetting(strDBKey);
                            //MySqlHelper mysqlhelper = new MySqlHelper(strConnValue);
                            MySQLHelper.BulkInsert1(dtTarget);
                        }
                    }
    
                    scope.Complete();
                }
                
    
                Dictionary<int, DataTable> dicDt = new Dictionary<int, DataTable>();
                if (dicTargetTable.Count != 0)
                {
                    dicDt.Add(0, dicTargetTable[0]);//添加正确的数据
                }
                if (dicErrorTable.Count != 0)
                {
                    dicDt.Add(1, dicErrorTable[0]);//添加错误的数据
                }
                if (dicThirdTable.Count != 0)
                {
                    dicDt.Add(2, dicThirdTable[0]);//添加第三张表的数据
                }
    
    
    
                return dicDt;
    
            }
            public Dictionary<int, DataTable> GetImportTable(string strPath, string strXMLName, Dictionary<string, string> dicDefaultColumn, string strDBConn,string TableName)
            {
                //由于DataSet内table名称不能重复，因此，返回值选择用Dictionary类型
                Dictionary<int, DataTable> dicImportTable = new Dictionary<int, DataTable>();
                int intImportIndex = 0;
                int intErrorIndex = 0;
    
                //加载对应的XML配置文件
                XmlDocument doc = new XmlDocument();
                string strRelativeXMLPath =ConfigurationManager.AppSettings["ExcelImportXMLPath"].ToString();
                string strXMLPath = AppDomain.CurrentDomain.BaseDirectory + strRelativeXMLPath + "/" + strXMLName + ".xml";
                doc.Load(strXMLPath);    //加载Xml文件
                XmlElement rootElement = doc.DocumentElement;   //获取根节点
    
                #region 根据对应的XML配置文件下的Sheet数目遍历Excel
    
                for (int intSheetIndex = 0; intSheetIndex < rootElement.ChildNodes.Count; intSheetIndex++)
                {
                    XmlNode xnSheet = rootElement.SelectSingleNode("/Excel").ChildNodes[intSheetIndex];
                    ImportSheetConfigEntity enSheet = new ImportSheetConfigEntity();
    
                    #region 读取Sheet对应的数据表的信息
    
                    enSheet.Name = xnSheet.Attributes["name"] != null ? xnSheet.Attributes["name"].Value : "Sheet" + (intSheetIndex + 1);
                    //数据表名通过controller拼接得到
                    enSheet.TableName = TableName;
                    //enSheet.TableName = xnSheet.Attributes["table"] != null ? xnSheet.Attributes["table"].Value : null;
                    enSheet.PrimaryKey = xnSheet.Attributes["primaryKey"] != null ? xnSheet.Attributes["primaryKey"].Value : null;
                    enSheet.PkType = xnSheet.Attributes["pkType"] != null ? xnSheet.Attributes["pkType"].Value : null;
    
                    #endregion 读取Sheet对应的数据表的信息
    
                    DataTable dtExcel = ExcelToDataTable(strPath, enSheet.Name);
                    DataTable dtError = dtExcel.Clone();
                    dtExcel.TableName = enSheet.TableName;
                    dtError.TableName = enSheet.Name;
                    dtError.Columns.Add("错误原因");
                    if (enSheet.PrimaryKey == null)
                    {
                        throw new Exception("配置文件错误，请联系系统管理员！");
                    }
    
                    XmlNodeList xnlColumns = xnSheet.SelectNodes("./Column");
                    string strNecessaryError = "";
                    string strRepeatError = "";
    
                    Dictionary<string, string> dicNecessaryColumns = new Dictionary<string, string>();
                    Dictionary<string, string> dicReapeatColumns = new Dictionary<string, string>();
                    //Dictionary<string, string> dicForeignColumns = new Dictionary<string, string>();
                    IList<ImportColumnConfigEntity> listForeignColumnConfig = new List<ImportColumnConfigEntity>();
                    Dictionary<string, string> dicThirdTableColumns = new Dictionary<string, string>();
    
                    IList<Dictionary<string, int>> listDicRepeatBasicData = new List<Dictionary<string, int>>();
                    IList<Dictionary<string, Guid>> listDicForeignKey = new List<Dictionary<string, Guid>>();
                    IList<Dictionary<string, Guid>> listDicThirdRelated = new List<Dictionary<string, Guid>>();
                    Dictionary<ImportColumnConfigEntity, Dictionary<string, string>> dicConversionColumn = new Dictionary<ImportColumnConfigEntity, Dictionary<string, string>>();
                    //定义存储第三张表数据的DataSet
                    //DataSet dsThirdTable = new DataSet();
                    //key：目标表字段名，value：第三张表实体t
                    Dictionary<string, ImportThirdTableConfigEntity> dicThirdEntityMap = new Dictionary<string, ImportThirdTableConfigEntity>();
                    //存储默认列的键值对  列名-默认值
                    Dictionary<string, string> dicDefaultData = new Dictionary<string, string>();
    
                    #region 添加主键列
    
                    string targetPKParm = string.Format("Column[@field='{0}']", enSheet.PrimaryKey);
                    XmlNode xnPK = xnSheet.SelectSingleNode(targetPKParm);
                    if (xnPK == null)
                    {
                        Type typePkType;
                        try
                        {
                            typePkType = this.GetTypeByString(enSheet.PkType);
                        }
                        catch (Exception)
                        {
                            throw new Exception("配置文件错误，请联系系统管理员！");
                        }
                        dtExcel.Columns.Add(enSheet.PrimaryKey, typePkType);
                    }
    
                    #endregion 添加主键列
    
                    #region 遍历列
    
                    for (int intColumnIndex = 0; intColumnIndex < xnlColumns.Count; intColumnIndex++)
                    {
                        XmlNode xnColumn = xnlColumns[intColumnIndex];
                        ImportColumnConfigEntity enColumnConfig = new ImportColumnConfigEntity();
                        enColumnConfig.Name = xnColumn.Attributes["name"] != null ? xnColumn.Attributes["name"].Value : "";
                        enColumnConfig.Field = xnColumn.Attributes["field"] != null ? xnColumn.Attributes["field"].Value : "";
                        enColumnConfig.IsNecessary = xnColumn.Attributes["isNecessary"] != null ? xnColumn.Attributes["isNecessary"].Value : "";
                        enColumnConfig.IsVerifyRepeat = xnColumn.Attributes["isVerifyRepeat"] != null ? xnColumn.Attributes["isVerifyRepeat"].Value : "";
                        enColumnConfig.IsForeignKey = xnColumn.Attributes["isForeignKey"] != null ? xnColumn.Attributes["isForeignKey"].Value : "";
                        enColumnConfig.IsM2N = xnColumn.Attributes["isM2N"] != null ? xnColumn.Attributes["isM2N"].Value : "";
                        enColumnConfig.IsActive = xnColumn.Attributes["isActive"] != null ? xnColumn.Attributes["isActive"].Value : "";
                        enColumnConfig.DataType = xnColumn.SelectSingleNode("./DataType") != null ? xnColumn.SelectSingleNode("./DataType").InnerText : "";
    
                        #region 准备 处理转换简单数据
    
                        if (xnColumn.Attributes["conversionValue"] != null)
                        {
                            string strConversionValue = xnColumn.Attributes["conversionValue"].Value;
                            string[] strArraySecond;
                            string[] strArrayFirst = strConversionValue.Split(';');
                            Dictionary<string, string> dicDefaultValue = new Dictionary<string, string>();
                            for (int i = 0; i < strArrayFirst.Length; i++)
                            {
                                strArraySecond = strArrayFirst[i].Split('-');
                                dicDefaultValue.Add(strArraySecond[0], strArraySecond[1]);
                            }
                            dicConversionColumn.Add(enColumnConfig, dicDefaultValue);
                        }
    
                        #endregion 准备 处理转换简单数据
    
                        //调用列名操作方法：验证必要性以及更换列名
                        strNecessaryError += this.ColumnNameOperate(enColumnConfig, ref dtExcel);
                        if (strNecessaryError != "")
                        {
                            throw new Exception("导入Excel文件数据中必须存在" + strNecessaryError + "列！");
                        }
                        if (enColumnConfig.IsActive == "false")
                        {
                            dtExcel.Columns.Remove(enColumnConfig.Name);
                            continue;
                        }
    
                        #region 调用验证Excel中是否存在必要条件
    
                        if (enColumnConfig.IsNecessary == "true")
                        {
                            dicNecessaryColumns.Add(enColumnConfig.Field, enColumnConfig.Name);
                        }
    
                        #endregion 调用验证Excel中是否存在必要条件
    
                        #region 默认数据值的列处理
    
                        if (enColumnConfig.IsDefault == "true")
                        {
                            dicDefaultData.Add(enColumnConfig.Field, dicDefaultColumn[enColumnConfig.Field]);
                        }
    
                        #endregion 默认数据值的列处理
    
                        #region 唯一性要求存在验证数据准备
    
                        if (enColumnConfig.IsVerifyRepeat == "true")
                        {
                            strRepeatError += this.VerifyExcelRepeatData(enColumnConfig, ref dtExcel);
                            if (strRepeatError != "")
                            {
                                throw new Exception("Excel中有相同的" + strRepeatError);
                            }
                            //准备验证唯一性的数据库数据
                            XmlNode xnAvailable = xnColumn.SelectSingleNode("./AvailableField");
                            ImportAvailableFieldConfigEntity enAvailable = null;
                            if (xnAvailable != null && xnAvailable.Attributes["isExistAvailableField"].Value == "true")
                            {
                                enAvailable = new ImportAvailableFieldConfigEntity();
                                enAvailable.IsExistAvailableField = "true";
                                enAvailable.Field = xnAvailable.InnerText;
                                enAvailable.Value = xnAvailable.Attributes["enableValue"] != null ? xnAvailable.Attributes["enableValue"].Value : "";
                            }
                            dicReapeatColumns.Add(enColumnConfig.Field, enColumnConfig.Name);
                            listDicRepeatBasicData.Add(this.QueryRepeatDataInfo(enColumnConfig, enAvailable, enSheet.TableName, strDBConn));
                        }
    
                        #endregion 唯一性要求存在验证数据准备
    
                        #region 如果“是否外键”属性为true，则准备外键关联数据
    
                        if (enColumnConfig.IsForeignKey == "true")
                        {
                            //准备外键数据
                            ImportForeignKeyConfigEntity enForeign = new ImportForeignKeyConfigEntity();
                            XmlNode xnForeign = xnColumn.SelectSingleNode("./ForeignKey");
                            enForeign.TableName = xnForeign.SelectSingleNode("./TableName").InnerText;
                            enForeign.FieldName = xnForeign.SelectSingleNode("./FieldName").InnerText;
                            enForeign.PrimaryKey = xnForeign.SelectSingleNode("./PrimaryKey").InnerText;
    
                            //查询是否存在“是否可用”字段
                            XmlNode xnAvailable = xnForeign.SelectSingleNode("./AvailableField");
                            ImportAvailableFieldConfigEntity enAvailable = null;
                            if (xnAvailable != null && xnAvailable.Attributes["isExistAvailableField"].Value == "true")
                            {
                                enAvailable = new ImportAvailableFieldConfigEntity();
                                enAvailable.IsExistAvailableField = "true";
                                enAvailable.Field = xnAvailable.InnerText;
                                enAvailable.Value = xnAvailable.Attributes["enableValue"] != null ? xnAvailable.Attributes["enableValue"].Value : "";
                            }
                            //dicForeignColumns.Add(enColumnConfig.Field, enColumnConfig.Name);
                            enColumnConfig.enForeignKey = enForeign;
                            listForeignColumnConfig.Add(enColumnConfig);
    
                            listDicForeignKey.Add(this.QueryForeignInfo(enForeign, enAvailable, strDBConn));
    
                            //更改外键列的数据类型
                            dtExcel.Columns.Add("F" + enColumnConfig.Field, this.GetTypeByString(enColumnConfig.DataType));
                        }
    
                        #endregion 如果“是否外键”属性为true，则准备外键关联数据
    
                        #region 如果“是否多对多关系”属性为true，则准备关联表数据
    
                        int intThirdIndex = 0;
                        if (enColumnConfig.IsM2N == "true")
                        {
                            #region 读取配置文件信息
    
                            ImportThirdTableConfigEntity enThirdTableConfig = new ImportThirdTableConfigEntity();
                            enThirdTableConfig.ThirdTable = xnColumn.SelectSingleNode("./ThirdTable").InnerText;
                            enThirdTableConfig.PrimaryKey = xnColumn.SelectSingleNode("./ThirdTablePrimaryKey").InnerText;
                            enThirdTableConfig.PrimaryKeyType = xnColumn.SelectSingleNode("./ThirdTablePrimaryKey").Attributes["dataType"].Value.ToLower();
                            enThirdTableConfig.FirstPK = xnColumn.SelectSingleNode("./ThirdTableFirstPK").InnerText;
                            enThirdTableConfig.FirstPKType = xnColumn.SelectSingleNode("./ThirdTableFirstPK").Attributes["dataType"].Value.ToLower();
                            enThirdTableConfig.SecondPK = xnColumn.SelectSingleNode("./ThirdTableSecondPK").InnerText;
                            enThirdTableConfig.SecondPKType = xnColumn.SelectSingleNode("./ThirdTableSecondPK").Attributes["dataType"].Value.ToLower();
                            enThirdTableConfig.isDefaultRelated = xnColumn.SelectSingleNode("./RelatedTable").Attributes != null ? xnColumn.SelectSingleNode("./RelatedTable").Attributes["isDefault"].Value : "";
    
                            enThirdTableConfig.RelatedTable = xnColumn.SelectSingleNode("./RelatedTable").InnerText;
                            enThirdTableConfig.RelatedKey = xnColumn.SelectSingleNode("./RelatedKey").InnerText;
                            enThirdTableConfig.RelatedName = xnColumn.SelectSingleNode("./RelatedName").InnerText;
                            enThirdTableConfig.Separator = xnColumn.Attributes["separator"] != null ? xnColumn.Attributes["separator"].Value : "";
                            //enThirdTableConfig.listOtherFiled = new List<string>();
                            enThirdTableConfig.dicField = new Dictionary<string, string>();
                            //判断第三张表是否存在其他行
                            XmlNode xnThirdNodes = xnColumn.SelectSingleNode("./ThirdColumns");
                            if (xnThirdNodes != null)
                            {
                                enThirdTableConfig.dicDefaultField = new Dictionary<string, string>();
                                enThirdTableConfig.dicField = new Dictionary<string, string>();
                                for (int i = 0; i < xnThirdNodes.ChildNodes.Count; i++)
                                {
                                    XmlNode xnThirdColumn = xnThirdNodes.ChildNodes[i];
                                    if (xnThirdColumn.Attributes["isDefault"] != null && xnThirdColumn.Attributes["isDefault"].Value == "true")
                                    {
                                        string strDefaultField = xnThirdColumn.Attributes["field"].Value;
                                        string strDefaultFieldType = xnThirdColumn.Attributes["dataType"].Value;
                                        enThirdTableConfig.dicDefaultField.Add(strDefaultField, strDefaultFieldType);
                                    }
                                    else
                                    {
                                        string strField = xnThirdColumn.Attributes["field"].Value;
                                        string strFieldType = xnThirdColumn.Attributes["dataType"].Value;
                                        enThirdTableConfig.dicField.Add(strField, strFieldType);
                                    }
                                }
                            }
    
                            #region 如果关联表存在“是否可用”字段的处理
    
                            XmlNode xnAvailable = xnColumn.SelectSingleNode("./AvailableField");
                            ImportAvailableFieldConfigEntity enAvailable = null;
                            if (xnAvailable != null && xnAvailable.Attributes["isExistAvailableField"].Value == "true")
                            {
                                enAvailable = new ImportAvailableFieldConfigEntity();
                                enAvailable.IsExistAvailableField = "true";
                                enAvailable.Field = xnAvailable.InnerText;
                                enAvailable.Value = xnAvailable.Attributes["enableValue"] != null ? xnAvailable.Attributes["enableValue"].Value : "";
                            }
    
                            #endregion 如果关联表存在“是否可用”字段的处理
    
                            #endregion 读取配置文件信息
    
                            #region 准备第三张表需要的列信息以及关联数据
    
                            //标记第三张表关联列
                            dicThirdTableColumns.Add(enColumnConfig.Field, enColumnConfig.Name);
                            DataTable dtThirdTable = new DataTable(enThirdTableConfig.ThirdTable);
                            //第三张表（DataTable）中添加主键、关联表相关键列
                            dtThirdTable.Columns.Add(enThirdTableConfig.PrimaryKey, this.GetTypeByString(enThirdTableConfig.PrimaryKeyType));
                            dtThirdTable.Columns.Add(enThirdTableConfig.FirstPK, this.GetTypeByString(enThirdTableConfig.FirstPKType));
                            dtThirdTable.Columns.Add(enThirdTableConfig.SecondPK, this.GetTypeByString(enThirdTableConfig.SecondPKType));
                            //准备关联表数据
                            if (enThirdTableConfig.isDefaultRelated == "true")
                            {
                                dtThirdTable.Columns[enThirdTableConfig.SecondPK].DefaultValue = dicDefaultColumn[enThirdTableConfig.SecondPK];
                            }
                            else
                            {
                                listDicThirdRelated.Add(this.QueryThirdRelateInfo(enThirdTableConfig, enAvailable, strDBConn));
                            }
    
                            //第三张表添加其他列
                            foreach (string strField in enThirdTableConfig.dicField.Keys)
                            {
                                dtThirdTable.Columns.Add(strField).DataType = this.GetTypeByString(enThirdTableConfig.dicField[strField]);
                            }
                            //默认数据列
                            foreach (string strDefaultField in enThirdTableConfig.dicDefaultField.Keys)
                            {
                                dtThirdTable.Columns.Add(strDefaultField).DataType = this.GetTypeByString(enThirdTableConfig.dicDefaultField[strDefaultField]);
                                dtThirdTable.Columns[strDefaultField].DefaultValue = dicDefaultColumn[strDefaultField];
                            }
    
                            dicThirdTable.Add(intThirdIndex, dtThirdTable);
                            //map中添加数据
                            dicThirdEntityMap.Add(enColumnConfig.Field, enThirdTableConfig);
    
                            #endregion 准备第三张表需要的列信息以及关联数据
                        }
    
                        #endregion 如果“是否多对多关系”属性为true，则准备关联表数据
                    }
    
                    #endregion 遍历列
    
                    #region 遍历行
    
                    int intRowsCount = dtExcel.Rows.Count;
                    for (int intRowIndex = 0; intRowIndex < intRowsCount; intRowIndex++)
                    {
                        Boolean bolIsContinueRun = true;
                        //添加目标表主键列数据
                        //dtExcel.Rows[intRowIndex][enSheet.PrimaryKey] = Guid.NewGuid();
                        if (xnPK == null)
                        {
                            if (enSheet.PkType.ToLower() == "string")
                            {
                                dtExcel.Rows[intRowIndex][enSheet.PrimaryKey] = Guid.NewGuid().ToString();
                            }
                            else if (enSheet.PkType.ToLower() == "guid")
                            {
                                dtExcel.Rows[intRowIndex][enSheet.PrimaryKey] = Guid.NewGuid();
                            }
                        }
    
                        #region 验证必填数据列
    
                        //遍历需要验证唯一性的列名（listNecessaryColumns中）
                        foreach (string strNecessaryColumn in dicNecessaryColumns.Keys)
                        {
                            //判断必填内容是否存在数据
                            if (dtExcel.Rows[intRowIndex][strNecessaryColumn].ToString().Trim() == "")
                            {
                                //提示语
                                AddErrorRow(ref dtError, dicNecessaryColumns[strNecessaryColumn] + "为必填数据!", dtExcel.Rows[intRowIndex]);
                                dtExcel.Rows.RemoveAt(intRowIndex);
                                //遍历个数减去1
                                intRowsCount -= 1;
                                // 遍历索引1
                                intRowIndex -= 1;
                                bolIsContinueRun = false;
                                break;
                            }
                        }
    
                        #endregion 验证必填数据列
    
                        #region 验证重复数据
    
                        if (bolIsContinueRun)
                        {
                            //遍历需要验证重复的列名
                            int intColumnRepeatIndex = 0;
                            foreach (string strRepeatColumnName in dicReapeatColumns.Keys)
                            {
                                //验证重复性
                                if (listDicRepeatBasicData[intColumnRepeatIndex].ContainsKey(dtExcel.Rows[intRowIndex][strRepeatColumnName].ToString().Trim()))
                                {
                                    AddErrorRow(ref dtError, "该行的" + dicReapeatColumns[strRepeatColumnName] + "值在系统中已经存在！", dtExcel.Rows[intRowIndex]);
                                    dtExcel.Rows.Remove(dtExcel.Rows[intRowIndex]);
                                    //遍历个数减去1
                                    intRowsCount -= 1;
                                    // 遍历索引1
                                    intRowIndex -= 1;
                                    bolIsContinueRun = false;
                                    break;
                                }
                                ++intColumnRepeatIndex;
                            }
                        }
    
                        #endregion 验证重复数据
    
                        #region 默认数据值的列处理
    
                        foreach (string columnName in dicDefaultData.Keys)
                        {
                            dtExcel.Rows[intRowIndex][columnName] = dicDefaultData[columnName];
                        }
    
                        #endregion 默认数据值的列处理
    
                        #region 转换处理简单数据
    
                        if (bolIsContinueRun)
                        {
                            foreach (ImportColumnConfigEntity enColumn in dicConversionColumn.Keys)
                            {
                                StringBuilder sbAllowValue = new StringBuilder();
                                foreach (string strAllowValue in dicConversionColumn[enColumn].Keys)
                                {
                                    sbAllowValue.Append(strAllowValue + " ");
                                }
                                string strCellValue = dtExcel.Rows[intRowIndex][enColumn.Field].ToString();
                                if (!dicConversionColumn[enColumn].ContainsKey(strCellValue))
                                {
                                    AddErrorRow(ref dtError, "列" + enColumn.Name + "的值只允许填入" + sbAllowValue.ToString(), dtExcel.Rows[intRowIndex]);
                                    dtExcel.Rows.Remove(dtExcel.Rows[intRowIndex]);
                                    //遍历个数减去1
                                    intRowsCount -= 1;
                                    // 遍历索引1
                                    intRowIndex -= 1;
                                    bolIsContinueRun = false;
                                    break;
                                }
                                dtExcel.Rows[intRowIndex][enColumn.Field] = dicConversionColumn[enColumn][strCellValue];
                            }
                        }
    
                        #endregion 转换处理简单数据
    
                        #region 处理外键的数据
    
                        if (bolIsContinueRun)
                        {
                            int intColumnForeignIndex = 0;
                            //foreach (string strForeignColumnName in dicForeignColumns.Keys)
                            foreach (ImportColumnConfigEntity enColumnConfig in listForeignColumnConfig)
                            {
                                //获取外键列的值
                                string strForeignValue = dtExcel.Rows[intRowIndex][enColumnConfig.Field].ToString().Trim();
                                //判断Dic中是否存在对应的外键数据
                                if (listDicForeignKey[intColumnForeignIndex].ContainsKey(strForeignValue))
                                {
                                    dtExcel.Rows[intRowIndex]["F" + enColumnConfig.Field] = listDicForeignKey[intColumnForeignIndex][strForeignValue];
                                }
                                else if (enColumnConfig.IsNecessary != "true")
                                {
                                    continue;
                                }
                                else
                                {
                                    //外键列，没有匹配值--提示语
                                    AddErrorRow(ref dtError, "指定的" + enColumnConfig.Name + "在系统中不存在！", dtExcel.Rows[intRowIndex]);
                                    //外键数据不存在则从导入数据中移除
                                    dtExcel.Rows.Remove(dtExcel.Rows[intRowIndex]);
                                    //遍历个数减去1
                                    intRowsCount -= 1;
                                    // 遍历索引1
                                    intRowIndex -= 1;
                                    bolIsContinueRun = false;
                                    break;
                                }
                                ++intColumnForeignIndex;
                            }
                        }
    
                        #endregion 处理外键的数据
    
                        #region 第三张表添加关联数据
    
                        if (bolIsContinueRun)
                        {
                            int intThirdColumnIndex = 0;
                            foreach (string strThirdRelatedColumnName in dicThirdTableColumns.Keys)
                            {
                                ImportThirdTableConfigEntity enThirdTable = dicThirdEntityMap[strThirdRelatedColumnName];
                                //与关联表相关的数据，Excel中暂定由逗号分隔
                                String[] strRelateField = dtExcel.Rows[intRowIndex][strThirdRelatedColumnName].ToString().Split(enThirdTable.Separator.ToCharArray());
                                //循环Excel中一个字段中的分隔数据
                                for (int i = 0; i < strRelateField.Length; i++)
                                {
                                    DataRow drThirdTable = dicThirdTable[intThirdColumnIndex].NewRow();
    
                                    #region 添加主键
    
                                    if (enThirdTable.PrimaryKeyType == "guid")
                                    {
                                        drThirdTable[enThirdTable.PrimaryKey] = Guid.NewGuid();
                                    }
                                    else if (enThirdTable.PrimaryKeyType == "string")
                                    {
                                        drThirdTable[enThirdTable.PrimaryKey] = Guid.NewGuid().ToString();
                                    }
    
                                    #endregion 添加主键
    
                                    #region 添加关联目标表主键
    
                                    if (enThirdTable.FirstPKType == "guid")
                                    {
                                        drThirdTable[enThirdTable.FirstPK] = new Guid(dtExcel.Rows[intRowIndex][enSheet.PrimaryKey].ToString());
                                    }
                                    else if (enThirdTable.FirstPKType == "string")
                                    {
                                        drThirdTable[enThirdTable.FirstPK] = dtExcel.Rows[intRowIndex][enSheet.PrimaryKey].ToString();
                                    }
    
                                    #endregion 添加关联目标表主键
    
                                    #region 添加第三张表中关联表的主键
    
                                    if (enThirdTable.isDefaultRelated != "true")
                                    {
                                        drThirdTable[enThirdTable.SecondPK] = listDicThirdRelated[intThirdColumnIndex][strRelateField[i]];
                                    }
    
                                    //添加第三张表数据
                                    //dsThirdTable.Tables[enThirdTable.ThirdTable].Rows.Add(drThirdTable);
                                    dicThirdTable[intThirdColumnIndex].Rows.Add(drThirdTable);
                                }
                                ++intThirdColumnIndex;
                            }
                        }
    
                                    #endregion 添加第三张表中关联表的主键
    
                        #endregion 第三张表添加关联数据
                    }
    
                    #endregion 遍历行
    
                    foreach (ImportColumnConfigEntity enColumnConifg in listForeignColumnConfig)
                    {
                        dtExcel.Columns.Remove(enColumnConifg.Field);
                        dtExcel.Columns["F" + enColumnConifg.Field].ColumnName = enColumnConifg.Field;
                    }
                    foreach (string strThirdRelateColumnName in dicThirdTableColumns.Keys)
                    {
                        dtExcel.Columns.Remove(strThirdRelateColumnName);
                    }
    
                    dicImportTable.Add(intImportIndex, dtExcel);
                    ++intImportIndex;
                    dicErrorTable.Add(intErrorIndex, dtError);
                    ++intErrorIndex;
                }
    
                #endregion 根据对应的XML配置文件下的Sheet数目遍历Excel
    
                return dicImportTable;
            }
    
            #region Helper
            /// <summary>
            /// 存储解析Excel过程中出现的错误数据
            /// </summary>
            public Dictionary<int, DataTable> dicErrorTable = new Dictionary<int, DataTable>();
            /// <summary>
            /// 存储解析Excel过程中生成的第三张表数据
            /// </summary>
            public Dictionary<int, DataTable> dicThirdTable = new Dictionary<int, DataTable>();
            /// <summary>
            /// 从Excel中读取数据到DataTable的方法
            /// </summary>
            /// <param name="strSavePath">文件保存路径</param>
            /// <param name="strSheetName">Sheet名称</param>
            /// <returns></returns>
            protected DataTable ExcelToDataTable(string strSavePath, string strSheetName)
            {
                #region 读取文件Sheet，转换为DataTable
                string strConn;
                string strFileType = System.IO.Path.GetExtension(strSavePath);
                if (string.IsNullOrEmpty(strFileType)) return null;
    
             
                if (strFileType == ".xls")
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strSavePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                else
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strSavePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
    
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
         
                OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [" + strSheetName + "$]", strConn);
                DataSet myDataSet = new DataSet();
                try
                {
                    myCommand.Fill(myDataSet, "ExcelInfo");
                }
                catch
                {
                    throw new Exception("配置文件的Sheet名称配置错误！");
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        myCommand.Dispose();
                        conn.Dispose();
                    }
                }
                DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
                return table;
    
                #endregion 读取文件Sheet，转换为DataTable
            }
    
            #region 列处理及准备数据
    
            //列名操作：验证必要性以及替换列名
            protected string ColumnNameOperate(ImportColumnConfigEntity enColumnConfig, ref DataTable dtExcel)
            {
                if (dtExcel.Columns.Contains(enColumnConfig.Name) && enColumnConfig.IsActive != "false")
                {
                    dtExcel.Columns[enColumnConfig.Name].ColumnName = enColumnConfig.Field;
                }
                else if (enColumnConfig.IsNecessary == "true")
                {
                    return "\"" + enColumnConfig.Name + "\"";
                }
                return "";
            }
    
            //验证Excel中是否有重复数据
            protected string VerifyExcelRepeatData(ImportColumnConfigEntity enColumnConfig, ref DataTable dtExcel)
            {
                DataView dvExcel = new DataView(dtExcel);
                if (dvExcel.Count != dvExcel.ToTable(true, enColumnConfig.Field).Rows.Count)
                {
                    return enColumnConfig.Name;
                }
                return "";
            }
    
            //查询出验证唯一性的数据
            protected Dictionary<string, int> QueryRepeatDataInfo(ImportColumnConfigEntity enColumnConfig, ImportAvailableFieldConfigEntity enAvailable, string strSheetTable, string strDBConnKey)
            {
                //验证重复的字段名称
                String strFieldName = enColumnConfig.Field;
    
                String strSql;
                //导入目标表如果不存在“是否可用”的标志字段
                if (enAvailable != null && enAvailable.IsExistAvailableField == "true")
                {
                    strSql = "select " + enColumnConfig.Field + " from " + strSheetTable + " where " + enAvailable.Field + "=" + enAvailable.Value;
                }
                else
                {
                    strSql = "select " + enColumnConfig.Field + " from " + strSheetTable;
                }
    
                //调用SQLHelper，查询需要验证重复的该列的所有值
                DataTable dtVerifyBasicData = new SQLHelper(strDBConnKey).ExecuteQuery(strSql, CommandType.Text);
    
                Dictionary<string, int> dicVerifyBasicData = new Dictionary<string, int>();
    
                //验证重复字段--查询一次得出所有验证重复所需要的数据
                for (int i = 0; i < dtVerifyBasicData.Rows.Count; i++)
                {
                    dicVerifyBasicData.Add(dtVerifyBasicData.Rows[i][strFieldName].ToString(), i);
                }
                return dicVerifyBasicData;
            }
    
            //查询出外键数据
            protected Dictionary<string, Guid> QueryForeignInfo(ImportForeignKeyConfigEntity enForeignConfig, ImportAvailableFieldConfigEntity enAvailable, string strDBConnKey)
            {
                String strSql;
                //外键所在表如果不存在“是否可用”的标志字段
                if (enAvailable != null && enAvailable.IsExistAvailableField == "true")
                {
                    strSql = "select " + enForeignConfig.FieldName + ", " + enForeignConfig.PrimaryKey + " from " + enForeignConfig.TableName + " where " + enAvailable.Field + "=" + enAvailable.Value;
                }
                else
                {
                    strSql = "select " + enForeignConfig.FieldName + ", " + enForeignConfig.PrimaryKey + " from " + enForeignConfig.TableName;
                }
    
                DataTable dtForeignKey = new DataTable();
                //调用SQLHelper，查询出外键列的所有数据
                dtForeignKey = new SQLHelper(strDBConnKey).ExecuteQuery(strSql, CommandType.Text);
    
                Dictionary<string, Guid> dicForeignKey = new Dictionary<string, Guid>();
                //外键字段--查询一次得出所有外键所需要的数据
                for (int i = 0; i < dtForeignKey.Rows.Count; i++)
                {
                    string strDRFieldName = dtForeignKey.Rows[i][enForeignConfig.FieldName].ToString();
                    if (!dicForeignKey.ContainsKey(strDRFieldName))
                    {
                        dicForeignKey.Add(strDRFieldName, new Guid(dtForeignKey.Rows[i][enForeignConfig.PrimaryKey].ToString()));
                    }
                }
                return dicForeignKey;
            }
    
            //查询出关联表数据
            protected Dictionary<string, Guid> QueryThirdRelateInfo(ImportThirdTableConfigEntity enThirdTable, ImportAvailableFieldConfigEntity enAvailable, string strDBConnKey)
            {
                //获取关联表数据
                String strSql;
                //关联表如果不存在“是否可用”的标志字段
                if (enAvailable != null && enAvailable.IsExistAvailableField == "true")
                {
                    strSql = "select " + enThirdTable.RelatedName + ", " + enThirdTable.RelatedKey + " from " + enThirdTable.RelatedTable + " where " + enAvailable.Field + "=" + enAvailable.Value;
                }
                else
                {
                    strSql = "select " + enThirdTable.RelatedName + ", " + enThirdTable.RelatedKey + " from " + enThirdTable.RelatedTable;
                }
    
                Dictionary<String, Guid> dicRelatedData = new Dictionary<string, Guid>();
                DataTable dtRelatedTable = new DataTable();
                dtRelatedTable = new SQLHelper(strDBConnKey).ExecuteQuery(strSql, CommandType.Text);
    
                for (int i = 0; i < dtRelatedTable.Rows.Count; i++)
                {
                    dicRelatedData.Add(dtRelatedTable.Rows[i][enThirdTable.RelatedName].ToString(), new Guid(dtRelatedTable.Rows[i][enThirdTable.RelatedKey].ToString()));
                }
                return dicRelatedData;
            }
    
            #endregion 列处理及准备数据
    
            #region 向错误列表中添加当前行
    
            /// <summary>
            /// 向错误列表中添加当前行
            /// </summary>
            /// <param name="dtErrorRow">代表错误列表的datatable</param>
            protected void AddErrorRow(ref DataTable dtError, string reason, DataRow drError)
            {
                //新建数据行
                DataRow drAddErrorRow = dtError.NewRow();
                //填充数据行
                for (int i = 0; i < dtError.Columns.Count - 1; i++)
                {
                    drAddErrorRow[i] = drError[i];
                }
                drAddErrorRow["错误原因"] = reason;
                dtError.Rows.Add(drAddErrorRow);
            }
    
            #endregion 向错误列表中添加当前行
    
            /// <summary>
            /// 通过字符串得到相对应的类型
            /// </summary>
            /// <param name="type"></param>
            /// <returns></returns>
            protected Type GetTypeByString(string type)
            {
                switch (type.ToLower())
                {
                    case "bool":
                        return Type.GetType("System.Boolean", true, true);
    
                    case "byte":
                        return Type.GetType("System.Byte", true, true);
    
                    case "sbyte":
                        return Type.GetType("System.SByte", true, true);
    
                    case "char":
                        return Type.GetType("System.Char", true, true);
    
                    case "decimal":
                        return Type.GetType("System.Decimal", true, true);
    
                    case "double":
                        return Type.GetType("System.Double", true, true);
    
                    case "float":
                        return Type.GetType("System.Single", true, true);
    
                    case "int":
                        return Type.GetType("System.Int32", true, true);
    
                    case "uint":
                        return Type.GetType("System.UInt32", true, true);
    
                    case "long":
                        return Type.GetType("System.Int64", true, true);
    
                    case "ulong":
                        return Type.GetType("System.UInt64", true, true);
    
                    case "object":
                        return Type.GetType("System.Object", true, true);
    
                    case "short":
                        return Type.GetType("System.Int16", true, true);
    
                    case "ushort":
                        return Type.GetType("System.UInt16", true, true);
    
                    case "string":
                        return Type.GetType("System.String", true, true);
    
                    case "date":
                    case "datetime":
                        return Type.GetType("System.DateTime", true, true);
    
                    case "guid":
                        return Type.GetType("System.Guid", true, true);
    
                    default:
                        return Type.GetType(type, true, true);
                }
            }