小编最近在做的一个项目，项目中有一个需求是把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); } }