Failed to refresh pivot in an Excel workbook

Aspose.Cells Product Family
1

Hi,Im using the following code to refresh all the pivot table in an excel workbook: 

 //Refresh pivot table in each worksheet
                foreach (Aspose.Cells.Worksheet worksheet in workBook.Worksheets)
                {
                    foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
                    {
                        pivotTable.RefreshData();
                        pivotTable.CalculateData();
                        saveFileAtEndOfoperation = true;
                    }
                }
 //Refresh pivot table in each worksheet
                foreach (Aspose.Cells.Worksheet worksheet in workBook.Worksheets)
                {
                    foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
                    {
                        pivotTable.RefreshData();
                        pivotTable.CalculateData();
                        saveFileAtEndOfoperation = true;
                    }
                }
//save workbook...

It worked the first time it runs, but failed for the second run. I have attached 2 workbook for you to test with. SampleSalespersonReports.xls is the original workbook. SampleSalespersonReports (1).xls is the refreshed workbook.

If you run the refresh against SampleSalespersonReports.xls, it will pass. If SampleSalespersonReports.xls(1), it will fail. If you open SampleSalespersonReports.xls(1) in Excel, there will be a protective view popping up.

Could you check if this is an issue that could be fixed in ASPOSE?

Thanks and regards

Bill

2
Hi,

Thanks for the template files.

I tried the following code to refresh the pivot tables in the template file: "SampleSalespersonReports+(1).xls", I got the error: "IndexOutOfRange....":

Sample code:

Workbook workBook = new Workbook("e:\\test2\\SampleSalespersonReports+(1).xls");

foreach (Aspose.Cells.Worksheet worksheet in workBook.Worksheets)
{
foreach (Aspose.Cells.Pivot.PivotTable pivotTable in worksheet.PivotTables)
{
pivotTable.RefreshData();
pivotTable.CalculateData(); //Error Occurred here.
//saveFileAtEndOfoperation = true;
}
}

//...........

I have logged a ticket for the issue with an id: CELLSNET-40042. We will look into it soon.

Thank you.
3

Thanks Amjad! The error "IndexOutOfRange.." is exactly what I got.

4
Hi,

We are working to support calculating pivot table which has grouping fields now. We will try to support this issue in the next week.

Thank you.
5

Hi,

.Please download: Aspose.Cells for .NET v7.0.4.4

We have fixed this issue.

6

The issues you have found earlier (filed as CELLSNET-40042) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.