CODEWITHSUNDEEP
c#excelimportdatagridviewcompare
Toño MS
Toño MS

Reputation: 11

Comparing two datagridview and returning a datagridview by matching a column C#

C# WindowsForms.
Proyect: Import 2 Excel and choose the sheet that holds the table, then compare the column "Code" at both files and show the matching ones with their info on third dataGridView.

DGV1                
Code    Date        Tipe    Price   Account
3367    02-feb-18   NEW     25      N/A
8543    04-feb-18   NEW     25      N/A
3367    05-feb-18   RENEW   50      N/A
5542    07-feb-18   NEW     75      N/A
1069    27-jan-18   NEW     25      N/A

DGV2                
City    Code
Texas   3367
Texas   8543
Texas   5542
Texas   8673            

DGV3                
Code    Date        Tipe    Price   Account
3367    02-feb-18   NEW     25      N/A
3367    05-feb-18   RENEW   50      N/A
8543    04-feb-18   NEW     25      N/A
5542    07-feb-18   NEW     75      N/A

At the moment im tring with this guide:
https://forgetcode.com/CSharp/1508-Comparing-two-datatables-and-returning-a-datatable-by-matching-one-or-more-columns-using-LINQ# But i dont get it yet. Hope someone can help me with the remaining part as well with an explanation.

private void btnSelect1_Click(object sender, EventArgs e)
{
     try
     {
          OpenFileDialog openfiledialog1 = new OpenFileDialog();
          openfiledialog1.Filter = "Excel Files | *.xlsx; *.xls; *.xlsm";

          if (openfiledialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
          {
               this.tBox1.Text = openfiledialog1.FileName;
          }

          string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox1.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
          OleDbConnection con = new OleDbConnection(constr);
          con.Open();

          dropdown_sheet1.DataSource = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
          dropdown_sheet1.DisplayMember = "TABLE_NAME";
          dropdown_sheet1.ValueMember = "TABLE_NAME";
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
}

public void btnLoad1_Click(object sender, EventArgs e)
{
     try
     {
          string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox1.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
          OleDbConnection con = new OleDbConnection(constr);
          OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + dropdown_sheet1.SelectedValue + "]", con);
          DataTable dt1 = new DataTable();
          sda.Fill(dt1);

          foreach (DataRow row in dt1.Rows)
          {
               dataGridView1.DataSource = dt1;
          }
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
}

private void btnSelect2_Click(object sender, EventArgs e)
{
     try
     {
          OpenFileDialog openfiledialog2 = new OpenFileDialog();
          openfiledialog2.Filter = "Excel Files | *.xlsx; *.xls; *.xlsm";

          if (openfiledialog2.ShowDialog() == System.Windows.Forms.DialogResult.OK)
          {
               this.tBox2.Text = openfiledialog2.FileName;
          }

          string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox2.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
          OleDbConnection con = new OleDbConnection(constr);
          con.Open();

          dropdown_sheet2.DataSource = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
          dropdown_sheet2.DisplayMember = "TABLE_NAME";
          dropdown_sheet2.ValueMember = "TABLE_NAME";
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
}

public void btnLoad2_Click(object sender, EventArgs e)
{
     try
     {
          string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tBox2.Text + ";Extended Properties = \"Excel 12.0; HDR=YES;\" ; ";
          OleDbConnection con = new OleDbConnection(constr);
          OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + dropdown_sheet2.SelectedValue + "]", con);
          DataTable dt2 = new DataTable();
          sda.Fill(dt2);

          foreach (DataRow row in dt2.Rows)
          {
               dataGridView2.DataSource = dt2;
          }
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
}

I have already the import and sheet selection, but i dont understand the compare.

Upvotes: 1

Views: 94

Answers (1)

Sowmiya R
Sowmiya R

Reputation: 78

get data of both data grids as a data table.`

public DataTable getLinq(DataTable dt1, DataTable dt2)
        {
            DataTable dtMerged = (from a in dt1.AsEnumerable()
                                  join b in dt2.AsEnumerable()
                                  on a["code"].ToString() equals b["code"].ToString()
                                  into g
                                  where g.Count() > 0
                                  select a).CopyToDataTable();

            return dtsimilar;
        }`

       set dtsimilar as data source for  third grid

Upvotes: 1

Related Questions