Can't use formula as parameter of CustomFunction

Aspose.Cells Product Family
1

Hello,

It looks like i found bug in custom function implementation. See attached project. I use Aspose.Cells.dll 7.2.2.0.

In order to reproduce, run attached project and you will get exception.
2

Hi,


Thanks for the sample project.

I have tested to run your project and found the issue as you have mentioned. We need to further investigate it if it is an issue or we have some other way/workaround to cope with your custom function’s parameters (e.g B1+B1). We will do it soon.

I have logged a ticket with an id: CELLSNET-40818. We will look into it soon.

Thank you.
3

Hi,

Thanks for your patience and using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v7.2.2.7

Please remove space characters from your formula, so the parameter should be now B1+B1

Please change your line of code into the following.

C#


//Adding custom formula to Cell A1

workbook.Worksheets[0].Cells[“A1”].Formula = “=MyFunc(B1+B1,C1:C5)”;


And your custom function should look like this.

C#
public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)
{
//First parmeter is the sum of B1+B1.
double sumOfB1plusB1 =Convert.ToDouble(paramsList[0]);

//Second parmeter is the range of cells from C1 to C5
//Add their values into a sum
object[][] o2 = paramsList[1] as object[][];

double sumofC1TillC5 = 0.0;

foreach (object[] on in o2)
{
sumofC1TillC5 = sumofC1TillC5 + Convert.ToDouble(on[0]);
}

//Sum both of them and return
return sumOfB1plusB1 + sumofC1TillC5;
}


Please see the complete code below. I have attached the output xlsx file and the screenshot for your reference.

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Collections;

namespace ErrorInCustomFunctions
{
public class CustomFunction : ICustomFunction
{
public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)
{
//First parmeter is the sum of B1+B1.
double sumOfB1plusB1 =Convert.ToDouble(paramsList[0]);

//Second parmeter is the range of cells from C1 to C5
//Add their values into a sum
object[][] o2 = paramsList[1] as object[][];

double sumofC1TillC5 = 0.0;

foreach (object[] on in o2)
{
sumofC1TillC5 = sumofC1TillC5 + Convert.ToDouble(on[0]);
}

//Sum both of them and return
return sumOfB1plusB1 + sumofC1TillC5;
}
}

class Program
{
public static void Main(string[] args)
{
Workbook workbook = new Workbook();

//Obtaining the reference of the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

//Adding a sample value to "A1" cell
worksheet.Cells["B1"].PutValue(9);

//Adding a sample value to "A2" cell
worksheet.Cells["C1"].PutValue(100);

//Adding a sample value to "A3" cell
worksheet.Cells["C2"].PutValue(150);

//Adding a sample value to "B1" cell
worksheet.Cells["C3"].PutValue(60);

//Adding a sample value to "B2" cell
worksheet.Cells["C4"].PutValue(32);

//Adding a sample value to "B2" cell
worksheet.Cells["C5"].PutValue(62);

//Adding custom formula to Cell A1
workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1+B1,C1:C5)";

//Calcualting Formulas
workbook.CalculateFormula(false, new CustomFunction());

//Assign resultant value to Cell A1
workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);

//Output the workbook
workbook.Save("output.xlsx");
}
}
}

Screenshot:
4

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.