Interface IPivotDataField
Represents a data field in pivot table report.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.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();
}