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?