ICustomfunction : returning a range and not a single cell

dear support,


I am using the ICustomFunction which works well when the return cell is one cell.

However, when the source excel contains an UDF that spans multiple cells ( entered with the Ctrl,Shift, Enter keys ), it is impossible to return the results in all cells.

Ideally, the return should be an array of objects as to return an array of data from the UDF.
Now it is defined as a single object : Function CalculateCustomFunction(…) as object

Another way to see if the result is in an array is to look to the contextobjects. It seems contextobjects(2) gives me the cell that contains the UDF. An indeed, field contextobjetcs(2) is true, but I cant find any further indication of the source range to which the function was applied.

I have appended the source excel and the VB .NEt program

Any ideas on how to return a range ?








nw with attachment

Hi,

Thanks for your posting and using Aspose.Cells.

I have run your program and got the attached output pdf which looks fine to me. Do you see any problem with it, please explain it in detail so that we could investigate this issue further. Let us know where ICustomFunction is failing to fulfill your requirements.

thnaks for looking into it.


When the formula is entered over multiple cells, all cells are indeed update with the return value. That is correct - your screenshot shows this. However, that is rather useless.

However, I want my ICustomFunction to return multiple values and not a single value. The return object array ( not a single object ) can then be returned into Excel’s range.

So basicaly, the function should return an array of objects instead of a single value.
Function CalculateCustomFunction(…) as object(,)

This return array is then to be placed into the requesting cells ( now you are placing the return value into all requesting cells )

thanks for your consideration,

guido






Hi Guido,

Thanks for your explanation and using Aspose.Cells.

I have earlier attached your actual pdf. Could you please manually create your expected pdf? You can create it in some Excel file manually and save it to pdf. By comparing the actual and expected pdf, we will be in better position to understand your requirements fully and implement this feature in our future versions.

Thanks for you cooperation.

please find attached a word document with that explains the suggested functionality.


The idea is that function CalculateCustomFunction returns an object array and not a single object.

The results of this object array is then pasted into the excel, as shown in the screenshot.

If the dimensions of the calling excel range are smaller, only a subset of the object array is returned into excel. If the calling excel range is bigger, additional empty fields are appended.

This would be a great addition to Aspose.cells. I am looking forward to have it implemented in a future version.

thnaks,

guido






Hi Guido,

Thanks for explanation and using Aspose.Cells.

We have logged your requirement as a New Feature request in our database. We will look into it and support this feature. Once the feature is supported or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43703 - ICustomFunction - returning a range instead of a single cell

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for .NET v8.4.2.7
and let us know your feedback.

With latest fix and setting the custom function as array formula, you can get the expected result:


C#
Workbook wb = new Workbook();

Cells cells = wb.Worksheets[0].Cells;

Cell cell = cells[0, 0];

cell.SetArrayFormula(“=MYFUNC()”, 2, 2);

Style style = cell.GetStyle();

style.Number = 14;

cell.SetStyle(style);

CalculationOptions copt = new CalculationOptions();

copt.CustomFunction = new CustomFunctionStaticValue();

wb.CalculateFormula(copt);





class CustomFunctionStaticValue : ICustomFunction

{

public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)

{

return new object[][] {

new object[]{new DateTime(2015, 6, 12, 10, 6, 30), 2},

new object[]{3.0, “Test”}

};

}

}

hello, thnak you for your new version and answer.
I apologize for not being so good in C, so I have made an equivalent in VB .NET, which does not work.
What am I doing wrong ??
Resulting pdf is added in attachment.
thanks.


Module Module1
Sub Main()
Dim path As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase).Replace("file:\", "") & "\"
Dim f As New MWorksFunctions
Dim wb As New Aspose.Cells.Workbook
wb.Worksheets(0).Cells("A1").SetArrayFormula("=GUIDOTEST()", 2, 2)
wb.CalculateFormula(False, f)
wb.Save(path & "guido.pdf", Aspose.Cells.SaveFormat.Pdf)
End Sub
End Module
Public Class MWorksFunctions
Implements Aspose.Cells.ICustomFunction
Function CalculateCustomFunction(ByVal functionName As String, ByVal paramsList As System.Collections.ArrayList, ByVal contextObjects As System.Collections.ArrayList) As Object _
Implements Aspose.Cells.ICustomFunction.CalculateCustomFunction
Dim a As Object(,)
ReDim a(1, 1)
a(0, 0) = 3.5
a(0, 1) = DateTime.UtcNow
a(1, 0) = "ssss"
a(1, 1) = DateTime.Now
Return a
End Function
End Class

Hi,

Thanks for your posting and using Aspose.Cells.

I have tested the above sample C# code with the latest version: Aspose.Cells
for .NET v8.4.2.7
and it worked fine. I have attached the runnable sample C# console application project, please download and run it at your end.

I have also attached the output excel file and the output pdf file generated by the sample project for your reference.

We will look into it further and provide the equivalent VB.NET code soon.

Hi,

Thanks for using Aspose.Cells.

I have converted the TestProject.zip C# code into VB.NET code but it is not working. Here is the code for VB.NET. I have attached the VB.NET project for a reference.

I have reopened this issue for product team investigation and logged a comment. We will look into it and provide you a correct code or if there is some bug we will fix it. Once, there is some news for you, we will let you know asap.

VB.NET

Public Class CustomFunctionStaticValue

Implements Aspose.Cells.ICustomFunction

Function CalculateCustomFunction(ByVal functionName As String, ByValparamsList As System.Collections.ArrayList, ByVal contextObjects AsSystem.Collections.ArrayList) As Object _

Implements Aspose.Cells.ICustomFunction.CalculateCustomFunction

Dim o() As Object = New Object() {New Object() {New DateTime(2015, 6, 12, 10, 6, 30), 2}, New Object() {3.0, “Test”}}

Return o

End Function

End Class

hello support,


any progress on this ? thanks for your much appreciated help,

guido

Hi Guido,

Thanks for your posting and using Aspose.Cells.

There is no update for you at this momement. Please spare us some time. Once there is some update for you, we will let you know asap.

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.5.0.1 and let us know your feedback.

The issues you have found earlier (filed as CELLSNET-43703) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.