vb.net Compare two columns, one from datagridview and one from excel

I am writing a comparison tool. The gist is to compare two columns to confirm which row to get data from. I'm not sure what the best way to approach this would be. One column is in a datagridview and the other column in an excel spreadsheet that only the user knows which one to use for comparison. Right now I'm thinking pick the column in the datagridview (since I know how to look for the selected column), then the user will click on a cell in an excel spreadsheet which will tell me where the column is. My issue is that all click events pertain to the form that the datagridview is on, I don't know how to get the excel cell that the user clicked. I hope this makes sense. Is there a better way? How do I get the cell that the user clicks on?

Solution

I'd do this differently:

Your existing data in your datagridview should ideally be living in a datatable instead, and the datagridview is just binding to it. Such a setup would look like this:

    Dim dt as New DataTable
    dt.Columns.Add("ID", GetType(Integer))
    dt.Columns.Add("Name") 'string

    dt.Rows.Add(1, "John")
    dt.Rows.Add(2, "Mary")

    someDataGridView.DataSource = dt

For the data in an excel file I'd read it into a datatable too (some tweaks to the following conenction string and command/sheet name etc will be required)

    Dim con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\your.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""
    Dim cmd = "SELECT * FROM [Sheet1$]"
    Dim dt as New DataTable
    
    Using da as New OleDbDataAdapter(cmd, con)
      da.Fill(dt)
    End Using

    anotherDataGridView.DataSource = dt

I'd have 2 datagridviews (above, I've called them someDGV and anotherDGV) and I'd have them both in Full Column Select mode (and multi select off)
I'd get the user to click somewhere in the grid on each one thereby highlighting a column in each
I'd have a button with a click handler that retrieved each DGV's SelectedColumns(0) - the first selected DataGridViewColumn column, and from there I could get the .DataPropertyName of that column to know the name of the column in the datatable to which the column is bound, and then i could run the compare, whatever that means


    Dim someColumn = someDGV.SelectedColumns(0).DataPropertyName
    Dim dt = DirectCast(someDGV.DataSource, DataTable) 'get the datatable back
    For Each ro as DataRow in dt
      Dim value = ro(someColumn).ToString() 

      ...

You have to fill in the comparison logic yourself - each DGV's table data is gotten the same way.. I just don't know what your overall goal is, how you will link rows together etc