Modification of workbook names results in corrupt Excel workbook

Aspose.Cells Product Family
1

Using Aspose Cells for Java, version 19.1.1.

Sample code:

System.out.println(CellsHelper.getVersion());

Workbook workbook = new Workbook("noot.xlsx");
int nameIdx = workbook.getWorksheets().getNames().add("testComplexTableRefFormula");
Name name = workbook.getWorksheets().getNames().get(nameIdx);
name.setRefersTo("N(IF(1,((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Table1)/4))-1)*4)))");
name.setVisible(true);

nameIdx = workbook.getWorksheets().getNames().add("testSimpleSheetRefFormula");
name = workbook.getWorksheets().getNames().get(nameIdx);
name.setRefersTo("Sheet1!A1");
name.setVisible(true);

workbook.save("noot2.xlsx");

The workbook referred to in the sample code is attached:
noot.zip (6.4 KB)

2

@TarasTielkes,

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-42815 - Adding complex reference entry to workbook names collection results in corrupt Excel workbook
3

@TarasTielkes,

We evaluated your issue further. Generally you should add sheet references when using cell references in Named object’s formula. In your sample code when setting formula reference, “A:A” should be replaced by “Sheet1!A:A”. You may change the line of code, i.e.,

name.setRefersTo("N(IF(1,((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(Table1)/4))-1)*4)))");//This entry causes issues in the output file

with:

name.setRefersTo(“N(IF(1,((ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,ROWS(Table1)/4))-1)*4)))”); //

Also, we will enhance the API of Name.RefersTo to accept and process your original input like MS Excel does.

Once we figure it out, we will update you.

4

@TarasTielkes,

We explored the issue in details. Well, in MS Excel, there must be one active worksheet when defining a Name object. So, if you refer to a cell or range without the sheet reference, MS Excel will change it to the active worksheet automatically. For example, if you set the formula as “A1” for the Name, and current active worksheet is “Sheet1”, then the formula for the Name will be changed to “Sheet1!A1” automatically.

In your application, however, we do not know which sheet should be used as the default reference and current active sheet may be other type than worksheet and cannot be taken as cells reference. So, if your input formula has cell/range references without specified worksheet, from Aspose.Cells for Java v19.1.2 (coming fix/version) we take it as variant sheet reference. Take your input “=A1” as an example, the formula will be taken as “=!A1”, just like user input the formula as “=!A1” for the Name in MS Excel.

5

@TarasTielkes,

Please try our latest version/fix: Aspose.Cells for Java v19.1.2 (attached)

Your issue should be fixed in it.
Aspose.Cells for Java_v19.1.2.zip (6.4 MB)

Let us know your feedback.

6

The issues you have found earlier (filed as CELLSJAVA-42815) have been fixed in Aspose.Cells for Java 19.2. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi