ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Export multiple table record using join query to Excel and merge duplicate rows using C# and VB.Net in ASP.Net <p>Hi <a class="username" rel="PRA"> PRA</a>,</p> <p>I am working on it once done will get back to you.</p>https://www.aspforums.net:443/Threads/213037/Export-multiple-table-record-using-join-query-to-Excel-and-merge-duplicate-rows-using-C-and-VBNet-in-ASPNet/https://www.aspforums.net:443/Threads/213037/Export-multiple-table-record-using-join-query-to-Excel-and-merge-duplicate-rows-using-C-and-VBNet-in-ASPNet/Thu, 20 Jul 2017 03:09:19 GMTExport multiple table record using join query to Excel and merge duplicate rows using C# and VB.Net in ASP.Net <p>Hi <a class="username" rel="PRA"> PRA</a>,</p> <p>Please refer the below code. You need to get the result using join query to datatable.</p> <p><span style="text-decoration: underline;">C#</span></p> <pre class="brush: csharp">protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataTable dt = new DataTable(); dt.Columns.Add(&#34;Id&#34;); dt.Columns.Add(&#34;Name&#34;); dt.Columns.Add(&#34;Fruits&#34;); dt.Rows.Add(&#34;1&#34;, &#34;Rustam&#34;, &#34;Apple&#34;); dt.Rows.Add(&#34;2&#34;, &#34;Rustam&#34;, &#34;Orange&#34;); dt.Rows.Add(&#34;3&#34;, &#34;Rustam&#34;, &#34;Cherry&#34;); dt.Rows.Add(&#34;4&#34;, &#34;Rustam&#34;, &#34;Apricot&#34;); dt.Rows.Add(&#34;5&#34;, &#34;Firuz&#34;, &#34;Orange&#34;); dt.Rows.Add(&#34;6&#34;, &#34;Firuz&#34;, &#34;Cherry&#34;); dt.Rows.Add(&#34;7&#34;, &#34;Firuz&#34;, &#34;Limon&#34;); dt.Rows.Add(&#34;8&#34;, &#34;Firuz&#34;, &#34;Apricot&#34;); dt.Rows.Add(&#34;9&#34;, &#34;Asror&#34;, &#34;Limon&#34;); dt.Rows.Add(&#34;10&#34;, &#34;Asror&#34;, &#34;Apricot&#34;); if (dt.Rows.Count &gt; 0) { string path = Server.MapPath(&#34;exportedfiles\\&#34;); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } File.Delete(path + &#34;MutilationSheet.xlsx&#34;); Excel.Application xlAppToExport = new Excel.Application(); xlAppToExport.Workbooks.Add(&#34;&#34;); Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet); xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets[&#34;Sheet1&#34;]; int iRowCnt = 6; Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range; xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = &#34;Id&#34;; xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = &#34;Name&#34;; xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = &#34;Fruits&#34;; int i; string prename = string.Empty; for (i = 0; i &lt;= dt.Rows.Count - 1; i++) { xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field&lt;string&gt;(&#34;Id&#34;); xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field&lt;string&gt;(&#34;Name&#34;) != prename ? dt.Rows[i].Field&lt;string&gt;(&#34;Name&#34;) : &#34;&#34;; xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field&lt;string&gt;(&#34;Fruits&#34;); prename = dt.Rows[i].Field&lt;string&gt;(&#34;Name&#34;); iRowCnt = iRowCnt + 1; } xlWorkSheetToExport.SaveAs(path + &#34;MutilationSheet.xlsx&#34;); xlAppToExport.Workbooks.Close(); xlAppToExport.Quit(); xlAppToExport = null; xlWorkSheetToExport = null; } } }</pre> <p><span style="text-decoration: underline;">Vb.Net</span></p> <pre class="brush: vb">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(&#34;Id&#34;) dt.Columns.Add(&#34;Name&#34;) dt.Columns.Add(&#34;Fruits&#34;) dt.Rows.Add(&#34;1&#34;, &#34;Rustam&#34;, &#34;Apple&#34;) dt.Rows.Add(&#34;2&#34;, &#34;Rustam&#34;, &#34;Orange&#34;) dt.Rows.Add(&#34;3&#34;, &#34;Rustam&#34;, &#34;Cherry&#34;) dt.Rows.Add(&#34;4&#34;, &#34;Rustam&#34;, &#34;Apricot&#34;) dt.Rows.Add(&#34;5&#34;, &#34;Firuz&#34;, &#34;Orange&#34;) dt.Rows.Add(&#34;6&#34;, &#34;Firuz&#34;, &#34;Cherry&#34;) dt.Rows.Add(&#34;7&#34;, &#34;Firuz&#34;, &#34;Limon&#34;) dt.Rows.Add(&#34;8&#34;, &#34;Firuz&#34;, &#34;Apricot&#34;) dt.Rows.Add(&#34;9&#34;, &#34;Asror&#34;, &#34;Limon&#34;) dt.Rows.Add(&#34;10&#34;, &#34;Asror&#34;, &#34;Apricot&#34;) If dt.Rows.Count &gt; 0 Then Dim path As String = Server.MapPath(&#34;exportedfiles\&#34;) If Not Directory.Exists(path) Then Directory.CreateDirectory(path) End If File.Delete(path &amp; Convert.ToString(&#34;MutilationSheet.xlsx&#34;)) Dim xlAppToExport As New Excel.Application() xlAppToExport.Workbooks.Add(&#34;&#34;) Dim xlWorkSheetToExport As Excel.Worksheet = Nothing xlWorkSheetToExport = DirectCast(xlAppToExport.Sheets(&#34;Sheet1&#34;), Excel.Worksheet) Dim iRowCnt As Integer = 6 Dim range As Excel.Range = TryCast(xlWorkSheetToExport.Cells(1, 1), Excel.Range) xlWorkSheetToExport.Cells(iRowCnt - 1, 1) = &#34;Id&#34; xlWorkSheetToExport.Cells(iRowCnt - 1, 2) = &#34;Name&#34; xlWorkSheetToExport.Cells(iRowCnt - 1, 3) = &#34;Fruits&#34; 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)(&#34;Id&#34;) xlWorkSheetToExport.Cells(iRowCnt, 2) = If(dt.Rows(i).Field(Of String)(&#34;Name&#34;) &lt;&gt; prename, dt.Rows(i).Field(Of String)(&#34;Name&#34;), &#34;&#34;) xlWorkSheetToExport.Cells(iRowCnt, 3) = dt.Rows(i).Field(Of String)(&#34;Fruits&#34;) prename = dt.Rows(i).Field(Of String)(&#34;Name&#34;) iRowCnt = iRowCnt + 1 Next xlWorkSheetToExport.SaveAs(path &amp; Convert.ToString(&#34;MutilationSheet.xlsx&#34;)) xlAppToExport.Workbooks.Close() xlAppToExport.Quit() xlAppToExport = Nothing xlWorkSheetToExport = Nothing End If End If End Sub</pre> <p><span style="text-decoration: underline;">Output</span></p> <table border="1"> <tbody> <tr> <td>Id</td> <td>Name</td> <td>Fruits</td> </tr> <tr> <td>1</td> <td>Rustam</td> <td>Apple</td> </tr> <tr> <td>2</td> <td>&nbsp;</td> <td>Orange</td> </tr> <tr> <td>3</td> <td>&nbsp;</td> <td>Cherry</td> </tr> <tr> <td>4</td> <td>&nbsp;</td> <td>Apricot</td> </tr> <tr> <td>5</td> <td>Firuz</td> <td>Orange</td> </tr> <tr> <td>6</td> <td>&nbsp;</td> <td>Cherry</td> </tr> <tr> <td>7</td> <td>&nbsp;</td> <td>Limon</td> </tr> <tr> <td>8</td> <td>&nbsp;</td> <td>Apricot</td> </tr> <tr> <td>9</td> <td>Asror</td> <td>Limon</td> </tr> <tr> <td>10</td> <td>&nbsp;</td> <td>Apricot</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/213037/Export-multiple-table-record-using-join-query-to-Excel-and-merge-duplicate-rows-using-C-and-VBNet-in-ASPNet/https://www.aspforums.net:443/Threads/213037/Export-multiple-table-record-using-join-query-to-Excel-and-merge-duplicate-rows-using-C-and-VBNet-in-ASPNet/Fri, 21 Jul 2017 06:39:35 GMT