PivotTable.RefreshData throw error 'Index out of range'

Aspose.Cells Product Family
1

Hi,

When I call PivotTable.RefreshData method I got the exception
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Here is the part of the code that generate error:

            foreach (var sheet in excelFile.Worksheets)
            {
                foreach (var pivotTable in sheet.PivotTables)
                {
                    pivotTable.RefreshData();
                    pivotTable.CalculateData();
                }

                foreach (var chart in sheet.Charts)
                {
                    chart.RefreshPivotData();
                    chart.Calculate();
                }
            }

Please find the the file attached.

Bank Reconciliation.zip (70.7 KB)

I am not sure if it’s enough for diagnosing but feel free to let me know if you need more info.

Thanks for the help.

Shuxiao Miao

2

@SNP_Lightman,

Thanks for the template file and sample code.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample code with your template file. I found that using PivotTable.RefreshData throws ‘Index out of range’ exception:
e.g
Sample code:

 Workbook excelFile = new Workbook("e:\\test2\\Bank Reconciliation.xlsb"); 

            foreach (Worksheet sheet in excelFile.Worksheets) 
            { 
                foreach (PivotTable pivotTable in sheet.PivotTables) 
                { 
                    pivotTable.RefreshData();//exception 
                    pivotTable.CalculateData(); 
                } 

                foreach (Chart chart in sheet.Charts) 
                { 
                    chart.RefreshPivotData(); 
                    chart.Calculate(); 
                } 
            }

I have logged a ticket with an id “CELLSNET-46279” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

3

@SNP_Lightman,

This is to inform you that we have fixed your issue CELLSNET-46279 now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

4

@SNP_Lightman,

Please give a try to our latest fix Aspose.Cells for .NET 18.8.1 which should resolve your issue.

Let us know your feedback.

5

@Amjad_Sahi Thanks for the quick response. Do you know when this version will be released on nuget?

6

@SNP_Lightman,

Well, you may use this fix as an official release for production, it includes all the functionality full fledged (of v18.8) and includes other enhancements and fixes. However, we are scheduled to release our next official release (i.e., Aspose.Cells for .NET v18.9) to Nuget repos. in the third or fourth week of September 2018 (the release will include all previous fixes including this one). Please note only official releases (we publish once in a month) are published to Nuget repos.

7

The issues you have found earlier (filed as CELLSNET-46279) have been fixed in Aspose.Cells for .NET v18.9. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi