Adding Event to signal unrecognized functions in formula's

Aspose.Cells Product Family
1

Hi,

We have been using Aspose.Excel very successfully already for a number of situations. And we are planning to use it to convert some old excel based VBA app to an ASP.NET app.

This application makes use of formulas that reference VBA functions in the excel file. We are aware that it would be rather difficult to support VBA functions in the excel. Therefore we are planning to convert the functions to a .NET equivalent. Porting looks to be very straightforward for the functions that we have. The only remaining link to be able to execute these functions is to have a notification of the Aspose.Excel component when it encounters such an unknown function during the CalculateFormula method, and the Aspose.Excel component to pass along the Worksheet, the current Cell, the function name, the parameters.

The function can then be executed by our code and the result returned to Aspose.Excel for further use.

e.g. =IF(CalcFunc(A1) > 100, 0.1,1)

Then we would expect an event with a custom EventArgs that would pass along the worksheet (to be able to have references back to other cells, ranges, etc.), the current Cell (not needed right away, but seems good to have), the function name (string = "CalcFunc"), the parameters (= object Array {Cell = reference to cell A1)}. The return value can then be passed back to Aspose.Excel in the custom EventArgs class (object ReturnValue (property get/set)).

I feel confident that you will be able to add this event.
Can you please notify me asap if this would be feasible, and if so, what the expected development time would be for us to get a first test version (CTP), and an estimate on a full production version.

Thanks again already for the best support I have ever seen from a component vendor.

Kind Regards,

Rudi

2

Hi Rudi,

I have to discuss this issue with our team members. It is Friday evenining now in my place so I will reply you at the start of next week.

3

Hi Rudi,

We think it's better to provide an interface to allow you to extend the formula calculation engine.

We will define an interface:

public interface ICustomFunction
{
object CustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects);
}

functionName: "CalcFunc"

paramsList: - value of cell A1, not reference to A1. If you want to get the reference, please pass "A1" string to this method, then manipulate it by you own code.

contextObjects: current Worksheets object, Worksheet object and Cell object

Will this serve your need? If yes, we can make it in about 2 weeks.

4

Hi Laurence,

A few quick remarks about the Interface:

paramsList: I see no problem only getting the value for single cell parameters. But how would you see it to allow to pass along a range (e.g.: =MyFunc(A1:A12,true) )

contextObjects: What would be the additional benefit of making this an ArrayList as opposed to 3 fixed references? The ArrayList would always be a fixed ordered list of the three objects.

In general, how would you see us 'register' an object implementing this interface?

Rudi

5

Hi Rudi,

For A1:A12, I will give an object array in the first parameter.

contextObjects can be 3 object: Worksheets, Worksheet and Cell. And if you need more, I can add more contextObjects in the list. It can be a fixed ordered list.

To allow you register, I will provide a new CalculateFormula method:

public void CalculateFormula(ICustomFunction customFunction)

6

Hi Laurence,

We'd like you to go ahead with the implementation, it will be very usefull to us (and probably also others).

Can you give an estimate for a first version? Please contact me, since I am the Aspose programmer in our team.

Thanks,

TLB

7

I will start to make it at the start of next week and it will take about 2 weeks.

Please check our website back for this release. I will also reply here when we provide this feature.

8

Any news on this issue?

9

Sorry for not replying in time. Please download the latest version of Aspose.Cells. It allows you pass an interface to CalculateFormula method. Then you can use your own implementation for unrecognized functions.