Hi,
after I migrated Aspose.Cells for Java from 8.4.2 to 8.6.3 (latest version), I recognized that my program does not work anymore. In my program we created an excel file with different formulas containing a name which refers to a defined cell with a calculated value. After invoking calculateFormula()-method on the workbook object, the cell value contains the following string #ERROR[#NAME?]! . If I save the workbook after calculation and open it with excel the name field will be resolved successfully.
Can anyone help on this?
Hi Peter,
Thank you for contacting Aspose support.
I am afraid, we require the sample spreadsheet and an executable sample application to thoroughly investigate the matter on our side. However, please first give a try to the latest release of
Aspose.Cells for Java 8.6.3.4 to see if it resolves the problem. In case the issue persists, please provide us the problematic sample along with a sample application to replicate the issue on our side.
Hello Babar
I attached a zip archive. I created a working sample built as maven project. It can be imported with Eclipse IDE. Please read the readme.txt file, because it contains all the required information to reproduce the issue.
Regards,
Gökhan Demirkiyik
Hi Gökhan,
Thank you for the sample project. We are currently reviewing it, and we will shortly get back with updates in this regard.
Hi again,
Thank you for your patience.
I have checked the sample project by executing it against different revisions of Aspose.Cells for Java starting from 8.4.2 till 8.6.3 (latest major release). First of all, please note that the value in the cell
D54 of worksheet
Input und Output is resulting in an
Invalid Name Error (#NAME?) in source as well as resultant spreadsheet, therefore if you check the cell type using Aspose.Cells APIs, it should be
CellValueType.IS_ERROR (integer value 2) and not
CellValueType.IS_NUMERIC (integer value 4). There could be bug in Aspose.Cells for Java 8.4.2 that is fetching the type of aforementioned cell as
CellValueType.IS_NUMERIC, which seemed to have been fixed with immediate next release because I am getting the type as
CellValueType.IS_ERROR starting from 8.5.0 till the latest release at the moment. Please check the class
CellValueType for your reference. Moreover, please check the attached spreadsheet as well as the snapshot of the spreadsheet loaded in Excel 2013, you will notice that even Excel application is not able to resolve the named reference.
Hi Babar
Thank you for your reply. I cannot reproduce the same behaviour of the excel file. When I open the attached output.xls file, excel will resolve the named reference to the following value: 0.99999999999. So I think the excel file must be ok.
Hi again,
Thank you for writing back.
I have retested the scenario against Aspose.Cells for Java 8.4.2, and I am still getting the same result. Strangely, the test at the line 72 (assertEquals(“The cell type must be numeric.”, CellValueType.IS_NUMERIC, cell.getType())) passes with v8.4.2, where all recent releases fail it. That is the reason, I suspect the behaviour is caused due to some bug in v8.4.2.
Anyway, please check the resultant spreadsheet (attached) on your side for the problematic cell and share a snapshot of what you see. Moreover, please note that I have executed all test on Windows 10 x64 against JDK 1.8.0_66. If you can provide more details of your environment, I can try to simulate it in order to give this scenario another try.
Hello
Thanks for your effort.
All tests are executed on Windows 7 (x64) and against Java 1.8 (u45).
I have tested my sample application with all Aspose.Cells Versions (from the Aspose Cloud Maven Repository) and got the following results of the JUnit test case:
- 8.3.2 = passed
- 8.4.0 = passed
- 8.4.1 = passed
- 8.4.2 = passed
- 8.5.0 = not passed
- 8.5.1 = not passed
- 8.5.2 = not passed
- 8.6.0 = not passed
- 8.6.1 = not passed
- 8.6.2 = not passed
- 8.6.3 = not passed
My conclusion is that it was not a bug in 8.4.2 (see the test results below) Something changed in Version 8.5.0. I expect a number in cell D54 but get an error field. I have never changed the excel file.
Anyway, I attached a two snapshots (before and after calculation) of your spreadsheet.
Hi,
Thank you for writing back.
I have re-evaluated the scenario under Windows 7 Home Premium x64 to receive same results as shared in my previous responses. Moreover, I have logged an investigative ticket with Id CELLSJAVA-41704 in our issue tracking system to get the feedback from the product team on this matter. Please note, the aforementioned ticket is currently pending for analysis and have been pushed to the queue so we need some time in order to schedule the investigation. As soon as the product team has completed the preliminary analysis we will share the results here.
Regarding the output.xlsx shared in my previous response, the sample was shared mistakenly because I intended to share the output of the process generated against 8.4.2. Please accept my apologies for the inconvenience. Attached to this post are two XLS files generated via your sample project while referencing Aspose.Cells for Java 8.4.2 & 8.6.3. Could you please open the files in your Excel installation to take snapshots of cell D54 and share them here so I could attach the same to the aforementioned ticket?
Hello again,
No problem, I attached the snapshots of the two Excel files after opening the files. The output of 8.6.3 contains a name resolution error in cell D54.
Hi,
Thank you for sharing the snapshots. Please find the attachment for two snapshots that I took while loading the output(8.4.2).xls in Excel 2013 by first downloading it from my previous post. You will notice that the Excel application shows the desired results for cell D54 unless I click on Enable Editing link at the top bar of Excel application. I have recorded my observations along with the snapshots shared by you to the aforementioned ticket, and have requested the concerned member of the product team to schedule the analysis at earliest possible. In the meanwhile, we will keep you posted with updates in this regard.
Hi Gökhan,
Thank you for your patience with us.
This is to update you that we have looked further into this matter, and we are able to identify the cause of behaviour difference for formula calculation. Please check the formula at Modell - LOG REG’!D39 which is as follow =IF(C39=0,0,SQRT(MAX(0,IF(C39=“N/A”,0,ROUND(BRTEILJAHRE(C39,TODAY(),1)-0.5,0))))).
You can observe that the formula uses the function BRTEILJAHRE which cannot be parsed and calculated by Excel application in en-US environment and therefore formula results in #NAME? error. Please note, Aspose.Cells APIs can only support the standard function names in en_US environment.
The older versions of Aspose.Cells APIs were able to work for such non-English formula names because of the fact that when encounter functions that cannot be recognized, the Aspose.Cells calculation engine used to ignore the calculation. Instead, the older revisions of the APIs used cell’s original value (which was saved in the template file). However, for most situations, those functions should be calculated as #NAME? like what Excel application does. Therefore we modified the logic for those unknown functions to follow Excel’s standards and guidelines.
In order to avoid such situation while working with recent API revisions, we strongly suggest you to use the English based function names only. For instance, the function BRTEILJAHRE seems to be the locale name in de-DE environment for the standard function YEARFRAC so you can change this function to YEARFRAC, then it will be calculated by Aspose.Cells APIs.