I'm trying to read Excel cell's display text. There is a cell which format is: _ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_ You see the 3rd part of formatting string is [_ * "-"??_ ]. In Excel, if the cell's value is zero, it looks like " - ". In poi, Cellformat.apply(new Float(0)) will return a string: "- 0 ", contains a zero which shouldn't exist. I debugged the source code of ver3.14 and found it may caused by following code: Class: org.apache.poi.ss.format.CellNumberFormatter ------------------------------------------------ private void writeInteger(.... .......... if (resultCh != '0' || s.ch == '0' || s.ch == '?' || pos >= strip) { zeroStrip = s.ch == '?' && pos < strip; output.setCharAt(s.pos, (zeroStrip ? ' ' : resultCh)); lastOutputIntegerDigit = s; } ------------------------------------------------ The condition for variable "zeroStrip" checks the curren char's position. If it is the last char(pos equals strip), zeroStrip will be false. It leads the last zero will always be displayed. Shall we use "pos <= strip" instead of "pos < strip" to fix this issue? Best Regards
Thanks for reporting and investigating the issue. Could you create a Pull Request on https://github.com/apache/poi with a test case?
I'm not sure whether it's a bug or a feature. Maybe for some reasons, the zero character must appear at least once? If nobody knows that, I can create a pull request and do some unit test, a few days later. I need more time to confirm the current logic.
Found already there is a TODO in unit test TestCellFormat.java : // TODO Fix these to not have an incorrect bonus 0 on the end //assertEquals(" "+pound+" - ", cfUK.apply(Double.valueOf(0)).text); //assertEquals(" - "+euro+" ", cfFR.apply(Double.valueOf(0)).text); so it's a known issue. I have tried to fix code in my way, it works but leads other case failed. I will create a pull request if I find the correct way. I hope so.
I created a pull request: https://github.com/apache/poi/pull/106. Also found another bug when test with new test case for my code. This bug exists all the time. I have commented out the failed new case and add a TODO on it.
The PR causes unit-tests to fail, please try to adjust any other unit-test as well to still have tests succeed with the changes applied.
(In reply to Dominik Stadler from comment #5) > The PR causes unit-tests to fail, please try to adjust any other unit-test > as well to still have tests succeed with the changes applied. Sorry for that. I didn't notice the ooxml test cases. It seems hard to resolve it. I will try.
*** Bug 59433 has been marked as a duplicate of this bug. ***
When trying to apply the changes from the PR it fixes the issue here, but some other tests fail, so unfortunately this needs a bit more work to either change the tests or the fix.