Create pivot table in excel using VB.Net and C# in ASP.Net

Hi hemma123,

Please refer the below code. You need to modify as per your requirement.

C#

private void GeData()
{
    string connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=192.168.0.1\SQL2005;DataBase=Test;UID=sa;PWD=pass@123";
    string command = "SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5  FROM  PivotData";
    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("A1", "E10"));
    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, "PivotTable1", Type.Missing, Type.Missing);
    pivotTable.SmallGrid = false;
    pivotTable.ShowTableStyleRowStripes = true;
    pivotTable.TableStyle2 = "PivotStyleLight1";
    Excel.PivotFields rowField = (Excel.PivotFields)pivotTable.PivotFields(Type.Missing);
    int fieldCount = rowField.Count;

    for (int i = 1; i <= fieldCount; i++)
    {
        if ("Colunm" + i != "Colunm2" && "Colunm" + i != "Colunm5")
        {
            Excel.PivotField field = (Excel.PivotField)pivotTable.PivotFields("Column" + i);
            field.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
        }
    }

    pivotTable.AddDataField(pivotTable.PivotFields("Column4"), "Sum of Column4", Excel.XlConsolidationFunction.xlSum);
}

Vb.Net

    Private Sub GeData()
        Dim connection As String = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Server=.\SQL2005;DataBase=Test;UID=sa;PWD=pass@123"
        Dim command As String = "SELECT Column1,Column2,Column3,Column4,cast((Column5*1.00)/Column4 AS DECIMAL(16,2)) as Column5  FROM  PivotData"
        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("A1", "E10"), 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, "PivotTable1", Type.Missing, Type.Missing)
        pivotTable.SmallGrid = False
        pivotTable.ShowTableStyleRowStripes = True
        pivotTable.TableStyle2 = "PivotStyleLight1"
        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 "Colunm" & i <> "Colunm2" AndAlso "Colunm" & i <> "Colunm5" Then
                Dim field As Excel.PivotField = DirectCast(pivotTable.PivotFields("Column" & i), Excel.PivotField)
                field.Orientation = Excel.XlPivotFieldOrientation.xlRowField
            End If
        Next

        pivotTable.AddDataField(pivotTable.PivotFields("Column4"), "Sum of Column4", Excel.XlConsolidationFunction.xlSum)
    End Sub