Hi hafifiw,
Refer below code.
Namespaces
C#
using System.Data;
VB.Net
Imports System.Data
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
DataTable dtExcelFamily = new DataTable();
dtExcelFamily.Columns.Add("ID");
dtExcelFamily.Columns.Add("Name");
dtExcelFamily.Columns.Add("Date");
dtExcelFamily.Columns.Add("Place");
dtExcelFamily.Columns.Add("Father");
dtExcelFamily.Columns.Add("Mother");
dtExcelFamily.Rows.Add(5561, "Ali ", "10/10/1966", "Singapore", "Ahmed", "Nur");
dtExcelFamily.Rows.Add(5561, "Ali ", "10/10/1966", "Singapore", "Ahmed", "Nur");
dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei");
dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei");
dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei");
dtExcelFamily.Rows.Add(1920, "Mill", "1/4/1985", "Malaysia", "Zoey", "Lian");
dtExcelFamily.Rows.Add(1920, "Mill", "1/5/1985", "Malaysia", "Zoey", "Lian");
dtExcelFamily.Rows.Add(1920, "Mill", "1/6/1985", "Malaysia", "Zoey", "Lian");
dtExcelFamily.Rows.Add(1920, "Mill", "1/7/1985", "Malaysia", "Zoey", "Lian");
DataTable dtExcelFamilyDetails = new DataTable();
dtExcelFamilyDetails.Columns.Add("ID");
dtExcelFamilyDetails.Columns.Add("ChildName");
dtExcelFamilyDetails.Columns.Add("ChildAge");
dtExcelFamilyDetails.Rows.Add(5561, "Joe", 12);
dtExcelFamilyDetails.Rows.Add(5561, "Zac", 17);
dtExcelFamilyDetails.Rows.Add(9910, "Trichia", 21);
dtExcelFamilyDetails.Rows.Add(9910, "Efrin", 29);
dtExcelFamilyDetails.Rows.Add(9910, "Jack", 18);
dtExcelFamilyDetails.Rows.Add(1920, "Nene", 9);
dtExcelFamilyDetails.Rows.Add(1920, "Piney", 5);
dtExcelFamilyDetails.Rows.Add(1920, "Rio", 3);
dtExcelFamilyDetails.Rows.Add(1920, "Tini", 1);
DataTable dt = CombileDataTable(dtExcelFamily, dtExcelFamilyDetails);
string csv = string.Empty;
foreach (DataColumn column in dt.Columns)
{
csv += column.ColumnName + ',';
}
csv += "\r\n";
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
csv += row[column.ColumnName].ToString().Replace(",", ";") + ',';
}
csv += "\r\n";
}
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=DataTableExport.csv");
Response.Charset = "";
Response.ContentType = "application/text";
Response.Output.Write(csv);
Response.Flush();
Response.End();
}
public DataTable CombileDataTable(DataTable dt1, DataTable dt2)
{
DataTable dtNew = new DataTable();
foreach (DataColumn col in dt1.Columns)
{
dtNew.Columns.Add(col.ColumnName);
}
foreach (DataColumn col in dt2.Columns)
{
if (!dtNew.Columns.Contains(col.ColumnName))
{
dtNew.Columns.Add(col.ColumnName);
}
}
for (int i = 0; i < dt1.Rows.Count; i++)
{
DataRow dr = dtNew.NewRow();
foreach (DataColumn col in dt1.Columns)
{
dr[col.ColumnName] = dt1.Rows[i][col.ColumnName];
}
foreach (DataColumn col in dt2.Columns)
{
dr[col.ColumnName] = dt2.Rows[i][col.ColumnName];
}
dtNew.Rows.Add(dr);
dtNew.AcceptChanges();
}
return dtNew;
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim dtExcelFamily As DataTable = New DataTable()
dtExcelFamily.Columns.Add("ID")
dtExcelFamily.Columns.Add("Name")
dtExcelFamily.Columns.Add("Date")
dtExcelFamily.Columns.Add("Place")
dtExcelFamily.Columns.Add("Father")
dtExcelFamily.Columns.Add("Mother")
dtExcelFamily.Rows.Add(5561, "Ali ", "10/10/1966", "Singapore", "Ahmed", "Nur")
dtExcelFamily.Rows.Add(5561, "Ali ", "10/10/1966", "Singapore", "Ahmed", "Nur")
dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei")
dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei")
dtExcelFamily.Rows.Add(9910, "Rick", "12/1/1951", "Thailand", "Lee", "Mei")
dtExcelFamily.Rows.Add(1920, "Mill", "1/4/1985", "Malaysia", "Zoey", "Lian")
dtExcelFamily.Rows.Add(1920, "Mill", "1/5/1985", "Malaysia", "Zoey", "Lian")
dtExcelFamily.Rows.Add(1920, "Mill", "1/6/1985", "Malaysia", "Zoey", "Lian")
dtExcelFamily.Rows.Add(1920, "Mill", "1/7/1985", "Malaysia", "Zoey", "Lian")
Dim dtExcelFamilyDetails As DataTable = New DataTable()
dtExcelFamilyDetails.Columns.Add("ID")
dtExcelFamilyDetails.Columns.Add("ChildName")
dtExcelFamilyDetails.Columns.Add("ChildAge")
dtExcelFamilyDetails.Rows.Add(5561, "Joe", 12)
dtExcelFamilyDetails.Rows.Add(5561, "Zac", 17)
dtExcelFamilyDetails.Rows.Add(9910, "Trichia", 21)
dtExcelFamilyDetails.Rows.Add(9910, "Efrin", 29)
dtExcelFamilyDetails.Rows.Add(9910, "Jack", 18)
dtExcelFamilyDetails.Rows.Add(1920, "Nene", 9)
dtExcelFamilyDetails.Rows.Add(1920, "Piney", 5)
dtExcelFamilyDetails.Rows.Add(1920, "Rio", 3)
dtExcelFamilyDetails.Rows.Add(1920, "Tini", 1)
Dim dt As DataTable = CombileDataTable(dtExcelFamily, dtExcelFamilyDetails)
Dim csv As String = String.Empty
For Each column As DataColumn In dt.Columns
csv += column.ColumnName + ","c
Next
csv += vbCr & vbLf
For Each row As DataRow In dt.Rows
For Each column As DataColumn In dt.Columns
csv += row(column.ColumnName).ToString().Replace(",", ";") + ","c
Next
csv += vbCr & vbLf
Next
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=DataTableExport.csv")
Response.Charset = ""
Response.ContentType = "application/text"
Response.Output.Write(csv)
Response.Flush()
Response.End()
End Sub
Public Function CombileDataTable(ByVal dt1 As DataTable, ByVal dt2 As DataTable) As DataTable
Dim dtNew As DataTable = New DataTable()
For Each col As DataColumn In dt1.Columns
dtNew.Columns.Add(col.ColumnName)
Next
For Each col As DataColumn In dt2.Columns
If Not dtNew.Columns.Contains(col.ColumnName) Then
dtNew.Columns.Add(col.ColumnName)
End If
Next
For i As Integer = 0 To dt1.Rows.Count - 1
Dim dr As DataRow = dtNew.NewRow()
For Each col As DataColumn In dt1.Columns
dr(col.ColumnName) = dt1.Rows(i)(col.ColumnName)
Next
For Each col As DataColumn In dt2.Columns
dr(col.ColumnName) = dt2.Rows(i)(col.ColumnName)
Next
dtNew.Rows.Add(dr)
dtNew.AcceptChanges()
Next
Return dtNew
End Function
