File Formats

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

    Show / Hide Table of Contents

    Interface IPivotField

    Represents a field in a PivotTable report.

    Namespace: Syncfusion.XlsIO
    Assembly: Syncfusion.XlsIO.Base.dll
    Syntax
    public interface IPivotField

    Properties

    Axis

    Gets or sets the PivotAxisTypes of pivot field.

    Declaration
    PivotAxisTypes Axis { get; set; }
    Property Value
    Type Description
    PivotAxisTypes
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to sets the field axis in 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["A1:C1"].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;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets the axis of the pivot field      
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;        
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
    
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    CanDragOff

    Gets or sets a Boolean value indicating if user can remove field from view. The default value is True.

    Declaration
    bool CanDragOff { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use CanDragOff property in pivot 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 = "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);
    
            //Sets a Boolean value indicating if user can remove field from view
            pivotTable.Fields[0].CanDragOff = false;
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Page;
            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();
          }

    CanDragToColumn

    Gets or sets a Boolean value indicating if user can drag field to column area. The default value is True.

    Declaration
    bool CanDragToColumn { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use CanDragToColumn property in pivot 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 = "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);
    
            //Sets a Boolean value indicating if user can drag field to column area
            pivotTable.Fields[0].CanDragToColumn = false;
    
            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();
          }

    CanDragToData

    Gets or sets a Boolean value indicating if the specified field can be dragged to the data position. The default value is True.

    Declaration
    bool CanDragToData { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use CanDragToData property in pivot 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 = "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);
    
            //Sets a Boolean value indicating if user can drag field to data position
            pivotTable.Fields[0].CanDragToData = false;
    
            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();
          }

    CanDragToPage

    Gets or sets a Boolean value indicating if user can drag field to page area. The default value is True.

    Declaration
    bool CanDragToPage { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use CanDragToPage property in pivot 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 = "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);
    
            //Sets a Boolean value indicating if user can drag field to page area
            pivotTable.Fields[0].CanDragToPage = false;
    
            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();
          }

    CanDragToRow

    Gets or sets a Boolean value indicating if user can drag field to row area. The default value is True.

    Declaration
    bool CanDragToRow { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use CanDragToRow property in pivot 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 = "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);
    
            //Sets a Boolean value indicating if user can drag field to row area
            pivotTable.Fields[0].CanDragToRow = false;
    
            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();
          }

    FilterValue

    Gets or sets the Filter value of Page fields.

    Declaration
    [Obsolete("Use IPivotValueLableFilter's Value1 property instead of this property")]
    string FilterValue { get; set; }
    Property Value
    Type Description
    System.String
    Remarks

    To know more about Pivot Tables refer this link.

    Formula

    Gets or sets the formula for the calculated field.

    Declaration
    string Formula { get; set; }
    Property Value
    Type Description
    System.String
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use Formula property in pivot 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;
            IPivotField field = pivotTable.CalculatedFields.Add("AverageMark", "(Mark1+Mark2)");
    
            //Sets the formula for the calculated field
            field.Formula = "(Mark1+Mark2)/2";
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    IncludeNewItemsInFilter

    Gets or sets a Boolean value indicating whether newly added pivot table items should be tracked when manual filtering is applied to pivot field. If a Boolean value is true newly added items are included into the manual layout of the pivot table.

    Declaration
    bool IncludeNewItemsInFilter { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use IncludeNewItemsInFilter property in pivot 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 = "Mark";
            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 = 88;
            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;
    
            //Sets a Boolean value to include the newly added items in manual layout
            pivotTable.Fields[1].IncludeNewItemsInFilter = true;
    
            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();
          }

    IsFormulaField

    Gets a Boolean value indicating if this field is formula field. Read-only.

    Declaration
    bool IsFormulaField { get; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use IsFormulaField property in pivot 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;
            IPivotField field = pivotTable.CalculatedFields.Add("AverageMark", "(Mark1+Mark2)");
            field.Formula = "(Mark1+Mark2)/2";
    
            //Gets a Boolean value indicating if this field is formula field
            bool isFormula = field.IsFormulaField;
            if(isFormula)
            {
                //Your Code Here
            }
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    Items

    Gets IPivotFieldItems which are the individual data entries in a field category. Read-only.

    Declaration
    IPivotFieldItems Items { get; }
    Property Value
    Type Description
    IPivotFieldItems
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    Following code snippet illustrates how to use a Items property in pivot field item.

          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);  
    
            //Gets the field item from the collection based on the specified text
            IPivotField pivotField = pivotTable.Fields[1];
            pivotField.Items["Andrew"].Position = 4;
    
            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();
          }

    Name

    Gets or sets the name of the pivot field.

    Declaration
    string Name { get; set; }
    Property Value
    Type Description
    System.String
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates the use of Name property in pivot 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 = "Student ID";
            sheet.Range["B1"].Text = "Student Name";
            sheet.Range["C1"].Text = "Mark(Out of 100)";
            sheet.Range["A1:C1"].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;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets the name of the pivot field
            pivotTable.Fields[0].Name = "StudentID";
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;        
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    NumberFormat

    Gets or sets the number format for the pivot field.

    Declaration
    string NumberFormat { get; set; }
    Property Value
    Type Description
    System.String
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use the number format in 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["A1:C1"].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;
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:C6"]);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
    
            //Sets number format for the pivot field
            pivotTable.Fields[2].NumberFormat = "#,##0_);(#,##0)";
    
            pivotTable.Fields[0].Axis = PivotAxisTypes.Column;        
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    PivotFilters

    Gets the IPivotFilters collection for the pivot field. Read-only.

    Declaration
    IPivotFilters PivotFilters { get; }
    Property Value
    Type Description
    IPivotFilters
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    Following code snippet illustrates how to use PivotFilters property in 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];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            IPivotField field = pivotTable.Fields[1];
    
            //Adds filter with Value/Label based on the specified filter values
            field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");
    
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    Position

    Gets or sets the position of the field (first,second,third and so on) among all the fields in its Axis (Row,Column,Page,Data).

    Declaration
    int Position { get; set; }
    Property Value
    Type Description
    System.Int32
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use Position property in pivot 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 = "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;
            pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
    
            //Sets the position of the field
            pivotTable.Fields[0].Position = 1;
            pivotTable.Fields[1].Position = 0;
    
            IPivotField datafield = pivotTable.Fields[2];
            pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
            workbook.SaveAs("PivotTables.xlsx");
            workbook.Close();
          }

    RepeatLabels

    Declaration
    bool RepeatLabels { get; set; }
    Property Value
    Type Description
    System.Boolean

    ShowBlankRow

    Gets or sets a Boolean value indicating if a blank row is inserted after the specified row field in a PivotTable report.

    Declaration
    bool ShowBlankRow { get; set; }
    Property Value
    Type Description
    System.Boolean
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use ShowBlankRow property in pivot 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 = "Mark";
            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 = 88;
            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;
    
            //Sets a Boolean value to insert a blank row after the specified row field
            pivotTable.Fields[1].ShowBlankRow = true;
    
            pivotTable.Fields[0].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();
          }

    Subtotals

    Gets or sets the PivotSubtotalTypes used for pivot field.

    Declaration
    PivotSubtotalTypes Subtotals { get; set; }
    Property Value
    Type Description
    PivotSubtotalTypes
    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates how to use Subtotals property in pivot 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 = "Mark";
            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 = 88;
            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;
    
            //Sets the subtotal function used for pivot field
            pivotTable.Fields[1].Subtotals = PivotSubtotalTypes.Count;
    
            pivotTable.Fields[0].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

    AutoSort(PivotFieldSortType, Int32)

    Sorts pivot row or column values in the given sort type.

    Declaration
    void AutoSort(PivotFieldSortType sortType, int lineNumber)
    Parameters
    Type Name Description
    PivotFieldSortType sortType

    Type of sort that is applied to this field.

    System.Int32 lineNumber

    Row or Column number of the pivot table that the sort is applied to.

    Examples

    The following code illustrates sorting of pivot row and column values based on sort type.

    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;
    
    IWorkbook workbook = application.Workbooks.Open(@"PivotData.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];
    IPivotTable pivotTable = worksheet.PivotTables[0];
    
    //Pivot table row sorting.
    IPivotField columnField = pivotTable.ColumnFields[0];
    columnField.AutoSort(PivotFieldSortType.Ascending, 1);
    
    //Pivot table column sorting.
    IPivotField rowField = pivotTable.RowFields[0];
    rowField.AutoSort(PivotFieldSortType.Descending, 1);
    
    workbook.SaveAs("Output.xlsx");
    workbook.Close();
    excelEngine.Dispose();

    Sort(String[])

    Sorts pivot field items in the given order.

    Declaration
    void Sort(string[] orderByArray)
    Parameters
    Type Name Description
    System.String[] orderByArray

    Array of strings to be sorted.

    Remarks

    To know more about Pivot Tables refer this link.

    Examples

    The following code illustrates sorting of pivot field items based on custom set of strings.

          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 pivotField = pivotTable.Fields[1];
    
            //Sorts pivot field items in the given order
            pivotField.Sort(new string[5] { "Breto", "Marson", "Jack", "Andrew", "Sam" });
    
            IPivotField datafield = pivotTable.Fields[2];
            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