Esempio n. 1
0
        /// <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 = new Base_DataBaseBll().GetPrimaryKey(base_excellimport.ImportTable);
                //模板明细表
                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);
        }