Using custom functions in WebGrid using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
,
1

I have seen the code example where a custom function is created using the iCustomFunction interface. My question is can this somehow be directly used in the aspose.cells.webgrid by doing = myFunction(a1,a2) in a cell in the grid control

Or is it case that I have to select the cell and use the right click context menu to call a method that will use aspose.cells to perform the function.

2

Hi,


I am afraid, there is no such interface available in Aspose.Cells.GridWeb, so Aspose.Cells.GridWeb does not support to set or calculate custom functions. Well, you may use Aspose.Cells APIs to do the custom function’s calculations (in the sheet) if it suits your needs.

Thank you.
3

So it’s not possible to save a custom function as a formula in the ASPOSE cell? The example seems to imply this is more for static data analysis. It calculates the value and then re-sets the cells value as a value rather than a formula. I really need to be able to save a custom function as a formula in an aspose cell, just like you would for any other function. Its kind of a deal breaker as the client currently uses an Excel Add-in client solution that has a custom function in multiple cells saved with the workbook. Perhaps I am not understanding?

4

Hi,


Thanks for providing us further details.

I have logged a ticket with an id “CELLSNET-44724” for your requested feature “Support custom functions (i.e., ICustomFunction interface similar to what Aspose.Cells library uses) to be used directly in Aspose.Cells.GridWeb” for your requested feature. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.
5

Additionally I ran through the sample on using the ICustomFunction example. I put the calling code behind a button on the webform I am using to test. On calling the function I got the attached error. I am using aspose.cells 8.9.0. I did not modify any of the sample code.

6

Hi,


Thanks for the screenshot.

Well, we did change the custom formula calculations in recent versions. If one parameter for the custom function is cell reference, in old version Aspose.Cells APIs used to convert the cell reference to one cell value or an object array of all cell values in the referred area. Since v8.5.0, the API just puts the ReferredArea object into the “paramsList” when the corresponding parameter is a reference or its calculated result is reference. If you need the reference itself then you can use the ReferredArea directly. If you need to get one single cell value from the reference corresponding with the formula’s position, you can use ReferredArea.GetValue(rowOffset, int colOffset) method. If you need cell values array for the whole area, then you can use ReferredArea.GetValues method. Please see the document for your complete reference:

I have also changed the code segments in accordance with latest APIs change, please refer to it and update your code segment accordingly:
e.g
Sample code:

…
object firstParamB1 = paramsList[0];
if (firstParamB1 is ReferredArea) //fetch data from reference
{
ReferredArea ra = (ReferredArea)firstParamB1;
if (ra.IsArea)
{
firstParamB1 = ra.GetValues();
}
else
{
firstParamB1 = ra.GetValue(0, 0);
}

}

//get value of second parameter
object secondParamC1C5 = paramsList[1];

decimal total = 0M;

if (secondParamC1C5 is ReferredArea) //fetch data from reference
{
ReferredArea ra = (ReferredArea)secondParamC1C5;
if (ra.IsArea)
{
secondParamC1C5 = ra.GetValues();
}
else
{
secondParamC1C5 = ra.GetValue(0, 0);
}

}

if (secondParamC1C5 is Array)
{

// your code goes here.
}
…

Also, we will soon update the document/articles in the Docs to accommodate the new change.

Thank you.

7

Hi,


We have updated the document/article (with example code) to make it up-to-date based on latest APIs set. Please see the document/article for your complete reference:
Using ICustomFunction Feature

Thank you.

8

Hi,


We have analyzed the ticket logged earlier as “CELLSNET-44724”. We have now added GridAbstractCalculationEngine to support this feature, please check the below code for your reference:
e.g
Sample code:

private class CustomEngineSimple : GridAbstractCalculationEngine
{
public override void Calculate(GridCalculationData data)
{
if (!“MYTESTFUNC”.Equals(data.FunctionName.ToUpper()))
{
return;
}
data.CalculatedValue = (decimal)(2.0 * (double)data.GetParamValue(0)); //using decimal for CELLSNET-44525
}
}


…
private void Page_Load(object Sender, EventArgs e)
{
if (!Page.IsPostBack )
{
if (!gridweb1.IsPostBack)
{
CustomEngineSimple ce = new CustomEngineSimple();
gridweb1CustomCalculationEngine = ce;
GridWorksheet sheet = this.activities44724.ActiveSheet;
GridCell cell = sheet.Cells[“A1”];
cell.Formula = “=MYTESTFUNC(3.0)”;
…
gridweb1.CalculateFormula();
…

We hope, we will soon perform QA for the supported feature and incorporate other enhancements and fixes. We will provide you the fixed version in the next week or so.

Keep in touch.

Thank you.
9

Sorry, I have been inundated with other development work and only now have had the time to return to this. Has this work been incorporated into a latest release yet?

10

Hi,


Thanks for your posting and using Aspose.Cells.GridWeb.

This has been fixed as mentioned previously and also code example was provided to you but the fix is present inside the version 16.10.0 which is not released yet. But it is expected in this month may be before 25th of this month.

11

Hi,


Thanks for using Aspose.Cells.GridWeb.

It is to inform you that latest release version 16.10.0 is now available. Please download it from this link and give it a try. Let us know your feedback.

https://downloads.aspose.com/cells/net

12

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

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