Interface IPivotCalculatedFields

Represents collection of calculated fields in the pivot table.

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

Properties

Count

Gets the number of calculated fields in the pivot table. Read-only.

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

To know more about Pivot Tables refer this link.

Examples

The following code illustrates how to get count of calculated fields in the pivot table.

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

        int calculatedFieldCount = pivotSheet.PivotTables[0].CalculatedFields.Count;
        if(calculatedFieldCount > 0)
        {
            //Your Code Here
        }

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

Item[Int32]

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

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

Zero-based index of the item to get.
Property Value
Type
IPivotField
Remarks

To know more about Pivot Tables refer this link.

Examples

The following code illustrates the use of index property in pivot calculated field.

      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 = "ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark1";
        sheet.Range["D1"].Text = "Mark2";
        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"].Number = 87;
        sheet.Range["D3"].Number = 68;
        sheet.Range["D4"].Number = 98;
        sheet.Range["D5"].Number = 45;
        sheet.Range["D6"].Number = 70;
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        pivotTable.CalculatedFields.Add("Average", "(Mark1+Mark2)/2");

        //Gets a pivot field with the specified index
        IPivotField field = pivotTable.CalculatedFields[0];
        field.Name = "AverageMark";
        field.Subtotals = PivotSubtotalTypes.Average;

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

Item[String]

Gets a IPivotField with the specified name from the collection. Read-only.

Declaration
IPivotField this[string name] { get; }
Parameters
Type Name Description
System.String name

Calculated Field Name
Property Value
Type
IPivotField
Remarks

To know more about Pivot Tables refer this link.

Examples

The following code illustrates the use of name as index in pivot calculated field 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 = "ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark1";
        sheet.Range["D1"].Text = "Mark2";
        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"].Number = 87;
        sheet.Range["D3"].Number = 68;
        sheet.Range["D4"].Number = 98;
        sheet.Range["D5"].Number = 45;
        sheet.Range["D6"].Number = 70;
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        pivotTable.CalculatedFields.Add("Average", "(Mark1+Mark2)/2");

        //Gets a pivot field with the specified name
        IPivotField field = pivotTable.CalculatedFields["Average"];
        field.Name = "AverageMark";
        field.Subtotals = PivotSubtotalTypes.Average;

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

Methods

Add(String, String)

Adds calculated IPivotField to the pivot table based on the specified formula.

Declaration
IPivotField Add(string name, string formula)
Parameters
Type Name Description
System.String name

Represents the name of the calculated pivot field.
System.String formula

Represents the formula of the calculated pivot field.
Returns
Type Description
IPivotField

Returns the calculated IPivotField.
Remarks

To know more about Pivot Tables refer this link.

Examples

The following code illustrates the use Add method in pivot calculated field.

      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 = "ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark1";
        sheet.Range["D1"].Text = "Mark2";
        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"].Number = 87;
        sheet.Range["D3"].Number = 68;
        sheet.Range["D4"].Number = 98;
        sheet.Range["D5"].Number = 45;
        sheet.Range["D6"].Number = 70;
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;

        //Add calculated field to the pivot table with specified formula
        IPivotField field = pivotTable.CalculatedFields.Add("AverageMark", "(Mark1+Mark2)/2");

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

Extension Methods