将excel中的数据导入到DataGridView

c#中使用winform控件将excel中的数据导入到DataGridView

  private void button8_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            //openFileDialog1.Filter = "Excel 文件(*.xls)|*.xls|Excel 文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";
            openFileDialog1.Filter = "Excel 文件(*.xlsx)|*.xlsx|Excel 文件(*.xls)|*.xls|所有文件(*.*)|*.*";
            try
            {
                if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    string _filePath = openFileDialog1.FileName;
                    if (_filePath != "")
                    {
                        using (FileStream fs = new FileStream(_filePath, FileMode.Open, FileAccess.Read))
                        {
                            string select = "SELECT * FROM Student_Info";
                            String FileDir = Environment.CurrentDirectory;
                            DataTable dt = SqlHelper.Query(select).Tables[0];



                            Workbook wk = new Workbook();//工作簿
                            wk.Open(fs);  //打开excel文档
                            Worksheet worksheet = wk.Worksheets[0];//工作表
                            Cells cells = worksheet.Cells;//获取worksheet所有单元格
                            //判断excel导入格式
                            if (!cells[0, 0].StringValue.Contains("studentID"))
                            {
                                MessageBox.Show("导入的文档格式不对，请选择正确格式的文档导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                return;
                            }
                            //循环遍历所有单元格信息
                            for (int i = 1; i < cells.Rows.Count; i++)
                            {
                                if (string.IsNullOrEmpty(cells[i, 0].StringValue.Trim()))
                                {
                                    break;
                                }
                                DataRow dr = dt.NewRow();
                                dr["studentID"] = cells[1, 0].StringValue.Trim();
                                dr["studentName"] = cells[1, 1].StringValue.Trim();
                                dr["studentSex"] = cells[1, 2].StringValue.Trim();
                                dr["studentAge"] = cells[1, 3].StringValue.Trim();
                                dr["studentClass"] = cells[1, 4].StringValue.Trim();
                                //dr["列名1"] = cells[i, 0].StringValue.Trim();
                                //dr["列名2"] = cells[i, 1].StringValue.Trim();
                                //dr["列名3"] = cells[i, 2].StringValue.Trim();
                                //dr["列名4"] = cells[i, 3].StringValue.Trim();

                                dt.Rows.Add(dr);
                                System.Windows.Forms.Application.DoEvents();//实时更新表格

                            }

                            //gridControl1.DataSource = dt;
                            //gridView1.BestFitColumns();

                            dataGridView1.DataSource = dt;

                            int result = SqlHelper.Execute($"INSERT INTO Student_Info VALUES({cells[1, 0].StringValue.Trim()},'{cells[1, 1].StringValue.Trim()}','{cells[1, 2].StringValue.Trim()}','{cells[1, 3].StringValue.Trim()}','{cells[1, 4].StringValue.Trim()}')");
                            if (result > 0)
                            {
                                MessageBox.Show("新增成功");
                            }
                            else
                            {
                                MessageBox.Show("新增失败");
                            }
                            //gridView1.BestFitColumns();
                            //worksheet.Cells.;//列宽自适应
                            MessageBox.Show( "资料导入成功", "提示", MessageBoxButtons.OK);//提示保存成功
                        }
                        

                    }
                    //InitStudent();
                }
                
            }
             
            catch (Exception es)
            {
                MessageBox.Show(es.ToString());
            }

        }