How to pass the custom object/data to the the custom function in Excel spreadsheet using Java

Aspose.Cells Product Family
, ,
1

Hi
we are using the Aspose cell for Java. I want to pass the some objects to the custom function for given cell whiling calling cell.cacluate() method. I have looked the context object but it is pre- defined. Is there any any way to pass/or set custom object to cell while calling cell…calculate() method so that I can use it inside custom function?
Thanks

2

@cgkrish,
We have understood your requirement and logged it in our database for further investigation and a solution. We will write back here once any update is ready for sharing.

CELLSJAVA-43440 - How to pass the custom object/data to the the custom function

3

Thanks very much for quick reply.

4

@cgkrish,
When you set a custom object to a cell while calculating the custom function, we have to convert it into an excel compatible value so it can be kept in the excel data model and used by other references. So we are afraid such requirement cannot be supported directly by our calculation engine.

As a workaround, you may try to convert the custom object into string and set the string value to cell, then the cell’s string value may be parsed into custom object later by your custom function implementation.

If the custom object is 1D/2D array, you also may try array formulas with custom function. Please note, ICustomFunction has been replaced with a more flexible API: AbstractCalculationEngine(Returning a Range of Values using AbstractCalculationEngine|Documentation). Here is an example for your requirement:

        Workbook wb = new Workbook();
        Worksheet sheet = wb.Worksheets[0];
        Cells cells = sheet.Cells;
        cells[0, 0].Formula = "=UDF(B1:C3)";
        cells[0, 1].SetArrayFormula("=UDP()", 3, 2);
        CalculationOptions copts = new CalculationOptions();
        copts.CustomEngine = new ME();
        wb.CalculateFormula(copts);
        Console.WriteLine(cells[0, 0].Value);
...
    private class ME : AbstractCalculationEngine
    {
        public override void Calculate(CalculationData data)
        {
            if ("UDP".Equals(data.FunctionName))
            {
                data.CalculatedValue = new object[][]
                {
                    new object[] { "a", 1, },
                    new object[] { "b", true, },
                    new object[] { false, "c", },
                };
            }
            else if ("UDF".Equals(data.FunctionName))
            {
                ReferredArea ra = (ReferredArea)data.GetParamValue(0);
                object[][] vv = (object[][])ra.GetValues(true);
                StringBuilder sb = new StringBuilder();
                foreach (object[] ir in vv)
                {
                    foreach (object ic in ir)
                    {
                        sb.Append(ic);
                    }
                }
                data.CalculatedValue = sb.ToString();
            }
        }
    }

Let us know your feedback.