Is it possible to assign an array formula without actually knowing offhand how many rows/columns the array is going to return.
I have a scenario where my custom function can return 5 columns and 10 rows or for other parameters 3 columns and 100 rows. So, basically the number of rows returned is usually unknown.
I tried to to call SetArrayFormula by passing Int32.MaxValue for the rows/columns counts but it failed because the ASPOSE.Cells code doesn’t check the boundaries of the array to make sure it didn’t go beyond the number of columns/rows in the array.
Any suggestions?
Edit: I want to clarify a bit my message. My function returns a two dimensional array of data with a variable number of rows and columns. I want to assign this function call (as a formula) to, let’s say cell A1 and then have the calculation engine populate the surrounding cells with the data that comes from the two dimensional array returned by A1.
I know I could achieve this by using variables type smart markers, but in that case the formula would be evaluated before the worksheet is calculated, as in my case I can feed my array function parameters from other cells. So, I would rather not use variables.
Thank you,
PS: off topic, I tried to assign “Array Formula” as a tag to this message and every time I did it got a popup message saying undefined. But it seemed to have added it in the end.
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
Please provide your sample runnable project, your actual output and expected output files and screenshot showing the problem.
We will look into it and help you asap.
Hi,costab:
PS: off topic, I tried to assign "Array Formula" as a tag to this message and every time I did it got a popup message saying undefined. But it seemed to have added it in the end.
I was able to replicate this issue. When I tried to assign "Array Formula" as a tag to this message, I also got a popup message saying undefined.
Below is a screenshot for your reference. I will report it to Web Development team soon.
Screenshot:
Hi:
Here is my sample app (no need for spreadsheets):
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Aspose.Cells;
namespace TestAspose
{
class Program
{
static void Main(string[] args)
{
Procedure8();
Console.WriteLine(“Done!”);
Console.ReadKey();
}
private static void Procedure8()
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Get the cells collection in the sheet
Cells cells = worksheet.Cells;
// My question is: why do I have to specify the row count and column count in this particular case?
// they could be inferred from the number of columns/rows in the array when the function is evaluated
// I could also specify a row count or column count big enough to make sure the entire content is included but cells that go beyound the available data in the array are marked with #NA
cells[“A1”].SetArrayFormula("=myarrayfunc()", 10, 100);
workbook.CalculateFormula(true, new CustomFunction());
Console.WriteLine(cells[“E1”].Value);
Console.WriteLine(cells[“E2”].Value);
Console.WriteLine(cells[“E3”].Value);
}
public class CustomFunction : ICustomFunction
{
public object CalculateCustomFunction(string functionName, System.Collections.ArrayList paramsList, System.Collections.ArrayList contextObjects)
{
if (functionName.ToLower().Equals(“myarrayfunc”))
{
// Here I know how many rows/columns are in the array; is there a way of resetting the number of rows/columns specified in the SetArrayFormula call (in my case 10/100), using perhaps the contextObjects array ?
return new object[][]
{
new object[] { 11, 12, 13, 14, 15 },
new object[] { 21, 22, 23, 24, 25 },
new object[] { 31, 32, 33, 34, 35 }
};
}
return null;
}
}
}
If I assign an array formula to a cell is there a way to get my hands on (via spreadsheet functions of course) the array object itself and perhaps use some other functions to access its values or find out its dimensions?
Thanks
Hi,
Thanks for using Aspose.Cells.
I think, Aspose.Cells work same as MS-Excel does. You can apply your Array Formula to any number of rows and all the cells that go beyond the available data will show #NA
Anyway, I have logged this issue in our database along with your description and sample code. We will look into it further and give you advice or workaround.
This issue has been logged as CELLSNET-40916.
Workaround:
As a workaround, you can declare two variables that could be accessed in your main function as well as custom function, you can set them in your custom function an pass them as a parameter in your main function to SetArrayFormula() method.
Hi,
Please check this document ( Microsoft Support ) about Array Formula.
The range must be selected before setting array formula.
So we still hope that you can set an array formula with a range.
If you want to change the array with the result of the formula, please see the following code:
C#
cells[“A1”].SetArrayFormula(“=myarrayfunc()”, 1, 1);
workbook.CalculateFormula(true, new CustomFunction());
Console.WriteLine(cells[“E1”].Value);
Console.WriteLine(cells[“E2”].Value);
Console.WriteLine(cells[“E3”].Value);
workbook.Save(@“D:\fileTemp\dest.xls”);
}
public class CustomFunction : ICustomFunction
{
public object CalculateCustomFunction(string functionName, System.Collections.ArrayList paramsList, System.Collections.ArrayList contextObjects)
{
if (functionName.ToLower().Equals(“myarrayfunc”))
{
Cell cell = (Cell)contextObjects[2];
// Here I know how many rows/columns are in the array; is there a way of resetting the number of rows/columns specified in the SetArrayFormula call (in my case 10/100), using perhaps the contextObjects array ?
object[][] fv = new object[][]
{
new object[] { 11.0, 12.0, 13.0, 14.0, 15.0 },
new object[] { 21.0, 22.0, 23.0, 24.0, 25.0 },
new object[] { 31.0, 32.0, 33.0, 34.0, 35.0 }
};
cell.SetArrayFormula(cell.Formula, fv.Length, fv[0].Length);
return fv;
}
return null;
}
}
The above code is a temporary solution. We think there will be some potential risk if we change the formula when we call Workbook.CalculateFormula().