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

Last Reply one year ago By Indresh

Posted one year ago

Hi,

Can I get sample of code for create pivot table in excel (data from external) using vb.net?

Create based on SQL database.

Your help highly appreciated. Thanks

Posted one year ago

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