You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2016/02/03 16:52:54 UTC
[Bug 58966] New: Row.getCell doesn't retrieve the same value than
what VBA gives
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
Bug ID: 58966
Summary: Row.getCell doesn't retrieve the same value than what
VBA gives
Product: POI
Version: unspecified
Hardware: PC
Status: NEW
Severity: major
Priority: P2
Component: SS Common
Assignee: dev@poi.apache.org
Reporter: jmclej@gmail.com
Hello,
I am having a problem with retrieving the right data from excel:
I have the cell D5 that contains the formula "=3000000/D10" and D10 cell
contains the formula "=1.41973/1.2939".
When I read this cell within VBA macro like this : Worksheets("My
sheet").Cells(5, 4), I get 2734111.41555084 but when I read it via POI in Java:
XSSFSheet sheet = workbook.getSheetAt(1);
final Iterator<Row> rowIterator = sheet.iterator();
Cell cell;
while (rowIterator.hasNext()) {
final Row row = rowIterator.next();
...
cell = row.getCell(col, Row.RETURN_BLANK_AS_NULL);
...
}
cell contains here 2734111.4155508447 (see the extra 47), which is wrong.
Indeed, when I type in a calculator 3000000/(1.41973/1.2939), the result is
2734111.4155508441745965782226198 ()
I expect to get the same result as VBA gives.
Regards,
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #10 from jmclej@gmail.com ---
(In reply to jmclej from comment #8)
> Also, workbook is of type XSSFWorkbook. Can it have an effect on my issue ?
I have tried using WorkBook instead of XSSFWorkbook with WorkbookFactory.create
method but it still gives me the same result.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #14 from jmclej@gmail.com ---
When you read this for instance : https://support.microsoft.com/en-us/kb/269370
We understand that Excel never gives more than 15 digits, so neither should
POI, whatever the format put with more than 15 '#'. It should only add
non-significative zeros.
But in my case POI gives 17 digits (2734111.4155508447) instead of the 15 that
I am talking about (2734111.41555084).
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
jmclej@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|INVALID |---
--- Comment #6 from jmclej@gmail.com ---
Ok, Indeed, I didn't get that DataFormatter was provided by POI.
But when I am doing this now :
XSSFSheet sheet = workbook.getSheetAt(1);
final Iterator<Row> rowIterator = sheet.iterator();
Cell cell;
while (rowIterator.hasNext()) {
final Row row = rowIterator.next();
...
cell = row.getCell(col, Row.RETURN_BLANK_AS_NULL);
DataFormatter df = new DataFormatter();//same with DataFormatter(true)
org.apache.poi.ss.usermodel.CellValue cv = evaluator.evaluate(cell);
String val = df.formatCellValue(cell, evaluator);
...
}
I still get val that is equal to 2734111.4155508447
In
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html,
it says that the default format for decimal numbers is "#.##########", but it I
can't reproduce that. Or should I use a different code than the one above to
use DataFormatter ?
I also tried (new
java.text.DecimalFormat("#.##########")).format(cell.getNumericCellValue()) but
it gives also 2734111.4155508447 instead of 2734111.41555084 on Excel.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
jmclej@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
CC| |jmclej@gmail.com
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #12 from jmclej@gmail.com ---
Version of poi : 3.10.1
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
jmclej@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
OS|All |Windows 7
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|REOPENED |RESOLVED
Resolution|--- |INVALID
--- Comment #5 from Javen O'Neal <on...@apache.org> ---
(In reply to jmclej from comment #4)
> (In reply to Nick Burch from comment #1)
> > The number stored in the file is rarely what Excel shows.
> POI should reproduce the default behavior
> of Excel as well.
Re-read Nick's comment. POI does have rhe samw behavior as Excel when you use
the DataFormatter class.
https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #11 from jmclej@gmail.com ---
Additional info : I am using Excel 2013
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #8 from jmclej@gmail.com ---
Also, workbook is of type XSSFWorkbook. Can it have an effect on my issue ?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #9 from jmclej@gmail.com ---
Sorry about the different small comments, I am trying to give as much relevant
information as possible...
So I must say that I read a xlsm file.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #3 from Javen O'Neal <on...@apache.org> ---
(In reply to jmclej from comment #2)
> (...844174 cannot give ...8447)
Sure it can. You're thinking of base 10 rounding, while the computer does base
2 rounding. The binary representation of ...844174 and ...8447 might be
adjacent, depending on how the floats are stored, what arithmetic is used to
calculate the values, the rounding errors that are accumulated along the way,
etc. Even simple 3*(1.0/3) could give an answer like 1.000000000007 or
0.9999999999994, which doesn't round to the correct answer
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #7 from jmclej@gmail.com ---
I forgot to declare and define evaluator :
final FormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
jmclej@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Severity|major |critical
--- Comment #16 from jmclej@gmail.com ---
Hello,
Now that the bug has been prooved, how does it work? On which version can we
expect to have the correction and do we have an idea when it will be delivered
?
Thanks for your work,
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #4 from jmclej@gmail.com ---
(In reply to Nick Burch from comment #1)
> The number stored in the file is rarely what Excel shows. To get the value
> that Excel shows, as a String, you must use something like DataFormatter to
> apply the formatting rules to the raw cell value
In VBA, I have :
Dim MyVal As Double
MyVal = Worksheets("My sheet").Cells(5, 4)
And I am talking about the number I see in the file into which I print MyVal
without transformation or that I look at MyVal in the VBA spy window.
So I am expecting to get the same number via POI without the need to apply any
DataFormatter in Java, which I couldn't know which it is anyway as I am using
the default behavior of VBA. POI should reproduce the default behavior of Excel
as well.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #15 from Javen O'Neal <on...@apache.org> ---
More authoritatively, [1] which specifies
> Feature Maximum limit
> Number precision 15 digits
[1]
https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #17 from Javen O'Neal <on...@apache.org> ---
Refer to http://poi.apache.org/who.html
Please always remember: nobody is paid to work on POI, the team is a bunch of
volunteers who look at things in their free time. Because of that developers
might choose to work on things based on a different priority than yours!
Especially the quality and maturity of bug reports will affect if somebody
decides to look at it. So the best way to help a bug report see progress is to
provide more information if available or supply patches together with
unit-tests.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
jmclej@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Version|unspecified |3.10-FINAL
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
jmclej@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|INVALID |---
Status|RESOLVED |REOPENED
--- Comment #2 from jmclej@gmail.com ---
Independently of what Excel shows, there is definitely a problem with POI as
2734111.4155508441745965782226198 cannot be rounded to 2734111.4155508447 if
you look closely at the first number (...844174 cannot give ...8447)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |INVALID
OS| |All
--- Comment #1 from Nick Burch <ap...@gagravarr.org> ---
The number stored in the file is rarely what Excel shows. To get the value that
Excel shows, as a String, you must use something like DataFormatter to apply
the formatting rules to the raw cell value
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 58966] Row.getCell doesn't retrieve the same value than what
VBA gives
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=58966
--- Comment #13 from Javen O'Neal <on...@apache.org> ---
> text.DecimalFormat("#.##########"))
> 2734111.4155508447
Looks like the DataFormatter is working as expected. The number of digits is
conaistenr with the format string.
We could add another 2 # characters to the format string, but someone would
need to prove that every version of Excel on all platforms use the longer
format string for any number or formula. I'm guessing that the default format
string was chosen to be the length it is for a good reason, and wasn't selected
at random.
Until then, if you need your code to return exactly the same value as Excel,
you'll need to specify a custom format string.
If you have usage questions, please use the POI Users mailing list [1].
[1] https://poi.apache.org/mailinglists.html
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org