alexa
menu

MAUI

  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Search Results for

    Show / Hide Table of Contents

    Interface IPivotDataFields

    Represents the collection of data fields from the PivotTable reports in a workbook.

    Namespace: Syncfusion.XlsIO
    Assembly: Syncfusion.XlsIO.NET.dll
    Syntax
    public interface IPivotDataFields

    Properties

    Count

    Gets the number of data fields in this collection. Read-only.

    Declaration
    int Count { get; }
    Property Value
    Type
    int
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    Following code snippet illustrates how to use a Count property of the pivot field.

    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 the number of data fields in this collection
      int pivotDataFieldsCount = pivotSheet.PivotTables[0].DataFields.Count;
      if(pivotDataFieldsCount > 0)
      {
          //Your Code Here
      }
    
      workbook.SaveAs("PivotTables.xlsx");
      workbook.Close();
    }

    this[int]

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

    Declaration
    IPivotDataField this[int index] { get; }
    Parameters
    Type Name Description
    int index

    Zero-based index of the data field to return.

    Property Value
    Type
    IPivotDataField
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    Following code snippet illustrates how to use a index property in pivot data field 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"]);
      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);
    
      //Gets a pivot data field with the specified index from the collection
      IPivotDataField pivotDataField = pivotTable.DataFields[0];
      pivotDataField.Name = "SUM Marks";
      pivotDataField.Subtotal = PivotSubtotalTypes.Average;
    
      pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
      workbook.SaveAs("PivotTables.xlsx");
      workbook.Close();
    }

    this[string]

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

    Declaration
    IPivotDataField this[string name] { get; }
    Parameters
    Type Name Description
    string name

    Pivot data field name

    Property Value
    Type
    IPivotDataField
    Examples

    The following code illustrates how to get the pivot table data field by data field name.

    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"]);
      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);
    
      //Gets a pivot data field with the specified name from the collection
      IPivotDataField pivotDataField = pivotTable.DataFields["Sum of Marks"];
      pivotDataField.Name = "SUM Marks";
      pivotDataField.Subtotal = PivotSubtotalTypes.Average;
    
      pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
      workbook.SaveAs("PivotTables.xlsx");
      workbook.Close();
    }

    Methods

    Add(IPivotField, string, PivotSubtotalTypes)

    Adds a new data field to the IPivotDataFields collection.

    Declaration
    IPivotDataField Add(IPivotField field, string name, PivotSubtotalTypes subtotal)
    Parameters
    Type Name Description
    IPivotField field

    The parent field to use.

    string name

    The name for the new data field.

    PivotSubtotalTypes subtotal

    The subtotal function for the new data field.

    Returns
    Type Description
    IPivotDataField

    Returns a IPivotDataField .

    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    Following code snippet illustrates how to add a pivot data field to the pivot table.

    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"]);
      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];
    
      //Adds a new data field to the PivotDataField collection
      pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
    
      pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
      workbook.SaveAs("PivotTables.xlsx");
      workbook.Close();
    }
    In this article
    Back to top Generated by DocFX
    Copyright © 2001 - 2026 Syncfusion Inc. All Rights Reserved