My Blog

Creating/Formatting a Pivot Table

In continuation from here

Now we have learned how to create graph based on the data that we have on Excel. Now let’s try and create a pivot table based on the same data. The below code will create a pivot table in the same worksheet in Cell D1.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January" : .Range("A3").Value = "February"
            .Range("A4").Value = "March" : .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00" : .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00" : .Range("B5").Value = "1100.00"

            '~~> Set title for Total and Average Expenses
            .Range("A6").Value = "Total Expense"
            .Range("A7").Value = "Average Expense"

            '~~> Insert formulas
            .Range("B6").Formula = "=Sum(B2:B5)"
            .Range("B7").Formula = "=Average(B2:B5)"

            '~~> Change the range into a tabular format
            .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

            '~~> Format the table
            .ListObjects("Table1").TableStyle = "TableStyleLight8"

            '~~> Format the Total and Average Expenses cells
            With .Range("A6:A7")
                .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
                With .Font
                    .ColorIndex = 2 '<~~ Font Color White
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With

            '~~> Autofitting text in columns

            Dim wbname As String = xlWorkBook.Name

            '~~> Create and setup a pivot cache
            Dim ptCache As Excel.PivotCache = xlWorkBook.PivotCaches.Add( _
            SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:= _

            '~~> Create a pivot table
            Dim ptTable As Excel.PivotTable = .PivotTables.add(PivotCache:=ptCache, _
            TableDestination:=.Range("D1"), TableName:="My_Pivot_Table")

            '~~> Setup/Format the pivot table
            With ptTable
                .ManualUpdate = True
                '~~> Putting month in the Row Field
                .PivotFields("Month").Orientation = Excel.XlPivotFieldOrientation.xlRowField
                '~~> Select a formatting for the pivot
                .ManualUpdate = False
            End With

            '~~> Sum the amount spent
            ptTable.AddDataField(.PivotTables("My_Pivot_Table").PivotFields("Money Spent"), _
            "Sum of Money Spent", Excel.XlConsolidationFunction.xlSum)

            '~~> Format the Money Spent in the pivot to reflect in $
            ptTable.PivotFields("Sum of Money Spent").NumberFormat = "$#,##0.00"

        End With
    End Sub

When you run the data, the pivot will look like this.

Creating-Formatting a Pivot Table

You can change the way your pivot table looks by changing the xlReport1 in