/// <summary> /// 导入数据 /// </summary> /// <param name="dt">Excel数据</param> /// <returns></returns> public int ImportExcel(string moduleId, DataTable dt, out DataTable Result) { //构造导入返回结果表 DataTable Newdt = new DataTable("Result"); Newdt.Columns.Add("rowid", typeof(System.String)); //行号 Newdt.Columns.Add("locate", typeof(System.String)); //位置 Newdt.Columns.Add("reason", typeof(System.String)); //原因 int IsOk = 0; //获得导入模板 //模板主表 Base_ExcelImport base_excellimport = DataFactory.Database().FindEntity <Base_ExcelImport>("ModuleId", moduleId); if (base_excellimport.ImportId == null) { IsOk = 0; } else { string pkName = DatabaseCommon.GetKeyField("LeaRun.Entity." + base_excellimport.ImportTable).ToString(); //模板明细表 List <Base_ExcelImportDetail> listBase_ExcelImportDetail = DataFactory.Database().FindList <Base_ExcelImportDetail>("ImportId", base_excellimport.ImportId); //取出要插入的表名 string tableName = base_excellimport.ImportTable; if (dt != null && dt.Rows.Count > 0) { bool isExit = false; IDatabase database = DataFactory.Database(); DbTransaction isOpenTrans = database.BeginTrans(); try { #region 遍历Excel数据行 int rowNum = 1; int errorNum = 1; foreach (DataRow item in dt.Rows) { Hashtable entity = new Hashtable(); //最终要插入数据库的hashtable StringBuilder sb = new StringBuilder(); entity[pkName] = Guid.NewGuid().ToString(); //首先给主键赋值 #region 遍历模板,为每一行中每个字段找到模板列并赋值 foreach (Base_ExcelImportDetail excelImportDetail in listBase_ExcelImportDetail) { string value = ""; value = item[excelImportDetail.ColumnName].ToString(); DateTime dateTime = DateTime.Now; decimal num = 0; #region 单个字段赋值 switch (excelImportDetail.DataType) { //字符串 case "0": entity[excelImportDetail.FieldName] = value; break; //数字 case "1": if (decimal.TryParse(value, out num)) { entity[excelImportDetail.FieldName] = value; } else { if (base_excellimport.ErrorHanding == "0") { isExit = true; } DataRow dr = Newdt.NewRow(); dr = Newdt.NewRow(); dr[0] = errorNum; dr[1] = "第[" + rowNum.ToString() + "]行[" + excelImportDetail.ColumnName + "]"; dr[2] = "数字格式不正确"; Newdt.Rows.Add(dr); errorNum++; continue; } break; //日期 case "2": if (DateTime.TryParse(value, out dateTime)) { entity[excelImportDetail.FieldName] = value; } else { if (base_excellimport.ErrorHanding == "0") { isExit = true; } DataRow dr = Newdt.NewRow(); dr = Newdt.NewRow(); dr[0] = errorNum; dr[1] = "第[" + rowNum.ToString() + "]行[" + excelImportDetail.ColumnName + "]"; dr[2] = "日期格式不正确"; Newdt.Rows.Add(dr); errorNum++; continue; } break; //外键 case "3": sb.Clear(); sb.Append(" and "); sb.Append(excelImportDetail.CompareField); sb.Append("='"); sb.Append(value); sb.Append("' "); Hashtable htf = database.FindHashtable(excelImportDetail.ForeignTable, sb); if (htf.Count > 0) { entity[excelImportDetail.FieldName] = htf[excelImportDetail.BackField.ToLower()]; } else { if (base_excellimport.ErrorHanding == "0") { isExit = true; } DataRow dr = Newdt.NewRow(); dr = Newdt.NewRow(); dr[0] = errorNum; dr[1] = "第[" + rowNum.ToString() + "]行[" + excelImportDetail.ColumnName + "]"; dr[2] = excelImportDetail.ColumnName + "在系统中不存在"; Newdt.Rows.Add(dr); errorNum++; continue; } break; //唯一识别 case "4": //判断该值是否在表中已存在 sb.Clear(); sb.Append(" and "); sb.Append("='"); sb.Append(value); sb.Append("' "); Hashtable htm = database.FindHashtable(base_excellimport.ImportTable, sb); if (htm.Count > 0) { if (base_excellimport.ErrorHanding == "0") { isExit = true; } DataRow dr = Newdt.NewRow(); dr = Newdt.NewRow(); dr[0] = errorNum; dr[1] = "第[" + rowNum.ToString() + "]行[" + excelImportDetail.ColumnName + "]"; dr[2] = excelImportDetail.ColumnName + "在系统中已存在不能重复插入"; Newdt.Rows.Add(dr); errorNum++; continue; } entity[excelImportDetail.FieldName] = value; break; default: break; } #endregion 单字段赋值结束 } #endregion 遍历模板结束 if (isExit) { break; } database.Insert(base_excellimport.ImportTable, entity, isOpenTrans); rowNum++; } #endregion 遍历Excel数据行结束 database.Commit(); IsOk = 1; } catch (System.Exception ex) { database.Rollback(); Base_SysLogBll.Instance.WriteLog("", OperationType.Add, "-1", "异常错误:" + ex.Message); IsOk = -1; } } } Result = Newdt; return(IsOk); }