Hello Aspose team,
I have a problem or question. I’m using Aspose to let the user create formulas in different cells. When user provides the formula, if the worksheet has space then it should be wrapped in single quotes. For other R1C1 style worksheet names, even if we don’t wrap the worksheet name in single quotes, Excel will fix it when it opens. My question or problem is: “Do we have any mechanism to decide if we should wrap a woksheet name used in a formula in single quotes?”
E.g.
Let’s say we have 3 worksheets : “Test”, “A123”, “Test Space”
If I set the formula in a different sheet (e.g. ref) like this:
worksheet.Cells[0,0] = “Test!A1” ; // result in Excel = Test!A1
worksheet.Cells[1,0] = “A123!A1” ; // result in Excel = ‘A123’!A1
worksheet.Cells[2,0] = “Test Space!A1” ; // result in Excel = @With Space!A1
So, although I passed the R1C1 style sheetname (i.e. A123) without single quotes, Excel automatically wrapped it, but that wasn’t the same for “Test Space” worksheet!
So, how can we detect if the worksheet names should be wrapped with single quotes or not?
Thank you
@yashar.heydari
Currently, Aspose.Cells does not support detecting whether worksheet names should be wrapped in single quotes. As an alternative, you can add single quotes to all worksheet names.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-54376
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
1 Like
Thank you John,
I think Aspose is handling this to some extent. If we add Named Items to Names Manager in Excel pointing to cells in these worksheets. ( e.g. Name1 refers to A123!A1, and Name2 refers to Test!A1) then when we try to fetch these names from Workbook.worksheets.Names using indexer, it doesn’t care about the single quotes and it returns where we ask for A123!A1 or ‘A123’!A1 and the result names will have the correct single quotes.
Are you internally trying to fetch both with/without single quotes?
Cheers,
@yashar.heydari
Because the examples(e.g. Name1 refers to A123!A1, and Name2 refers to Test!A1) you provided do not include special characters. During program execution, we have logic to handle single quotes. At present, we do not provide a public API for users to call. We hope to provide this feature soon. Once there are updates, we will notify you immediately.
1 Like
@yashar.heydari
We will return references with or without single quotes as needed
If the name of the worksheet has special characters or is the same as the unit name, single quotation marks are required
If you are not sure which names need to be enclosed in single quotes, you can include all of them in single quotes.
We can support above two formulas though the second formula need single quotes.
This formula could not be supported because "Space“ is a region operator in Excel
@yashar.heydari,
This is to inform you that your issue (logged earlier as “CELLSNET-54376”) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.11) that we plan to release in the first half of November. You will be notified when new versions are released.
The issues you have found earlier (filed as CELLSNET-54376) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi
Thank you team for supporting this. Could you please point me to the API or documentation added for this as well?
@yashar.heydari
Aspose.Cells adds CellsHelper.NeedQuoteInFormula() method to check if quotes need to be added.
Please check the release notes and apis.
1 Like