I created the sample program below. Please note this statement:
cells[“A3”].SetFormula("=sum(myrange())", 100);
I basically want myrange() to return a cells range and them the sum would compute the sum for all numbers in that range. This doesn’t seem to work though, the value of A3 is 0.
Is it possible to achieve this?
Thanks
static void Main(string[] args)
{
// Procedure1();
// Procedure2();
// Procedure6();
Procedure5();
Console.WriteLine(“Done!”);
Console.ReadKey();
}
private static void Procedure5()
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Get the cells collection in the sheet
Cells cells = worksheet.Cells;
cells[“A1”].SetArrayFormula("=myarrayfunc()", 1, 5);
cells[“A2”].SetArrayFormula("=A1:E1*100", 1, 5);
cells[“A3”].SetFormula("=sum(myrange())", 100);
workbook.CalculateFormula(false, new CustomFunction());
Console.WriteLine(“A1={0}”, cells[“A1”].Value);
Console.WriteLine(“B1={0}”, cells[“B1”].Value);
Console.WriteLine(“C1={0}”, cells[“C1”].Value);
Console.WriteLine(“A2={0}”, cells[“A2”].Value);
Console.WriteLine(“B2={0}”, cells[“B2”].Value);
Console.WriteLine(“A3={0}”, cells[“A3”].Value);
string fileName = “…\…\TemplateResult.xlsx”;
workbook.Save(fileName);
}
public class CustomFunction : ICustomFunction
{
public object CalculateCustomFunction(string functionName, System.Collections.ArrayList paramsList, System.Collections.ArrayList contextObjects)
{
if (functionName.ToLower() == “myfunc”)
{
// //get value of first parameter
// decimal firstParamB1 = Convert.ToDecimal(paramsList[0]);
//
// //get value of second parameter
// Array secondParamC1C5 = (Array)(paramsList[1]);
//
// decimal total = 0M;
//
// // get every item value of second parameter
// foreach (object[] value in secondParamC1C5)
// {
//
// total += Convert.ToDecimal(value[0]);
//
// }
//
// total = total / firstParamB1;
//
// //return result of the function
// return total;
return paramsList[0];
}
if (functionName.ToLower().Equals(“myarrayfunc”))
{
return new object[] {1, 2, 3, 4, 5};
}
if (functionName.ToLower().Equals(“myrange”))
{
Worksheet worksheet = (Worksheet) contextObjects[1];
return worksheet.Cells.CreateRange(“A1”, “F1”);
}
return null;
}
}
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
At present, only simple objects can be passed and returned from the custom functions, we need to enhance it so that it could work with complex objects like ranges.
Anyway, could you please provide us a runnable project to investigate this issue? Please also provide your source xls/xlsx files you are using and your actual and expected output xls/xlsx files.
We will look into your issue and find out if there is some workaround to return cells ranges from custom function and let you know asap.
Hi,
We have logged a ticket for an enhancement for your requirements with an id: CELLSNET-40828. We will soon look into it and enhance the Custom formula calculations accordingly. Once we have an update on it, we will let you know here.
Thank you.
The issues you have found earlier (filed as CELLSNET-40828) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.