Workbook wb = new Workbook();
Cell cell = wb.getWorksheets().get(0).getCells().get(0, 0);
cell.setFormula("='File1.xlam'!myFormula()");
wb.save("1.xls"); //OK
if (wb.hasExernalLinks()) {
ExternalLinkCollection ec = wb.getWorksheets().getExternalLinks();
ec.get(0).setDataSource("");
wb.save("1.xls"); //Exception occurred ()
}@huichen,
Please share your sample file and stack trace of exception with us for our testing. We will reproduce the problem and provide our feedback after analysis.
We evaluated your issue further.
I have tested your code segment with latest version/fix (please try it): Aspose.Cells for Java v19.11.7(attached)
aspose-cells-19.11.7.zip (6.7 MB)
I could not find the exception with v19.11.7. However, generally the data source of one external link should not be set as empty as it may cause unexpected results.
If your goal is to convert the external addin function to the one in the Workbook itself (already logged a ticket for it: CELLSJAVA-43056), we are afraid it is not the proper way and cannot give you the expected results.
Regarding the issue CELLSJAVA-43056, by our tests, if one addin file has been loaded into MS Excel, then when you input one defined function in the addin file, even without the external references, MS Excel will recognize it as the known one defined in the addin file and link to it automatically. If you find the different behavior at your end, please describe the steps and we may make further investigations. Of course, if you removes the addin file from MS Excel, then MS Excel will not be able to recognize function anymore without the explicit external reference. For Aspose.Cells, it is unknown whether the addin function should be recognized as external or internal. To support your requirements, we are considering to provide one option when setting formulas, which denotes whether we should take the custom function without external references as inner one or link it to existing external links.
Hi,
Thanks for the update.
In v19.11.7, my above code piece has no exception, but save() still throws NullPointerException after setDataSource("") for given file. saveFailed.zip (178.3 KB)
Based on my understanding, Null Pointer check should be added to avoid NullPointerException in this case. I’m expecting this can be fixed as an alternative solution for CELLSJAVA-43056.
Regarding CELLSJAVA-43056, the following is the scenario,
- User A creates a XLS file(1.xls) containing formula with external link, e.g. “File1.xlam!”. The file File1.xlam exists in his local machine.
- User B gets 1.xls and open it in MS EXCEL, but File1.xlam does NOT exists his local machine.
- User B tries to remove the “‘File1.xlam’!” in the formula and he is able to do so.
- We were using v18.10 to remove “‘File1.xlam’!” by setFormula() and it’s working well. If we are not able to remove external source (i.e. “‘File1.xlam’!”) by setFormula(), it will increase our extra effort to evaluate and calculate the formula. So I’m expecting setFormula() can have the same behavior as v18.10.
After an initial test, I am able to observe the issue as you mentioned by using the following sample code with your template file. I found an exception “java.lang.NullPointerException” on Workbook.save after setting external data source to “”
e.g
Sample code:
Workbook wb = new Workbook("f:\\files\\saveFailed.xls");
Cell cell = wb.getWorksheets().get(0).getCells().get(0, 0);
cell.setFormula("='File1.xlam'!myFormula()");
wb.save("f:\\files\\1.xls"); //OK
if (wb.hasExernalLinks()) {
ExternalLinkCollection ec = wb.getWorksheets().getExternalLinks();
ec.get(0).setDataSource("");
wb.save("f:\\files\\1.xls"); //Exception occurred ()
}
I have logged a ticket with an id “CELLSJAVA-43060” for your issue. We will look into it soon.
Also, thanks for providing us your scenario/ case for CELLSJAVA-43056. We will evaluate it thoroughly as well.
Once we have an update on the issue(s), we will let you know.
@Amjad_Sahi, thanks for the quick update. Just want to mention that setFormula() works on some while does not work others. The different behavior is confusing. Please check the sample for your further analysis: setFormulaSample.zip (239.0 KB)
Code piece for your reference,
Workbook wb = new Workbook(“sample file”);
for (int k=0; k<wb.getWorksheets().getCount(); k++)
{
Cells cs = wb.getWorksheets().get(k).getCells();
Cell cell = cs.findFormulaContains("’!", null);
while (null != cell) {
String str = cell.getFormula();
String strToRemove = str.substring(str.indexOf(’’’), str.indexOf(’!’)+1);
String strWanted = cell.getFormula().replace(strToRemove, “”);
System.out.println(“original formula: " + cell.getFormula()+", wanted formula: " + strWanted);
cell.setFormula(strWanted);
System.out.println(“updated formula : " + cell.getFormula());
cell = cs.findFormulaContains(”’!", cell);
}
}
@huichen,
Thank you for providing more information. We have logged it along with the ticket for our reference.
What’s the difference of the behaviors for your provided two files? Could you please point out which cell can show the issue. Here is what we found with your code and files:
For notworking.xlsm, the external links in formulas cannot be removed, the reason is what we said: the external links of the addin files have been existing in the template file and for such situation we attach the external link automatically to the formula. MS Excel can detect whether the addin files have been loaded and then determine whether the external link should be appended to the formula. But for a library we cannot know whether those addin files should be taken as having been loaded or not. So we will provide option to let you specify this in later fix.
For working.xls, we didn’t find the external links being removed for formulas either. In fact large amount of formulas that have references to other sheets in the workbook was changed(sheet references were removed) by your code, we don’t think it is the expected result for you.
You should be able to observe that formulas are successfully updated in working.xls while formulas are NOT updated in notworking.xlsm
Code piece,
Workbook wb = new Workbook(“type sample file path here”);
ExternalLinkCollection externalLinkCollection = wb.getWorksheets().getExternalLinks();
ExternalLink externalLink = null;
String dataSource;
Cells cs;
Cell cell;
for (int i = 0; i < externalLinkCollection.getCount(); i++) {
externalLink = externalLinkCollection.get(i);
dataSource = externalLink.getDataSource();
for (int k=0; k<wb.getWorksheets().getCount(); k++) {
cs = wb.getWorksheets().get(k).getCells();
cell = cs.findFormulaContains(dataSource, null);
while (null != cell) {
String str = cell.getFormula();
String strToRemove = str.substring(str.indexOf("’"), str.indexOf("!")+1);
String strWanted = cell.getFormula().replace(strToRemove, “”);
System.out.println("cell: " + cell.getWorksheet().getName() + " - " + cell.getName() + “, Datasource:” + dataSource);
System.out.println("original formula: " + cell.getFormula());
System.out.println("wanted formula: " + strWanted);
cell.setFormula(strWanted);
System.out.println("updated formula : " + cell.getFormula());
cell = cs.findFormulaContains(dataSource, cell);
}
}
}
Output for working.xls =>
cell: Citi&Cons _FY1 - Q12, Datasource:dataciti.xlam
original formula: =‘dataciti.xlam’!dcGroup(‘dataciti.xlam’!dcCountries($B12,“taxonomy=productcountry”,“constituent=company”),“name”,“constituent=company”)
wanted formula: =dcGroup(dcCountries($B12,“taxonomy=productcountry”,“constituent=company”),“name”,“constituent=company”)
updated formula : =dcGroup(dcCountries($B12,“taxonomy=productcountry”,“constituent=company”),“name”,“constituent=company”)
cell: Citi&Cons _FY2 - Q11, Datasource:dataciti.xlam
original formula: =‘dataciti.xlam’!dcGroup(‘dataciti.xlam’!dcCountries($B11,“taxonomy=productcountry”,“constituent=company”),“name”,“constituent=company”)
wanted formula: =dcGroup(dcCountries($B11,“taxonomy=productcountry”,“constituent=company”),“name”,“constituent=company”)
updated formula : =dcGroup(dcCountries($B11,“taxonomy=productcountry”,“constituent=company”),“name”,“constituent=company”)
Output for notworking.xlsm =>
cell: PreCalc - C2, Datasource:dcTemplateProcessor.xlam
original formula: =‘dcTemplateProcessor.xlam’!TPEST()
wanted formula: =TPEST()
updated formula : =‘dcTemplateProcessor.xlam’!TPEST()
cell: PreCalc - B2, Datasource:dataciti.xlam
original formula: =‘dataciti.xlam’!dcnonvolatile(“Today()”)
wanted formula: =dcnonvolatile(“Today()”)
updated formula : =‘dataciti.xlam’!dcnonvolatile(“Today()”)
Thank you for providing further details and code snippet.
We have logged it along with the ticket into our database for evaluation.
Once we have an update on it, we will let you know.
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-43056”) now.
.
We will 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.
We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
Also, for the issue “CELLSJAVA-43060”, please note:
When the external link has other resources such as worksheets, the data source should not be set as empty, otherwise the re-saved excel file will cause corruption or protect view. So in the coming fix we will add constraint for setting ExternalLink.DataSource. If the new value may cause invalid data, we will throw CellsException to fail the operation.
We found there are some users who have used this API for such kind of external links. To make users’ existing applications work with later fixes/versions of our component and make it backward compatible, we cannot add the constraint to fail the operation. So with the new fix (Aspose.Cells for Java v19.11.9), we just handle the exception while saving XLS files for such kind of data.
Thanks for your understanding!
And, here is the new fix/version: Aspose.Cells for Java v19.11.9 (attached)
Aspose_Cells_Java_v19.11.9.zip (6.7 MB)
Thanks for the update. This exception issue has been resolved under v19.11.9, but CELLSJAVA-43056 remains (i.e. the formula is still not updated using code in #8 even though the behavior becomes consistent).
Good to know that your issue “CELLSJAVA-43060” is resolved by the new fix. Regarding your other issue “CELLSJAVA-43056”, please follow up your other thread.
The issues you have found earlier (filed as CELLSJAVA-43056,CELLSJAVA-43060) have been fixed in Aspose.Cells for Java v19.12. This message was posted using Bugs notification tool by ahsaniqbalsidiqui