Class PivotTableCollection

PivotTableCollection class

Represents the collection of all the PivotTable objects on the specified worksheet.

public class PivotTableCollection : CollectionBase<PivotTable>, IDisposable

Properties

NameDescription
Capacity { get; set; }
Count { get; }
Item { get; }Gets the PivotTable report by index. (3 indexers)
Item { get; set; }

Methods

NameDescription
Add(PivotTable, string, string)Adds a new PivotTable based on another PivotTable.
Add(string, string, string)Adds a new PivotTable.
Add(PivotTable, int, int, string)Adds a new PivotTable based on another PivotTable.
Add(string, int, int, string)Adds a new PivotTable.
Add(string, string, string, bool)Adds a new PivotTable.
Add(string, int, int, string, bool)Adds a new PivotTable.
Add(string, string, string, bool, bool)Adds a new PivotTable.
Add(string[], bool, PivotPageFields, string, string)Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
Add(string, int, int, string, bool, bool)Adds a new PivotTable.
Add(string[], bool, PivotPageFields, int, int, string)Adds a new PivotTable Object to the collection with multiple consolidation ranges as data source.
BinarySearch(PivotTable)
BinarySearch(PivotTable, IComparer<PivotTable>)
BinarySearch(int, int, PivotTable, IComparer<PivotTable>)
Clear()Clear all pivot tables. (2 methods)
Contains(PivotTable)
CopyTo(PivotTable[])
CopyTo(PivotTable[], int)
CopyTo(int, PivotTable[], int, int)
Dispose()Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
Exists(Predicate<PivotTable>)
Find(Predicate<PivotTable>)
FindAll(Predicate<PivotTable>)
FindIndex(Predicate<PivotTable>)
FindIndex(int, Predicate<PivotTable>)
FindIndex(int, int, Predicate<PivotTable>)
FindLast(Predicate<PivotTable>)
FindLastIndex(Predicate<PivotTable>)
FindLastIndex(int, Predicate<PivotTable>)
FindLastIndex(int, int, Predicate<PivotTable>)
GetEnumerator()
IndexOf(PivotTable)
IndexOf(PivotTable, int)
IndexOf(PivotTable, int, int)
LastIndexOf(PivotTable)
LastIndexOf(PivotTable, int)
LastIndexOf(PivotTable, int, int)
Remove(PivotTable)Deletes the specified PivotTable and delete the PivotTable data
Remove(PivotTable, bool)Deletes the specified PivotTable
RemoveAt(int)Deletes the PivotTable at the specified index and delete the PivotTable data (2 methods)
RemoveAt(int, bool)Deletes the PivotTable at the specified index

Examples

namespace AsposeCellsExamples
{
    using Aspose.Cells;
    using Aspose.Cells.Pivot;
    using System;
    using System.Drawing;

    public class PivotTableCollectionDemo
    {
        public static void PivotTableCollectionExample()
        {
            // Create a new workbook
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            Cells cells = sheet.Cells;

            // Add sample data to the worksheet
            cells[0, 0].Value = "fruit";
            cells[1, 0].Value = "grape";
            cells[2, 0].Value = "blueberry";
            cells[3, 0].Value = "kiwi";
            cells[4, 0].Value = "cherry";
            cells[5, 0].Value = "grape";
            cells[6, 0].Value = "blueberry";
            cells[7, 0].Value = "kiwi";
            cells[8, 0].Value = "cherry";

            cells[0, 1].Value = "year";
            cells[1, 1].Value = 2020;
            cells[2, 1].Value = 2020;
            cells[3, 1].Value = 2020;
            cells[4, 1].Value = 2020;
            cells[5, 1].Value = 2021;
            cells[6, 1].Value = 2021;
            cells[7, 1].Value = 2021;
            cells[8, 1].Value = 2021;

            cells[0, 2].Value = "amount";
            cells[1, 2].Value = 50;
            cells[2, 2].Value = 60;
            cells[3, 2].Value = 70;
            cells[4, 2].Value = 80;
            cells[5, 2].Value = 90;
            cells[6, 2].Value = 100;
            cells[7, 2].Value = 110;
            cells[8, 2].Value = 120;

            // Access the PivotTableCollection
            PivotTableCollection pivots = sheet.PivotTables;

            // Add a PivotTable to the worksheet
            int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
            PivotTable pivot = pivots[pivotIndex];

            // Configure the PivotTable
            pivot.AddFieldToArea(PivotFieldType.Row, "fruit");
            pivot.AddFieldToArea(PivotFieldType.Column, "year");
            pivot.AddFieldToArea(PivotFieldType.Data, "amount");

            // Set PivotTable style
            pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;

            // Change PivotField's attributes
            PivotField rowField = pivot.RowFields[0];
            rowField.DisplayName = "custom display name";

            // Add PivotFilter
            int filterIndex = pivot.PivotFilters.Add(0, PivotFilterType.Count);
            PivotFilter filter = pivot.PivotFilters[filterIndex];
            filter.AutoFilter.FilterTop10(0, false, false, 2);

            // Add PivotFormatCondition
            int formatIndex = pivot.PivotFormatConditions.Add();
            PivotFormatCondition pfc = pivot.PivotFormatConditions[formatIndex];
            FormatConditionCollection fcc = pfc.FormatConditions;
            fcc.AddArea(pivot.DataBodyRange);
            int idx = fcc.AddCondition(FormatConditionType.CellValue);
            FormatCondition fc = fcc[idx];
            fc.Formula1 = "100";
            fc.Operator = OperatorType.GreaterOrEqual;
            fc.Style.BackgroundColor = Color.Red;

            // Refresh and calculate the PivotTable data
            pivot.RefreshData();
            pivot.CalculateData();

            // Save the workbook
            workbook.Save("PivotTableCollectionExample.xlsx");
        }
    }
}

See Also