Formulas containing names that don't exist show up as `WorkbookName`!Name

Aspose.Cells Product Family
1

Hi,

We recently encountered a challenge where if we created cells with formulas containing names that don’t exist, Cell.Formula would display the name as ‘workbookName’!name.

For example, if the workbook is “Workbook Name.xlsx”, and the formula of a cell when seen through excel is “=some_name_that_exists + some_name_that_doesnt_exist”, getting the cell’s formula using aspose returns:

=some_name_that_exists + ‘Workbook Name.xlsx’!some_name_that_doesnt_exist

We would like to know if this is expected or a bug. If it’s expected, what are the reasons behind this design decision?

In addition, we found that these names that don’t exist do not show up in Excel’s names manager, but Aspose will still find and create these names with refers to being null.

Thank you

2

@jasonleecanalyst,
Please share your template Excel file, runnable console application and expected output for our analysis. We will reproduce the issue here and provide assistance accordingly.

3

`
string path = “Sample Workbook.xlsx”;

Workbook workbook = new Aspose.Cells.Workbook(path);

Aspose.Cells.Worksheet worksheet = workbook.Worksheets[“Sheet1”];

string formula = worksheet.Cells[0,0].Formula.ToString();

/*

We expect formula to be “=name_that_doesnt_exist”

But you will see that it is “=‘Sample Workbook.xlsx’!name_that_doesnt_exist”

*/
`

I am trying to upload my excel file but your forum doesn’t allow for excel attachements.

You can use this code snippet by creating an excel file, and populating the very first cell in Sheet1 with the formula “=name_that_doesnt_exist”

4

@jasonleecanalyst,

We need your template file to evaluate your issue precisely. Please zip your template Excel file prior attaching it here.

5

Sample Workbook.zip (6.7 KB)

6

@jasonleecanalyst,
I have tested your sample code with the template file but could not observe any issue as the formula is retrieved as expected. You may try the scenario using the latest version Aspose.Cells for .NET 20.7 and share the feedback. If the issue is not resolved, share a runnable simple solution which can be compiled and executed here for testing this issue.

Formula.PNG (25.6 KB)

7

Sample Workbook.zip (7.2 KB)

My apologies. Can you try again with this file instead?

Here, the cell has a worksheet-scoped name that doesn’t exist, the formula is “=Sheet2!name_that_doesnt_exist”

Yet when you get the formula, it becomes ‘Sample Workbook’!name_that_doesnt_exist

8

@jasonleecanalyst,

Thanks for the new file.

Yes, we noticed this behavior by Aspose.Cells. Well, the formula in A1 evaluates to “Invalid Formula error” and it is Aspose.Cells style that sets the invalid name “#NAME?” to give you output in the format like, “file_name!name_that_doesnt_exist” I guess this does not matter whether you get “=‘Sample Workbook.xlsx’!name_that_doesnt_exist” or “=Sheet2!name_that_doesnt_exist” as both evaluate to give you “#NAME?” error as result. Do you need to get it as “=Sheet2!name_that_doesnt_exist”?

9

Hi,

We would like to get it as Sheet2!name_that_doesnt_exist. This is in the case where in the future, that name might actually be added into the workbook. If that’s the case, we do not want to lose context of the worksheet it should be scoped to in the formula.

Please let me know if that’s possible, thank you.

10

@jasonleecanalyst,
We have noted this behavior and logged it in our database for further analysis to maintain the sheet name instead of replacing it with the workbook name in the formula. We will write back here once any update is ready to share.

This issue is logged as:
CELLSNET-47531-Formulas containing names that don't exist show up as WorkbookName!Name

11

@jasonleecanalyst,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47531”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

12

@jasonleecanalyst,

Please try our latest version/fix: Aspose.Cells for .NET v20.8.1 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells20.8.1 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.8.1 For .Net4.0.Zip (5.4 MB)

13

Hi, sorry for the late response.

This works. Thanks.

14

The issues you have found earlier (filed as CELLSNET-47531) have been fixed in Aspose.Cells for .NET v20.9. This message was posted using Bugs notification tool by Amjad_Sahi
You may also get the new version @ nuget repos. (NuGet Gallery | Aspose.Cells 20.9.0)