asp.net 中excel 匯入資料庫

標籤：

protected void Button1_Click(object sender, EventArgs e)    {        SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LYConnectionString"].ConnectionString); ;//連結資料庫        conn.Open();        try        {            string fileurl = typename(FileUpload1);//調用typename方法取得excel檔案路徑            DataSet ds = new DataSet();//取得資料集            ds = xsldata(fileurl);            int errorcount = 0;//記錄錯誤資訊條數            int insertcount = 0;//記錄插入成功條數            int updatecount = 0;//記錄更新資訊條數            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)            {                string stuid = ds.Tables[0].Rows[i][0].ToString();                string stuname = ds.Tables[0].Rows[i][1].ToString();                string stusex = ds.Tables[0].Rows[i][2].ToString();                string zhuanye = ds.Tables[0].Rows[i][3].ToString();                string classname = ds.Tables[0].Rows[i][4].ToString();                Response.Write(stuid);                Response.Write(stuname);                Response.Write(stusex);                Response.Write(zhuanye);                Response.Write(classname);                       if (stuid != "" && stuname != "" && stusex != "" && zhuanye != "" && classname != "")                {                    SqlCommand selectcmd = new SqlCommand("select count(*) from stud  ", conn);                    int count = Convert.ToInt32(selectcmd.ExecuteScalar());                    if (count > 0)                    {                        SqlCommand selectcmd2 = new SqlCommand("select count(*) from stud where   stuid=‘" + stuid + "‘", conn);                        int count2 = Convert.ToInt32(selectcmd2.ExecuteScalar());                        if (count2 > 0)                        {                            SqlCommand updatecmd = new SqlCommand("update stud set   stuname=‘" + stuname + "‘,stusex=‘" + stusex + "‘,zhuanye=‘" + zhuanye + "‘,classname=‘" + classname + "‘   where stuid=‘" + stuid + "‘", conn);                            updatecmd.ExecuteNonQuery();                            updatecount++;                        }                        else                        {                            SqlCommand insertcmd = new SqlCommand("insert into  stud    values(‘" + stuid + "‘,‘" + stuname + "‘,‘" + stusex + "‘,‘" + zhuanye + "‘,‘" + classname + "‘)", conn);                            insertcmd.ExecuteNonQuery();                            insertcount++;                        }                    }                    else                    {                        SqlCommand insertcmd = new SqlCommand("insert into  stud    values(‘" + stuid + "‘,‘" + stuname + "‘,‘"                       + stusex + "‘,‘" + zhuanye + "‘,‘" + classname + "‘)", conn);                        insertcmd.ExecuteNonQuery();                         //break;                    }                }                else                {                    errorcount++;                }            }            Response.Write("<script language=‘javascript‘>alert(‘" + insertcount + "條資料匯入成功！" + updatecount + "條資料更新成功！" + errorcount + "條資料部分資訊為空白沒有匯入！‘);</script>");        }        //catch (Exception exp)        //{        //    Response.Write("<script language=‘javascript‘>alert(‘匯入失敗！‘);</script>");        //}        finally        {            conn.Close();        }        }//判斷上傳檔案，並儲存檔案    private String typename(FileUpload fileloads)    {        string fullfilename = fileloads.PostedFile.FileName;        string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\\\") + 1);        string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);        string murl = "";        if (type == "xls")        {            fileloads.PostedFile.SaveAs(Server.MapPath("excel") + "\\\\" + filename);            murl = (Server.MapPath("excel") + "\\\\" + filename).ToString();        }        else        {            Response.Write("<script language=‘javascript‘>alert(‘匯入檔案格式不對!‘);</script>");        }        return murl;    } // 資料庫匯入資料集dataset     private DataSet xsldata(string filepath)     {        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=‘Excel 8.0;IMEX=1‘";        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);        string strCom = "SELECT * FROM [Sheet1$]";        Conn.Open();        System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);        DataSet ds = new DataSet();        myCommand.Fill(ds, "[Sheet1$]");        Conn.Close();        return ds;    }}

 

asp.net 中excel 匯入資料庫