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
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
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
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
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
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
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();
      }

EnableQuartersAndYears

Get or set a Boolean value indicating whether the Quarters and Years fields are applied to a date field or not.

Declaration

bool EnableQuartersAndYears { get; set; }

Property Value

Type
System.Boolean

Remarks

To know more about Pivot Tables refer this link.

Examples

The following code illustrates how to use EnableQuartersandYears 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 = "Date";
        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"].Value = "7/23/2014";
        sheet.Range["A3"].Value = "1/13/2015";
        sheet.Range["A4"].Value = "8/1/2016";
        sheet.Range["A5"].Value = "6/15/2017";
        sheet.Range["A6"].Value = "5/30/2018";
        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;
        pivotTable.Fields[0].Axis = PivotAxisTypes.Row;

        //Get or set the Quarters and Years fields for a date field in a pivot table.
        pivotTable.Fields[0].EnableQuartersAndYears = 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();
      }

FieldGroup

Gets the IPivotFieldGroup in current pivot field.

Declaration

IPivotFieldGroup FieldGroup { get; }

Property Value

Type
IPivotFieldGroup

Remarks

To know more about Pivot Tables refer this link.

Examples

The following code illustrates how to get pivot field group and use it for date time grouping.

   using (ExcelEngine excelEngine = new ExcelEngine())
   {
        IApplication application = excelEngine.Excel;
        IWorkbook workbook = application.Workbooks.Open("Input.xlsx");
        IWorksheet worksheet = workbook.Worksheets[0];

        //Create Pivot cache with the given data range
        IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:C8"]);

         //Create "PivotTable1" with the cache at the specified range
        IPivotTable pivotTable = worksheet.PivotTables.Add("PivotTable1", worksheet["A1"], cache);

        //Get the pivot group field from pivot field.
        IPivotFieldGroup fieldGroup = pivotTable.Fields[0].FieldGroup;

        //Set the pivot field group type.
        fieldGroup.GroupBy = PivotFieldGroupType.Months;

        //Set the pivot field group start date.
        fieldGroup.StartDate = new DateTime(2023,5,20)

        //Set the pivot field group end date.
        fieldGroup.EndDate = new DateTime(2023,10,3);

        workbook.SaveAs("Output.xlsx");
        workbook.Close();
        excelEngine.Dispose();
    }

FilterValue

Gets or sets the Filter value of Page fields.

Declaration

string FilterValue { get; set; }

Property Value

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

ShowSubtotalAtTop

Gets or sets a boolean value indicating whether the subtotal is positioned at the top. The default value is True.

Declaration

bool ShowSubtotalAtTop { get; set; }

Property Value

Type
System.Boolean

Remarks

To know more about Pivot Tables refer this link.

Examples

The following code illustrates how to use ShowSubtotalAtTop 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].ShowSubtotalAtTop = false;

        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
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();
      }