Hi,
I have an issue using Cell.setFormula() in v19.10.
NOTE: v18.10 doesn’t have such issue.
Could you please check? THANKS.
@huichen,
Please share your sample file, program output file and expected output file created by MS Excel along with a runnable console application with us for our testing. We will reproduce the problem and provide our feedback after analysis.
Code sample:
Workbook worlbook = new Workbook();
Cells cells = worlbook.getWorksheets().get(0).getCells();
Cell cell = cells.get(0, 0);
cell.setFormula("=‘File1.xlam’!myFormula()");
System.out.println(“original formula: " + cell.getFormula());
cell.setFormula(”=myFormula()");
System.out.println("updated formula : " + cell.getFormula());
Output:
original formula: =‘File1.xlam’!myFormula()
updated formula : =‘File1.xlam’!myFormula()
@huichen,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-43056 – Cell.setFormula() not updating for external links
We evaluated your issue further. Well, it is not an issue with Aspose.Cells. It is the expected result and same with MS Excel’s behavior when setting formula with addin functions. In MS Excel, when you have loaded the addin file and input the addin function, there is no need to input the addin file reference. For the “working fine” code, the addin file “File1.xlam” has not been registered when using myFormula function, so the function will be registered in the current workbook.
Hi,
I believe that it’s NOT the same behavior as MS Excel. In MS Excel, we are able to manually remove the external reference (e.g. “‘File1.xlam’!”) in formula if the external reference is not available. But in Aspose Cells v19.10, there is no way to do the same since Cell.setFormula() is NOT able to remove the reference not matter the external reference exists or not.
Please let me know if you are able to fix it or not. Otherwise, we will have to apply our workaround solution.
@huichen,
Thank you for sharing the feedback. We are analysing this information and will share our feedback soon.
Please try our latest version/fix: Aspose.Cells for Java v19.11.9 (attached)
Aspose_Cells_Java_v19.11.9.zip (6.7 MB)
Your issue should be fixed in it.
Let us know your feedback.
Thanks for the update. I rerun the above code piece with v19.11.9 and the output is the same as before.
Output:
original formula: =‘File1.xlam’!myFormula()
updated formula : =‘File1.xlam’!myFormula()
Please note, in the new fix, we provide new API for your requirement about external references:
Cell.SetFormula(string formula, FormulaParseOptions options, object value)
For FormulaParseOptions, you may set CheckAddIn as false. To be compatible with older versions, by default this property is true. See the sample updated code segment which works fine as I tested:
e.g
Sample code:
Workbook worlbook = new Workbook();
Cells cells = worlbook.getWorksheets().get(0).getCells();
Cell cell = cells.get(0, 0);
FormulaParseOptions options = new FormulaParseOptions();
options.setCheckAddIn(false);
cell.setFormula("='File1.xlam'!myFormula()", options, null);
//cell.setFormula("='File1.xlam'!myFormula()");
System.out.println("original formula: " + cell.getFormula());
cell.setFormula("=myFormula()", options, null);
System.out.println("updated formula : " + cell.getFormula());
Hope, this helps a bit.
Good to know that your issue is sorted out by using the suggested code segment with newer APIs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
The issues you have found earlier (filed as CELLSJAVA-43056) have been fixed in Aspose.Cells for Java v19.12. This message was posted using Bugs notification tool by ahsaniqbalsidiqui