Aspose-cells formula error

Aspose.Cells Product Family
1

Some of my files have formula errors when converting from xlsx files to xls files. I don’t care whether these formulas are correct or not. I just want to convert them to xls files. Would you add an option to convert them directly without checking the formula when converting the file format?

归档.zip (151.9 KB)

2

@wanghq09,
Would you like to retest using the latest java version? We can achieve the correct results through normal conversion. Please refer to the attachment (133.7 KB).

3

@wanghq09,

Moreover, if you still find the issue with latest version (as @John.He suggested), please share screenshots to highlight the problematic areas while comparing input XLSX file(s) with output XLS file(s) and sample code that you are using. This will help us evaluate your issue precisely to figure it out soon.

4

i could not make it, there must be something wrong.

here is my code

com.aspose.cells.LoadOptions options = new com.aspose.cells.LoadOptions();
options.setParsingFormulaOnOpen(false);
options.setKeepUnparsedData(false);
options.setCheckDataValid(false);
options.setCheckExcelRestriction(false);
Workbook workbook = new Workbook(“e_collab_4EZlwyyxPmQWqxAY_9af7e8fd-9059-453e-a291-9e47e2869705.xlsx”, options);
String xlsPath = “e_collab_4EZlwyyxPmQWqxAY_9af7e8fd-9059-453e-a291-9e47e2869705.xls”;
workbook.save(xlsPath);

and here is my mvn dependency

<dependency>
        <groupId>com.aspose</groupId>
        <artifactId>aspose-cells</artifactId>
        <version>23.6</version>
    </dependency>

here is my one of my error message
com.aspose.cells.CellsException: Invalid reference: $1:$1048575(Based on cell 7月（勿动）!AJ138)

5

@wanghq09
Through the sample code you provided, we can reproduce the issue. Invalid data exception occurred when saving the file to xls.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45489

As a temporary solution, you can use the following code to temporarily resolve exceptions.

options.setParsingFormulaOnOpen(true);
6

@wanghq09
Please comment options.setParsingFormulaOnOpen(false);
or calling workbook.parseFormulas(true); before saving the xls file.

7

@wanghq09
We have fixed this issue. Please wait for next release 23.7.

8

What is the function of this method? According to the API, it means to parse the formula when reading the file. Why not parse the wrong formula will lead to errors, while parsing the wrong formula will be no problem. Apart from solving this problem, will changing this to true bring other side effects?

9

@wanghq09,
Thank you for your feedback. The function of this method is to specify whether to parse formulas when opening a file. The xlsx and xls formats have different data when storing formulas. When reading a file, if parsing is required, the data will be reorganized. No matter whether you parse or not, there should be no error when saving. So we created a list for tracking and resolving. After solving this issue, there will be no side effects.

10

The issues you have found earlier (filed as CELLSJAVA-45489) have been fixed in Aspose.Cells for Java 23.7.