如何从excel将数据加载到datagridview并将其从datagridview插入db [英] how to load data into datagridview from excel and insert it to db from datagridview

问题描述

你好



如何从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

推荐答案

））
{
sheetName = sheetName.Substring（ 0 ，sheetName.Length - 1 ）;
}
sheets.Add（sheetName）;
}
/ * 将数据绑定到列表框* /
combo_sheetname.DataSource = sheets;
}

")) { 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;
}

", 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

最好使用参数化的sql命令而不是构建命令字符串。



如果你必须构建一个字符串，那么通常是非数字字段的数据用单引号括起来，你没有任何地方例如：



VALUES（'+ dataGridView1.Rows [i] .Cells [ 0] .Value.ToString（）+'，'+

dataGridView1.Rows [i] .Cells [1] .Value.ToString（）+'，'......等。



假设上面的两个字段是字符串。

Best to use a parameterised sql command rather than build a command string.

If you must build a string then normally a non-numeric field's data is enclosed in single quote and you do not have any anywhere for example:

VALUES ('"+ dataGridView1.Rows[i].Cells[0].Value.ToString() + "','" +
dataGridView1.Rows[i].Cells[1].Value.ToString() + "','" ...etc.

Assuming the two fields above are string.

这篇关于如何从excel将数据加载到datagridview并将其从datagridview插入db的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！