Unique function adding @ symbol before = and formula not working in dynamically created excel

Aspose.Cells Product Family
1

Hi, I am using Aspose.Cells for NET 3.0 version. I am creating excel dynamically and adding Unique formula in second sheet, here it is adding @ symbol before = sign and in the cell it is showing #NAME? error. Once we delete @ sign manually it is showing up the data properly. How to remove this @ symbol programmatically to avoid #NAME? error. Please suggest.

2

@AmitNamdev,

Could you please share your sample code (runnable) and sample file(s) to reproduce the issue on our end, we will check it soon.

PS. please zip the files prior attaching here.

4

PFA the similar code which is giving same result and if you suggest in this code it will be helpful to resolve the issueAsposeUniqueFormulaIssue.jpg (115.3 KB)

5

@AmitNamdev,

We are not sure about your type of underlying Data (definition) as you are importing custom objects in code, so might not accurately evaluate your issue. We appreciate if you could create a standalone console application in VS.NET, zip the project and post us (please exclude Aspose.Cells.Dll prior attaching here) to reproduce the issue on our end, we will check your issue soon.

6

Hi Please find the console application in VS,NET which is creating the excel file with Unique formula. Here I am getting @ sign and the formula is not working properly. I am expecting Unique values where formula applied in dynamically created excel file.
Please check and suggest how to remove @ sign and work Unique formula.
ExcelDownload.zip (4.1 MB)

Thanks,
Amit Namdev

7

@AmitNamdev,

Please notice, I am able to reproduce the issue using Office 365 Excel (viewer) by opening the output file (generated by Aspose.Cells after using your sample code) into it. I found “Unique” function is adding “@” symbol before the formula but it works fine in office 365 on my end. Anyways, we have logged a ticket with an id “CELLSNET-49070” for your issue. We will evaluate and look into it soon. The issue is also reproduced in common .NET framework versions (other than .NET Core/.NET Standard).

Once we have an update on it, we will let you know.

8

@AmitNamdev,

We evaluated your issue in details.

For UNIQUE function, as it may produce multiple values which should be spilled to multiple cells instead of a single one. If it is the case and you set the formula to a single cell, MS Excel will add ‘@’ automatically. If you input such kind of formula in MS Excel, MS Excel will take it as dynamic array formula and spill it to adjacent cells automatically.

For Aspose.Cells, currently we cannot support such kind of automatic operation. Instead, we provide a separate method to set such kind of formula. So, please change the code:

...
Cell cellA1 = sheet2.Cells["A2"];
cellA1.Formula = "=UNIQUE(NameSheet!B2:B11)"; //Here it is adding @ before UNIQUE formula in excel file 
...

to

...
Cell cellA1 = sheet2.Cells["A2"];
cellA1.SetDynamicArrayFormula("=UNIQUE(NameSheet!B2:B11)", new FormulaParseOptions(), true); 
...

Let us know if you still find any issue.

9

Hi,
Thanks for suggestion, but I am getting the same thing there is no difference I am seeing.
Still it is showing #SPILL! error. Please suggest the proper solution.

10

@AmitNamdev,

You are right. Now I get “#SPILL!” error in the cell after using the line of code instead, although “@” is not appended any more:

cellA1.SetDynamicArrayFormula("=UNIQUE(NameSheet!B2:B11)", new FormulaParseOptions(), true);

The issue can be seen when opening the output XLSX file into office 365 for Excel. I have reopened your issue again. We will evaluate it and look into it soon.

11

@AmitNamdev,

For “#SPILL!” error, it is because there is not enough spaces for the dynamic array formula to spill its calculated results. When you set the dynamic array formula in MS Excel manually, you will get the same error.

To remove this error, you should set this formula to another cell, there are enough empty cells from this cell to fill the calculated result. Or, you have to use ‘@’ sign to denote that you only need the first element of the calculated result and do not want the set formula to be taken as dynamic array formula.