DataGridView doesn't show data when imported

Question

Behind code of aspx page, I have a Datatable:

            Dim people As DataTable = New DataTable()

            people.Columns.Add("ID", System.Type.GetType("System.Int32"))
            people.Columns.Add("FirstName", System.Type.GetType("System.String"))
            people.Columns.Add("LastName", System.Type.GetType("System.String"))

            people.Rows.Add(10, "Merci", "Beaucoup")

and this the GridViewData: (Not asp:GridView)

        Dim gvPeople As System.Windows.Forms.DataGridView = New DataGridView()


        gvPeople.AutoGenerateColumns = False

        'Set Columns Count
        gvPeople.ColumnCount = 3

        'Add Columns
        gvPeople.Columns(0).Name = "ID"
        gvPeople.Columns(0).HeaderText = "ID"
        gvPeople.Columns(0).DataPropertyName = "ID"

        gvPeople.Columns(1).Name = "FirstName"
        gvPeople.Columns(1).HeaderText = "FirstName"
        gvPeople.Columns(1).DataPropertyName = "FirstName"

        gvPeople.Columns(2).Name = "LastName"
        gvPeople.Columns(2).HeaderText = "LastName"
        gvPeople.Columns(2).DataPropertyName = "LastName"

Here I set the datasource of the DataGridView to the DataTable:

        gvPeople.DataSource = people

When I import the DataGridView into the Excel sheet using Gembox.Spreadsheet, it only shows me the headerText of the DataGridView without the data. This is the Import Code:

        DataGridViewConverter.ImportFromDataGridView(ws, gvPeople, New ImportFromDataGridViewOptions() With _
            {
               .ColumnHeaders = True,
               .StartRow = 8,
               .StartColumn = 0
             })

I tried multiple things such as:

• setting up the .DataMember to the DataTable name : gvPeople.DataMember=people.TableName

• Refresh() or Update() the DataGridView after assigning the .Datasource.

Note: This is not an asp:GridView, it's a DataGridView and it does not have a DataBind() method.

Answer 1

if using asp:GridView, Just use the databind function

gvPeople.DataSource = people
gvPeople.Databind()

Note: It is not recommended to use a datagridView in a Web project this control is designed for winforms applications.

If using windows.Forms.DataGridView

DataGridView must be added to a form controls collection to perform its layout

Me.Controls.Add(gvPeople)

But this cannot be done when using web project (Getting Error)

so you have to do some workaround

First i created a Function that convert a datarow to an array of string

Public Function ToStringArray(ByVal dRow As DataRow) As String()

    Dim lst As New List(Of Object)
    lst.AddRange(dRow.ItemArray)

    Return lst.Select(Function(x) x.ToString).ToArray()

End Function

Then i used the Following code

this is your code:

    Dim people As DataTable = New DataTable("people")

    people.Columns.Add("ID", System.Type.GetType("System.Int32"))
    people.Columns.Add("FirstName", System.Type.GetType("System.String"))
    people.Columns.Add("LastName", System.Type.GetType("System.String"))

    people.Rows.Add(10, "Merci", "Beaucoup")

    Dim gvPeople As System.Windows.Forms.DataGridView = New System.Windows.Forms.DataGridView()


    gvPeople.AutoGenerateColumns = False

    ''Set Columns Count
    gvPeople.ColumnCount = 3

    ''Add Columns
    gvPeople.Columns(0).Name = "ID"
    gvPeople.Columns(0).HeaderText = "ID"
    gvPeople.Columns(0).DataPropertyName = "ID"

    gvPeople.Columns(1).Name = "FirstName"
    gvPeople.Columns(1).HeaderText = "FirstName"
    gvPeople.Columns(1).DataPropertyName = "FirstName"

    gvPeople.Columns(2).Name = "LastName"
    gvPeople.Columns(2).HeaderText = "LastName"
    gvPeople.Columns(2).DataPropertyName = "LastName"

And here is my Added Code

    For Each drow As DataRow In people.Rows
        gvPeople.Rows.Add(ToStringArray(drow))
    Next

    gvPeople.RowCount = people.Rows.Count

    gvPeople.Refresh()