PPMT formula is causing file recovery after CalculateFormula()

Aspose.Cells Product Family
1

Hello,

We’ve encountered an issue when loading, calculating and then saving an excel file containing a PPMT formula (PPMT.xlsx file in the attached zip file).

I’ve provided a small console application for testing, if you run in debug mode and then you try to open the \AsposeCells.InvalidFile.AfterSave\bin\Debug\TestFiles\PPMT_AfterSave.xlsx file, the following error is reported: "Excel found unreadable content in PPMT_AfterSave.xlsx’, then after recovery the following details are reported:
<<Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Repaired Records: Cell information from /xl/worksheets/sheet1.xml part>>

Aspose version is : 7.3.0.0 .

Please provide some feedback.

Thanks in advance,
Mihai Andrei,
Sr. Software Engineer,
IBM Romania

2

Hi,

Thanks for your project files and using Aspose.Cells for .NET

I was able to replicate this problem using the following sample codes with the latest version:
Aspose.Cells
for .NET v7.3.0.1


We have logged this issue in our database. We will look into it and fix the problems and once there is some fix available or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-40874.

I have attached the source file, output file and screenshot for a reference.

C#


string filePath = @“F:\TestFiles\PPMT.xlsx”;


Workbook wb = new Workbook(filePath);

wb.CalculateFormula(false);

wb.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);


C#
string filePath = @"F:\TestFiles\PPMT.xlsx";

byte[] fileContent = File.ReadAllBytes(filePath);
Console.WriteLine("File content successfully retrieved");

using (MemoryStream memoryStream = new MemoryStream(fileContent))
{
Workbook workbook = new Workbook(memoryStream);

workbook.CalculateFormula(false);

byte[] fileContentAfterSave;
using (MemoryStream memoryStream2 = new MemoryStream())
{
workbook.Save(memoryStream2, SaveFormat.Xlsx);

fileContentAfterSave = memoryStream2.ToArray();
}

File.WriteAllBytes(filePath + ".out.xlsx", fileContentAfterSave);
Console.WriteLine("Saved file with Aspose");
}//using

Screenshot:
3

Hi,

Thanks for using Aspose.Cells.

We have looked into this issue and found that the issue is caused by nper is too huge(8000).

And we think you must have input an invalid formula, the correct formula should be =PPMT(Data!AJ4,Data!AK4,Data!AL4,Data!W4)

Anyway, we will fix this issue soon.

4

Hi,

We have fixed this issue.
.
Please download and try this fix: Aspose.Cells for .NET v7.3.0.2

5

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

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