alexa
menu

MAUI

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

    Show / Hide Table of Contents

    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
    int
    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
    int
    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
    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
    string
    Examples

    The following code snippet illustrates how to use a NumberFormat property in pivot data field.

           <pre><code class="lang-csharp">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();
    }
    In this article
    Back to top Generated by DocFX
    Copyright © 2001 - 2026 Syncfusion Inc. All Rights Reserved