Click here to Skip to main content
15,039,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi everyone! I'm a bit troubled about how to update an existing datagridview with data from mysql with specific excel cell values (not excel sheets) imported from excel.
The available solutions seam to import the entire Sheet and replace the existing datagridview.

Thanks.

What I have tried:

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog dlg = new OpenFileDialog();
                dlg.Filter = "Excel File|*.xlx;*.xlsx;*.xlsm;";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    textBox1.Text = dlg.FileName;

                    string name = "sheet1";
                    string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + "; Extended Properties='Excel 12.0 XML;HDR=YES;';";
                    OleDbConnection Con = new OleDbConnection(constr);
                    OleDbCommand OleCon = new OleDbCommand("SELECT * FROM [" + name + "$]", Con);
                    Con.Open();
                    OleDbDataAdapter sda = new OleDbDataAdapter(OleCon);
                    DataTable data = new DataTable();

                    sda.Fill(data);
                  
dataGridView1.DataSource = data;

                    for (int i = 0; i < dataGridView1.RowCount - 1; i++)
                    {
                        dataGridView1.Rows[i].Cells["Ab"].Value = sda;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Posted
Updated 9-Jan-20 21:18pm

1 solution

I'd get MySql data into datatable1, then i'd get Excel data into datatable2. Then i'd join them on key field and finally - display in a datagridview component.

C#
public static DataTable ReadMySqlData(string myConnString, string mySelectQuery)
{
    DataTable dt = new DataTable();
    using(MySqlConnection myConnection = new MySqlConnection(myConnString))
        using (MySqlCommand myCommand = new MySqlCommand(mySelectQuery,myConnection))
        {
            myConnection.Open();
            using(MySqlDataReader myReader= myCommand.ExecuteReader())
                dt.Load(myReader);
            myConnection.Close();
        }
    return dt;
}


Use the same way to fetch Excel Data. Note: replace MySql objects with OlDb objects.

To get common data, you can use Linq:
C#
DataTable MySqlDt = ReadMySqlData(..., ...);
DataTable ExcelDt = ReadExcelData(..., ...);

var commonData = (from md in MySqlDt.AsEnumerable()
    join ed in ExcelDt.AsEnumerable() on md.Field<int>("ID") equals ed.Field<int>("EmployeeID")
    select new
    {
        ID = md.Field<int>("ID"),
        Name = md.Field<string>("Name"),
        SurName = md.Field<string>("SurName"),
        Phone = ed.Field<string>("Phone")
    }).ToList();
)
DataGridView1.DataSource = commonData;


Good luck!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900