Building DataGridView Display in Code (VB 2010 NET)

Question

I'm extracting information from an Excel Spreadsheet and trying to build the DataGridView in code. I found this information very helpful. In debug, everything appears to be right. I have the right number of columns and rows properly formatted but nothing displays on the DataGridView. Why? This is really frustrating.

Dim dTable as New DataTable
For Each currcol As Excel.Range In inputRange.Columns
    dTable.Columns.Add(currcol.Value2, GetType(String))
Next

inputRange = objXLWs.Range("A" & HeaderRow + 1 & ":" & EndingColumn & EndingRow)
Dim i as Integer
For Each row As Excel.Range In inputRange.Rows
    Dim dataRow As DataRow = dTable.NewRow()
    i=0
    For Each incell As Excel.Range In row.Columns
        dataRow(i) = incell.Value2
        i += 1
    Next
    dTable.Rows.Add(dataRow)
Next

bs4DataPreview.DataSource = dTable         ' Set up BindingSource
dgv4PreviewData.AutoGenerateColumns = False
dgv4PreviewData.DataSource = bs4DataPreview
dgv4PreviewData.Show()
dgv4PreviewData.Refresh()

Edited source code to reflect use of DataTable. Still getting nothing on DataGridView.

binding datagridview to an `iList`? try `DataTable` instead. although never tried it, your Of `String()` is an Array, which is not `iEnumerable`. — porkchop, May 27 '14 at 22:31
Is your inputRange correct? What does this `"A" & HeaderRow + 1 & ":" & EndingColumn & EndingRow` result in? — Andy G, May 27 '14 at 22:34
The data I'm interested in follows the header row which may not be row 1. Likewise, the number of columns is variable. I get the ending row from this beauty: Dim EndingRow As Integer = objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row — Ebassador, May 27 '14 at 22:38
I agree with porkchop, use a DataTable as your DataSource instead. — SSS, May 28 '14 at 01:28
Changed from using List(of String) to DataTable. Same results! Nothing displays in the DataGridView even though everything appears to be correct when viewed from Debug mode. — Ebassador, May 28 '14 at 22:18
For the record, the problem was due to DataGridView. I had to change several settings to get SelectionMode = FullColumnSelect to work. That included flagging every columns SortMode to NotSortable before I could set the SelectionMode the way I wanted it. Strangely, I had to add a non-visible column to DGV before it would accept the new columns from the DataTable object. It is now working fine. — Ebassador, May 29 '14 at 21:55

score 0 · Accepted Answer · answered Oct 30 '14 at 15:31

It has been a long time since this question was originally posed. I continued working on the problem and finally came up with a solution that is working. It required building more intelligence into the DataTable.

Dim inputRange As Excel.Range
Dim i As Integer

BuildPreview = False
EndingColumn = ColumnIndexToColumnLetter(EndingColumnNo)
EndingRow = objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row

inputRange = objXLWs.Range("A" & HeaderRow & ":" & EndingColumn & HeaderRow)
i = 1
For Each currcol As Excel.Range In inputRange.Columns
    Dim column As New DataColumn

    column.DataType = GetType(String)
    column.ColumnName = "Column" & i.ToString
    column.Caption = NS(currcol.Value2)
    dTable.Columns.Add(column)
    i += 1
Next

inputRange = objXLWs.Range("A" & HeaderRow + 1 & ":" & EndingColumn & Math.Min(EndingRow, HeaderRow + previewRows))
For Each row As Excel.Range In inputRange.Rows
    Dim dataRow As DataRow = dTable.NewRow()
    i = 1
    For Each incell As Excel.Range In row.Columns
        dataRow("Column" & i.ToString) = NS(incell.Value2)
        i += 1
    Next
    dTable.Rows.Add(dataRow)
Next

I hope this proves to be helpful for someone else.

Building DataGridView Display in Code (VB 2010 NET)

1 Answers1