Using Array parameters in Custom Function in Excel spreadsheet in .NET

Aspose.Cells Product Family
1

I'm implementing a CalculateCustomFunction and have a question about using ranges in custom functions.

In a case when we have a range as a parameter it is passed in CalculateCustomFunction as an array of objects. Let's say we have:

  1. =Func(A1:A3) in B1
  2. =Func(C4:C6) in D5

In both cases we have an array - something like {1,2,3} - as a parameter in "paramList[0]". In the 1) case we need paramList[0][0]. But in the 2) case we need paramList[0][1].

So the question is - how to get an offset in the array in order to get proper values. The cell's row&column cannot be used here as the ranges are shifted relatively B1 and D5 cells.

For example, if we replace Func() formula by Excel's SIN() then it will return SIN(A1) value for 1) and SIN(C5) value for 2).

I could use "contextObjects" to get a string representation of the formulas. It's easy for ranges like "A1:A3". But more complex scenarios require a full syntax analysis. The things like this are already implemented in your product. So it will be logical to add some kind of information about the offsets in the function's input parameters.

Thanks,
Leo

2

Hi,

Thanks for your posting and considering Aspose.Cells for .NET.

We have forwarded your question to our development team and have logged this issue in our database. Once we get any update regarding it, we will let you know asap.

This issue has been logged as CELLSNET-31100.

3

Hi,

If we add ReferredAreaCollection to contextObjects, does it fit your need?

4

Hi,

My last post disappeared... Just in case, I repeat it again:

Yes it does. So that I can use ReferredAreaCollection with the ArrayList of parameters.

Thanks,
Leo

5

Hi,


No problem, we noted it down already.

Aspose Forums (on the site) were not working yesterday and there might be a few posts disappeared in the threads (posted on October 12, 13 etc.). Our Web team has already fixed the Aspose.Forums issues. They are also trying hard to make the lost/deleted posts available again in these threads.

Thank you.
6

Hi,

We have added ReferredAreaCollection to contextObjects.

Please download:
Aspose.Cells for .NET v7.0.2.1