Auto expand array formula

Aspose.Cells Product Family
1

Hi,

we are using the .NET version of your product and we are trying to trigger a calculation of an excel sheet which includes custom functions that return arrays and we need to auto expand the formulas.

I followed the instructions in Set an array formula without knowing the range of cells but this didn’t work. What I am observing is that if I set to adjacent cells with the same formula then one of them will auto-expand and populate the cells with values but one on its own will not show any values.

Below is the code I have used.

var workbook = new Workbook(args[0]);

        workbook.Settings.CalcMode = CalcModeType.Manual;

        Cells cells = workbook.Worksheets[0].Cells;
        Cell cell = cells[0, 0];
        cell.SetArrayFormula("=MyUdf()", 1, 1);

// if you uncomment the code below then you can see values for the first formula but not the second
//Cell cell2 = cells[0, 1];
// cell2.SetArrayFormula(“=MyUdf()”, 1, 1);

        workbook.CalculateFormula(true, new MyCustomFunctions());

public class MdapCustomFunctions : ICustomFunction
{
public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)
{
var cell = (Cell)contextObjects[2];

            var result =  new object[][] {
                new object[]{new DateTime(2015, 6, 12, 10, 6, 30), 2},
                new object[]{3.0, "Test"}
            };

           
            cell.SetArrayFormula(cell.Formula, 2, 2);
           return result;
    }
}

Could you please provide some guidance on this?

Thanks,
Alex

2

@alexkdigiterre,

Thanks for your query.

I am analyzing this issue and need further assistance regarding the following comments:

Here you mentioned that value will be displayed for the first formula only however I am getting values for both the functions. Could you please test this code with the latest version Aspose.Cells for .NET 18.10? Output of this program with un-commented code is attached here for your reference.

outputUncommentedCode.zip (5.8 KB)

Similarly could you please explain in more detail why following line of code is used in the CalculateCustomFunction()? It will help me to understand the flow of your program and provide the feedback.

cell.SetArrayFormula(cell.Formula, 2, 2);
3

@alexkdigiterre,

We have investigated the issue in more detail and suggest that you should never modify the data directly in workbook in the formula calculating process, otherwise unexpected result or Exception will be caused. So expanding the array formula in the implementation of ICustomFunction or AbstractCalculationEngine is illegal. Please set all formulas and other data before calling formula calculation and feel free to write back if you have any other query related to this issue.

4

Hi,

thanks for your quick reply.

I used cell.SetArrayFormula(cell.Formula, 2, 2); because that seemed to be what was suggested in Set an array formula without knowing the range of cells .

If that doesn’t work then is there a way to automatically expand a function that returns an array without knowing the size of the range in advance?

Kind regards,
Alex

5

@alexkdigiterre,

I am afraid, the solution provided in the mentioned post is not very accurate and won’t work in newer versions. Maybe it worked for some older versions (at that time) and some similar scenarios, but it is completely unsafe and may cause unexpected troubles or results.

If you need to use the custom function in this way, I am afraid you have to calculate the formulas twice. In the first calculation, you will only gather the data (for your scenario), it is to gather the map of those special cells to the array formula dimensions. After the first calculation, you can reset formulas accordingly with the gathered data. And, then calculate formulas again to calculate the expected results.

Hope, this helps a bit.