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