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.Create pivot table in excel using VB.Net and C# in ASP.Net<p>Hi <a class="username" rel="hemma123">hemma123</a>,</p> <p>Please refer the below code. You need to modify as per your requirement.</p> <p><span style="text-decoration: underline;">C#</span></p> <pre class="brush: csharp">private void GeData() { string connection = @&#34;OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=192.168.0.1\SQL2005;DataBase=Test;UID=sa;PWD=pass@123&#34;; string command = &#34;SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5 FROM PivotData&#34;; Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Type.Missing); Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; Excel.PivotCache pivotCache = app.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, (Excel.Range)sheet.get_Range(&#34;A1&#34;, &#34;E10&#34;)); pivotCache.Connection = connection; pivotCache.MaintainConnection = true; pivotCache.CommandText = command; pivotCache.CommandType = Excel.XlCmdType.xlCmdSql; Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Type.Missing); Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, app.ActiveCell, &#34;PivotTable1&#34;, Type.Missing, Type.Missing); pivotTable.SmallGrid = false; pivotTable.ShowTableStyleRowStripes = true; pivotTable.TableStyle2 = &#34;PivotStyleLight1&#34;; Excel.PivotFields rowField = (Excel.PivotFields)pivotTable.PivotFields(Type.Missing); int fieldCount = rowField.Count; for (int i = 1; i &lt;= fieldCount; i++) { if (&#34;Colunm&#34; + i != &#34;Colunm2&#34; &amp;&amp; &#34;Colunm&#34; + i != &#34;Colunm5&#34;) { Excel.PivotField field = (Excel.PivotField)pivotTable.PivotFields(&#34;Column&#34; + i); field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; } } pivotTable.AddDataField(pivotTable.PivotFields(&#34;Column4&#34;), &#34;Sum of Column4&#34;, Excel.XlConsolidationFunction.xlSum); }</pre> <p><span style="text-decoration: underline;">Vb.Net</span></p> <pre class="brush: vb"> Private Sub GeData() Dim connection As String = &#34;OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123&#34; Dim command As String = &#34;SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5 FROM PivotData&#34; Dim app As Excel.Application = New Microsoft.Office.Interop.Excel.Application() Dim workbook As Excel.Workbook = DirectCast(app.Workbooks.Add(Type.Missing), Microsoft.Office.Interop.Excel.Workbook) Dim sheet As Excel.Worksheet = DirectCast(workbook.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet) Dim pivotCache As Excel.PivotCache = app.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, DirectCast(sheet.Range(&#34;A1&#34;, &#34;E10&#34;), Excel.Range)) pivotCache.Connection = connection pivotCache.MaintainConnection = True pivotCache.CommandText = command pivotCache.CommandType = Excel.XlCmdType.xlCmdSql Dim pivotTables As Excel.PivotTables = DirectCast(sheet.PivotTables(Type.Missing), Excel.PivotTables) Dim pivotTable As Excel.PivotTable = pivotTables.Add(pivotCache, app.ActiveCell, &#34;PivotTable1&#34;, Type.Missing, Type.Missing) pivotTable.SmallGrid = False pivotTable.ShowTableStyleRowStripes = True pivotTable.TableStyle2 = &#34;PivotStyleLight1&#34; Dim rowField As Excel.PivotFields = DirectCast(pivotTable.PivotFields(Type.Missing), Excel.PivotFields) Dim fieldCount As Integer = rowField.Count For i As Integer = 1 To fieldCount If &#34;Colunm&#34; &amp; i &lt;&gt; &#34;Colunm2&#34; AndAlso &#34;Colunm&#34; &amp; i &lt;&gt; &#34;Colunm5&#34; Then Dim field As Excel.PivotField = DirectCast(pivotTable.PivotFields(&#34;Column&#34; &amp; i), Excel.PivotField) field.Orientation = Excel.XlPivotFieldOrientation.xlRowField End If Next pivotTable.AddDataField(pivotTable.PivotFields(&#34;Column4&#34;), &#34;Sum of Column4&#34;, Excel.XlConsolidationFunction.xlSum) End Sub</pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/202738/Create-pivot-table-in-excel-using-VBNet-and-C-in-ASPNet/https://www.aspforums.net:443/Threads/202738/Create-pivot-table-in-excel-using-VBNet-and-C-in-ASPNet/Fri, 11 Aug 2017 08:22:01 GMT