HOW TO EXPORT A PARENT/CHILD DATAGRIDVIEW TO EXCEL?

See more:

VB

SQL

MS-Excel

For example , I have two tables in my database:

CustomerTable
Id - PK
Name
Age

OrderTable
OrderId - PK
Product
Qty
Id- FK


I wanted to export the data in excel. with the format like this:

---[new row]------1 Justin 24y/o
---[new row]----------------- 1 , squash , 3
---[new row]----------------- 2 , melon , 2
[space]
---[new row]------2 Kristy 34y/o
---[new row]-----------------4 , apple , 3
---[new row]----------------- 2 , papaya , 2
[space]


The only way I know in exporting is just like this:


---[new row]------1 Justin 24y/o 1 , squash , 3
---[new row]------1 Justin 24y/o 2 , melon , 2
---[new row]-------2 Kristy 34y/o, 4 , apple , 3
---[new row]--------2 Kristy 34y/o, 2 , papaya , 2

This is the code I am using:

On Pageload()

(JOIN QUERY HERE : Select Buyer_Table.*, Order_Table.*)
buyer_table.datasource = ds
buyer_table.databind()

If ((Buyer_TableDataGridView.Columns.Count = 0) Or (Buyer_TableDataGridView.Rows.Count = 0)) Then
         Exit Sub
     End If

Dim dset As New DataSet

     dset.Tables.Add()

  For i As Integer = 0 To Buyer_TableDataGridView.ColumnCount - 1
         dset.Tables(0).Columns.Add(Buyer_TableDataGridView.Columns(i).HeaderText)
     Next
     'add rows to the table
     Dim dr1 As DataRow
     For i As Integer = 0 To Buyer_TableDataGridView.RowCount - 1
         dr1 = dset.Tables(0).NewRow
         For j As Integer = 0 To Buyer_TableDataGridView.Columns.Count - 1
             dr1(j) = Buyer_TableDataGridView.Rows(i).Cells(j).Value
         Next
         '    dset.Tables(0).Rows.Add(dr1)
     Next


     Dim excel As New Microsoft.Office.Interop.Excel.Application
     Dim wBook As Microsoft.Office.Interop.Excel.Workbook
     Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

     wBook = excel.Workbooks.Add()
     wSheet = wBook.ActiveSheet()

     Dim dt As System.Data.DataTable = dset.Tables(0)
     Dim dc As System.Data.DataColumn
     Dim dr As System.Data.DataRow
     Dim colIndex As Integer = 0
     Dim rowIndex As Integer = 0

     For Each dc In dt.Columns
         colIndex = colIndex + 1
         excel.Cells(1, colIndex) = dc.ColumnName
     Next

     For Each dr In dt.Rows
         rowIndex = rowIndex + 1
         colIndex = 0
         For Each dc In dt.Columns
             colIndex = colIndex + 1
             excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

         Next
     Next

can someone has a useful code to make this kind of output in excel using two tables?