I have used a formula like below, means when there is sheetname soon after the ‘=’ sign then additional symbol ‘@’ comes it self while exporting the excel.
e.g:
string strFormula = "IFERROR(IF(MATCH(1,(D1=‘sheet name’!D:D)*…so on
but in excel file this formula comes like:
"IFERROR(IF(MATCH(1,(D1=@‘sheet name’!D:D)*…so on
means extra @ comes itself. so cell formula doesn’t work.
Can someone please help on it,
Please try our latest version/fix (Aspose.Cells v21.9.x) if you are not already using it. If you still find the issue with latest version, kindly create a standalone console application (runnable), zip the project to post us with template Excel files. We will check your issue soon.
By the way, if this is an array formula, you may try to use SetArrayFormula() method instead of Formula attribute.
Yes, it worked, Many thanks.
Good to know that latest version fixes your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
@amjad.sahi
I am facing the same issue in the Aspose.Cells version v23.8.0, v23.10.0 as well as v23.11.0
I have an ‘.xlsx’ file pre-filled with formulae. The extra “@” character is introduced in this formulae while saving this excel file and the cell formula does not work.
Could you please help?
@Phalguna_Y
Would you like to provide your sample file and executable Console project? We will check it soon.
Hi @John,
Replying behalf of Phalguna_Y.
I have attached the sample output file ‘AsposeFormulaIssue_output.xlsx’ and console project. The cells highlighted in red are having @ symbol in formula.
This cells_obj.setFormula() method causing this issue we think. Please review. Thank you.
AsposeFomulaIssue.zip (20.8 KB)
I simply checked your output file “AsposeFormulaIssue_output.xlsx” by opening the file into MS Excel 2019 manually. I could not spot any “@” sign there in your mentioned formula cells (in red). See the screenshot attached.
sc_shot1.png (90.5 KB)
Which MS Excel version you are using? How we could spot those “@” sign in the formulas?
@veera11693, @Phalguna_Y
Aspose.Cells follows MS-Excel specifications. This phenomenon is caused by the difference in text processing and display between WPS and Excel. By testing with sample code in the latest version v23.12. In Excel 2016, we cannot see the @ symbol, but in WPS, we can observe the @ symbol. Please refer to the attachment (111.5 KB).
Are you using WPS to view the result file? If so, this phenomenon is caused by differences in text display processing between WPS and Excel. If not, please share more detailed information, such as Excel version, Aspose.Cells version, etc. We will check it soon.
@veera11693
Newer version of ms excel introduced the new type of formula which is called Dynamic array formula. If one formula’s result is a set of values instead of single one and you set it as a common formula instead of dynamic array formula, newer version of excel will add ‘@’ automatically to make it give a single value.
When you using setFormula(value), the formula will be set as a common formula, so sometimes you may see ‘@’ when view the formula in ms excel. To set dynamic array formula, please use setDynamicArrayFormula(arrayFormula, options, calculateValue) instead.