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

Last Reply one year ago By Indresh

Posted one year ago

Hi! I have three table. Person , fruits and boughttable.

Id

Name

1

Rustam

2

Firuz

3

Asror

4

Nurullo

 

Id

Name

1

Apple

2

Orange

3

Cherry

4

Limon

5

Apricot

 

Id

PersonId

FruitId

1

1

2

2

1

1

3

2

3

4

2

5

5

1

3

6

1

5

7

2

4

8

2

2

9

3

3

10

3

5

11

4

1

12

4

4

 

Using Asp.Net show result in excel. For example:

Id

Name

Fruits

1

Rustam

Apple

2

 

Orange

3

 

Cherry

4

 

Apricot

5

Firuz

Orange

6

 

Cherry

7

 

Limon

8

 

Apricot

9

Asror

Limon

10

 

Apricot

Posted one year ago

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

Id Name Fruits
1 Rustam Apple
2   Orange
3   Cherry
4   Apricot
5 Firuz Orange
6   Cherry
7   Limon
8   Apricot
9 Asror Limon
10   Apricot