We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Setting formula referring to external workbook creates 3d formula

Hi,


Using Aspose Cells for Java, version 16.12.7.

Sample code:
<pre style=“background-color: rgb(255, 255, 255); font-family: “Courier New”; font-size: 9pt;”><pre style=“font-family: “Courier New”; font-size: 9pt;”>Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet1 = worksheets.add(“S.02.01.01.01”);
Cell cell = sheet1.getCells().get(0, 0);
cell.setFormula("=[model.xlsx]S.02.01.01.01!A1");

Worksheet sheet2 = worksheets.add(“S.03.01.01.01”);
cell = sheet2.getCells().get(0, 0);
cell.setFormula("=[model.xlsx]S.03.01.01.01!A1");
System.out.println("formula: " + cell.getFormula());

worksheets.removeAt(“Sheet1”);
workbook.save(“sheets.xlsx”);
As you can see, two sheets are created. Both sheets contain a cell that has a formula pointing to an external workbook (“model.xlsx”).

When opening the generated workbook in Excel 2010, the formula in the first sheet (“S.02.01.01.01”) shows as expected. However, the formula in the second sheet (“S.03.01.01.01”) has unexpectedly been tranformed to a “3D” Excel formula that spans multiple sheets, looking like the following:
=’[model.xlsx]S.03.01.01.01:S.02.01.01.01’!A1

Note that you can see this happening already from the System.out.println() in the sample code.
Generated workbook is attached.

Thanks in advance,
Taras

After a bit more testing, it seems that the bug occurs when setting the first formula pointing to an external workbook on a given sheet.


Any subsequently inserted formulas pointing to an external workbook (on the same sheet) result in correct Excel formulas.

Hi Taras,


Thank you for sharing the sample snippet.

I have logged an investigative ticket (CELLSJAVA-42152) for product team’s review. Please spare us little time to further evaluate the scenario and revert back with updates in this regard.

Hi again,


This is to update you that the ticket logged earlier as CELLSJAVA-42152 has been marked resolved. We will shortly share the fix here for your testing.

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


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

Hi,


We can confirm that 17.1.0 does indeed resolve the issue reported.
Thank you for the quick analysis and fix!

Kind regards,
Taras

Hi,


Thanks for your feedback.

Good to know that your issue is sorted out by the new version/fix. Feel free to write us back if you have further comments or questions.

Thank you.