Aspose.Cells.Cell.Formula - Single quotes are removed from sheet name in formula

Aspose.Cells Product Family
1

Hi, I am using Aspose.Cells for .Net (version 22.9.0) and facing the following issue: when the excel workbook contains a formula of type =SUMIF(‘A&B’!$B$5:$BT$5,$H$5,‘A&B’!$B$58:$BT$58), the single quotes are removed and Cell.Formula is changed to =SUMIF(A&B!$B$5:$BT$5,$H$5,A&B!$B$58:$BT$58) if the workbook doesn’t actually contain a worksheet named “A&B”.
My requirement is to cache the original formula and then set that as the formula for a different cell later, irrespective of whether the original formula is valid or not. However, because of the single quote removal, the following exception is thrown when I try to set the formula in the second step: Invalid text(cell reference) for the defined name.
Could you please help me with this issue and also clarify in which exact scenarios quotes are removed from formulas?
Thanks in advance!

2

@AvneetK,

I tested your scenario/case a bit. I created a sample Excel in MS Excel manually which does not have “A&B” in it. I set the formula “=SUMIF('A&B'!$B$5:$BT$5,$H$5,'A&B'!$B$58:$BT$58)”. I saved the file in MS Excel (please find attached the file). Then, I tried the following sample code:
e.g.
Sample code:

Workbook wb = new Workbook("e:\\test2\\Bk_singlequotes1.xlsx");
foreach (Worksheet worksheet in wb.Worksheets)
{
      foreach (Cell cell in worksheet.Cells)
      {
              if (!cell.IsFormula) continue;
              string formula = cell.Formula;
              Console.WriteLine(formula);
 
      }
}

I got formula string as:
=SUMIF(A&B!$B$5:$BT$5,$H$5,A&B!$B$58:$BT$58)

It is by design as Aspose.Cells omits unnecessary single quotes around (invalid) sheet name (may be for performance considerations). I then checked the source xml data for the Excel XLSX file and found MS Excel actually saves it as:
'[1]A&B' which is again not in the format you want.

Please add/place quotes around such (invalid) sheet names manually after you get the formula string by Aspose.Cells APIs.
files1.zip (6.5 KB)

3

Thanks for the response Amjad! I’m still unclear on why single quotes should ever be removed from the sheet name portion of a formula, because sheet names can contain spaces/special characters, and we can’t use such names in an Excel formula without single quotes. It seems a little contradictory that Aspose modifies the original cell formula to a value that it itself considers invalid.

Workbook wb = new Workbook("e:\\test2\\Bk_singlequotes1.xlsx");
foreach (Worksheet worksheet in wb.Worksheets)
{
foreach (Cell cell in worksheet.Cells)
{
if (!cell.IsFormula) continue ;
string formula = cell.Formula;
Console.WriteLine(formula);
cell.Formula = formula; // ERROR

}
}

4

@AvneetK,

We will evaluate it further and get back to you soon.

5

@AvneetK,

We find the issue and have created one ticket for it(CELLSNET-52232). We will fix it soon.

6

Thank you! Please let me know when it is fixed.

7

@AvneetK,

Sure, we will keep you posted with latest updates once available.

8

@AvneetK,

This is to inform you that your issue has been resolved now. The fix will be included in our upcoming release (Aspose.Cells v22.11) which is scheduled in this week. You will also be notified when the next version is released.

1 Like
9

The issues you have found earlier (filed as CELLSNET-52232) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi