Interface IPivotCalculatedFields

Represents collection of calculated fields in the pivot table.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Portable.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();
      }