Incorrect formula returned

Aspose.Cells Product Family
1

Hi,

I have an .xlsx file with some formulas. I’m trying to get the formula from the cell A6, but it’s returned incorrectly. One apostrophe is missing.

I get:
=‘http:\www.uni’pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7

But in file, it’s:
=‘http:\www.uni’‘pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7

Here’s the code I use:

String filePath = “Formula.xlsx”;

InputStream inputStream = new FileInputStream(filePath);

Workbook workbook = new Workbook(inputStream);

inputStream.close();

Worksheet worksheet = workbook.getWorksheets().get(0);

Cells cells = worksheet.getCells();
System.out.println(cells.get(“A6”).getFormula());

I’m using Aspose.Cells for Java 9.0.0.

Can you check this?

Thanks,
Zeljko

2

Hi,

Thanks for providing us template file and sample code.

After an initial test, I observed the issue as you mentioned by using your sample code with your template file. I found an incorrect formula string returned from a worksheet cell, one apostrophe is missing as you pointed out.
I confirmed as I get:
=‘http:\www.uni’pd.it\rapportistrutture\missioni[modulo missioni
informatizzato.xls]richiesta anticipo’!A7

but in the file, it’s:
=‘http:\www.uni’‘pd.it\rapportistrutture\missioni[modulo missioni
informatizzato.xls]richiesta anticipo’!A7

I have logged a ticket with an id “CELLSJAVA-42019” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

3

Hi again,


This is to update you that the ticket logged earlier as CELLSJAVA-42019 has been marked resolved. We will share the fix here after ensuring the quality and incorporating other enhancements.
4

Hi Zeljko,


Please try the case against the latest version of Aspose.Cells for Java 16.10.2 and share your feedback.
5

Hi Babar,

I switched to the version you provided and the primary issue is fixed. But there is another problem now.

If I get the formula from the cell, slightly change it, set it back to the cell and get it again, instead of two apostrophes, there are now four of them.

Here’s the code I use:

String filePath = “Formula.xlsx”;

InputStream inputStream = new FileInputStream(filePath);

Workbook workbook = new Workbook(inputStream);

inputStream.close();

Worksheet worksheet = workbook.getWorksheets().get(0);

Cells cells = worksheet.getCells();
String formula = cells.get(“A6”).getFormula();

System.out.println("Source formula: " + formula);

formula = formula.replaceAll(“http”, “ftp”);

System.out.println("Modified formula: " + formula);

cells.get(“A6”).setFormula(formula);

System.out.println(cells.get(“A6”).getFormula());

Here’s the output:

Source formula: =‘http:\www.uni’‘pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7

Modified formula: =‘ftp:\www.uni’‘pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7

=‘ftp:\www.uni’’’‘pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7

Can you check this?

Thanks,
Zeljko

6

Hi Zeljko,


Thank you for the feedback.

I have evaluated the recently shared scenario and I am able to replicate the said problem on my side. I have raised it as a separate ticket CELLSJAVA-42025 for further investigation & correction. Please spare us little time for the proper analysis. In the meanwhile, we will keep you posted with updates in this regard.
7

Hi,


Please try our latest version/fix: Aspose.Cells for Java v16.10.3

We have fixed the issue now.

Let us know your feedback.

Thank you.
8

Hi Shakeel,

Thank you for providing me with the new version of Aspose.Cells.

The issue has been fixed for the actual case I posted above, but if you make slight changes, the returned value contains four apostrophes again.

Please try this code on your side:

String filePath = “Formula.xlsx”;

InputStream inputStream = new FileInputStream(filePath);

Workbook workbook = new Workbook(inputStream);

inputStream.close();

Worksheet worksheet = workbook.getWorksheets().get(0);

Cells cells = worksheet.getCells();
String formula = cells.get(“A6”).getFormula();

System.out.println("Source formula: " + formula);

formula = formula.substring(0, 2) + “T_” + formula.substring(2);

System.out.println("Modified formula: " + formula);

cells.get(“A6”).setFormula(formula);

System.out.println(cells.get(“A6”).getFormula());

This is the output I get:

Source formula: =‘http:\www.uni’‘pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7
Modified formula: =‘T_http:\www.uni’‘pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7
=‘T_http:\www.uni’’’‘pd.it\rapportistrutture\missioni[modulo missioni informatizzato.xls]richiesta anticipo’!A7

Regards,
Zeljko

9

Hi,


Thanks for your feedback and using Aspose.Cells.

We were able to observe this issue as per your console output. We will look into it and provide you a fix for it. Once, there is some news for you, we will let you know asap.

10

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

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

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

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