关闭

Execl数据导入Sql Server表

406人阅读 评论(0) 收藏 举报

        //将Execl数据放入DataSet
        public DataSet ExecleDs(string filenameurl, string table)
        {
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            odda.Fill(ds, table);
            return ds;
        }

        //导入按钮事件
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile == false)
            {
                Response.Write("<script>alert('请您选择Excel文件')</script> ");
                return;//当无文件时,返回
            }
            string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
            if (IsXls != ".xls")
            {
                Response.Write("<script>alert('只可以选择Excel文件')</script>");
                return;//当选择的不是Excel文件时,返回
            }
           
            string strpath = FileUpload1.PostedFile.FileName.ToString();   //获取Execle文件路径
            string filename = FileUpload1.FileName;                       //获取Execle文件名
            DataSet ds = ExecleDs(strpath, filename);
            DataRow[] dr = ds.Tables[0].Select();                        //定义一个DataRow数组
            int rowsnum = ds.Tables[0].Rows.Count;
            if (rowsnum == 0)
            {
                Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
            }

            else
            {
                int  count = dr.Length;
                int execCount = 0;
                int yiCunZaiCount = 0;
                for (int i = 0; i < dr.Length; i++)
                {
                    string customerCode = dr[i][0].ToString();
                    string customerName = dr[i][1].ToString();
                    //
                    string cityId = dr[i]["所在城市"].ToString();

                    string address = dr[i]["联系地址"].ToString();
                    string phoneAreaCode = dr[i]["电话区号"].ToString();
                    string telPhone = dr[i]["电话"].ToString();
                    string telFenJi = dr[i]["电话分机号"].ToString();
                    string fax = dr[i]["传真区号"].ToString();
                    string faxFenJi = dr[i]["传真分机号"].ToString();
                    string webUrl = dr[i]["客户网址"].ToString();
                    //
                    string source = dr[i]["客户来源"].ToString();
                    string status = dr[i]["客户状态"].ToString();
                    string stype = dr[i]["客户类型"].ToString();
                    string level = dr[i]["客户等级"].ToString();
                    string industry = dr[i]["客户所属行业"].ToString();


                    string soucreId = string.Empty;
                    string statusId = string.Empty;
                    string stypeId = string.Empty;
                    string levelId = string.Empty;
                    string industryId = string.Empty;

                    //核对客户来源是否存在
                    soucreId = checkSource(source);
                    //核对客户状态是否存在
                    statusId = checkStatus(status);
                    //核对客户类型是否存在
                    stypeId = checkType(stype);
                    //核对客户等级是否存在
                    levelId = checkLevel(level);
                    //核对客户所属行业是否存在
                    industryId = checkIndustry(industry);

                    string[] param ={customerCode,customerName,cityId,address,phoneAreaCode, telPhone, telFenJi,fax,faxFenJi,webUrl,
                                    soucreId,statusId,stypeId,levelId,industryId,ClsCommon.getUserInfo().USER_ID
                                    };
                    string insertSqlStr = @"insert into T_Customer
                               (CustomerCode,CustomerName,CityID,Address,TelAreaCode,
                                Telephone,TelExtNo,FaxAreaCode,Fax,WebSiteUrl,
                                SourceId,StatusId,TypeId,LevelId,IndustryId,CreateUserId)
                                values ('{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},{11},{12},{13},{14},'{15}' ";
                    insertSqlStr = string.Format(insertSqlStr, param);

                    insertSqlStr += @")";
                    string sqlcheck = "select  *  from T_Customer where CustomerName='" + customerName + "'";
                    DataSet dss = DbHelperSQL.Query(sqlcheck);
                    if (dss.Tables[0].Rows.Count <= 0)
                    {
                        try
                        {
                            int scuess = DbHelperSQL.ExecuteSql(insertSqlStr);
                            execCount++;
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                    else
                    {
                        yiCunZaiCount++;
                    }
                }
                Response.Write("<script> alert('Excle表导入成功!!,导入" + execCount + "条新客户信息;" + yiCunZaiCount + "条老客户信息');</script>");
            }
        }


        //核对客户来源是否存在
        public string checkSource(string sourceName)
        {
            string sql = " select * from dbo.T_Customer_Source where SourceName='" + sourceName+"'";
            string i = ClsDbAccess.ExecuteScalar(sql);
            string j = string.Empty;
            if (!string.IsNullOrEmpty(i))
            {
                return i;
            }
            else
            {
                string inseSql = "insert into dbo.T_Customer_Source (SourceName) values ('" + sourceName + "')";
                j = ClsDbAccess.ExecuteScalar(inseSql);
                return j;
            }
        }

        //核对客户状态是否存在
        public string checkStatus(string status)
        {
            string sql = " select * from T_Customer_Status where StatusName='" + status + "'";
            string i = ClsDbAccess.ExecuteScalar(sql);
            string j = string.Empty;
            if (!string.IsNullOrEmpty(i))
            {
                return i;
            }
            else
            {
                string inseSql = "insert into T_Customer_Status (StatusName) values ('" + status + "')";
                j = ClsDbAccess.ExecuteScalar(inseSql);
                return j;
            }
        }
        //核对客户类型是否存在
        public string checkType(string type)
        {
            string sql = " select * from dbo.T_Customer_Type where TypeName='" + type+"'";
            string i = ClsDbAccess.ExecuteScalar(sql);
            string j = string.Empty;
            if (!string.IsNullOrEmpty(i))
            {
                return i;
            }
            else
            {
                string inseSql = "insert into dbo.T_Customer_Type (TypeName) values ('" + type + "')";
                j = ClsDbAccess.ExecuteScalar(inseSql);
                return j;
            }
        }
        //核对客户级别是否存在
        public string checkLevel(string level)
        {
            string sql = " select * from dbo.T_Customer_Level where LevelName='" + level + "'";
            string i = ClsDbAccess.ExecuteScalar(sql);
            string j = string.Empty;
            if (!string.IsNullOrEmpty(i))
            {
                return i;
            }
            else
            {
                string inseSql = "insert into T_Customer_Level (LevelName) values ('" + level + "')";
                j = ClsDbAccess.ExecuteScalar(inseSql);
                return j;
            }
        }
        //核对客户所属行业是否存在
        public string checkIndustry(string industry)
        {
            string sql = " select * from dbo.T_Customer_Industry where IndustryName='" + industry + "'";
            string i = ClsDbAccess.ExecuteScalar(sql);
            string j = string.Empty;
            if (!string.IsNullOrEmpty(i))
            {
                return i;
            }
            else
            {
                string inseSql = "insert into T_Customer_Industry (IndustryName) values ('" + industry + "')";
                j = ClsDbAccess.ExecuteScalar(inseSql);
                return j;
            }
        }

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:23529次
    • 积分:442
    • 等级:
    • 排名:千里之外
    • 原创:16篇
    • 转载:15篇
    • 译文:0篇
    • 评论:3条
    文章分类
    最新评论