Filters applied to Excel Pivot table column lost when data refreshed in Aspose Cells

Aspose.Cells Product Family
1
I am using the Aspose Cells control in the attached example VS2010 project.

I have 2 sheets, the first being a data sheet and the second a pivot table of the data
In code, I am replacing the data in the data sheet and refreshing the pivot table to show the new data.
However when i do this, if I have a filter on the data in the pivot table sheet (in the template file there is a filter in PivotSheet Column B set to only show Pheonix items), this is lost when the newfile is created.

Is there a way to clear the data rows in the datasheet and refresh the pivot table but leave any filters that have been applied? I can do this in Excel but not in code?

Thanks

Martin
2

Hi,


Thanks for providing us sample project with template file.

I observed the issue using your Sample project with v8.3.2.x and got the output Excel file with your mentioned issue. Filters applied to Excel PivotTable lost and was set to “(All)” instead of “Pheonix”. When I tested your scenario/ case using our latest version/fix: Aspose.Cells for .NET v8.6.3.x, somehow I got the exception “Invalid formula:“DataSheet!A1:3”.” on the following code segment in your project:
e.g
Sample code:

…
// Manipulate the row number in the end range cell
String newEndCellRow = endCellRows.Substring(0, endCellRows.LastIndexOf("$") + 1);
newEndCellRow += (resultsDataTable.Rows.Count + 1).ToString(); //it gives 3 where it should be D3.
String newSourceRange = groupItems[1] + “!” + groupItems[2] + “:” + newEndCellRow;

pivotTableItem.ChangeDataSource(new String[] { newSourceRange });

…

And, if I manually use “DataSheet!A1:D3”, I got the same output as you got (with issue) via older version that you are using. We will evaluate your issue soon.

By the way, if you ask MS Excel to do the refreshing PivotTable(s) in the template file, it works fine. I mean, if you could comment out the following lines of code, it would work as expected and Ms Excel does the refreshing and calculation of PivotTable(s) fine:
e.g
Sample code:

…
//TryRefreshData(pivotTableItem);
// TryCalculateData(pivotTableItem);
// pivotTableItem.CalculateRange();

Could this workaround applicable to you and suit your requirements?

Thank you.

3

The issue is that our customers will be providing the template.xlsx file themselves, so i cannot guarantee that the template file is set to refresh the data.

However it seems it is the RefreshData() method that is clearing the filters.
If I do this:

if (!pivotTableItem.RefreshDataOnOpeningFile)
{
TryRefreshData(pivotTableItem);
}
TryCalculateData(pivotTableItem);
pivotTableItem.CalculateRange();

the filters remain in place. It would be a workaround, but ideally we would want the filters to not be cleared at all, whether by Aspose Cells or by Excel.

Regards

Martin
4

Hi,


I think you may try to use the workaround (skip the line(s) of code regarding RefreshData() method) for now with your Aspose.Cells version, it would work fine. In case you find any issue, do let us know with details and samples, we will check it soon.

Thank you.


5

Hi


Could you please advise if your solution is correct?
i.e. If the Excel file is set to update data on opening, does this mean I do not have to set RefreshData(), CalculateData() or CalculateRange() in code? I am not sure what these methods actually do and the help doc doesn’t explain it

Regards

Martin
6

Hi,


Well, your understanding is correct. Also, you may set MS Excel to refresh/update Pivot table programmatically on opening the file into it, you may simply (only) try to use PivotTable.RefreshDataOnOpeningFile to set it to true.

Hope, this helps a bit.

Thank you.