Hi brave00heart0...,

Using the below article i have created the example.

Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net

Check this example. Now please take its reference and correct your code.

Code

C#

private void btnSelect_Click(object sender, EventArgs e)
{
    openFileDialog1.ShowDialog();
}

private void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
{
    string filePath = openFileDialog1.FileName;
    BindDataGrid(filePath, txtPrefix.Text.Trim());
}

private void BindDataGrid(string filePath, string prefix)
{
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        DataTable dt = new DataTable();
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                if (string.IsNullOrEmpty(prefix))
                {
                    dt.Rows.Add();
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        i++;
                    }
                }
                else
                {
                    int i = 0;
                    foreach (IXLCell cell in row.Cells())
                    {
                        if (cell.Value.ToString().ToLower().Contains(prefix.ToLower()))
                        {
                            dt.Rows.Add();
                            int j = 0;
                            foreach (IXLCell cell1 in row.Cells())
                            {
                                dt.Rows[dt.Rows.Count - 1][j] = cell1.Value.ToString();
                                j++;
                            }
                            i++;
                            break;
                        }
                    }
                }
            }
        }

        dataGridView1.DataSource = dt;
    }
}

VB.Net

Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
    OpenFileDialog1.ShowDialog()
End Sub

Private Sub openFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
    Dim filePath As String = OpenFileDialog1.FileName
    BindDataGrid(filePath, txtPrefix.Text.Trim())
End Sub

Private Sub BindDataGrid(ByVal filePath As String, ByVal prefix As String)
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As DataTable = New DataTable()
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                If String.IsNullOrEmpty(prefix) Then
                    dt.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                        i += 1
                    Next
                Else
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        If cell.Value.ToString().ToLower().Contains(prefix.ToLower()) Then
                            dt.Rows.Add()
                            Dim j As Integer = 0
                            For Each cell1 As IXLCell In row.Cells()
                                dt.Rows(dt.Rows.Count - 1)(j) = cell1.Value.ToString()
                                j += 1
                            Next
                            i += 1
                            Exit For
                        End If
                    Next
                End If
            End If
        Next

        DataGridView1.DataSource = dt
    End Using
End Sub

Screenshot