如何从excel将数据加载到datagridview并将其从datagridview插入db | cel将数据加载到datagridview并将其从datagri

本文介绍了如何从excel将数据加载到datagridview并将其从datagridview插入db的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

你好

如何从excel将数据加载到datagridview并从datagridview将其插入db

这是我从excel文件加载数据的代码

选择excel表格代码

Hello

how to load data into datagridview from excel and insert it to db from datagridview

this is my code for load data from excel file

select excel sheet code

private void btnselect_file_Click(object sender, EventArgs e)
      {
          OpenFileDialog _objdlg = new OpenFileDialog();
          DialogResult _objdlgresult = _objdlg.ShowDialog();
          if (_objdlgresult == DialogResult.OK)
          {
              textBox_filename.Text = _objdlg.FileName;
          }
          GetExcelSheetNames(textBox_filename.Text);
      }

GetExcelSheetNames无效

GetExcelSheetNames Void

public void GetExcelSheetNames(string path)
     {
         List<string> sheets = new List<string>();
         string connectionString =
             String.Format(
                 @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""",
                 path);
         DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
         DbConnection connection = factory.CreateConnection();
         connection.ConnectionString = connectionString;
         connection.Open();
         DataTable tbl = connection.GetSchema("Tables");
         connection.Close();
         foreach (DataRow row in tbl.Rows)
         {
             string sheetName = (string) row["TABLE_NAME"];
             if (sheetName.EndsWith("$"))
             {
                 sheetName = sheetName.Substring(0, sheetName.Length - 1);
             }
             sheets.Add(sheetName);
         }
         /*Bind data to listbox*/
         combo_sheetname.DataSource = sheets;
     }

将所选工作表加载到datagridview

load the selected sheet into datagridview

private void btnsload_file_Click(object sender, EventArgs e)
{
    String name = "Items";
    String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox_filename.Text +
                    ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

    OleDbConnection con = new OleDbConnection(constr);
    OleDbCommand oconn = new OleDbCommand("Select * From [" + combo_sheetname.Text + "$]", con);
    con.Open();

    OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
    DataTable data = new DataTable();
    sda.Fill(data);
    dataGridView1.DataSource = data;
}

这是我将数据从datagridview插入db的代码

and this is my code to insert the data from datagridview into db

private void btn_savefromexcel_Click(object sender, EventArgs e)
       {

           SqlConnection cne =
               new SqlConnection(ConfigurationManager.ConnectionStrings["payroll"].ConnectionString);
           for (int i = 0; i < dataGridView1.Rows.Count; i++)
           {
               SqlCommand cmdz =new SqlCommand(@"INSERT INTO WPS_data (emp_id,emp_QID,emp_visa_ID,emp_name,bank_short_name,bank_account_number, salary_frequency, working_days, net_salary, basic_salary, extra_hours, extra_income, Deductions, Payment_Type, Comments) VALUES ("
                                  + dataGridView1.Rows[i].Cells[0].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[1].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[2].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[3].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[4].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[5].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[6].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[7].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[8].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[9].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[10].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[11].Value.ToString() + ","
                                  + dataGridView1.Rows[i].Cells[12].Value.ToString() + ", " +
                                  dataGridView1.Rows[i].Cells[13].Value.ToString() + " ," +
                                  dataGridView1.Rows[i].Cells[14].Value.ToString() + ");", cne);
               cmdz.Connection = cne;
               cmdz.CommandType = CommandType.Text;
               cne.Open();
               cmdz.ExecuteScalar();
               cne.Close();
               MessageBox.Show(@"Insert New Data ", @"New Data Has Been Imported Successfully !",
                   MessageBoxButtons.OK, MessageBoxIcon.Information);
           }

我遇到的错误是列名无效，它看到单元格为列idk为什么

这是我的excel文件

http://i.imgur.com/hF4NoZS.png

the error that i faced is invalid column name and its see the cell as column idk why

here is my excel file

http://i.imgur.com/hF4NoZS.png

推荐答案