ICustomFunction does not support String[][] parameters

Aspose.Cells Product Family
1

When trying to implement the missing TRANSPOSE and MMULT functions, I ran into the problem that currently the ICustomFunction interface does not support parameters of type String[][]. Instead, only Double[][] is allowed.

I’ve attached a sample XLS file which explains the issue.

I’m using the following code to calculate the sheet:

public static void main(String[] args) throws IOException {
Workbook workbook = new Workbook();
workbook.open(“aspose-transpose-strings.xls”);
workbook.calculateFormula(false,new CustomFunction());
}

static class CustomFunction implements ICustomFunction {
public Object calculateCustomFunction(String name, ArrayList params, ArrayList contexts) {
Workbook workbook = (Workbook)contexts.get(0);
Worksheet sheet = (Worksheet)contexts.get(1);
Cell cell = (Cell)contexts.get(2);
if (“TRANSPOSE”.equals(name)) {
Double[][] matrix = (Double[][])params.get(0);
if (matrix == null) {
System.out.println(“String parameter not supported”);
}
int width=matrix.length;
int height=matrix[0].length;
Double[][] transposedMatrix = new Double[matrix[0].length][matrix.length];
for (int x=0; x<width; x++) {
for (int y=0; y<height; y++) {
transposedMatrix[y][x] = matrix[x][y];
}
}
return transposedMatrix;
}
return null;
}
}

Would it be possible to support Strings in addition to Doubles ?

With kind regards,

Barry

2

Hi Barry,

We are able to reproduce the issue mentioned by you. Your request for supporting parameters of type String[][] along with Double[][] in ICustomFunction interface has been logged into our Issue Tracking System with ID CELLSJAVA-20037. We will work on the possibilities and will update you accordingly.

Thanks,

3

Hi,

Please try the attached version. In the new fix we have provided a new Interface for custom functions: ICustomFunctionEx

In the attachment “demo.zip”, you may find the javadoc for this interface and the sample implementation for it which should be modified from your case of implementing ICustomFunction. To calculate formulas with it, please call Workbook.calculateFormula(boolean ignoreError, ICustomFunctionEx customFunction).

Thank you.

4

Thanks for the updated library.

There still seems to be a bug in the new ICustomFunctionEx interface:

When I use the old ICustomFunction interface, the attached Excel is calculated fine. However, when I use the new ICustomFunctionEx, the parameters for ‘MMULT’ are integer 0 and a matrix. For some reason, it seems that using two custom function calls in one formula is not supported.

Example: =SUM(MMULT(E2:E3,TRANSPOSE(E2:E3)))
returns 49 using ICustomFunction (and is also 49 in Excel), but returns 0 using ICustomFunctionEx

I have attached a sample Excel file and a small Java program to highlight the differences:

workbook.calculateFormula(false,(ICustomFunctionEx)new CustomFunctionEx());
System.out.println(“A1=”+sheet.getCells().getCell(“A1”).getValue());
System.out.println(“A2=”+sheet.getCells().getCell(“A2”).getValue());
System.out.println(“A3=”+sheet.getCells().getCell(“A3”).getValue());

workbook.calculateFormula(false,(ICustomFunction)new CustomFunctionEx());
System.out.println(“A1=”+sheet.getCells().getCell(“A1”).getValue());
System.out.println(“A2=”+sheet.getCells().getCell(“A2”).getValue());
System.out.println(“A3=”+sheet.getCells().getCell(“A3”).getValue());

Results in:
A1=0.0
A2=7.0
A3=9.0

A1=49.0
A2=7.0
A3=9.0

With kind regards,

Barry

5

It seems that the second parameter to MMULT is an Integer 0, while it should have been the transposed matrix.

For some reason, when combining multiple custom functions in a single cell, the result is turned into an Integer 0. When using the TRANPOSE() function alone in a cell, it works fine. When using the MMULT function alone in a cell, it works fine. When using MMULT(TRANSPOSE(…),…), the result of TRANSPOSE is lost.

Do I need to use the new getSettings() method somehow ?

Barry

6

Hi,

Thanks for your sample code and template file with details.

We will look into your issue and get back to you soon.

Thank you.

7

Hi Barry,

Please use the updated version Aspose.CellsV2.4.0.7.zip attached. The issue has been fixed.

Thanks,

8

Thanks, works great !

Barry

9

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

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