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()

Answer 2

You should really reconsider that approach (using DataGridView in ASP.NET application).

If you want to insert only a data from GridView control into an Excel file, then use DataTable.

As you mentioned in the comments, you want to customize which columns to export and the order of the columns. In that case, you just need to adjust the DataTable itself, for example try the following:

' Sample DataTable.
Dim people As New DataTable()

people.Columns.Add("ID", GetType(Integer))
people.Columns.Add("FirstName", GetType(String))
people.Columns.Add("LastName", GetType(String))

people.Rows.Add(1, "John", "Doe")
people.Rows.Add(2, "Jane", "Doe")
people.Rows.Add(10, "Merci", "Beaucoup")

' Remove "ID" column.
people.Columns.Remove("ID")

' Set "LastName" column on first place.
people.Columns("LastName").SetOrdinal(0)

' Create Excel file.
Dim ef As New ExcelFile()
Dim ws As ExcelWorksheet = ef.Worksheets.Add("Sheet1")

' Insert DataTable into Excel file.
Dim op As New InsertDataTableOptions()
op.ColumnHeaders = True
ws.InsertDataTable(people, op)

' Save Excel file.
ef.Save("Sample.xlsx")

I hope this helps.
Also just in case you're interested here is how you would insert the data together with the styling and formatting from the GridView control into an Excel file with GemBox.Spreadsheet: https://www.gemboxsoftware.com/support-center/kb/articles/export-gridview-and-or-datagrid-control-to-excel-file

Answer 3

Instead of using datagrid in asp.net application you can use asp gridview which is possible according below link

 https://www.gemboxsoftware.com/spreadsheet/examples/asp-net-excel-export/5101