Free Support Forum - aspose.com

Cell.setFormula() not working for external links

#1

Hi,

I have an issue using Cell.setFormula() in v19.10.

  1. A cell contains the formula like “=‘File1.xlam’!myFormula()” while File1.xlam is external link.
  2. Cell.setFormula("=myFormula()") is NOT working and formula unchanged. i.e ‘File1.xlam’ is not removed.

NOTE: v18.10 doesn’t have such issue.

Could you please check? THANKS.

#2

@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.

#3

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()

#4

@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

#5

@huichen,

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.

#6

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.

#7

@huichen,
Thank you for sharing the feedback. We are analysing this information and will share our feedback soon.