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.

Fri Jul 30, 2021 8:12 am

Hi,
I have an Excel file that has a Pivot table with multiple filters in it. When the file is opened one of the Filters is set. I can get a reference to the PivotTable and to the PivotFilter class.
If I set the FilterString to be the value that I want it to be, how do I get the data in the table to refresh programmatically?
If I Run pivotTable.CalculateData() then nothing happens.
Is it possible to get the data in the PivotTable to refresh in memory?

Thanks
Dominic

DomsRoberts
 
Posts: 3
Joined: Fri Jul 30, 2021 8:07 am

Fri Jul 30, 2021 9:51 am

Hello Dominic,

Thanks for your inquiry.
Please refer to the following code to meet your needs.
Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("pivotTable.xlsx");

            Worksheet sheet = workbook.Worksheets[0];
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

            PivotReportFilter pivotReportFilter = new PivotReportFilter("your FilterString", true);
            pt.ReportFilters.Add(pivotReportFilter);

            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);


If this is not what you want, please provide your input file and your desired output to help us investigate further. You can send them to us (support@e-iceblue.com) via email, thanks in advance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Fri Jul 30, 2021 3:35 pm

Hi,
I was hoping to do it in memory without saving to a file, so that I can then extract the value from the data.
I open the spreadsheet and connect to the Pivot table filter, update the filter values and then want to be able to access the updated values.
Is it possible without performing a save?

Thanks

DomsRoberts
 
Posts: 3
Joined: Fri Jul 30, 2021 8:07 am

Mon Aug 02, 2021 8:02 am

Hello,

Thanks for your clarification.
Please refer to the following code to get the filtered and updated values of the pivot table.
Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("pivotTable.xlsx");

            Worksheet sheet = workbook.Worksheets[0];
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

            PivotReportFilter pivotReportFilter = new PivotReportFilter("your FilterString", true);
            pt.ReportFilters.Add(pivotReportFilter);
            CellRange location = pt.Location;
            foreach (CellRange row in location.Rows)
            {
                foreach (CellRange cell in row.CellList)
                {
                    string value = cell.Value;
                }
            }

If this is not what you want, please provide your input file and your desired output to help us investigate further. You can send them to us (support@e-iceblue.com) via email, thanks in advance.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Mon Aug 02, 2021 8:31 am

I am trying to manipulate a Pivot Table that already exists in the Spreadsheet rather than adding a new filter.
I want to change the filter value in the first filter in the table. I would like something more specific but this suffices for the moment.

Code: Select all
var pivotTable = worksheet.PivotTables.FirstOrDefault() as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
            var reportFilters = pivotTable.ReportFilters;
            reportFilters[0].FilterItemStrings = new List<string> {"2019"};
            pivotTable.CalculateData();


I can see that this sets the FilterString on the required Filter. However, it does not refresh the data.
I have then tried saving to file and saving to Stream and reloading it.
Neither of these causes the Pivot Table to update with the data for the updated dates.
Is it possible to change the filters in an existing Pivot? Or does this feature not exists currently?

Thanks

DomsRoberts
 
Posts: 3
Joined: Fri Jul 30, 2021 8:07 am

Mon Aug 02, 2021 10:10 am

Hello Dominic,

Thanks for your response.
I am sorry that our Spire.XLS does not support this feature at present. But we have added it as a new feature into our upgrade list. If it is implemented in the future, we will inform you immediately.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Tue Sep 14, 2021 11:27 am

Hello Dominic,

Greetings from E-iceblue!
Glad to inform you that we just released Spire.XLS Pack(Hotfix) Version:11.9.2 which supports the feature your mentioned, please download it from the following links to test on your side. Looking forward to your test result.
Website link: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS/11.9.2

The sample code as below.

Code: Select all
Workbook workbook = new Workbook();
workbook.LoadFromFile("pivotTable.xlsx");

Worksheet sheet = workbook.Worksheets[0];
XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

//Add filter
PivotReportFilter reportFilter = new PivotReportFilter("Product" , pt);
reportFilter.IsMutipleSelect = false;      //Set whether the value of the filter is multi-select
reportFilter.FilterItemStrings = new List<string>{"SpireXls"};     //Set the selected value for the filter
pt.ReportFilters.Add(reportFilter);

pt.ReportFilters[0].FilterItemStrings = new List<string> { "March" };     //Set the selected value for the first filter
pt.ReportFilters.RemoveAt(0);
pt.ReportFilters.Clear();

pt.CalculateData();

workbook.SaveToFile("result.xlsx");


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Thu Sep 23, 2021 10:21 am

Hello,

Greetings from E-iceblue.
Does this hotfix meet your needs? Could you please give us some feedback at your convenience?

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS