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 Jul 12, 2011 10:47 pm

Hi,
I want to know in the pivot table, how do I add Report Filter not just column or row?


Thanks!

ctseng123
 
Posts: 12
Joined: Sun May 22, 2011 10:25 pm

Wed Jul 13, 2011 1:56 am

Dear ctseng123,
Thanks for your inquiry.
We'll add this feature at next version.
Please wait patiently.
Once added we'll inform you.
Justin
Technical Support / Developer,
e-iceblue Support Team
User avatar

Justin Weng
 
Posts: 110
Joined: Mon Mar 28, 2011 5:54 am

Mon Apr 08, 2013 8:58 pm

Is this feature available yet? If so, is there documentation on how to use it?

abeverly@precastcorp.com
 
Posts: 1
Joined: Tue Apr 02, 2013 5:09 pm

Tue Apr 09, 2013 9:18 am

Dear Abeverly,

Thanks for your inquiry.
Our dev team are working on the feature. Once the feature has finished, we will release a new version and inform you.
Have any issues, please feel free to contact us.

Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Thu Aug 29, 2013 7:58 am

Hello,

Now our Spire.Xls could support the feature to add Report Filter in pivot, and the newest hot_fix has been released, please download the Spire.XLS Pack Hotfix Version:7.1.6 and try the following method.
http://www.e-iceblue.com/Download/download-excel-for-net-now.html
Code: Select all
pivotTable.ReportFilters[1].IsMultipleSelect = false;
pivotTable.ReportFilters[1].FieldString = "FedEx";
pivotTable.ReportFilters[1].FilterItemStrings=new List<string>() { "114", "110" };


If there are any questions, welcome to get it back to us.

Thanks And Regards,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Wed Sep 04, 2013 9:36 am

Hello,

Have you tested the new feature? Does it fulfills your needs? Could you please give us some feedback if convenience?

If there are any questions, welcome to get it back to us.

Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Jun 09, 2014 12:10 pm

Thanks Gary,

But it doesn't work.
it was displayed following message when I open the excel.
"Excel found unreadable content in file name."

Please let me know the solution.
Code: Select all
        Dim sheet1 As Worksheet = workbook.Worksheets(0)
        sheet1.Name = "Test"
        Dim sheet2 As Worksheet = workbook.Worksheets(2)
        sheet2.Name = "Data Source"

        Dim dataRange As CellRange = sheet2.Range("A1:G10")
        Dim cache As PivotCache = workbook.PivotCaches.Add(dataRange)
        Dim pt As PivotTable = sheet1.PivotTables.Add("Item Analysis", sheet2.Range("A1"), cache)
        Dim filter As Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter = New Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter("Item#", True)
        Dim ra1 = pt.PivotFields("Category")
        Dim ra2 = pt.PivotFields("Destination")
        ra1.Axis = AxisTypes.Column
        ra2.Axis = AxisTypes.Row
        pt.ReportFilters.Add(filter)
        pt.DataFields.Add(pt.PivotFields("Quantity"), "Quantity", SubtotalTypes.Sum)

        workbook.SaveToFile("test.xlsx", ExcelVersion.Version2007)


jouji_s
 
Posts: 6
Joined: Fri Mar 07, 2014 6:22 pm

Tue Jun 10, 2014 3:02 am

Hello,

Thanks for your feedback. Please provide us your test excel document for testing.

Thanks,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Thu Jun 26, 2014 9:43 pm

hi, I am also facing the same issue. I have attached my test excel here.

for Report filter i used below code, but it gives me error on line workbook.SaveToFile(PathToFile, ExcelVersion.Version2007);
:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary


Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter filter = new Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter("PRNT_PROD_CD", true);
pt.ReportFilters.Add(filter);
pt.ReportFilters[0].IsMultipleSelect = true;
pt.ReportFilters[0].FieldString = "DENTAL";
pt.ReportFilters[0].FilterItemStrings = new List<string>() { "DENTAL", "VISION" };

Can you please help me with the sample code for below two issues :

1) Add a Report filter for field "PRNT_PROD_CD"
2) Add a filter on column "FCTR_SYN_TXT".

vikaash
 
Posts: 1
Joined: Thu Jun 26, 2014 7:59 pm

Fri Jun 27, 2014 7:42 am

Hello,

Thanks for your feedback.

1) Add a Report filter for field "PRNT_PROD_CD"

You only need to use the following codes.

Code: Select all
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = sheet.PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;

Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter filter = new Spire.Xls.Core.Spreadsheet.PivotTables.PivotReportFilter("PRNT_PROD_CD",true);

pivotTable.ReportFilters.Add(filter);

2) Add a filter on column "FCTR_SYN_TXT".

Sorry that currently our product doesn't support the feature to add a filter. Add we have added it to our schedule

Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Fri Dec 06, 2019 6:00 pm

Hello,

I am able to create the Pivot Table in excel without any issues. But I am not able to add report filters into PivotTable .

Code:
//Add Pivot Table
PivotTable ptPercentage = sheet2.PivotTables.Add("PivotTable2", sheet2.Range["A1"], pivotCache);

//filters
PivotReportFilter f1 = new PivotReportFilter("Terminated Within Eval Month", true);
ptPercentage.ReportFilters.Add(f1);
PivotReportFilter f2 = new PivotReportFilter("Terminated Outside Eval Month Not Submitted", true);
ptPercentage.ReportFilters.Add(f2);

//Even after adding two filters, the ptPercentage.ReportFilters.Count shows 0.

I am using the Nuget Package "FreeSpire.XLS" version 9.10.11.
<package id="FreeSpire.XLS" version="9.10.11" targetFramework="net40" />

sthakuri
 
Posts: 2
Joined: Fri Dec 06, 2019 5:31 pm

Mon Dec 09, 2019 2:29 am

Hi,

Thanks for your inquiry. Sorry to reply late for weekend.
I firstly suggest you try to use the latest commercial version of Spire.XLS Pack(Hotfix) Version:9.11.14 which has more improvements than free version.

If the issue still exists after trying, to help us investigate it accurately, please offer us the following information.
1. Your input Excel file(if any).
2. The complete code you were using which could reproduce your issue directly.
3. Your desired result file.

You could upload them here or send us(support@e-iceblue.com) via email.

Best wishes,
Amber
E-iceblue support team
User avatar

Amber.Gu
 
Posts: 525
Joined: Tue Jun 04, 2019 3:16 am

Return to Spire.XLS

cron