Worksheet has no method calcualteFormula(String formula- CalculationOptions options)

Aspose.Cells Product Family
1

My application is required to calculate excel formulas with custom functions in them.
The method
Worksheet.calculateFormula(boolean recursive, boolean ignoreError, ICustomFunction customFunction)
solves my problem here, but it forces me to write formulas into worksheet cells, which I would like to avoid.

Would it be possible to expose a method that would take a String formula and CalcualtionOptions (or at least ICustomFunction):
Worksheet.calcualteFormula(String formula, CalculationOptions options) or Worksheet.calculateFormula(String formula, ICustomFunction customFunction)?


Thanks!

2

Hi,

Thanks for your posting and using Aspose.Cells.

When formulas are calculated, their data is retrieved from cells or ranges, so it is necessary to put your data in worksheet before calculating the formulas.

Could you please provide us your hypothetical code which could give us clue about your requirements? We will investigate it and implement it if possible. Thanks for your cooperation.

3

Today if I have a formula that uses only existing Excel functions, I could use the following code:

Workbook workbook = new Workbook(“C:\file.xlsx”);
Worksheet worksheet = workbook.getWorksheet(0);
Object result = worksheet.calculateFormula("=A1 + A2");

However, as soon as I have to use a custom function, I need to do the following:

Workbook workbook = new Workbook(“C:\file.xlsx”);
Worksheet worksheet = workbook.getWorksheet(0);
// Find an unoccupied cell:
worksheet.getCells().get(100, 100).setFormula("=MyCompany.CustomFunction(A1) + A2");
// Calculate ALL formulas in the worksheet, including ONE SINGLE formula that I need
worksheet.calculateFormula(true, false, new ICustomFunction() {
@Override
public Object calculateCustomFunction(String name, ArrayList args, ArrayList context) {
if (name.equals(“MyCompany.CustomFunction”)) {
// do some magic here and return the magic number
return 42;
}
return null;
}
});
// Now read the result from the same cell:
Object result = worksheet.getCells().get(100, 100).getValue();

So me and my colleagues would like to have an ability to do something like this:

Workbook workbook = new Workbook(“C:\file.xlsx”);
Worksheet worksheet = workbook.getWorksheet(0);
Object result = worksheet.calculateFormula(
“=MyCompany.CustomFunction(A1) + A2”,
new ICustomFunction() {
@Override
public Object calculateCustomFunction(String name, ArrayList args, ArrayList context) {
if (name.equals(“MyCompany.CustomFunction”)) {
// do some magic here and return the magic number
return 42;
}
return null;
}
});

To make things even more generic, the new calculateFormula method could take CalculationOptions, which already has a spot for ICustomFunction object.

4

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged your requirements in our database for analysis and investigation. We will look into it and implement it if feasible. Or we may suggest you a workaround that could suit you. Once there is some fix or other news for you, we will let you know asap.

This issue has been logged as CELLSJAVA-41820.

5

Thanks!
Will be looking forward to it.

6

Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSJAVA-41820 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

7

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for Java v8.8.0.3 and let us know your feedback.

8

Thanks for the response! Looks fine so far!

9

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that the latest fix is working well so far at your end. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

10

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.