Cannot add Report Filter to Pivot Table

Aspose.Cells Product Family
1

I have a C# MVC project that allows users to export a datagrid to Excel. When users opt to export the displayed data, I need to also include another dataset on a separate worksheet and build a pivot table on a third worksheet. I have all of these steps completed except for the following:

  • Add report filters as shown in PivotTable Field List > Report Filter section in Excel (screenshot attached)
  • Change and format text in PivotTable column headers as needed

Can anybody provide example code on how to accomplish these tasks? Code snippet is below...

NOTES:

Using version 5.1.4.0 of Aspose.Cells.dll with .NET 4.0 framework

CODE SNIPPET:

#region Add pivot table

//Getting the pivottables collection in the sheet

Aspose.Cells.Pivot.PivotTableCollection pivotTables = pivotTableWorksheet.PivotTables;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=Resource Capacity Data!A1:H" + Convert.ToString(resourceCapacityData.Count), "A1", "PivotTable1");

//Accessing the instance of the newly added PivotTable

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

PivotFieldCollection flds = pivotTable.PageFields;

//Showing the grand totals

pivotTable.RowGrand = true;

pivotTable.ColumnGrand = true;

//Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat = true;

//Setting the PivotTable autoformat type.

pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;

//Add the first field to the page/filter area.

//pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 0);

//the above doesn't work to put a filter/page into the pivot table...

//Add the second field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);

//Add the third field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);

//Add the fifth field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 4);

//Add the sixth field to the column area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 5);

//Add the eighth field to the data area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 7);

pivotTable.RefreshDataOnOpeningFile = true;

#endregion

2
Hi,

We have enhanced Pivot Table feature further in later versions of the product. We recommend you to kindly upgrade to and try our latest version if possible e.g v7.3.5.

a14437:
  • Add report filters as shown in PivotTable Field List > Report Filter section in Excel (screenshot attached)

See a sample code snippet on how to use Pivot Filters by Aspose.Cells for .NET APIs, you may write your own code using the relevant APIs.

Sample code:

//...........
PivotTable table = workbook.Worksheets[1].PivotTables[0];

int index = table.PivotFilters.Add(1, PivotFilterType.Count);

PivotFilter filter = table.PivotFilters[index];

filter.AutoFilter.FilterTop10(0, true, false, 1);

filter.MeasureFldIndex = 3;

index = table.PivotFilters.Add(1, PivotFilterType.CaptionGreaterThan);

filter = table.PivotFilters[index];

filter.Value1 = "1";

filter.AutoFilter.Custom(0, FilterOperatorType.GreaterThan, 1);

index = table.PivotFilters.Add(0, PivotFilterType.Count);

filter = table.PivotFilters[index];

filter.AutoFilter.FilterTop10(0, true, false, 10);

filter.MeasureFldIndex = 0;



a14437:
  • Change and format text in PivotTable column headers as needed

You may use some properties/APIs like:
e.g

pivotTable.ColumnHeaderCaption = "your caption";

pivotTable.ColumnFields[0].NumberFormat = "dd/mmm";


Hope, this helps.


Thank you.

3

Amjad,

Thank you very much for your assistance. I have a follow-up question...

I inherited this project from another developer who has departed from our company. Can you tell me how to request the newest version of the Aspose.Cells.dll? I'm assuming upgrades are included in the initial purchase price.

Kind regards

4

Hi,


Well, upgrades are available but make sure that your license is not expired. For your information, when you purchase a license, you are authorized to use this license file with any new (and upcoming) versions / hot fixes of the component(s) for the next whole year before your subscription expiry date.

I am afraid, if you are currently using v5.x.x.x or so which you actually purchased at that time, then you cannot use latest Aspose components with your current license as your license was expired after a year from the date of purchase. Either you should persist with older versions of Aspose products (that should be released before your expiry date (you may open your license into notepad and check the license’s expiry date) or upgrade your subscription to use our latest versions. For upgradation or subscription or licensing matters, please contact our Sales representative, you may post a query into Aspose.Purchase forum.



Thank you.