Interface IPivotTable
Represents a PivotTable on a worksheet.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.Portable.dll
Syntax
public interface IPivotTableProperties
BuiltInStyle
Gets or sets the PivotBuiltInStyles of the pivot table.
Declaration
Nullable<PivotBuiltInStyles> BuiltInStyle { get; set; }Property Value
| Type | Description |
|---|---|
| System.Nullable<PivotBuiltInStyles> |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the BuiltInStyle property of 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["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);
//Sets the built-in style of the pivot table
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}CacheIndex
Gets the index number of the PivotTable cache. Read-only.
Declaration
int CacheIndex { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the CacheIndex property of 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["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 cacheStuName = workbook.PivotCaches.Add(sheet["A1:C3"]);
IPivotCache cacheStuMark = workbook.PivotCaches.Add(sheet["A1:C4"]);
IPivotCache cacheStuMarkName = workbook.PivotCaches.Add(sheet["A1:C6"]);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cacheStuMarkName);
//Gets the index number of the PivotTable cache
int pivotCacheIndex = pivotTable.CacheIndex;
if(pivotCacheIndex >= 0)
{
//Your Code Here
}
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();
}CalculatedFields
Gets the collection of IPivotCalculatedFields in the pivot table. Read-only.
Declaration
IPivotCalculatedFields CalculatedFields { get; }Property Value
| Type | Description |
|---|---|
| IPivotCalculatedFields |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the CalculatedFields property of 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 = "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;
//Gets the collection of calculated fields in the pivot table
IPivotCalculatedFields calculatedFields = pivotTable.CalculatedFields;
calculatedFields.Add("AverageMark", "(Mark1+Mark2)/2");
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}ColumnFields
Gets the collection of IPivotFields in the pivot table. Read-only.
Declaration
IPivotFields ColumnFields { get; }Property Value
| Type | Description |
|---|---|
| IPivotFields |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the ColumnFields property of 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 = "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;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
//Gets the collection of row fields in the pivot table
IPivotFields columnFields = pivotTable.ColumnFields;
columnFields[0].CanDragToRow = false;
IPivotField datafield = pivotTable.Fields[2];
pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}ColumnGrand
Gets or sets value indicating whether the PivotTable contains column with grand totals for rows (same as ColumnGrand in VBA). Default value is true.
Declaration
bool ColumnGrand { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the ColumnGrand property of 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["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 value indicating whether the PivotTable contains column with grand totals for rows
pivotTable.ColumnGrand = false;
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();
}ColumnsPerPage
Gets the number of columns per page for this PivotTable that the filter area will occupy. Read-only.
Declaration
int ColumnsPerPage { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the ColumnsPerPage property of the pivot table.
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];
//Gets the number of columns per page for this PivotTable
int columnPerPage = pivotSheet.PivotTables[0].ColumnsPerPage;
if(columnPerPage != 0)
{
//Your Code Here
}
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}DataFields
Gets the collection of IPivotDataFields in pivot table. Read-only.
Declaration
IPivotDataFields DataFields { get; }Property Value
| Type | Description |
|---|---|
| IPivotDataFields |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates the use of DataFields property in 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["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.Name = "Sample Pivot Table";
pivotTable.Fields[0].Axis = PivotAxisTypes.Column;
pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
IPivotField datafield = pivotTable.Fields[2];
//Add the DataField to the Data fields collection
pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}DisplayFieldCaptions
Gets or sets a Boolean value indicating whether filter buttons and pivot field captions for rows and columns are displayed in the grid. The default value is True.
Declaration
bool DisplayFieldCaptions { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates use of DisplayFieldCaptions 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["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 a Boolean value to pivot field captions are displayed in the grid
pivotTable.DisplayFieldCaptions = false;
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();
}Fields
Gets the collection of IPivotFields. Read-only.
Declaration
IPivotFields Fields { get; }Property Value
| Type | Description |
|---|---|
| IPivotFields |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates the use of Fields property in 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["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.Name = "Sample Pivot Table";
//Sets the value for pivot field using index from pivot fields collection
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();
}Location
Gets or sets IRange of pivot table location in the worksheet.
Declaration
IRange Location { get; set; }Property Value
| Type | Description |
|---|---|
| IRange |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the Location property of 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["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 pivot table location in the worksheet
pivotTable.Location = pivotSheet["B2"];
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();
}Name
Gets or sets the pivot table name. This property allows you to Read and Write a pivot table name.
Declaration
string Name { get; set; }Property Value
| Type | Description |
|---|---|
| System.String |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how the use of Name property in 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["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 pivot table name
pivotTable.Name = "Sample Pivot Table";
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();
}Options
Gets the IPivotTableOptions of pivot table. Read-only.
Declaration
IPivotTableOptions Options { get; }Property Value
| Type | Description |
|---|---|
| IPivotTableOptions |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the Options property of 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["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);
//Gets the pivot table options
IPivotTableOptions pivotTableOptions = pivotTable.Options;
pivotTableOptions.ColumnHeaderCaption = "Student Details";
pivotTableOptions.RowHeaderCaption = "Student Records";
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();
}PageFields
Gets the collection of IPivotFields in the pivot table. Read-only.
Declaration
IPivotFields PageFields { get; }Property Value
| Type | Description |
|---|---|
| IPivotFields |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the PageFields property of 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 = "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;
pivotTable.Fields[3].Axis = PivotAxisTypes.Page;
//Gets the collection of page fields in the pivot table
IPivotFields pageFields = pivotTable.PageFields;
pageFields[0].CanDragToRow = false;
IPivotField datafield = pivotTable.Fields[2];
pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}PivotEngineValues
Gets or sets the PivotEngine of the pivot table.
Declaration
PivotEngine PivotEngineValues { get; set; }Property Value
| Type | Description |
|---|---|
| PivotEngine |
Remarks
This property is not supported in UWP, Xamarin and ASP.NET Core platforms. To know more about Pivot Tables refer this link.
Examples
Following code snippet illustrates how to use PivotEngineValues property.
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;
pivotTable.Layout();
//Gets the pivot engine of the pivot table
Syncfusion.XlsIO.Implementation.PivotAnalysis.PivotEngine pivotEngine = pivotTable.PivotEngineValues;
if(pivotEngine != null)
{
//Your Code Here
}
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}RepeatItemsOnEachPrintedPage
Gets or sets a Boolean value indicating whether labels must be repeated on every page.The default value is True.
Declaration
bool RepeatItemsOnEachPrintedPage { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
True if row, column, and item labels appear on the first row of each page when the specified PivotTable report is printed. False if labels are printed only on the first page. To know more about Pivot Tables refer this link.
Examples
Following code illustrates use of RepeatItemsOnEachPrintedPage 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["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 a Boolean value indicating whether labels must be repeated on every page
pivotTable.RepeatItemsOnEachPrintedPage = true;
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();
}RowFields
Gets the collection of IPivotFields in the pivot table. Read-only.
Declaration
IPivotFields RowFields { get; }Property Value
| Type | Description |
|---|---|
| IPivotFields |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the RowFields property of 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 = "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;
//Gets the collection of row fields in the pivot table
IPivotFields rowFields = pivotTable.RowFields;
rowFields[0].CanDragToColumn = false;
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();
}RowGrand
Gets or sets value indicating whether the PivotTable contains row with grand totals for columns (same as RowGrand in VBA). Default value is true.
Declaration
bool RowGrand { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
To know more about Pivot Tables refer this link.
Examples
The following code illustrates how to access the RowGrand property of 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["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 value indicating whether the PivotTable contains row with grand totals for columns
pivotTable.RowGrand = false;
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();
}RowsPerPage
Gets the number of rows per page for this PivotTable that the filter area will occupy. Read-only.
Declaration
int RowsPerPage { get; }Property Value
| Type | Description |
|---|---|
| System.Int32 |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the RowsPerPage property of the pivot table.
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];
//Gets the number of rows per page for this PivotTable
int rowsPerPage = pivotSheet.PivotTables[0].RowsPerPage;
if(rowsPerPage != 0)
{
//Your Code Here
}
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}ShowColumnGrand
Gets or sets a Boolean value indicating whether the PivotTable contains grand totals for columns.
Declaration
bool ShowColumnGrand { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the ShowColumnGrand property of 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["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 a Boolean value indicating whether the PivotTable contains grand totals for columns
pivotTable.ShowColumnGrand = false;
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();
}ShowDataFieldInRow
Gets or sets a Boolean value indicating whether the PivotTable data fields are shown in rows.
Declaration
bool ShowDataFieldInRow { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the ShowDataFieldInRow property of 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 = "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;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField datafield = pivotTable.Fields[2];
pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
//Sets a Boolean value to the PivotTable data fields are shown in rows
pivotTable.ShowDataFieldInRow = true;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}ShowDrillIndicators
Gets or sets a Boolean value indicating whether to toggle the display of drill indicators in the PivotTable. Default value is true.
Declaration
bool ShowDrillIndicators { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the ShowDrillIndicators property of 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 = "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;
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;
//Sets a Boolean value to display of drill indicators in the PivotTable
pivotTable.ShowDrillIndicators = false;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}ShowRowGrand
Gets or sets a Boolean value indicating whether the PivotTable contains grand totals for rows.
Declaration
bool ShowRowGrand { get; set; }Property Value
| Type | Description |
|---|---|
| System.Boolean |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to access the ShowRowGrand property of 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["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 a Boolean value to PivotTable contains grand totals for rows
pivotTable.ShowRowGrand = false;
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();
}Methods
ClearTable()
Clears all the fields, deletes all filtering and sorting applied to the PivotTable.
Declaration
void ClearTable()Remarks
To know more about Pivot Tables refer this link.
Examples
Following code illustrates how to clear the all fields, filters and sorting using ClearTable method.
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;
//Clears all the fields, deletes all filtering and sorting applied to the PivotTable
pivotTable.ClearTable();
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}GetCellFormat(String)
Get cell format for the given pivot range.
Declaration
IPivotCellFormat GetCellFormat(string range)Parameters
| Type | Name | Description |
|---|---|---|
| System.String | range | Pivot range to get the cell format. |
Returns
| Type | Description |
|---|---|
| IPivotCellFormat | Pivot cell format |
Examples
Following code snippet illustrates how to get pivot cell format from 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["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;
//Get the cell format for "A1" pivot range.
IPivotCellFormat cellFormat = pivotTable.GetCellFormat("A1");
cellFormat.BackColor = ExcelKnownColors.Red;
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}Layout()
Draws a layout for the pivot table.
Declaration
void Layout()Remarks
This property is not supported in UWP, Xamarin and ASP.NET Core platforms. To know more about Pivot Tables refer this link.
Examples
Following code snippet illustrates how to layout a pivot table similar to MS Excel.
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;
//The following code sample must be included to XlsIO layout the pivot table like MS Excel.
pivotTable.Layout();
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}