File Formats

Code Examples Upgrade Guide User Guide Demos Support Forums Download
  • Code Examples
  • Upgrade Guide
  • User Guide
  • Demos
  • Support
  • Forums
  • Download
Interface IPivotDataFields

    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.Base.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 Description
    System.Int32
    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();
          }

    Item[Int32]

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

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

    Zero-based index of the data field to return.

    Property Value
    Type Description
    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();
          }

    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.

    System.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();
          }
    Back to top Generated by DocFX
    Copyright © 2001 - 2023 Syncfusion Inc. All Rights Reserved