Hi Team,
We have value 1234567.0375395 when we try to use below methods it return the values as listed below
cell.getStringValueWithoutFormat() --> 1234567.0375394989
cell.getStringValue() --> 1234567.04
cell.getValue() --> 1234567.0375394989
cell.getDoubleValue() --> 1234567.0375394989
we even tried with current version 21.5 but we could not get the expected value 1234567.0375395. Kindly suggest.
@Javith,
We have tried the following sample code with the latest version but could not observe this issue. Please share your environment details with us for our analysis.
Workbook workbook = **new** Workbook("Book1.xlsx");
//workbook.getWorksheets().get(0).getCells().get("A1").setValue(1234567.0375395);
Cell cell = workbook.getWorksheets().get(0).getCells().get("A1");
System.out.println(cell.getStringValueWithoutFormat());//=>1234567.0375395
System.out.println(cell.getStringValue());//=>1234567.038
System.out.println(cell.getValue());//=>1234567.0375395
System.out.println(cell.getDoubleValue());//=>1234567.0375395
System.out.println(cell.getDisplayStringValue());//=>1234567.038
Book1.xlsx.zip (6.4 KB)
@ahsan
Below are the environment details
could not able to download template shared , getting below message.
“Sorry, this file is private. Only visible to topic owner and staff members.”
Can you update the number to 1513628.0375395 and try again with the same code.
I am trying it in a similar environment except Windows 10 as I have Windows 7.
It is a simple Excel file containing only one value in cell A1. You may create your own file similar to this.
Here is the program output having sample value 1513628.0375395.
1513628.0375395
1513628.038
1513628.0375395
1513628.0375395
1513628.038@Javith,
For numeric values, Cell.StringValueWithoutFormat just gives the string of the numeric value kept in data model, such as by Double.toString().
We think you may want to get the string of the double value with maximum digits like what is shown in ms excel. If so, please format the numeric value with your custom format pattern. For ms excel, commonly the maximum precision for numeric values is 15 digits.
Because Cell.StringValueWithoutFormat leads to ambiguity for users, we have made it obsolete in recent versions and will remove it later in this year. Please format double values with your own format pattern according to your requirement instead. For cells, maybe you need to check whether the value in data model is numeric firstly, Cell.IsNumericValue should be helpful for such situation.
Hi,
I tried with new value 5589.39608967839 using the below code and still the result is not accurate.
System.out.println(cell.getStringValueWithoutFormat()); // 5589.396089678397
System.out.println(cell.getStringValue()); // 5589.3960896784
System.out.println(cell.getDoubleValue()); // 5589.396089678397
System.out.println(cell.getValue()); // 5589.396089678397
I could not able to find method Cell.IsNumericValue in latest aspose cells version 21.5.
I even try to use custom format for the cell using the below code but still it is not correct.
Style st = cell.getStyle();
st.setCustom("#.##############");
cell.setStyle(st);
StyleFlag flg = new StyleFlag();
flg.setNumberFormat(true);
cell.setStyle(st, flg);
Kindly helps us to get the correct value 5589.39608967839 using the suitable method.
Please try to simulate the issue from your side using windows and jdk1.8.0_231.
Thanks
Javith
For the method IsNumericValue, it is added from our recent fixes (e.g. 21.5.4), so please try our latest fix:
aspose-cells-21.5.6-java.zip (7.3 MB)
We recommended you should use the pattern to format the numeric value by yourselves. For Java program, you should get the double value from a cell, then build your own DecimalFormat with the pattern and use it to format the double value accordingly.
Anyways, if you still find any issue, kindly do share your exact code (runnable) to trace the issue. We will check your issue soon.
@Amjad,
Can you provide a sample code as per your recommedation. so that i can try the same. i already tried using the pattern and it didn’t worked.
@Javith,
Here is a sample code recommended by @Amjad_Sahi.
Workbook workbook = new Workbook();
workbook.getWorksheets().get(0).getCells().get("A1").setValue(5589.39608967839);
Cell cell = workbook.getWorksheets().get(0).getCells().get("A1");
Double value = cell.getDoubleValue();
NumberFormat formatter = new DecimalFormat("#0.###########");
System.out.println(formatter.format(value));
@ahsaniqbalsidiqui and @Amjad_Sahi
formatter code does not works always. As per your code we have 11 # after decimal (.###########) but if we have A1 value as “1513628.0375395” then it will print “1513628.0375394989” instead of “1513628.0375395”. So I believe formatter does not work always.
We are are expecting why value “1513628.0375395” is not printing even when we use getStringValueWithoutFormat() method. We need to print what ever the value in the cells without formatting. The value can contains any number of values after decimal. it may have 7 values after decimal or 11 values but the result should print the exact value which is in the cell without formatting.
Kindly help us to resolve the issue.
@Javith,
You may please share your sample excel file and runnable code to observe this issue as we cannot reproduce this issue here.
The problem you are facing is exactly what we are facing for the implementation of Cell.StringValueWithoutFormat. And for the precision of decimal values, the behavior of the formatter may be different for different environments(such as JDK version).
As a component provider, we must also consider all possible situations, that will make the performance of our implementation very poor. At the same time, different users have different requirement for StringValueWithoutFormat property, that makes this API ambiguous for different users, because no matter what results we provide, we cannot meet all those different needs.
So, we have made Cell.StringValueWithoutFormat obsolete and will remove it in future. User needs to format the original double value by themselves according to their specific requirement. Commonly user should know their data better and can choose a special solution(formatter) to get the expected result.
Hi,
I have a problem similar to one posted here some years ago.
I’ m using last Aspose Cells version, in .NET:
Real value typed in excel sheet: 8959108000156850000
Aspose conversion:
How can I have original value inserted (19 digits)?
I already tried to modify:
txtLoadOptions.KeepPrecision = true;
txtLoadOptions.ConvertNumericData = false;
txtLoadOptions.KeepUnparsedData = true;
Note that I can’t modify original file and I don’t know in which column this kink of numbers appear.
Thanks
Please note, this is not an issue with Aspose.Cells APIs (as we told above) but with JDK capacity/limitation itself. We have also made Cell.StringValueWithoutFormat obsolete, so this will not help at all. In short, you need to format the original double value by yourselves accordingly for your specific requirement.
For example, in your template Excel file, you have stored value “8959108000156850000” into A1 cell of the first worksheet. Now you need to extract the original value, so you may try using the following sample code. This still might not accurately give you 100% same results, the result might have difference after 16 digits due to capacity/limits.
e.g.
Sample code:
.......
Cell cell = workbook.getWorksheets().get(0).getCells().get("A1");
Double value = cell.getDoubleValue();
java.text.NumberFormat formatter = new java.text.DecimalFormat("0");
System.out.println(formatter.format(value));
........
Hope, this helps a bit.
Thanks for your reply @Amjad_Sahi,
unfortunately I don’t know in which column these values are, it should be an automatic process.
I’ll keep trying