SetSharedFormula problems on xlsx

Hello,
I’m using Aspose.Cells for .NET v7.0.2.0. It seems that the SetSharedFormulas does not always work correctly on an XLSX file. When I run the following code using a new or existing xlsx file, the cell(s) containing the shared formula are incorrect. Although the Formula property correctly has “=$D$6”, the R1C1Formula property has “=R6C3” when it should have “=R6C4”. This problem does not happen when using the xls format type.

public void SetSharedFormulaWithAbsoluteRefTest()
{
//var workbook = new Workbook(@“E:\Temp\Blank.xlsx”); //existing file
var workbook = new Workbook {FileFormat = FileFormatType.Xlsx}; //new file

var worksheet = workbook.Worksheets[0];

var originalCell = worksheet.Cells[17, 16];
originalCell.Formula = “=$D$6”;
Assert.That(originalCell.Formula, Is.EqualTo("=$D$6") );
Assert.That(originalCell.R1C1Formula, Is.EqualTo("=R6C4"));

originalCell.SetSharedFormula(originalCell.Formula, 2, 1);

var sharedCell = worksheet.Cells[18, 16];
Assert.That(sharedCell.Formula, Is.EqualTo("=$D$6"), “Shared formula is incorrect”);
Assert.That(sharedCell.R1C1Formula, Is.EqualTo("=R6C4"), “Shared formula reference is incorrect”); //this fails
}

Please note, this is a problem for me because I eventually need to convert the file to SpreadsheetML format, and would like to use the LimitAsXls = false option, so that columns past IV will work. When I save the xlsx created above (using the following code) the shared formula is incorrect.

workbook.Save(@“E:\Temp\Result.xml”, new SpreadsheetML2003SaveOptions { LimitAsXls = false });

Hi,

I have tested your problem with the following code using the latest version:
Aspose.Cells for .NET v7.0.2.7 and I was able to reproduce this.

I have logged this issue in our database. We will fix it asap and let you know.

This issue has been logged as CELLSNET-40088.

Please see the code and the comments.

C#


var workbook = new Workbook { FileFormat = FileFormatType.Xlsx }; //new file


var worksheet = workbook.Worksheets[0];


var originalCell = worksheet.Cells[17, 16];

originalCell.Formula = “=$D$6”;



Debug.WriteLine(originalCell.Formula); //Prints “=$D$6” — OK

Debug.WriteLine(originalCell.R1C1Formula); //Prints “=R6C4” — OK



originalCell.SetSharedFormula(originalCell.Formula, 2, 1);


var sharedCell = worksheet.Cells[18, 16];


Debug.WriteLine(sharedCell.Formula); //Prints “=$D$6” — OK

Debug.WriteLine(sharedCell.R1C1Formula); //Prints “=R6C3” — Wrong



Hi,

We have fixed this issue. Please download: Aspose.Cells for .NET v7.0.3.0