Office addin custom function as formula

Aspose.Cells Product Family
1

Hi,

We’re using an Office Excel Addin which is implemented using Office JS API. It exposes some custom functions (e.g. Store.Availability) the syntax looks like this
=Store.Avail(“ItemName”, [“StoreName”])
e.g. =Store.Avail(“T-Shirt”)
When we type this directly into Excel cell, it returns a Numeric value.

Now when we try to set this formula into a cell using Aspose, using Formula property or SetFormula method and when we open the Excel, it shows #NAME. Although when we go to edit mode and press enter is calls the function!

Is there any way to make Excel show the value of calculated custom function when it opens?
I also compare the cells that are created manually vs using Aspose, and it seems the IsErrorValue is true for the Aspose-created cell.

2

@yashar.heydari,
Would you like to provide your sample file? We will check it soon.

3

I could share my sample file, but the add-in that I’m using its custom functions requires login before it’ll work. If that’s not an issue and you just want to check the cells, I can share it with you.

4

@yashar.heydari,
Yes, detecting the data in the file can help us locate the issue. Please share your sample file. We will check it soon.

5

@yashar.heydari
We need to check the file to find how to set your metioned formulas .

6

Thank you @John.He

Here is an example file with formulas in both columns A and B, Column A formulas is set using Aspose’s properties e.g. Formula property, SetFormula method with/without FormulaParseOption parameter, SetSharedFormula, SetDynamicArrayFromula, and non of them is called by excel ( considered as add-in formulas).
Formulas in column B is directly inserted into cell in Excel and they get calculated fine.

Result_13_35_49.zip (9.5 KB)

Thank you,

7

@yashar.heydari
Through the provided sample file for testing, we can reproduce the issue. It was found that the formula calculation cannot obtain the correct value.
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-54039

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.

8

@yashar.heydari
Please set formula as _xldudf_TEGUS_CD(“AMZN_US”,“MO_RIS_REV”,“FY2022”), not TEGUS_CD(“AMZN_US”,“MO_RIS_REV”,“FY2022”)

1 Like
9

Thank you it works this way.

10

@yashar.heydari,

Good to know that by setting the suggested formula works for your needs. Feel free to write us back if you have further queries or comments, we will be happy to assist you soon.

11

Thank you @amjad.sahi and @simon.zhao

Could you please provide some context around this name conversion ? Is this something we should do to make Excel Engine detect the Custom Functions? And is there a rule of thump to convert the function name/namespace to this format? e.g. what should we do if the function name had an _ in it ? e.g.
MY_TEGUS.CD (p1, p2, p3)

Thank you for knowledge sharing

12

@yashar.heydari,
The conversion rules for addin custom function are variable. Aspose.Cells cannot distinguish these rules and therefore cannot be automatically converted. You need to input all the addin custom formulas in Excel and view the final results of these formulas in Excel. You can also unzip the file and view the final saved data in the XML file.

13

@yashar.heydari,

The rules of converting those user defined functions are unknown to us too. From the “correct” expressions of column B in your file, it seems the ‘.’ should be replaced as ‘_’. But we are not sure whether there are some other rules determining this behavior. For your example MY_TEGUS.CD, we are afraid you have to try it in ms excel and check the xml content in the xlsx file saved by ms excel.

If we can get enough information about those rules, then we can make the detection and conversion automatic, without the need of doing the conversion manually by users. Currently because we cannot know those rules, we are afraid you have to do those works manually. And if you can share your findings during the process of those functions, it would be much appreciated and helpful for us to know them better and make it possible to provide proper operations and solutions for users.

14

Dear @John.He
have you got any news about the issue reported here? Is your internal ticket CELLSNET-54039 solved and available into a new Aspose Cells release?
In case not, have you a feasible workaround?

Thanks in advance

15

To give you further context: we have observed this issue in the Java API.

16

@diguida
There are currently no updates or alternative solutions regarding your issue. Once we have any new information, we will share it with you. We will get back to you soon.

17

@diguida
We have not investigated this feature further yet. If it is supported in future, it will be supported for both .NET version and JAVA version. However, we are afraid we cannot supported it in near future. According to our plan, we may investigate this feature in first half of the year 2024 and then we may know whether it is possible for user to add such kind of addins by APIs. When we have any progress for this task, we will update you here.