Execl数据导入Sql Server表

原创 2012年03月26日 10:26:39

        //将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;
            }
        }

相关文章推荐

Execl数据如何导入SQL Server 2005

  • 2012年12月10日 20:50
  • 29KB
  • 下载

Execl(2003)数据 导入 SQL Server(2005)

最近在做这个问题的时候,发现网上很多资料不是很全,而且有些是有错的,现在我把问题解决了,特把相应的方法贴出来。   在执行插入语句前,需要做下面几步操作: 1、从开始菜单进入到 配置工具 > s...

Excel表数据导入Sql Server数据库中

C#、ASP.NET中将Excel表中的数据导入到Sql Server数据库中对应的表中。 这里我们将E:\test.xls中Sheet1表中的数据导入到test数据库中的my_test表中。 软...

SQL SERVER 2005连接其它数据库并导入数据表

把一个数据库中数据表中的内容,从一个SQL SERVER服务器 导出到另一个SQL Server服务器 不同服务器数据库之间的数据操作 --创建链接服务器  exec sp_addlinke...

SQL SERVER 2005连接其它数据库并导入数据表

把一个数据库中数据表中的内容,从一个SQL SERVER服务器 导出到另一个SQL Server服务器 不同服务器数据库之间的数据操作 --创建链接服务器  exec sp_addlinke...

Sql Server2005导入导出表数据

使用Sql Server2005导入 导出 表 数据到文本文件  cmd运行 DTSWizard.exe 启动导入导出工具 导入表数据到文本文件 选择目标 平面文件目标  所选择如下图 ...

把excel表中的数据导入到SQL Server2008的库中

首先,打开数据库,找到你想要的那个数据库右击该数据库,然后把鼠标移到任务上面会看到导入数据,然后单击导入数据在数据源那项选择exce会看到下面窗体 然后浏览选择你要导入的那个excel,然后下一步会...

Sql Server 导入另一个数据库中的表数据

在涉及到SQL Server编程或是管理时一定会用到数据的导入与导出, 导入导出的方法有多种,此处以SQL Server导入表数据为例,阐述一下:1、打开SQL Server Management S...

asp.net 操作Excel表数据导入到SQL Server数据库

代码全部贴出,主要是Excel表中的数据要和数据库中的数据类型要匹配。 这里Excel表中的字段是: 姓名、性别、班级、学号、初始密码 SQL Server表tb_Users中的字段是;...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Execl数据导入Sql Server表
举报原因:
原因补充:

(最多只允许输入30个字)