Export multiple table record using join query to Excel and merge duplicate rows using C# and VB.Net in ASP.Net

Hi PRA,

Please refer the below code. You need to get the result using join query to datatable.

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Id");
        dt.Columns.Add("Name");
        dt.Columns.Add("Fruits");
        dt.Rows.Add("1", "Rustam", "Apple");
        dt.Rows.Add("2", "Rustam", "Orange");
        dt.Rows.Add("3", "Rustam", "Cherry");
        dt.Rows.Add("4", "Rustam", "Apricot");
        dt.Rows.Add("5", "Firuz", "Orange");
        dt.Rows.Add("6", "Firuz", "Cherry");
        dt.Rows.Add("7", "Firuz", "Limon");
        dt.Rows.Add("8", "Firuz", "Apricot");
        dt.Rows.Add("9", "Asror", "Limon");
        dt.Rows.Add("10", "Asror", "Apricot");

        if (dt.Rows.Count > 0)
        {
            string path = Server.MapPath("exportedfiles\\");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            File.Delete(path + "MutilationSheet.xlsx");
            Excel.Application xlAppToExport = new Excel.Application();
            xlAppToExport.Workbooks.Add("");
            Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
            xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
            int iRowCnt = 6;
            Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range;
            xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Id";
            xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Name";
            xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "Fruits";
            int i;
            string prename = string.Empty;
            for (i = 0; i <= dt.Rows.Count - 1; i++)
            {
                xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field<string>("Id");
                xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field<string>("Name") != prename ? dt.Rows[i].Field<string>("Name") : "";
                xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field<string>("Fruits");
                prename = dt.Rows[i].Field<string>("Name");
                iRowCnt = iRowCnt + 1;
            }

            xlWorkSheetToExport.SaveAs(path + "MutilationSheet.xlsx");
            xlAppToExport.Workbooks.Close();
            xlAppToExport.Quit();
            xlAppToExport = null;
            xlWorkSheetToExport = null;
        }
    }
}

Vb.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
	If Not IsPostBack Then
		Dim dt As New DataTable()
		dt.Columns.Add("Id")
		dt.Columns.Add("Name")
		dt.Columns.Add("Fruits")
		dt.Rows.Add("1", "Rustam", "Apple")
		dt.Rows.Add("2", "Rustam", "Orange")
		dt.Rows.Add("3", "Rustam", "Cherry")
		dt.Rows.Add("4", "Rustam", "Apricot")
		dt.Rows.Add("5", "Firuz", "Orange")
		dt.Rows.Add("6", "Firuz", "Cherry")
		dt.Rows.Add("7", "Firuz", "Limon")
		dt.Rows.Add("8", "Firuz", "Apricot")
		dt.Rows.Add("9", "Asror", "Limon")
		dt.Rows.Add("10", "Asror", "Apricot")

		If dt.Rows.Count > 0 Then
			Dim path As String = Server.MapPath("exportedfiles\")
			If Not Directory.Exists(path) Then
				Directory.CreateDirectory(path)
			End If
			File.Delete(path & Convert.ToString("MutilationSheet.xlsx"))
			Dim xlAppToExport As New Excel.Application()
			xlAppToExport.Workbooks.Add("")
			Dim xlWorkSheetToExport As Excel.Worksheet = Nothing
			xlWorkSheetToExport = DirectCast(xlAppToExport.Sheets("Sheet1"), Excel.Worksheet)
			Dim iRowCnt As Integer = 6
			Dim range As Excel.Range = TryCast(xlWorkSheetToExport.Cells(1, 1), Excel.Range)
			xlWorkSheetToExport.Cells(iRowCnt - 1, 1) = "Id"
			xlWorkSheetToExport.Cells(iRowCnt - 1, 2) = "Name"
			xlWorkSheetToExport.Cells(iRowCnt - 1, 3) = "Fruits"
			Dim i As Integer
			Dim prename As String = String.Empty
			For i = 0 To dt.Rows.Count - 1
				xlWorkSheetToExport.Cells(iRowCnt, 1) = dt.Rows(i).Field(Of String)("Id")
				xlWorkSheetToExport.Cells(iRowCnt, 2) = If(dt.Rows(i).Field(Of String)("Name") <> prename, dt.Rows(i).Field(Of String)("Name"), "")
				xlWorkSheetToExport.Cells(iRowCnt, 3) = dt.Rows(i).Field(Of String)("Fruits")
				prename = dt.Rows(i).Field(Of String)("Name")
				iRowCnt = iRowCnt + 1
			Next

			xlWorkSheetToExport.SaveAs(path & Convert.ToString("MutilationSheet.xlsx"))
			xlAppToExport.Workbooks.Close()
			xlAppToExport.Quit()
			xlAppToExport = Nothing
			xlWorkSheetToExport = Nothing
		End If
	End If
End Sub

Output