Bug 62242

Summary: Shouldn't show '0' if number format is "??" and cell value is 0
Product: POI Reporter: linghao2000
Component: SS CommonAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: normal CC: cgh_chen
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description linghao2000 2018-04-01 04:33:51 UTC
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
Comment 1 PJ Fanning 2018-04-01 15:14:03 UTC
Thanks for reporting and investigating the issue. Could you create a Pull Request on https://github.com/apache/poi with a test case?
Comment 2 linghao2000 2018-04-02 07:48:53 UTC
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.
Comment 3 linghao2000 2018-04-03 08:29:44 UTC
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.
Comment 4 linghao2000 2018-04-04 01:34:03 UTC
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.
Comment 5 Dominik Stadler 2018-04-05 18:24:47 UTC
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.
Comment 6 linghao2000 2018-04-06 03:22:33 UTC
(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.
Comment 7 Dominik Stadler 2020-03-07 16:04:17 UTC
*** Bug 59433 has been marked as a duplicate of this bug. ***
Comment 8 Dominik Stadler 2020-03-08 07:49:22 UTC
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.