将Excel数据导入数据库！

public DataSet ExcelToDS(string Path)
    {

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path +";"+"Extended Properties=Excel 8.0;";

        OleDbConnection conn = new OleDbConnection(strConn);

        conn.Open();

        string strExcel = "";

        OleDbDataAdapter myCommand = null;

        DataSet ds = null;

        strExcel="select * from [sheet1$]";

        myCommand = new OleDbDataAdapter(strExcel, strConn);

        ds = new DataSet();

        myCommand.Fill(ds,"table1");

        return ds;

    }

 

 

 

  protected void btnUpload_Click(object sender, EventArgs e)
    {
        ClientScriptManager cs = Page.ClientScript;
        Type cstype = this.GetType();

        string saveFileName = attachfile.PostedFile.FileName;
        string fileExt = System.IO.Path.GetExtension(attachfile.PostedFile.FileName);
        
        string sAttachGuid = DateTime.Now.Year.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + System.Guid.NewGuid().ToString();
        string saveFilePath = uploadFolder + sAttachGuid;

        string ParentPath = System.IO.Directory.GetParent(saveFilePath).FullName;
        //判断上传文件夹是否存在,如果不存在,则新建
        if (!System.IO.Directory.Exists(ParentPath))
            System.IO.Directory.CreateDirectory(ParentPath);
        //保存文件
        attachfile.PostedFile.SaveAs(saveFilePath + fileExt);

        
        //把Excel表数据写入DataSet
        DataSet ds = ExcelToDS(saveFilePath + fileExt);
        //临时变量
        string UserCode = "";   //用户UserCode
        string UserId = "";     //用户登录名
        string UserName = "";   //用户显示名
        string UserEmail = "";  //用户邮件
        string UserMobile = ""; //用户移动电话
        string IsOutlookHidden = "";    //是否OutLook显示
        string UserDept = "";      //用户部门Code
        string SendNum = "";        //用户短信数量
        string Func = "";       //用户职务
        string IdCard = "";     //用户身份证号
        string PositionName = "";   //用户岗位
        string UserTelephone = ""; //电话号码
        int UserPrio = 0;    //排序

        int count = 0;

        // string sql = "INSERT INTO SYS_USER (User_Code,User_ID,User_Name,User_Rank,User_Email,User_Mobile,IsOutlookHidden,User_Dept,SendNum,User_Telephone,IsCurADUser,IfEmailRemind,IfSmsRemind,PassWord,USER_BANK,USER_PRIO,FUNC,USER_SIGN,InComDateTime,PositionName,unitCode,Language,IDCard)" +
        //+"VALUES(User_Code,User_ID,User_Name,User_Rank,User_Email,User_Mobile,IsOutlookHidden,User_Dept,SendNum,User_Telephone,IsCurADUser,IfEmailRemind,IfSmsRemind,PassWord,USER_BANK,USER_PRIO,FUNC,USER_SIGN,InComDateTime,PositionName,unitCode,Language,IDCard)";
 
        //把数据写入数据库
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            //获取Excel表中的数据
            UserName = dr[0].ToString();
            UserId = dr[1].ToString();
            UserDept = dr[2].ToString();
            PositionName = dr[3].ToString();
            Func = dr[4].ToString();
            IdCard = dr[5].ToString();
            UserMobile = dr[6].ToString();
            UserTelephone = dr[7].ToString();
            UserEmail = dr[8].ToString();
            UserPrio = Convert.ToInt32(dr[9].ToString());

            //判断岗位是否存在
            if (!PositionName.Trim().Equals("局领导") && !PositionName.Trim().Equals("二级部门正职") && !PositionName.Trim().Equals("二级部门副职") && !PositionName.Trim().Equals("三级部门正职") && !PositionName.Trim().Equals("内勤"))
            {
                PositionName = "";
            }

            //判断职务是否存在
            int j = 0;
            string[] func =  { "局长", "副局长", "政治处主任", "政治处副主任", "纪委书记", "纪委副书记", "支队长", "支队长(政委)", "政委", "指挥中心主任", "副支队长", "副支队长(副政委)", "副政委", "指挥中心副主任", "科长", "大队长", "教导员", "场长", "主任", "副科长", "副大队长", "副教导员", "副场长", "副主任", "中队长", "指导员", "副中队长", "副指导员", "股长", "副股长", "科员", "办事员" };
            for (int i = 0; i < func.Length; i++)
            {
                if (!Func.Trim().Equals(func[i]))
                {
                    j++;
                }
                
            }
            if (j > 0)
            {
                Func = dr[4].ToString();
            }
            else
            {
                Func = "";
            }

            //获取自动生产的用户User_Code
            UserCode = System.Guid.NewGuid().ToString();

            //获取部门的Code
            string DeptId="";

            //判断部门是否存在,不存在就为默认值“韶关市公安局”
            if (CCommonDB.GetFieldValue("Select Count(*) From SYS_DEPARTMENTS where Dept_Name='" + UserDept + "'", strConString).ToString() != "0")
            {
                DeptId = CCommonDB.GetFieldValue("Select Dept_ID From SYS_DEPARTMENTS where Dept_Name='" + UserDept + "'", strConString);
            }
            else
            {
                DeptId = CCommonDB.GetFieldValue("Select Dept_ID From SYS_DEPARTMENTS where Dept_Name='韶关市公安局'", strConString);
            }

            //判断有效是用户Id是否已添加
            if (CCommonDB.GetFieldValue("Select Count(*) From SYS_USER Where User_ID = '" + UserId + "' And IsCurADUser=1", strConString).ToString() == "0")
            {
                string sql = "insert into sys_User(User_Code,User_ID,User_Name,User_Email,User_Mobile,User_Dept,User_Telephone,IsCurADUser,PassWord,FUNC,USER_BANK,InComDateTime,PositionName,IDCard,USER_PRIO) values ('" + UserCode + "','" + UserId + "','" + UserName + "', '" + UserEmail + "','" + UserMobile + "', '" + DeptId + "', '" + UserTelephone + "', '1', '123456', '" + Func + "', '0', '" + System.DateTime.Now.ToString() + "', '" + PositionName + "','" + IdCard + "','" + UserPrio + "')";
                CCommonDB.ExecuteNonQuery(sql, strConString);
            }
            else
            {
                //记录插入失败的用户信息
                string sql = "insert into ErrorMessage values('" + UserId + "','" + UserName + "','" + UserEmail + "','" + UserMobile + "','" + UserDept + "','" + UserTelephone + "','" + PositionName + "','" + DateTime.Now.ToString() + "')";
                CCommonDB.ExecuteNonQuery(sql, strConString);
                //记录导入失败的数量
                count++;
            }
        }
        string rtnStr = "<script> alert('导入完成，有个" + count + "用户导入失败！');</script>";
        Response.Write(rtnStr);
        
    }