Interface IPivotCalculatedFields
Represents collection of calculated fields in the pivot table.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public interface IPivotCalculatedFields
Properties
Count
Gets the number of calculated fields in the pivot table. Read-only.
Declaration
int Count { get; }
Property Value
| Type |
|---|
| int |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to get count of calculated fields in the pivot table.
<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.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
int calculatedFieldCount = pivotSheet.PivotTables[0].CalculatedFields.Count; if(calculatedFieldCount > 0) { //Your Code Here }
workbook.SaveAs("PivotTables.xlsx"); workbook.Close(); }
this[int]
Gets a IPivotField with the specified index from the collection. Read-only.
Declaration
IPivotField this[int index] { get; }
Parameters
| Type | Name | Description |
|---|---|---|
| int | index | Zero-based index of the item to get. |
Property Value
| Type |
|---|
| IPivotField |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates the use of index property in pivot calculated 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 = "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;
pivotTable.CalculatedFields.Add("Average", "(Mark1+Mark2)/2");
//Gets a pivot field with the specified index IPivotField field = pivotTable.CalculatedFields[0]; field.Name = "AverageMark"; field.Subtotals = PivotSubtotalTypes.Average;
workbook.SaveAs("PivotTables.xlsx"); workbook.Close(); }
this[string]
Gets a IPivotField with the specified name from the collection. Read-only.
Declaration
IPivotField this[string name] { get; }
Parameters
| Type | Name | Description |
|---|---|---|
| string | name | Calculated Field Name |
Property Value
| Type |
|---|
| IPivotField |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates the use of name as index in pivot calculated field collection.
<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 = "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;
pivotTable.CalculatedFields.Add("Average", "(Mark1+Mark2)/2");
//Gets a pivot field with the specified name IPivotField field = pivotTable.CalculatedFields["Average"]; field.Name = "AverageMark"; field.Subtotals = PivotSubtotalTypes.Average;
workbook.SaveAs("PivotTables.xlsx"); workbook.Close(); }
Methods
Add(string, string)
Adds calculated IPivotField to the pivot table based on the specified formula.
Declaration
IPivotField Add(string name, string formula)
Parameters
| Type | Name | Description |
|---|---|---|
| string | name | Represents the name of the calculated pivot field. |
| string | formula | Represents the formula of the calculated pivot field. |
Returns
| Type | Description |
|---|---|
| IPivotField | Returns the calculated IPivotField. |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates the use Add method in pivot calculated 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 = "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;
//Add calculated field to the pivot table with specified formula IPivotField field = pivotTable.CalculatedFields.Add("AverageMark", "(Mark1+Mark2)/2");
workbook.SaveAs("PivotTables.xlsx"); workbook.Close(); }