excel文件导入sql

最新推荐文章于 2024-09-30 17:00:00 发布

cx5z3i

最新推荐文章于 2024-09-30 17:00:00 发布

阅读量228

点赞数

文章标签： excel string datagrid dataset properties javascript

本文链接：https://blog.csdn.net/cx5z3i/article/details/5528103

版权

protected void Button1_Click(object sender, EventArgs e)
    {
        //if (File1.PostedFile != null)
        if (FileUpload1.PostedFile.FileName != null && FileUpload1.PostedFile.FileName !="")
        {
            rd = new Random(1);
            string filename = DateTime.Now.Date.ToString("yyyymmdd") + DateTime.Now.ToLongTimeString().Replace(":", "") + rd.Next(9999).ToString() + ".xls";
            FileUpload1.PostedFile.SaveAs(@Server.MapPath("file/") + filename);
            string a = Server.MapPath("file/") + filename;

            Label1.Text = "文件名为" + filename;
            string conn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + a + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";//HDR=Yes;IMEX=1是为了防止护照中的数字和字母组合读取不了；

            OleDbConnection thisconnection = new OleDbConnection(conn);
            thisconnection.Open();
            string Sql = "select * from [Sheet1$]";
            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
            DataSet ds = new DataSet();
            mycommand.Fill(ds, "[Sheet1$]");
            thisconnection.Close();
            DataGrid1.DataSource = ds;
            DataGrid1.DataBind();

            int count = ds.Tables["[Sheet1$]"].Rows.Count;
            for (int i = 0; i < count; i++)
            {

                string Name = ds.Tables["[Sheet1$]"].Rows[i]["中文名"].ToString();
                string ENname = ds.Tables["[Sheet1$]"].Rows[i]["护照名"].ToString();
                string Nationality = ds.Tables["[Sheet1$]"].Rows[i]["国籍"].ToString();
                string NationalityEN = ds.Tables["[Sheet1$]"].Rows[i]["nationality"].ToString();
                string StudentID = ds.Tables["[Sheet1$]"].Rows[i]["学号"].ToString();
                string Passport = ds.Tables["[Sheet1$]"].Rows[i]["护照号码"].ToString();
                string StudyTime = ds.Tables["[Sheet1$]"].Rows[i]["Term Period"].ToString();
                string Class = ds.Tables["[Sheet1$]"].Rows[i]["班级"].ToString();
                DataBaseStudent.AddOneStudent(Name, ENname, Nationality, NationalityEN, StudentID, Passport, StudyTime, Class);
            }
            Response.Write("更新成功");

            System.IO.File.Delete(@Server.MapPath("file/") + filename);

        }
        else
        {
            Response.Write("<script language='javascript'>alert('请上传Excel文件!');window.location.href='test.aspx'</script>");
        }
    }