Documents for Excel .NET Edition | GCDocuments
Features / Pivot Table / Pivot Table Settings
In This Topic
    Pivot Table Settings
    In This Topic

    You can modify the setting of the pivot table created in a spreadsheet by performing the following tasks:

    Configure Pivot Table Fields

    You can configure the fields of your pivot table using the properties and methods of the IPivotCaches interface and IPivotTables interface.

    Refer to the following example code to configure the pivot table fields in a worksheet.

    C#
    Copy Code
    //Source data for PivotCache
    object[,] sourceData = new object[,] {
    { "Order ID", "Product",  "Category",   "Amount", "Date",                    "Country" },
    { 1,          "Carrots",  "Vegetables",  4270,    new DateTime(2012, 1, 6),  "United States" },
    { 2,          "Broccoli", "Vegetables",  8239,    new DateTime(2012, 1, 7),  "United Kingdom" },
    { 3,          "Banana",   "Fruit",       617,     new DateTime(2012, 1, 8),  "United States" },
    { 4,          "Banana",   "Fruit",       8384,    new DateTime(2012, 1, 10), "Canada" },
    { 5,          "Beans",    "Vegetables",  2626,    new DateTime(2012, 1, 10), "Germany" },
    { 6,          "Orange",   "Fruit",       3610,    new DateTime(2012, 1, 11), "United States" },
    { 7,          "Broccoli", "Vegetables",  9062,    new DateTime(2012, 1, 11), "Australia" },
    { 8,          "Banana",   "Fruit",       6906,    new DateTime(2012, 1, 16), "New Zealand" },
    { 9,          "Apple",    "Fruit",       2417,    new DateTime(2012, 1, 16), "France" },
    { 10,         "Apple",    "Fruit",       7431,    new DateTime(2012, 1, 16), "Canada" },
    { 11,         "Banana",   "Fruit",       8250,    new DateTime(2012, 1, 16), "Germany" },
    { 12,         "Broccoli", "Vegetables",  7012,    new DateTime(2012, 1, 18), "United States" },
    { 13,         "Carrots",  "Vegetables",  1903,    new DateTime(2012, 1, 20), "Germany" },
    { 14,         "Broccoli", "Vegetables",  2824,    new DateTime(2012, 1, 22), "Canada" },
    { 15,         "Apple",    "Fruit",       6946,    new DateTime(2012, 1, 24), "France" },
    };
    
    //Initialize the WorkBook and fetch the default WorkSheet
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
    // Assigning data to the range
    worksheet.Range["A1:F16"].Value = sourceData;
    // Creating pivot
    var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
    var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["L7"], "pivottable1");
                
    // Configuring pivot table fields
    var field_Category = pivottable.PivotFields["Category"];
    field_Category.Orientation = PivotFieldOrientation.RowField;
    
    var field_Product = pivottable.PivotFields["Product"];
    field_Product.Orientation = PivotFieldOrientation.ColumnField;
    
    var field_Amount = pivottable.PivotFields["Amount"];
    field_Amount.Orientation = PivotFieldOrientation.DataField;
    
    var field_Country = pivottable.PivotFields["Country"];
    field_Country.Orientation = PivotFieldOrientation.PageField;

    Add Field Function

    Refer to the following example code to add field function in a pivot table.

    C#
    Copy Code
    //Set field amount function
    field_Amount.Function = ConsolidationFunction.Average;

    Filter Pivot Table

    Refer to the following example code to filter a pivot table.

    C#
    Copy Code
    var field_product = pivottable.PivotFields[1];
    field_product.Orientation = PivotFieldOrientation.RowField;
    
    var field_Amount = pivottable.PivotFields[3];
    field_Amount.Orientation = PivotFieldOrientation.DataField;
    
    var field_Country = pivottable.PivotFields[5];
    field_Country.Orientation = PivotFieldOrientation.PageField;
    
    //row field filter.
    field_product.PivotItems["Apple"].Visible = false;
    field_product.PivotItems["Beans"].Visible = false;
    field_product.PivotItems["Orange"].Visible = false;
    
    //page filter.
    field_Country.PivotItems["United States"].Visible = false;
    field_Country.PivotItems["Canada"].Visible = false;

    Manage Pivot Field Level

    Refer to the following example code to manage the field level of a pivot table.

    C#
    Copy Code
    //product in level 1.
    var field_product = pivottable.PivotFields["Product"];
    field_product.Orientation = PivotFieldOrientation.RowField;
    
    //category in level 2.
    var field_category = pivottable.PivotFields["Category"];
    field_category.Orientation = PivotFieldOrientation.RowField;
    
    var field_Amount = pivottable.PivotFields[3];
    field_Amount.Orientation = PivotFieldOrientation.DataField;
    
    //category will in level 1 and product in level 2.
    field_product.Position = 1;
    field_category.Position = 0;

    Manage Grand Total Visibility Settings

    The Grand total in pivot table helps in analyzing the total sum of the data in the pivot table. You can display or hide the grand total for the row or column field by setting the visibility of ColumnGrand and RowGrand properties of the IPivotTable interface. These properties take boolean values and are set to true by default. For example, if you want to display the grand total only for rows, then set the RowGrand property to true and ColumnGrand to false.

    Refer to the following example code to manage the visibility settings of the grand total field.

    C#
    Copy Code
    // Set the PivotTable report to show grand totals for columns & rows
    pivottable.ColumnGrand = true;
    pivottable.RowGrand = true;

    Change Row Axis Layout

    The display of pivot table can be changed to any desired layout using the LayoutRowType enumeration. The following options are provided by this enumeration:

    Note: The SubtotalLocationType enumeration can only be set to Bottom if the LayoutRowType is set to TabularRow.

    Refer to the following example code to set the row axis layout of the pivot table to TabularRow.

    C#
    Copy Code
    // Set the PivotTable LayoutRowType to Tabular Row
    pivottable.SetRowAxisLayout(LayoutRowType.TabularRow);
    

    Change Pivot Table Layout

    The different layouts of a pivot table makes it more flexible and convenient to analyse its data. GcExcel supports the following pivot table layouts:

    In addition to these, you can also choose to insert blank rows, set the position of subtotals, show all items or to repeat any item in the pivot table layouts.

    Refer to the following example code to set the layout of pivot table and additional options.

    C#
    Copy Code
    //set pivot table layout
    field_Category.LayoutForm = LayoutFormType.Tabular;
    field_Category.LayoutBlankLine = true;
    
    field_Country.LayoutForm = LayoutFormType.Outline;
    field_Country.LayoutCompactRow = false;
    
    //set subtotal location
    field_Country.LayoutSubtotalLocation = SubtotalLocationType.Bottom;
    field_Country.ShowAllItems = true;

    Rename Pivot Table Fields

    Sometimes, the pivot table fields are not easily comprehendible and hence can be renamed to meaningful and easily understandable names.

    Refer to the following example code to rename the pivot table fields.

    C#
    Copy Code
    //config pivot table's fields
    var field_Date = pivottable.PivotFields["Date"];
    field_Date.Orientation = PivotFieldOrientation.PageField;
    
    // Renaming PivotField "Category" to "Type of Category"
    var field_Category = pivottable.PivotFields["Category"];
    field_Category.Name = "Type of Category";
    field_Category.Orientation = PivotFieldOrientation.RowField;
    
    var field_Product = pivottable.PivotFields["Product"];
    field_Product.Orientation = PivotFieldOrientation.ColumnField;
    
    var field_Amount = pivottable.PivotFields["Amount"];
    field_Amount.Orientation = PivotFieldOrientation.DataField;
         
    
    var field_Country = pivottable.PivotFields["Country"];
    field_Country.Orientation = PivotFieldOrientation.RowField;
    
    // Renaming DataField "Sum of Amount" to "Amount Total"
    pivottable.DataFields[0].Name = "Amount Total";
    
    

    Refresh Pivot Table

    Refer to the following example code to refresh a pivot table.

    C#
    Copy Code
    var field_product = pivottable.PivotFields["Product"];
    field_product.Orientation = PivotFieldOrientation.RowField;
    
    var field_Amount = pivottable.PivotFields[3];
    field_Amount.Orientation = PivotFieldOrientation.DataField;
    
    //change pivot cache's source data.
    worksheet.Range["D8"].Value = 3000;
    
    //sync cache's data to pivot table.
    worksheet.PivotTables[0].Refresh();

    Apply Different Calculations on a Pivot Field

    In GcExcel, you can add a pivot table field to a pivot table multiple times by applying various calculation functions on it. These functions include sum, average, min, max, count etc. The final pivot table output will contain multiple data fields based on the calculations applied over the pivot table field.

    Refer to the following example code to add a pivot table field as multiple data fields by applying different calculation functions.

    C#
    Copy Code
    //config pivot table's fields
    var field_Category = pivottable.PivotFields["Category"];
    field_Category.Orientation = PivotFieldOrientation.RowField;
    
    var field_Product = pivottable.PivotFields["Product"];
    field_Product.Orientation = PivotFieldOrientation.RowField;
    
    //sum function on Amount field
    var field_Amount = pivottable.PivotFields["Amount"];
    pivottable.AddDataField(field_Amount, "sum amount", ConsolidationFunction.Sum);
    
    //count function on Amount field
    var field_Amount2 = pivottable.PivotFields["Amount"];
    pivottable.AddDataField(field_Amount2, "count amount", ConsolidationFunction.Count);

    The output of above example code when viewed in Excel, looks like below:

     

    Defer Layout Update

    In case of huge amount of data, the performance of a pivot table might get affected while updating its layout by adding or moving fields in the different areas of a pivot table.

    GcExcel provides DeferLayoutUpdate property which improves the performance of a pivot table by deferring its layout updates. When set to true, the pivot table is recalculated only after all the fields are added or moved instead of getting recalculated after each change. You can choose to update the pivot table output after making all the changes by calling the Update method.

    Refer to the following example code to defer layout updates to a pivot table.

    C#
    Copy Code
    //defer layout update
    pivottable.DeferLayoutUpdate = true;
    
    //config pivot table's fields
    var field_Category = pivottable.PivotFields["Category"];
    field_Category.Orientation = PivotFieldOrientation.RowField;
    
    var field_Product = pivottable.PivotFields["Product"];
    field_Product.Orientation = PivotFieldOrientation.ColumnField;
    
    var field_Amount = pivottable.PivotFields["Amount"];
    field_Amount.Orientation = PivotFieldOrientation.DataField;
    
    //must update the pivottable
    pivottable.Update();

    Use Pivot Table Options

    GcExcel supports the following layout and formatting options in a pivot table:

    Refer to the following example code to set various layout and format options in a pivot table.

    C#
    Copy Code
    //set layout and format options
    pivottable.PageFieldOrder = Order.OverThenDown;
    pivottable.PageFieldWrapCount = 2;
    
    pivottable.CompactRowIndent = 2;
    
    pivottable.ErrorString = "Error";
    pivottable.NullString = "Empty";
    
    pivottable.DisplayErrorString = true;
    pivottable.DisplayNullString = true;

    Sort Pivot Table Fields

    GcExcel supports sorting data fields in a pivot table by using AutoSort method and defining ascending or descending as its sort order.

    You can also retrieve the name of data field used to sort the specified PivotTable field by using AutoSortField property and its sorting order by using AutoSortOrder property. The position of an item in its field can also be set or retrieved by using the Position property of IPivotItem interface.

    Refer to the following example code to sort 'Product' field in a pivot table.

    C#
    Copy Code
    //sort the product items 
    field_Product.AutoSort(SortOrder.Descending);

    Retrieve Pivot Table Ranges

    The structure of a pivot table report is comprised of different ranges. In order to retrieve a specific range of pivot table, it is important to understand the structure of a pivot table.

    Pivot table report

     As can be observed from the above screenshot, the structure of a pivot table can be explained as:

    GcExcel provides API to retrieve the detailed ranges of a pivot table to apply any operation or style on them to make the result more readable and distinguishable. Detailed pivot table ranges which can be retrieved are:

    Refer to the following example code to get a specific range and set its style in a pivot table report.

    C#
    Copy Code
    //get detail range and set style
    foreach (var item in pivottable.PivotRowAxis.PivotLines)
    {
        if (item.LineType == PivotLineType.Subtotal)
        {
            item.PivotLineCells[0].Range.Interior.Color = Color.GreenYellow;
        }
    }

    The output of above code example when viewed in Excel, looks like below: 

    Pivot table report output

    Note: Style applied to a pivot table is lost if the pivot table is changed in any way.

    Get Pivot Table Data

    GcExcel.NET provides GETPIVOTDATA function which queries the pivot table to fetch data as per the specified parameters. The function has two required arguments, that is, name of the data field that contains the required data and range of cells in a PivotTable. You can also use the IRange.GenerateGetPivotDataFunction to generate the GETPIVOTDATA function automatically on selecting a cell in the PivotTable while entering the function. As the function returns the data based on the pivot table structure instead of cell references, always correct data is returned even if there is change in layout.

    C#
    Copy Code
    var worksheet2 = workbook.Worksheets.Add();
    worksheet.Range["H25"].Formula = worksheet.Range["G6"].GenerateGetPivotDataFunction(worksheet2.Range["A1"]);
    worksheet2.Range["H24"].Formula = @"=GETPIVOTDATA(""Amount"",Sheet1!$A$1,""Category"",""Mobile"",""Country"",""Australia"")";

    Set Conditional Formatting

    Refer to the following example code to set conditional formatting in last row of a pivot table report by setting cell color when the values are above average.

    C#
    Copy Code
    // set condional format to the last row
    int rowCount = pivottable.DataBodyRange.RowCount;
    IAboveAverage averageCondition = pivottable.DataBodyRange.Rows[rowCount - 1].FormatConditions.AddAboveAverage();
    averageCondition.AboveBelow = AboveBelow.AboveAverage;
    averageCondition.Interior.Color = Color.Pink;

    Note: Conditional formatting applied to a pivot table is lost if the pivot table is changed in any way.

     

    Disable Automatic Grouping of Date/Time Columns

    The Date/Time columns in a pivot table are grouped together by default. GcExcel allows you to disable this grouping by setting AutomaticGroupDateTimeInPivotTable property to false before creating the pivot cache while creating a pivot table.

    When AutomaticGroupDateTimeInPivotTable = False When AutomaticGroupDateTimeInPivotTable = True (default)

    Refer to the following example code to disable automatic grouping of date/time columns.

    C#
    Copy Code
    // Set false to group date/time fields in PivotTable automatically
    workbook.Options.Data.AutomaticGroupDateTimeInPivotTable = false;

    Expand or Collapse Pivot Table Fields

    GcExcel provides ShowDetail property in IPivotItem interface which allows you to expand or collapse the outline of pivot table fields. The default value of the property is True which shows the expanded state of pivot table fields. However, it can be set to False to display the collapsed state.

    Refer to the following example code to set collapsed state of two pivot table fields.

    C#
    Copy Code
    worksheet.Range["A1:F16"].Value = sourceData;
    IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
    IPivotTable pivotTable = worksheet.PivotTables.Add(pivotCache, worksheet.Range["H7"], "pivottable1");
            
    pivotTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField;
    pivotTable.PivotFields["Country"].Orientation = PivotFieldOrientation.RowField;
    pivotTable.PivotFields["Category"].Orientation = PivotFieldOrientation.ColumnField;
    pivotTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
            
    //Set collapsed state
    pivotTable.PivotFields["Product"].PivotItems["Banana"].ShowDetail = false;
    pivotTable.PivotFields["Product"].PivotItems["Carrots"].ShowDetail = false;
    
    workbook.Save("CollapsePivotFields.xlsx");