Winform：Excel数据导入DataGridView，DataGridView数据导入数据库

1.Excel数据导入DataGridView

这里用到了两个组件，记得在工具栏加进来

textBox：用于存放文件路径（不想看见把Visible设为False即可）

openFileDialog：用于打开文件

private void import_Click(object sender, EventArgs e)
        {
            //打开文件，获取到文件存储路径，并保存在textBox中
            this.openFileDialog.ShowDialog();
            this.textBox1.Text = this.openFileDialog.FileName;

            //导入文件，读取文件的路径，并将其导入到DataGridView中
            string fileName = "";
            fileName = this.textBox1.Text;
            if (this.textBox1.Text != "")
            {
                try
                {
                    string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + " ;Extended Properties=Excel 8.0";
                    System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
                    string strCom = " SELECT * FROM [sheet1$] ";
                    //sheet1为对应表名，如果不是初始默认的，记得更改
                    System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
                    System.Data.DataTable dt = new System.Data.DataTable();
                    myCommand.Fill(dt);
                    //importExcel为DataGridView的Name
                    this.importExcel.DataSource = dt;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show("请选择Excel文件");
            }

        }

导入后excel表头会自动读取为表头，所以数据开始起始位置依旧为excel.Rows[0].Cells[0]

Row：行

Cells：单元格

2.DataGridView数据存入数据库

private void importSQL_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=callnum;User Id=sa;Pwd=123456");

            for (int i = 0; i < importExcel.Rows.Count; i++)
            {

                if (importExcel.Rows[i].Cells[0] != null)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    //插入的数据为数据库对应列数，无论是否设置可为null，都需要进行添加，若列数不匹配，会报错
                    cmd.CommandText = "INSERT INTO Wait VALUES('" + "未完成" + "','" + null + "','" + importExcel.Rows[i].Cells[0].Value + "','" + importExcel.Rows[i].Cells[1].Value + "','" + importExcel.Rows[i].Cells[2].Value + "','" + null + "')";
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                   
                }
            }
            MessageBox.Show("导入成功");
        }