Interface IPivotField
Represents a field in a PivotTable report.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.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.
<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);
//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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use CanDragOff property in pivot 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["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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use CanDragToColumn property in pivot 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["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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use CanDragToData property in pivot 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["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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use CanDragToPage property in pivot 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["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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use CanDragToRow property in pivot 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["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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use EnableQuartersandYears property in pivot 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 = "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.
<pre><code class="lang-csharp">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
[Obsolete("Use IPivotValueLableFilter's Value1 property instead of this property")]
string FilterValue { get; set; }
Property Value
| Type |
|---|
| 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 |
|---|
| string |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use Formula property in pivot 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;
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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use IncludeNewItemsInFilter property in pivot 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 = "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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use IsFormulaField property in pivot 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;
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 |
|---|
| string |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates the use of Name property in pivot 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);
//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 |
|---|
| 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.
<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);
//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 |
|---|
| int |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use Position property in pivot 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["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 |
|---|
| bool |
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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use ShowBlankRow property in pivot 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 = "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 |
|---|
| bool |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to use ShowSubtotalAtTop property in pivot 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 = "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.
<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 = "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, int)
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. |
| int | 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 |
|---|---|---|
| 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();
}