ICustomFunction and string concatenation problem

Aspose.Cells Product Family
1

We are trying to workaround the problem that Aspose.Cells does not support the ‘CELL’ function.

For this, we have created the following (simple) implementation:

public static void main(String[] args) throws IOException {
Workbook workbook = new Workbook();
workbook.open(“aspose-test-cell.xlsx”);
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 (“CELL”.equals(name)) {
String param1 = (String)contexts.get(3);
if ("“address”".equalsIgnoreCase(param1)) {
String location = (String)contexts.get(4);
return location; // TODO: support reference to other sheets and defined-names
}
}
return null;
}
}

However, when trying to concatenate the result of the CELL function with another string ( ="#" & CELL(“address”,B1) ) we get the following exception:

Exception in thread “main” com.aspose.cells.FormulaCalcException: Sheet1!A1[="#"&CELL(“address”,B1)]
at com.aspose.cells.Cell.g(Unknown Source)
at com.aspose.cells.fP.a(Unknown Source)
at com.aspose.cells.fP.a(Unknown Source)
at com.aspose.cells.eG.a(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at AsposeCellsTestMain.main(AsposeCellsTestMain.java:25)

According to the JavaDoc, the allowed return-types are Boolean, Integer, Double, String and double[][] and Double[][]. When the CELL function is used directly in a cell (e.g. =CELL(“address”,B1) ) then no exception occurs.

Concatenating strings should be straightforward, right ?

Thanks for the support,

With kind regards, Barry

2

Hi,

After an initial test, I have found the issue as you have mentioned. We will figure it out soon.

I have logged your issue into our issue tracking system with an id: CELLSJAVA-20021.

Thank you.

3

Hi Barry,

Please use the updated version Aspose.CellsV2.4.0.5.zip attached. The calculating CustomFunction issue has been fixed.

Thanks,

4

Thanks,

The issue has indeed been fixed. I have now ran into another problem with ICustomFunction, which I will explain in a separate thread.

Many thanks !

Barry

5

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

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