Export (0) Print
Expand All
Expand Minimize

NamedRange.PivotField Property

Gets a PivotField that represents the PivotTable field containing the upper left corner of the NamedRange control.

Namespace:  Microsoft.Office.Tools.Excel
Assembly:  Microsoft.Office.Tools.Excel (in Microsoft.Office.Tools.Excel.dll)

PivotField PivotField { get; }

Property Value

Type: Microsoft.Office.Interop.Excel.PivotField
A PivotField that represents the PivotTable field containing the upper left corner of the NamedRange control.

The following code example creates a PivotTable report and a NamedRange inside the area of the PivotTable report. It then uses the PivotTable, LocationInTable, PivotCell, PivotItem, and PivotField properties to display information about the placement of the NamedRange within the PivotTable report. The example also uses the Group method to perform numeric grouping based on the first value in the field.

This example is for a document-level customization.

private void DisplayPivotTableInformation()
{
    // Specify values for the PivotTable. 
    this.Range["A1"].Value2 = "Date";
    this.Range["A2"].Value2 = "March 1";
    this.Range["A3"].Value2 = "March 8";
    this.Range["A4"].Value2 = "March 15";

    this.Range["B1"].Value2 = "Customer";
    this.Range["B2"].Value2 = "Smith";
    this.Range["B3"].Value2 = "Jones";
    this.Range["B4"].Value2 = "James";

    this.Range["C1"].Value2 = "Sales";
    this.Range["C2"].Value2 = "23";
    this.Range["C3"].Value2 = "17";
    this.Range["C4"].Value2 = "39";

    // Create and populate the PivotTable.
    Excel.PivotTable table1 = this.PivotTableWizard(
        Excel.XlPivotTableSourceType.xlDatabase,
        this.Range["A1", "C4"],
        this.Range["A10"], "Sales Table", false,
        false, true, false, false, false,
        Excel.XlOrder.xlDownThenOver);

    Excel.PivotField customerField =
        (Excel.PivotField)table1.PivotFields("Customer");
    customerField.Orientation =
        Excel.XlPivotFieldOrientation.xlRowField;
    customerField.Position = 1;

    Excel.PivotField dateField =
        (Excel.PivotField)table1.PivotFields("Date");
    dateField.Orientation =
        Excel.XlPivotFieldOrientation.xlColumnField;
    dateField.Position = 1;

    table1.AddDataField(table1.PivotFields("Sales"),
        "Sales Summary", Excel.XlConsolidationFunction.xlSum);

    // Create a NamedRange in the PivotTable and display the  
    // location.
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(
        this.Range["B11"], "namedRange1");
    namedRange1.Select();

    MessageBox.Show("The NamedRange is in the PivotTable report '" +
        namedRange1.PivotTable.Name + "' at the location '" +
        namedRange1.LocationInTable.ToString() + "'.");

    MessageBox.Show("The NamedRange has a PivotCell type of: " +
         namedRange1.PivotCell.PivotCellType.ToString());

    MessageBox.Show("The NamedRange is in the PivotTable field: " +
         namedRange1.PivotField.Name);

    MessageBox.Show("The NamedRange is in the PivotTable item: " +
        namedRange1.PivotItem.Name);

    namedRange1.Group(true);
}

Show:
© 2014 Microsoft