When evaluating the following formula, Aspose.Cells for Java cannot calculate the value: (A1 contains a number, e.g. 100)

=SQRT(A1:A1)

Excel allows a single-cell range as an argument to SQRT. Currently, Aspose.Cells returns #VALUE after a workbook.calculateFormula();

I have not tried other functions which expect a numeric argument whether it is allowed to supply a single-cell range as a parameter.

With kind regards,

Barry

Hi,

Well, yes, allowing single cell range for SQRT formula is not supported. I have logged your feature request to our tracking system with an id: CELLSJAVA-20060. We will support it soon.

Thank you.

Hi,

We have fixed your issue for calculating formulas, please try the attached version.

Thank you.

It still doesn’t work when used in combination with ArrayFormula and CustomFunction:

Workbook workbook = new Workbook();

Worksheet sheet1 = workbook.getWorksheets().getSheet(0);

sheet1.getCells().getCell(“A1”).setValue(2);

sheet1.getCells().getCell(“A2”).setArrayFormula("=SQRT(DUMMY(A1:A1))",1,1);

sheet1.getCells().getCell(“A3”).setArrayFormula("=SQRT(A1:A1)",1,1);

workbook.calculateFormula(false,new ICustomFunctionEx() {

public Object getSettings(Object o) {

return null;

}

public Object calculateCustomFunction(String name, String[] paramLiterals, Object[] params, Object[] contexts) {

Cell cell = (Cell)contexts[0];

Worksheet worksheet = cell.getWorkSheet();

Workbook workbook = worksheet.getWorkbook();

if (“DUMMY”.equals(name)) {

return params[0]; // dummy

}

else {

return null;

}

}

});

System.out.println("Value: "+sheet1.getCells().getCell(“A2”).getValue());

System.out.println("Value: "+sheet1.getCells().getCell(“A3”).getValue());

When a CustomFunction is called (in this case DUMMY, which simply returns the first parameter), then the calculation-engine cannot perform the SQRT on a single cell:

Value: #VALUE!

Value: 1.4142135623730951

Unfortunately, the Excel sheet that we are trying to calculate uses a lot of these formulas (e.g. {=SQRT(MMULT(A1:A2,TRANSPOSE(B1:B2)))} … )

With kind regards,

Barry

Hi,

Thanks for the sample code.

I can find the issue as you have mentioned using your code. I have reopened the issue and we will figure it out soon.

Thank you.

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

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