Aspose.Cells.Cell.Formula drops the single quotes from Sheet Names with Extra Characters

Aspose.Cells Product Family
1

Hi,
I’m using Aspose.Cells (22.4.0) and when I set a Formula in the Cell.Formula, the single quotes are dropped.
For example, When I am trying to set a cell.Formula like, the only time it preserves the Single Quotes is when the sheet name contains a space:

Formula = “=‘Sheet1’!$C$4” ; result --> “=Sheet1!$C$4” --Which Cause No Problem
Formula = “=‘Sheet One’!$C$4” ; result --> “=‘Sheet One’!$C$4” – Which is correct (Space)
Formula = “=’&Sheet1’!$C$4” ; result --> “=&Sheet1!$C$4” – Not Correct, Sheet Name starting with “&” should referred with Singles Quotes. Expected --> “=’&Sheet1’!$C$4”

Could you please check this issue?

Thanks

2

@yashar.heydari,

I tested using the following sample code and I could not find any issue. The formula in the output file is also fine tuned when opening the output file into MS Excel:
e.g.
Sample code:

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.Worksheets;
Worksheet worksheet = workbook.Worksheets[0];
Worksheet worksheet1 = worksheets.Add("&Sheet1");
worksheet.Cells[0, 0].Formula = "='&Sheet1'!$C$4";
Console.WriteLine(worksheet.Cells["A1"].Formula);//='&Sheet1'!$C$4
workbook.Save("e:\\test2\\out1.xlsx");

Could you provide more details on how I can find your mentioned issue, we will check it soon.

3

Hi @Amjad_Sahi ,
Thanks for prompt reply, this is my Unit Test Code, as I’m not directly working with file and worksheet now:

    [Test]
    [TestCase("=Model!$C$4", "=Model!$C$4" , TestName = "SetFormulaInCell_Simple Sheet Name in Formula")]
    [TestCase("='Model'!$C$4", "=Model!$C$4", TestName = "SetFormulaInCell_Quoted Sheet Name in Formula")]
    [TestCase("='&Model'!$C$4", "='&Model'!$C$4", TestName = "SetFormulaInCell_Quoted Ampersand Sheet Name in Formula")]
    public void SetFormulaInCell(string formula , string expected)
    {
        IRange formulaCell = BlankWorksheet.GetCell(1, 1); // A1

        formulaCell.Formula = formula; 
        Assert.AreEqual(expected, formulaCell.Formula);

    }
  • formulaCell is an Aspose.Cells.Cell
  • The first two tests pass
  • Third test case fails due to the deletion of the Single Quotes

Thanks for checking it…

4

There is no IRange class and Worksheet.GetCell method in Aspose.Cells APIs. It looks like you are mixing the things a bit or you are mixing the Aspose.Cells API with some other component. Please use only Aspose.Cells APIs if you can reproduce the issue. If true, please provide sample code using Aspose.Cells APIs only. We will check your issue soon.

5

Thanks for sharing your code, the tricky point was that you have to set your sheet name to the name used in formula ("&Sheet1"). If we don’t set the name of the sheet, it will drop the Single Quotes and Contains External Links will be ‘true’!!
I wonder if formula refers to multiple sheets, then the single quotes will be preserved for those referring to current sheet and will be dropped for others or not?!. Actually based on my issue, I believe it drops the single quotes for non-current sheet referrals in the formula, assuming that they are referring to external links!

6

@yashar.heydari,

When you perform the task in MS Excel manually (if there is no sheet (existed) with that name), then MS Excel also tries to get external reference/link. If we cancel setting with external reference file, it will give “#REF!” error or invalid reference. So, it does not matter whether to add or drop single quotes because this would be invalid link/reference.

1 Like