GridView批量导入

最新推荐文章于 2021-04-03 06:02:29 发布

oushine

最新推荐文章于 2021-04-03 06:02:29 发布

阅读量188

点赞数

分类专栏： ASP.NET 文章标签： dataset string textbox excel button properties

本文链接：https://blog.csdn.net/oushine/article/details/5855225

版权

ASP.NET 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

从Excel中将数据导入
protected void Button1_Click(object sender, EventArgs e)
    {
        GridView1.DataSource = createDataSource();
        GridView1.DataBind();
    }
    /// <summary>
    /// 以Excel为数据源获取数据集
    /// </summary>
    /// <returns></returns>
    private DataSet createDataSource()
    {
        string strCon;
        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Files/Employee.xls") + ";Extended Properties=Excel 8.0;";
        OleDbConnection con = new OleDbConnection(strCon);
        OleDbDataAdapter da = new OleDbDataAdapter("select * from [Employee$]", con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
//未整理
protected void Button1_Click(object sender, EventArgs e)
    {
        string type1 = FileUpload1.PostedFile.ContentType;
        string type = type1.Substring(type1.LastIndexOf("-") + 1, 5);
        //string dbName = Path.GetFileName(FileUpload1.FileName).Replace(".xls", "");
        if (string.Equals(type, "excel"))
        {
            string newName = Server.MapPath("App_Data//") + DateTime.Now.ToString("hhmmss") + ".xls";
            FileUpload1.SaveAs(newName);
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newName + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1';";
            OleDbConnection conn = new OleDbConnection(connStr);
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            OleDbDataAdapter oda = new OleDbDataAdapter("select F1 as 标题,F2 as 类型,F3 as 解答,F4 as 答案,F5 as 分值,F6 as 题库,F7 as 分级,F8 as 选项 from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            oda.Fill(ds,"aa");
            conn.Close();
            GridView1.DataSource = ds;
            GridView1.DataBind();
            Session["a"] = ds;
            File.Delete(newName);
            Button2.Visible = true;
        }
        else
        {
            this.Page.RegisterStartupScript("", "<script>alert('文件格式不正确')</script>");
        }

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        DataSet ds1 = (DataSet)Session["a"];
        cn.Open();
        OleDbCommand cmd = new OleDbCommand();
        for (int i = 0; i < ds1.Tables["aa"].Rows.Count; i++)
        {
            string stquestion = ds1.Tables["aa"].Rows[i][0].ToString();
            string stoption = ds1.Tables["aa"].Rows[i][7].ToString();
            int stoptionans = Convert.ToInt32(ds1.Tables["aa"].Rows[i][3].ToString());
            string stremark = ds1.Tables["aa"].Rows[i][2].ToString();
            int stavg = Convert.ToInt32(ds1.Tables["aa"].Rows[i][4]);
            //int stinans = Convert.ToInt32(ds.Tables[0].Rows[i][5]);
            int stfenji = Convert.ToInt32(ds1.Tables["aa"].Rows[i][6]);
            //string stbiaojian = ds.Tables[0].Rows[i][7].ToString();
            //int stsort = Convert.ToInt32(ds.Tables[0].Rows[i][8]);
            string stname = ds1.Tables["aa"].Rows[i][5].ToString();
            string sttype = ds1.Tables["aa"].Rows[i][1].ToString();
            gtx.GetTixingID(sttype);
            gtk.GetTikuID(stname);
            string sql = "insert into [exam_shiti](stquestion,stoption,stoptionans,stremark,stavg,stinans,stfenji,stbiaojian,stsort,stname,sttype,txid,tkid) values('" + stquestion + "','" + stoption + "'," + stoptionans + ",'" + stremark + "'," + stavg + ",0," + stfenji + ",'jj',0,'" + stname + "','" + sttype + "'," + gtx.txid + "," + gtk.tkid + ")";
            cmd.Connection = cn.con;
            cmd.CommandText = sql;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Response.Write("插入失败!由于：" + ex.Message);
            }
        }
        cn.Close();
        Response.Write("<SCRIPT>alert('数据已成功导入到数据库！');</SCRIPT>");
    }
}

for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            StringBuilder query = new StringBuilder();
            GridViewRow row = GridView1.Rows[i];
            string empID = ((TextBox)row.Cells[0].FindControl("txtID")).Text.Replace("'", "");
            string empRealName = ((TextBox)row.Cells[0].FindControl("txtRealName")).Text.Replace("'", "");
            string empSex = ((DropDownList)row.Cells[0].FindControl("ddlSex")).SelectedValue;
            string empAddress = ((TextBox)row.Cells[0].FindControl("txtAddress")).Text.Replace("'", "");
            query.Append("update Employee set EmpID='" + empID + "',EmpRealName='" + empRealName + "',EmpSex='" + empSex + "',EmpAddress='" + empAddress + "' where ID=" + GridView1.DataKeys[i].Value + "");
            Common.ExecuteSql(query.ToString());
        }