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

PRA
 
on Jul 20, 2017 03:08 AM
Sample_213037.zip
2983 Views

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

Download FREE API for Word, Excel and PDF in ASP.Net: Download
AnandM
 
on Jul 20, 2017 03:09 AM

Hi PRA,

I am working on it once done will get back to you.

Indresh
 
on Jul 21, 2017 06:39 AM

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