PivotTable.RefreshData corrupting content

Aspose.Cells Product Family
1

Hi,

After calling PivotTable.RefreshData method seems the content of the workbook is corrupted.
The exception thrown when trying to use the content after calling the method is:

"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.Collections.ArrayList.get_Item(Int32 index)
at Aspose.Cells.Pivot.PivotFieldCollection.get_Item(Int32 index)"

To reproduce the issue please use the attached file and the following code:

var workbook = new Workbook(“test.xls”);

foreach (var worksheet in workbook.Worksheets)
{
foreach (var pivotTable in worksheet.PivotTables)
{
if (pivotTable.RefreshDataOnOpeningFile)
pivotTable.RefreshData();
}

var memoryStream = new MemoryStream();
workbook.Save(memoryStream, SaveFormat.Excel97To2003);

var newWorkbook = new Workbook(memoryStream);

Thank you,
Aurelian Iordache
Software Developer
IBM Romania

2

Hi,


Thanks for the template file and sample code.

After an initial test, I observed the issue as you mentioned. Using the following
sample code, I got the exception when opening the saved file from memory
stream:

“Index was out of range. Must be non-negative and less than the size of the collection.


Parameter name: index”


e.g


Sample code:


Workbook workbook = new Workbook("e:\\test2\\test.xls");

foreach (Worksheet worksheet in workbook.Worksheets)
{
foreach (PivotTable pivotTable in worksheet.PivotTables)
{
if (pivotTable.RefreshDataOnOpeningFile)
pivotTable.RefreshData();
}
}
var memoryStream = new MemoryStream();
workbook.Save(memoryStream, SaveFormat.Excel97To2003);
memoryStream.Position = 0;
var newWorkbook = new Workbook(memoryStream);

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

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

Thank you.
3

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.3.2.3 and let us know your feedback.

4

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
5

Hello,

It seems that this bug has only been partially fixed. It does not throw the ‘Index out of range’ exception anymore but the file is still reported corrupt by Excel:
"Excel was able to open the file by repairing or removing the unreadable content.
Repairs were made to PivotTable report ‘Draaitabel1’ on ‘[test.xls]Pivot’."

I have attached the xls document used and this is the code used to reproduce this behaviour:
—
var excelDocument = new Workbook(“test.xls”);

foreach (var pivotTable in from worksheet in excelDocument.Worksheets from pivotTable in worksheet.PivotTables where pivotTable.RefreshDataOnOpeningFile select pivotTable)
{
pivotTable.RefreshData();
}

excelDocument.Save(“test_out.xls”, SaveFormat.Excel97To2003);
—

InternalID: 14276

Thank you,
Stanescu Jean-Alexandru
Junior Developer
IBM Romania

6

Hi,


Thanks for providing us template file and some details.

After an initial test, I observed the issue as you mentioned. I found the output file is corrupted after refreshing PivotTable(s) in the template file. I used the following sample code with your template file:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\test.xls”);

foreach (Worksheet worksheet in workbook.Worksheets)
{
foreach (PivotTable pivotTable in worksheet.PivotTables)
{
if (pivotTable.RefreshDataOnOpeningFile)
pivotTable.RefreshData();
}
}

workbook.Save(“e:\test2\out1.xls”, SaveFormat.Excel97To2003);

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

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

Thank you.
7
Hi,

Please try our latest version/fix: Aspose.Cells for .NET v8.5.1.3

We have fixed your issue "CELLSNET-43786" now.

Let us know your feedback.

Thank you.
8

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
9

Hello again,

It seems that the issue is fixed only with the file provided. It is still reproducible with the attached file and following code:

var excelDocument = new Workbook(“testFile.xls”);

foreach (var pivotTable in from worksheet in excelDocument.Worksheets
from pivotTable in worksheet.PivotTables
where pivotTable.RefreshDataOnOpeningFile
select pivotTable)
{
pivotTable.RefreshData();
}

excelDocument.Save(“testOut.xls”, SaveFormat.Excel97To2003);

When opening testOut.xls, it says it is corrupted and repairs it: PivotTable report ‘Draaitabel1’ on ‘[testOut.xls]Log’ was discarded due to integrity problems.

InternalID: 14276

Best regards,
Stanescu Jean-Alexandru
Junior Developer
IBM Romania

10

Hi,


Thanks for the template file and sample code.

You are right. I observed the issue for your newly attached file. I am using latest Aspose.Cells for .NET version v8.5.2. I found the output file is corrupted after refreshing PivotTable(s) in the template file. I used the following sample code with your template file:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\TestFile.xls”);

foreach (Worksheet worksheet in workbook.Worksheets)
{
foreach (PivotTable pivotTable in worksheet.PivotTables)
{
if (pivotTable.RefreshDataOnOpeningFile)
pivotTable.RefreshData();
}
}
workbook.Save(“e:\test2\out1.xls”, SaveFormat.Excel97To2003);

I have reopened your issue “CELLSNET-43786” again. We will look into it soon.

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

Thank you.
11

Hi,


I appear to be getting the same issue in an older xls workbook which is causing an issue in our program. I was just wondering if there was any update to this ticket?

For reference the code I am using to refresh the pivot table is slightly different and I’ve included it below:

foreach (PivotTable pt in ws.PivotTables)
{
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.RefreshDataFlag = false;
pt.CalculateData();
}
12

Hi,

Thanks for your posting and using Aspose.Cells.

Such issue can occur on different type of pivot tables with different type of reasons. So we recommend you first download and try the latest version: Aspose.Cells
for .NET v8.5.2.3 and see if it does resolve your issue.

If your issue still occurs, then please provide us your source excel file containing your pivot table and causing this issue. We will look into it and help you asap. Thanks for your cooperation.

13

Hello. Do you happen to have an update for our issue CELLSNET-43786? Thanks!


14

Hi Raluca,


I am afraid, we haven’t yet received updates regarding the ticket CELLSNET-43786. We have recorded a note for the concerned member of the product team to share the insight of the issue and most preferably an estimated release schedule for the fix. As soon as we get any news in this regard, we will post here for your kind reference.

Thank you for your patience with us.
15

Hi,

Thanks for using Aspose.Cells.

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

Please spare us some time till 30th October 2015. Hopefully, you will get a fix till then.

16

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.6.1.1 and let us know your feedback.

17

Hello,

I have tested the scenario with your latest version and, indeed, it seems to work. Could you please provide the Cells v8.6.1.1. for .NET Framework 4.5?

Best regards,
Jean-Alexandru Stanescu
Junior Developer
IBM Romania

18

Hi,


We will check if we could provide you the 3.5 compiled version of the fix (v8.6.1.x), we may post the required version here. Otherwise you have to wait for at least couple of weeks as our next official version (v8.6.2) is due in the third week of November 2015 (which would include your required .NET framework compiled version).

Thank you.