Interface IPivotCaches

Represents the collection of memory caches from the PivotTable reports in a workbook.

Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public interface IPivotCaches

Properties

Count

Gets number of pivot cache items in the collection. Read-only.

Declaration
int Count { get; }
Property Value
Type
System.Int32
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to get a Count of pivot cache collections.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Open("SamplePivot.xlsx");
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];

        //Gets number of pivot cache items in the collection
        int pivotCachesCount = workbook.PivotCaches.Count;
        if(pivotCachesCount > 0)
        {
            //Your Code Here
        }

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Item[Int32]

Gets a IPivotCache with the specified index from the collection. Read-only.

Declaration
IPivotCache this[int index] { get; }
Parameters
Type Name Description
System.Int32 index

Zero-based index of the cache to return.
Property Value
Type
IPivotCache
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use index property in PivotCache collections.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Create(2);
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];
        sheet.Range["A1"].Text = "Student ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark(Out of 100)";
        sheet.Range["D1"].Text = "Student City";
        sheet.Range["A1:D1"].AutofitColumns();
        sheet.Range["A2"].Number = 1;
        sheet.Range["A3"].Number = 2;
        sheet.Range["A4"].Number = 3;
        sheet.Range["A5"].Number = 4;
        sheet.Range["A6"].Number = 5;
        sheet.Range["B2"].Text = "Andrew";
        sheet.Range["B3"].Text = "Marson";
        sheet.Range["B4"].Text = "Jack";
        sheet.Range["B5"].Text = "Sam";
        sheet.Range["B6"].Text = "Breto";
        sheet.Range["C2"].Number = 77;
        sheet.Range["C3"].Number = 45;
        sheet.Range["C4"].Number = 92;
        sheet.Range["C5"].Number = 39;
        sheet.Range["C6"].Number = 55;
        sheet.Range["D2"].Text = "Chennai";
        sheet.Range["D3"].Text = "Bangalore";
        sheet.Range["D4"].Text = "Chennai";
        sheet.Range["D5"].Text = "Mysore";
        sheet.Range["D6"].Text = "Bangalore";
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);

        //Gets a pivot cache value from the index of the collection
        ExcelDataSourceType cacheSourceType = workbook.PivotCaches[0].SourceType;
        if(cacheSourceType == ExcelDataSourceType.Worksheet)
        {
            //Your Code Here
        }

        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);  
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
        IPivotField datafield = pivotTable.Fields[2];    
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Methods

Add(IRange)

Adds a new PivotTable cache to a IPivotCaches collection.

Declaration
IPivotCache Add(IRange range)
Parameters
Type Name Description
IRange range

IRange that contains data to cache.
Returns
Type Description
IPivotCache

Returns a IPivotCache .
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to add a cache to PivotCaches collection.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Create(2);
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];
        sheet.Range["A1"].Text = "Student ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark(Out of 100)";
        sheet.Range["D1"].Text = "Student City";
        sheet.Range["A1:D1"].AutofitColumns();
        sheet.Range["A2"].Number = 1;
        sheet.Range["A3"].Number = 2;
        sheet.Range["A4"].Number = 3;
        sheet.Range["A5"].Number = 4;
        sheet.Range["A6"].Number = 5;
        sheet.Range["B2"].Text = "Andrew";
        sheet.Range["B3"].Text = "Marson";
        sheet.Range["B4"].Text = "Jack";
        sheet.Range["B5"].Text = "Sam";
        sheet.Range["B6"].Text = "Breto";
        sheet.Range["C2"].Number = 77;
        sheet.Range["C3"].Number = 45;
        sheet.Range["C4"].Number = 92;
        sheet.Range["C5"].Number = 39;
        sheet.Range["C6"].Number = 55;
        sheet.Range["D2"].Text = "Chennai";
        sheet.Range["D3"].Text = "Bangalore";
        sheet.Range["D4"].Text = "Chennai";
        sheet.Range["D5"].Text = "Mysore";
        sheet.Range["D6"].Text = "Bangalore";

        //Adds a new PivotTable cache to a PivotCaches collection
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);

        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);  
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
        IPivotField datafield = pivotTable.Fields[2];    
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Extension Methods