Creating PivotTables in Excel 2007

  • Article

Summary:  Learn how to programmatically add a PivotTable to an Excel 2007 worksheet.

Office Visual How To

Applies to:  2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Visual Studio 2008

Joel Krist, iSoftStone

May 2009

Overview

Excel PivotTables allow you to quickly and interactively summarize large amounts of data. PivotTables allow detailed data analysis and make it possible to answer unanticipated questions. This Visual How To illustrates using the Excel 2007 Primary Interop Assembly (PIA) and the Workbook.PivotTableWizard method to programmatically add a PivotTable to an Microsoft Office Excel 2007 worksheet.

 See It Creating Pivot Tables video splash screen

Watch the Video

Length: 04:22 | Size: 5.70 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Sample Code

This Visual How To walks through the following steps to illustrate the process of programmatically creating a PivotTable:

  1. Creating a Windows console application solution in Visual Studio 2008.

  2. Adding a reference to the Excel 2007 Primary Interop Assembly.

  3. Importing the Excel 2007 Primary Interop Assembly namespace.

  4. Adding the sample code to the solution.

Creating a Windows Console Application in Visual Studio 2008

This Visual How To uses a Windows console application as the framework for the sample code. The console application type was selected only for its simplicity. Other application types could use the same approach presented here.

To Create a Windows Console Application in Visual Studio 2008

  1. Start Microsoft Visual Studio 2008.

  2. From the Project menu, click File, then click New.

  3. In the New Project dialog box, click the Visual Basic Windows or Visual C# Windows type in the Project types pane.

  4. In the Templates pane, click Console Application.

  5. Name the project and solution CreatingPivotTable.

    Figure 1. Creating the Solution

    Creating the Solution

  6. Click OK to create the solution.

Adding a Reference to the Excel 2007 Primary Interop Assembly

This Visual How To uses the Excel 2007 Primary Interop Assembly (PIA) to create and manipulate an Excel workbook. The Excel 2007 Primary Interop Assembly exposes the Excel 2007 object model to managed code and is installed with Excel 2007. It is also available for download as part of the 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies

To Add a Reference to the Excel 2007 Primary Interop Assembly

  1. On the Add Reference menu, select the Visual Studio Project.

  2. In the Add Reference dialog box, select version 12.0.0.0 of the Microsoft.Office.Interop.Excel component and click OK to add the reference.

    Figure 2. Adding the Reference

    Adding the Reference

Importing the Excel 2007 Primary Interop Assembly Namespace

Importing the Excel 2007 PIA namespace allows code to work with the objects and types defined in the namespace without specifying the fully qualified namespace path each time.

For a Visual Basic project, add the following line to the top of the Module1.vb source file.

Imports Microsoft.Office.Interop.Excel

For a C# project, add the following line to the top of the Program.cs source file below the other using statements generated by Visual Studio.

using Microsoft.Office.Interop.Excel;

Adding the Sample Code to the Solution

For a Visual Basic project, replace the contents of the Module1 module in the Module1.vb source file with the following code.

Sub SetCellValue(ByVal targetSheet As Worksheet, _
    ByVal cell As String, ByVal value As Object)
    targetSheet.Range(cell).Value = value
End Sub

Sub Main()
    ' Declare variables that hold references to Excel objects.
    Dim excelApplication As ApplicationClass = Nothing
    Dim excelWorkBook As Workbook = Nothing
    Dim targetSheet As Worksheet = Nothing
    Dim pivotTable As PivotTable = Nothing
    Dim pivotData As Range = Nothing
    Dim pivotDestination As Range = Nothing
    Dim salesRegion As PivotField = Nothing
    Dim salesAmount As PivotField = Nothing

    ' Declare helper variables.
    Dim workBookName As String = "C:\temp\pivottablesample.xlsx"
    Dim pivotTableName As String = "Sales By Region"
    Dim workSheetName As String = "Quarterly Sales"

    ' Create an instance of Excel.
    excelApplication = New ApplicationClass()

    Try
        ' Create a workbook.
        excelWorkBook = _
        excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
        targetSheet = excelWorkBook.Worksheets(1)
        targetSheet.Name = workSheetName

        ' Add Data to the sheet.
        SetCellValue(targetSheet, "A1", "Sales Person")
        SetCellValue(targetSheet, "B1", "Region")
        SetCellValue(targetSheet, "C1", "Sales Amount")
        SetCellValue(targetSheet, "A2", "Joe")
        SetCellValue(targetSheet, "B2", "North")
        SetCellValue(targetSheet, "C2", "260")
        SetCellValue(targetSheet, "A3", "Robert")
        SetCellValue(targetSheet, "B3", "South")
        SetCellValue(targetSheet, "C3", "660")
        SetCellValue(targetSheet, "A4", "Michelle")
        SetCellValue(targetSheet, "B4", "East")
        SetCellValue(targetSheet, "C4", "940")
        SetCellValue(targetSheet, "A5", "Erich")
        SetCellValue(targetSheet, "B5", "West")
        SetCellValue(targetSheet, "C5", "410")
        SetCellValue(targetSheet, "A6", "Dafna")
        SetCellValue(targetSheet, "B6", "North")
        SetCellValue(targetSheet, "C6", "800")
        SetCellValue(targetSheet, "A7", "Rob")
        SetCellValue(targetSheet, "B7", "South")
        SetCellValue(targetSheet, "C7", "900")

        ' Select a range of data for the Pivot Table.
        pivotData = targetSheet.Range("A1", "C7")

        ' Select location of the Pivot Table.
        pivotDestination = targetSheet.Range("A11")

        ' Add a pivot table to the worksheet.
        excelWorkBook.PivotTableWizard( _
            XlPivotTableSourceType.xlDatabase, _
            pivotData, pivotDestination, pivotTableName, True, True, _
            True, True, , , False, False, XlOrder.xlDownThenOver, 0)

        ' Set variables for used to manipulate the Pivot Table.
        pivotTable = targetSheet.PivotTables(pivotTableName)
        salesRegion = pivotTable.PivotFields(2)
        salesAmount = pivotTable.PivotFields(3)

        ' Format the Pivot Table.
        pivotTable.Format(XlPivotFormatType.xlReport2)
        pivotTable.InGridDropZones = False

        ' Set Sales Region as a Row Field.
        salesRegion.Orientation = XlPivotFieldOrientation.xlRowField

        ' Set Sum of Sales Amount as a Value Field.
        salesAmount.Orientation = XlPivotFieldOrientation.xlDataField
        salesAmount.Function = XlConsolidationFunction.xlSum

        ' Save the Workbook.
        excelWorkBook.SaveAs(workBookName, , , , , , _
                             XlSaveAsAccessMode.xlNoChange)
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    Finally
        ' Release the references to the Excel objects.
        salesAmount = Nothing
        salesRegion = Nothing
        pivotDestination = Nothing
        pivotData = Nothing
        pivotTable = Nothing
        targetSheet = Nothing

        ' Release the Workbook object.
        If Not excelWorkBook Is Nothing Then
            excelWorkBook = Nothing
        End If

        ' Release the ApplicationClass object.
        If Not excelApplication Is Nothing Then
            excelApplication.Quit()
            excelApplication = Nothing
        End If

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try
End Sub

For a C# project, replace the contents of the Program class in the Program.cs source file with the following code.

static object useDefault = Type.Missing;

/// <summary>
/// Helper method to set a value on a single cell.
/// </summary>
static void SetCellValue(Worksheet targetSheet, string cell,
    object value)
{
    targetSheet.get_Range(cell, useDefault).set_Value(
        XlRangeValueDataType.xlRangeValueDefault, value);
}

static void Main(string[] args)
{
    // Declare variables that hold references to excel objects.
    ApplicationClass excelApplication = null;
    Workbook excelWorkBook = null;
    Worksheet targetSheet = null;
    PivotTable pivotTable = null;
    Range pivotData = null;
    Range pivotDestination = null;
    PivotField salesRegion = null;
    PivotField salesAmount = null;

    // Declare helper variables.
    string workBookName = @"C:\temp\pivottablesample.xlsx";
    string pivotTableName = @"Sales By Region";
    string workSheetName = @"Quarterly Sales";

    try
    {
        // Create an instance of Excel.
        excelApplication = new ApplicationClass();

        //Create a workbook and add a worksheet.
        excelWorkBook = excelApplication.Workbooks.Add(
            XlWBATemplate.xlWBATWorksheet);
        targetSheet = (Worksheet)(excelWorkBook.Worksheets[1]);
        targetSheet.Name = workSheetName;

        // Add Data to the Worksheet.
        SetCellValue(targetSheet, "A1", "Sales Person");
        SetCellValue(targetSheet, "B1", "Region");
        SetCellValue(targetSheet, "C1", "Sales Amount");
        SetCellValue(targetSheet, "A2", "Joe");
        SetCellValue(targetSheet, "B2", "North");
        SetCellValue(targetSheet, "C2", "260");
        SetCellValue(targetSheet, "A3", "Robert");
        SetCellValue(targetSheet, "B3", "South");
        SetCellValue(targetSheet, "C3", "660");
        SetCellValue(targetSheet, "A4", "Michelle");
        SetCellValue(targetSheet, "B4", "East");
        SetCellValue(targetSheet, "C4", "940");
        SetCellValue(targetSheet, "A5", "Erich");
        SetCellValue(targetSheet, "B5", "West");
        SetCellValue(targetSheet, "C5", "410");
        SetCellValue(targetSheet, "A6", "Dafna");
        SetCellValue(targetSheet, "B6", "North");
        SetCellValue(targetSheet, "C6", "800");
        SetCellValue(targetSheet, "A7", "Rob");
        SetCellValue(targetSheet, "B7", "South");
        SetCellValue(targetSheet, "C7", "900");

        // Select a range of data for the Pivot Table.
        pivotData = targetSheet.get_Range("A1", "C7");

        // Select location of the Pivot Table.
        pivotDestination = targetSheet.get_Range("A11", useDefault);

        // Add a Pivot Table to the Worksheet.
        excelWorkBook.PivotTableWizard(
            XlPivotTableSourceType.xlDatabase,
            pivotData,
            pivotDestination,
            pivotTableName,
            true,
            true,
            true,
            true,
            useDefault,
            useDefault,
            false,
            false,
            XlOrder.xlDownThenOver,
            0,
            useDefault,
            useDefault
            );

        // Set variables for used to manipulate the Pivot Table.
        pivotTable =
            (PivotTable)targetSheet.PivotTables(pivotTableName);
        salesRegion = ((PivotField)pivotTable.PivotFields(2));
        salesAmount = ((PivotField)pivotTable.PivotFields(3));

        // Format the Pivot Table.
        pivotTable.Format(XlPivotFormatType.xlReport2);
        pivotTable.InGridDropZones = false;

        // Set Sales Region as a Row Field.
        salesRegion.Orientation =
            XlPivotFieldOrientation.xlRowField;

        // Set Sum of Sales Amount as a Value Field.
        salesAmount.Orientation =
            XlPivotFieldOrientation.xlDataField;
        salesAmount.Function = XlConsolidationFunction.xlSum;

        // Save the Workbook.
        excelWorkBook.SaveAs(workBookName, useDefault, useDefault,
            useDefault, useDefault, useDefault,
            XlSaveAsAccessMode.xlNoChange, useDefault, useDefault,
            useDefault, useDefault, useDefault);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        // Release the references to the Excel objects.
        salesAmount = null;
        salesRegion = null;
        pivotDestination = null;
        pivotData = null;
        pivotTable = null;
        targetSheet = null;

        // Release the Workbook object.
        if (excelWorkBook != null)
            excelWorkBook = null;

        // Release the ApplicationClass object.
        if (excelApplication != null)
        {
            excelApplication.Quit();
            excelApplication = null;
        }

        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

The sample code shown here creates a workbook named PivotTableSample.xlsx in the C:\Temp folder. Modify the sample code and change the value of the workBookName variable to change the name and location of the created workbook.

Build and run the solution in Visual Studio by pressing Ctrl-F5. The CreatingPivotTable.exe console application is launched which creates a workbook named PivotTableSample.xlsx. The workbook contains a worksheet named Quarterly Sales that contains sales data and a PivotTable based on the data named Sales By Region.

Figure 3. Created PivotTable

Created PivotTable

Read It

The Workbook.PivotTableWizard method exposed by the Excel 2007 PIA allows you to create a PivotTable in a worksheet. The sample code shown with this Visual How To creates a workbook, adds a worksheet, and adds data to the worksheet. The code then uses the Workbook.PivotTableWizard method to create a PivotTable and the methods and properties of the PivotTable and PivotField classes to format the PivotTable.

' Select a range of data for the Pivot Table.
pivotData = targetSheet.Range("A1", "C7")

' Select location of the Pivot Table.
pivotDestination = targetSheet.Range("A11")

' Add a Pivot Table to the Worksheet.
excelWorkBook.PivotTableWizard( _
    XlPivotTableSourceType.xlDatabase, _
    pivotData, pivotDestination, pivotTableName, True, True, _
    True, True, , , False, False, XlOrder.xlDownThenOver, 0)

' Set variables for used to manipulate the Pivot Table.
pivotTable = targetSheet.PivotTables(pivotTableName)
salesRegion = pivotTable.PivotFields(2)
salesAmount = pivotTable.PivotFields(3)

' Format the Pivot Table.
pivotTable.Format(XlPivotFormatType.xlReport2)
pivotTable.InGridDropZones = False

' Set Sales Region as a Row Field.
salesRegion.Orientation = XlPivotFieldOrientation.xlRowField

' Set Sum of Sales Amount as a Value Field.
salesAmount.Orientation = XlPivotFieldOrientation.xlDataField
salesAmount.Function = XlConsolidationFunction.xlSum

// Select a range of data for the Pivot Table.
pivotData = targetSheet.get_Range("A1", "C7");

// Select location of the Pivot Table.
pivotDestination = targetSheet.get_Range("A11", useDefault);

// Add a Pivot Table to the Work Sheet.
excelWorkBook.PivotTableWizard(
    XlPivotTableSourceType.xlDatabase,
    pivotData,
    pivotDestination,
    pivotTableName,
    true,
    true,
    true,
    true,
    useDefault,
    useDefault,
    false,
    false,
    XlOrder.xlDownThenOver,
    0,
    useDefault,
    useDefault
    );

// Set variables for used to manipulate the Pivot Table.
pivotTable =
    (PivotTable)targetSheet.PivotTables(pivotTableName);
salesRegion = ((PivotField)pivotTable.PivotFields(2));
salesAmount = ((PivotField)pivotTable.PivotFields(3));

// Format the Pivot Table.
pivotTable.Format(XlPivotFormatType.xlReport2);
pivotTable.InGridDropZones = false;

// Set Sales Region as a Row Field.
salesRegion.Orientation =
    XlPivotFieldOrientation.xlRowField;

// Set Sum of Sales Amount as a Value Field.
salesAmount.Orientation =
    XlPivotFieldOrientation.xlDataField;
salesAmount.Function = XlConsolidationFunction.xlSum;

Explore It