I start by saying it’s not a function name (I did some search before posting). In the attached project, you will notice that F28 and F29 contain formulas. Both depend on the “hidden” column E values. The formula is as simple as:
=IF(LEN(E28)>0,IF(E28=“TRUE”,“Yes”,“”),“”)
The cell value becomes “0”. If I call manually “CalculateFormula” for that Worksheet, the value then changes to “” (empty string).
On E28, for example, the content is just “true” (lower case). According to some sources, if we use “=” to compare a cell to a string, this comparison is case insensitive. If we want to force it to be case sensitive, we should use “EXACT” instead.
I noticed that if I save this file (using excel), the value and formula start to get read correctly, although I don’t control this Excel file. I can’t just save it again. Is there any way to fix this?
AsposeTest12.zip (280.7 KB)
Reference: How to easily compare cells by case sensitive or insensitive in Excel?
@brunojuliao,
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
CELLSNET-46539 - Formula case sensitive issue
1 Like
@brunojuliao,
We evaluated your issue further. The “0” is just the value saved in your template file for the formula, such as F29. So, when the Workbook is being loaded from the template file, the value was kept as it was in the file until you re-calculate formulas with our component. As you have said, after calling CalculateFormula(), the value got is the correct one, that means there is no issue for our formula calculation. If you open the file in MS Excel, MS Excel will re-calculate formulas automatically and display the newly calculated results, so you will always find that the cell value is the correct one in MS Excel. When you save the file by MS Excel again, the re-calculated value will be saved to replace the old one in the file(0.0). So, when loading the re-saved with our component, we will get the correct value and provide it to you. For such kind of file, you have to call CalculateFormula() method manually after loading it to get the correct value for formulas.
1 Like
Thanks for clarifying why I got “0” instead of “Yes” or “”. Although, if you look E28 and E29, they have as value “true” (which Aspose reports as string). The formula says if E28=“TRUE” then “Yes”, which is not being evaluated as true, the expression, as the result for F28 is “”.
@brunojuliao,
Thanks for your feedback.
We will log your findings against your issue into our database. We will look into it and get back to you with more details.
1 Like
@brunojuliao,
In the original file, the formula data of those cells is invalid. MS Excel will fix those invalid data automatically when it loads the template file. That is also why our component can calculate the file re-saved by MS Excel correctly but cannot do the same for the original file. Now that MS Excel can fix the data, we will support to fix it too. We will provide fix for it soon.
1 Like
@brunojuliao,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46539”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
1 Like
The issues you have found earlier (filed as CELLSNET-46539) have been fixed in Aspose.Cells for .NET v19.1. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi
1 Like
