Interface IPivotDataField

Represents a data field in pivot table report.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public interface IPivotDataField

Properties

BaseField

Gets or sets the base field for a custom calculation.

Declaration

int BaseField { get; set; }

Property Value

Type
System.Int32

Remarks

Valid only for data fields. To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a BaseField property in pivot data 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;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);       
        IPivotDataField pivotDataField = pivotTable.DataFields[0];
        pivotDataField.ShowDataAs = PivotFieldDataFormat.Difference;

        //Sets the base field for a custom calculation
        pivotDataField.BaseField = 1;
        pivotDataField.BaseItem = 2;

        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

BaseItem

Gets or sets the base item for a custom calculation.

Declaration

int BaseItem { get; set; }

Property Value

Type
System.Int32

Remarks

Valid only for data fields. To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a BaseItem property in pivot data 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;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);       
        IPivotDataField pivotDataField = pivotTable.DataFields[0];
        pivotDataField.ShowDataAs = PivotFieldDataFormat.Difference;

        //Sets the base item for a custom calculation
        pivotDataField.BaseItem = 2;
        pivotDataField.BaseField = 1;

        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Name

Gets or sets name of the data field.

Declaration

string Name { get; set; }

Property Value

Type
System.String

Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a Name property in pivot data 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;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
        IPivotDataField pivotDataField = pivotTable.DataFields[0];

        //Sets name of the data field
        pivotDataField.Name = "SUM Marks";

        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

NumberFormat

Gets or sets the number format for the pivot data field.

Declaration

string NumberFormat { get; set; }

Property Value

Type
System.String

Examples

The following code snippet illustrates how to use a NumberFormat property in pivot data 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);       
        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;                 

        //Sets number format for the pivot data field
        pivotTable.DataFields[0].NumberFormat = "#,##0_);(#,##0)";

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

ShowDataAs

Gets or sets the PivotFieldDataFormat indicating how the pivot data is shown.

Declaration

PivotFieldDataFormat ShowDataAs { get; set; }

Property Value

Type
PivotFieldDataFormat

Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a ShowDataAs property in pivot data 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;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);       
        IPivotDataField pivotDataField = pivotTable.DataFields[0];

        //Sets the pivot table format indicating how the pivot data is shown
        pivotDataField.ShowDataAs = PivotFieldDataFormat.PercentageOfTotal;

        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Subtotal

Gets or sets the PivotSubtotalTypes function used for data field.

Declaration

PivotSubtotalTypes Subtotal { get; set; }

Property Value

Type
PivotSubtotalTypes

Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a Subtotal property in pivot data 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;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);       
        IPivotDataField pivotDataField = pivotTable.DataFields[0];

        //Sets the subtotal function used for data field
        pivotDataField.Subtotal = PivotSubtotalTypes.Average;

        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Methods

SetNextBaseItem()

Set the next value to BaseItem.

Declaration

void SetNextBaseItem()

Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a SetNextBaseItem method in pivot data 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;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);       
        IPivotDataField pivotDataField = pivotTable.DataFields[0];
        pivotDataField.ShowDataAs = PivotFieldDataFormat.Difference;
        pivotDataField.BaseField = 1;
        pivotDataField.BaseItem = 2;

        //Set the next value to base item
        pivotDataField.SetNextBaseItem();

        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

SetPreviousBaseItem()

Set the previous value to BaseItem.

Declaration

void SetPreviousBaseItem()

Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use a SetPreviousBaseItem method in pivot data 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;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);       
        IPivotDataField pivotDataField = pivotTable.DataFields[0];
        pivotDataField.ShowDataAs = PivotFieldDataFormat.Difference;
        pivotDataField.BaseField = 1;
        pivotDataField.BaseItem = 2;

        //Set the previous value to base item
        pivotDataField.SetPreviousBaseItem();

        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }