PivotTable and filter --> Excel Crashes

Aspose.Cells Product Family
1

Hi,

I can’t set a filter in my pivotTable?!
I tested with a created pivotTable with aspose, and with an existing PivotTable, where I just added a filter.

The index Column 0 is a column with autoID

I tested this codes:

Dim index2 As Integer = myPivot.PivotFilters.Add(0, Pivot.PivotFilterType.CaptionEqual)
Dim Filter As Pivot.PivotFilter = myPivot.PivotFilters(index2)
Filter.Value1 = “4169”
Filter.AutoFilter.Filter(0, “4169”) 'I tested without it
Filter.AutoFilter.Refresh() 'I tested without it
myPivot.RefreshData() 'I tested without it
myPivot.CalculateData() 'I tested without it
–> no filter set

Dim index As Integer = myPivot.PivotFilters.Add(0, Pivot.PivotFilterType.CaptionEqual)
Dim Filter As Pivot.PivotFilter = myPivot.PivotFilters(index)
Filter.Value1 = “4169”
Filter.AutoFilter.Custom(0, FilterOperatorType.Equal, “4169”)
Filter.AutoFilter.Refresh() 'I tested without it
myPivot.RefreshData() 'I tested without it
myPivot.CalculateData() 'I tested without it
–> no filter set

When I declare the new filter, in my mind I thought that it was better with “ValueEqual” instead “CaptionEqual”

Dim index2 As Integer = myPivot.PivotFilters.Add(0, Pivot.PivotFilterType.ValueEqual)
Dim Filter As Pivot.PivotFilter = myPivot.PivotFilters(index2)
Filter.Value1 = “4169”
Filter.AutoFilter.Refresh() 'I tested without it
myPivot.RefreshData() 'I tested without it
myPivot.CalculateData() 'I tested without it

–> with ValueEqual, my Excel crashes when I open the file :
"We found a problem with some content in xxx.xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click yes

I click yes, and then this message whows:
Removed Feature: PivotTable report from /xxxxxx1.xml part (PivotTable view)

error043040_01.xml

Des erreurs ont été détectées dans le fichier « C:\Users\e01641\AppData\Local\Temp\OSRC-61.xlsx »Fonction supprimée: Rapport de tableau croisé dynamique dans la partie /xl/pivotTables/pivotTable11.xml (Mode Tableau croisé dynamique)

My Excel is:
Excel for Office 365 MSO (16.0.11328.20418) 32 bits

here is my code to download the file:
monExcel.Save(Me.Response, “OSRC.xlsx”, Aspose.Cells.ContentDisposition.Attachment, New Aspose.Cells.XlsSaveOptions(SaveFormat.Xlsx))

2

@fredlang,
Thank you for your query. Please share your source Excel file, program output file and an expected output file created with MS Excel along with the runnable console application with us for our testing. We will reproduce the problem and provide our feedback after analysis.

3

Hi,

Thanks for your reply.

I cant give you my real project becauseof confidentiality.

I created a sample project.

The first sheet contains data. The second sheet contains pivottable.

I want to filter in the region column (like the screenshot_1.png)
Region=Central for example

I tested with a lot of code, but no one works. Can you give me the code to filter the region?

If not possible, I can create the filter manually, but I would have to change the filter value in code.

I dont put the licence file, and I dont put the dll because it s too big. (I tested with the latest version)

In the zip I save in SaveFormat.Excel97To2003 but I tested (and I want this format) SaveFormat.Xlsx

Thanks a lot
test pivottable excel aspose.zip (6.5 MB)

4

@fredlang,
We are analysing this information and will share our feedback soon.

5

@fredlang,
We are working over your requirement but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as
CELLSNET-47037 – Adding filter to PivotTable

6

@fredlang

Please try our latest version/fix: Aspose.Cells for .NET v19.11.8 (attached).

Your issue should be fixed in it. See the sample code for your referfence:
e.g
Sample code:

Workbook wb = new Workbook(filePath + "SampleData.xlsx");

PivotTable pivot = wb.Worksheets[1].PivotTables[0];
pivot.AddFieldToArea(PivotFieldType.Page, "Region");
PivotField pageField = pivot.PageFields[0];
pageField.CurrentPageItem = 0;

PivotFieldCollection columnFields = pivot.ColumnFields;
PivotField firstField = columnFields[0];
firstField.HideItemDetail(2, false);

pivot.RefreshDataFlag = true;
pivot.RefreshData();
pivot.CalculateData();
pivot.RefreshDataFlag = false;

wb.Save(filePath + "out.xlsx");

Let us know your feedback.
Aspose.Cells19.11.8 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells19.11.8 For .Net4.0.Zip (5.0 MB)