C# excel文件导入sql 2005

最新推荐文章于 2024-10-21 15:06:42 发布

allen0118

最新推荐文章于 2024-10-21 15:06:42 发布

阅读量641

点赞数

分类专栏： Winform 文章标签： excel sql c# string dataset datagrid

本文链接：https://blog.csdn.net/chenpeng0118/article/details/5685949

版权

Winform 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

        private void dgv_MouseClick(object sender, MouseEventArgs e)
        {
            int index = dgv.CurrentCell.RowIndex;
            txtPOName.Text = dgv[0, index].Value.ToString();
            txtMaterialNO.Text = dgv[1, index].Value.ToString();
            txtMaterialNum.Text = dgv[4, index].Value.ToString();

            txtMaterialName.Text = dgv[2, index].Value.ToString();
            txtMaterialType.Text = dgv[3, index].Value.ToString();
            txtMaterialPrice.Text = dgv[5, index].Value.ToString();
            txtMaterialSize.Text = dgv[6, index].Value.ToString();
            txtUnit.Text = dgv[7, index].Value.ToString();
            txtRemark.Text = dgv[11, index].Value.ToString();
        }

        private void tsInputPO_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; " + "Data Source =" + txtFilePath.Text.Trim() + ";Extended Properties=Excel 8.0";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string sql = "Select * FROM [Sheet1$]";
            try
            {
                myConn.Open();
                OleDbDataAdapter oda = new OleDbDataAdapter(sql, myConn);

oda.Fill(ds, "[Sheet1$]");
myConn.Close();

            }
            catch (Exception ex)
            {

MessageBox.Show(ex.Message);
}

            //这里的连接用来将数据写入SQLDB
            string connectionString = ConfigurationManager.ConnectionStrings["WMS_StrConnection"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            string sqlGetAllDB = "select * from PO";
            SqlDataAdapter daAllDB = new SqlDataAdapter(sqlGetAllDB, con);
            DataSet dsAllDB = new DataSet();
            DataGrid mygrid = new DataGrid();
            mygrid.BeginInit();
            mygrid.Location = new System.Drawing.Point(10, 240);
            mygrid.Width = 1020;
            mygrid.Height = 300;
            this.Controls.Add(mygrid);
            mygrid.EndInit();
            mygrid.SetDataBinding(ds, "[Sheet1$]");

            try
            {
                int num = ds.Tables[0].Rows.Count;
                for (int i = 0; i < num; i++)
                {
                    string ch1 = mygrid[i, 0].ToString();
                    string ch2 = mygrid[i, 1].ToString();
                    string ch3 = mygrid[i, 2].ToString();

                    string ch4 = mygrid[i, 3].ToString();
                    string ch5 = mygrid[i, 4].ToString();
                    string ch6 = mygrid[i, 5].ToString();

                    string ch7 = mygrid[i, 6].ToString();
                    string ch8 = mygrid[i, 7].ToString();
                    string ch9 = mygrid[i, 8].ToString();

                    string ch10 = mygrid[i, 9].ToString();
                    string ch11 = mygrid[i, 10].ToString();
                    string ch12 = mygrid[i, 11].ToString();

                    string ch13 = mygrid[i, 12].ToString();
                    string ch14 = mygrid[i, 13].ToString();
                    string ch15 = mygrid[i, 14].ToString();

                    string strii = "select * into from" + ds.Tables[0].TableName;
                    string strsql = "insert into PO values('" + ch1 + "','" + ch2 + "','" + ch3 + "','" + ch4 + "','" + ch5 + "','" + ch6 + "','" + ch7 + "','" + ch8 + "','" + ch9 + "','" + ch10 + "','" + ch11 + "','" + ch12 + "','" + ch13 + "','" + ch14 + "','" + ch15 + "')";
                    con.Open();
                    System.Data.DataTable dt = new System.Data.DataTable("PO");
                    SqlDataAdapter da = new SqlDataAdapter(strsql, con);
                    da.Fill(dt);
                    this.lblMessage.Text = "数据导入成功!";
                    lblMessage.ForeColor = Color.Green;
                    con.Close();
                    BindDGV();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("失败！");
            }
        }