VB.NET - Create Excel file with Pivot Table using VB.Net

In this example I create Excel file with 2 sheets
- one with data in table format
- another with pivot table

This code can be copied and pasted, don't forget to add Excel reference -


Of course, Excel must be installed on the computer that will run this code.


    Dim fileTest As String = "C:\Temp\ExcelTest\testPivot.xlsx"
        If File.Exists(fileTest) Then
            File.Delete(fileTest) ' oh, file is still open
        End If

        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        oBook = oExcel.Workbooks.Add
        oSheet = oExcel.Worksheets(1)

        oSheet.Name = "Report"
        oSheet.Range("A1").Value = "First Name"
        oSheet.Range("B1").Value = "Year"
        oSheet.Range("C1").Value = "Salary"

        oSheet.Range("A2").Value = "Frank"
        oSheet.Range("B2").Value = "2012"
        oSheet.Range("C2").Value = "30000"

        oSheet.Range("A3").Value = "Frank"
        oSheet.Range("B3").Value = "2011"
        oSheet.Range("C3").Value = "25000"

        oSheet.Range("A4").Value = "Ann"
        oSheet.Range("B4").Value = "2011"
        oSheet.Range("C4").Value = "55000"

        oSheet.Range("A5").Value = "Ann"
        oSheet.Range("B5").Value = "2012"
        oSheet.Range("C5").Value = "35000"

        oSheet.Range("A6").Value = "Ann"
        oSheet.Range("B6").Value = "2010"
        oSheet.Range("C6").Value = "35000"

        ' OK, at this point we have Excel file with 1 sheet with data
        ' Now let's create pivot table

        ' first get range of cells from sheet 1 that will be used by pivot
        Dim xlRange As Excel.Range = CType(oSheet, Excel.Worksheet).Range("A1:C6")

        ' create second sheet
        If oExcel.Application.Sheets.Count() < 2 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(2)
        End If
        oSheet.Name = "Pivot Table"

        ' specify first cell for pivot table on the second sheet
        Dim xlRange2 As Excel.Range = CType(oSheet, Excel.Worksheet).Range("B3")

        ' Create pivot cache and table
        Dim ptCache As Excel.PivotCache = oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)
        Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:="Summary")

        ' create Pivot Field, note that pivot field name is the same as column name in sheet 1
        Dim ptField As Excel.PivotField = ptTable.PivotFields("Salary")
        With ptField
            .Orientation = Excel.XlPivotFieldOrientation.xlDataField
            .Function = Excel.XlConsolidationFunction.xlSum
            .Name = " Salary" ' by default name will be something like SumOfSalary, change it here to Salary, note space in front of it -
            ' this field name cannot be the same as therefore that space
            ' also it cannot be empty

            '' add another field
            'ptField = ptTable.PivotFields("Year")
            'With ptField
            '    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
            '    .Function = Excel.XlConsolidationFunction.xlMax
            '    .Name = " Year" ' this is how you create another field, in my example I don't need it so let's comment it out
            'End With

            ' add column
            ptField = ptTable.PivotFields("First Name")
            With ptField
                .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                .Name = " "
            End With

        End With
        ' add grouping - again I don't need this in my example, this is just to show how to do it
        'oSheet.Range("C5").Group(1, 20, 40)

        oBook.SaveAs(fileTest)
        oBook.Close()
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing

same example on Youtube - part 1


same example on Youtube - part 2