Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue May 26, 2015 12:40 pm

Hi, I have created an excel template with a pivot table within a work sheet. With Spire.XLS, is it possible to filter through page fields within a pivot table?

Image

Using Interop library I can add the page field within a pivot table and toggle as true or false which filter I would want to show.
Code: Select all
//(Interop code)
PivotTable pvt = xlWorkSheet.PivotTables("PivotByTest") as PivotTable;
PivotField pfTMonth = (PivotField)pvt.PivotFields("Month");
pfTMonth.Orientation = XlPivotFieldOrientation.xlPageField;

pfTMonth.EnableMultiplePageItems = true;
for (int i = 1; i <= pfTMonth.PivotItems().count; i++)
{
    if (pfTMonth.PivotItems(i).Value.ToLower() != "(blank)")
        pfTMonth.PivotItems(i).Visible = (pfTMonth.PivotItems(i).Value == DateTime.Now.ToString("MMMM"));
    else
        pfTMonth.PivotItems(i).Visible = false;
}


I know that the pagefield can be targeted by
Code: Select all
//(Spire.XLS code)
pvt.PageFields[0]

Is there a way to manipulate them as showing true or false?

sgrech
 
Posts: 5
Joined: Tue May 26, 2015 8:17 am

Wed May 27, 2015 5:58 am

Hello,

Thanks for your inquiry.
Please refer to the below code:
Code: Select all
 Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;         
            PivotReportFilter filter = new PivotReportFilter("Name", true);       
            pivotTable.ReportFilters.Add(filter);


Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Wed May 27, 2015 7:36 am

Thanks for the code sample which is useful, but I would want to manipulate the items within a report filter. Eg, I would need to toggle one item as true while the other items within the report filter are set to false.

sgrech
 
Posts: 5
Joined: Tue May 26, 2015 8:17 am

Wed May 27, 2015 9:07 am

Hello,

Thanks for your response.
Sorry that our product does't support this feature at this stage.

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Wed May 27, 2015 9:49 am

Would this feature be supported in the future?

An other question aside. Is Spire.XLS built from Open.XML platform?

sgrech
 
Posts: 5
Joined: Tue May 26, 2015 8:17 am

Thu May 28, 2015 2:14 am

Hello,

Thanks for your reply.
Sorry that our product does't support this feature in the future.
I recommend you to use add and remove items to implement this function.
About the Open.XML platform, do you mean ods format?

Best Regards,
Sweety
E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Tue Jun 02, 2015 9:35 am

Hello,

Has your issue been resolved?
Thanks for your response.

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Tue Jun 02, 2015 10:01 am

Hi, yes I manage to find a way to work on my problem. Thanks.

sgrech
 
Posts: 5
Joined: Tue May 26, 2015 8:17 am

Wed Jun 03, 2015 1:14 am

Hello,

Thanks for yor feedback.
I am glad to hear that.
Please feel free to contact us, if you have any questions or needs. We are here for help.

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Return to Spire.XLS