DO NOT REPLY [Bug 47490] New: HSSFDataFormatter.createFormat(double,int,string) does not handle '@' format

https://issues.apache.org/bugzilla/show_bug.cgi?id=47490

           Summary: HSSFDataFormatter.createFormat(double,int,string) does
                    not handle '@' format
           Product: POI
           Version: 3.2-FINAL
          Platform: PC
        OS/Version: Windows Server 2003
            Status: NEW
          Severity: major
          Priority: P2
         Component: HSSF
        AssignedTo: dev@...
        ReportedBy: jgarrison@...


This method contains the following code at the end:

    // TODO - when does this occur?
    return null;

The answer is that this occurs whenever a numeric cell is given "Text" format
(format string '@') in Excel.  Returning null at this point results in the
value being formatted with the default numeric format (a decimal place) even
though the value displayed in Excel is an integer.

I would contribute a patch but don't yet understand enough of the framework to
know the best way to fix this.

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

https://issues.apache.org/bugzilla/show_bug.cgi?id=47490





--- Comment #1 from Jim Garrison <jgarrison@...>  2009-07-07 14:34:57 PST ---
After more research on Excel 2003, I've discovered the following.  

If you take a numeric cell and apply the 'Text' format to it, Excel DOES NOT
change the cell's internal representation to String.  So you end up with
something POI reads as numeric but the user believes is a String, because it's
left justified.  However if you export the spreadsheet as XML at this point you
will see that the cell still has a numeric type.  

While the cell is in the weird 'display as text but really still a number'
state, Excel seems to use a left-justified version of General format, and still
performs numeric display adjustments such as rounding if the column is too
narrow for all the decimal places, or switching to scientific notation if the
column is too narrow for the magnitude.

If you make any changes to the cell, even a null change such as clicking in the
formula bar and then clicking the green checkmark (or pressing Enter), then
Excel converts the stored value to text and attaches the green warning triangle
to the top left corner of the cell.  At this point the cell really contains
text.

What I think needs to happen is that a numeric cell with format '@' needs to be
formatted as "General".

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

https://issues.apache.org/bugzilla/show_bug.cgi?id=47490


Jim Garrison <jgarrison@...> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|major                       |normal




--- Comment #2 from Jim Garrison <jgarrison@...>  2009-07-07 15:21:57 PST ---
Based on my analysis I've come up with a workaround.  At the point in my code
where I'm calling HSSFDataFormatter.formatCellValue(cell) I know that I want
the numeric cell as a string, so I force the cell style to General as follows:

    HSSFCellStyle cs = cell.getCellStyle();
    cs.setDataFormat((short)-1);
    cell.setCellStyle(cs);
    value = new HSSFDataFormatter().formatCellValue(cell);

This returns the value formatted as it would be displayed assuming the column
width were sufficient -- I.e. it provides the entire value and not a rounded or
scientific-notation abbreviation.

I'm not sure how POI should handle the situation, so I'll leave that up to more
knowledgeable developers.

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...

https://issues.apache.org/bugzilla/show_bug.cgi?id=47490

Nick Burch <nick@...> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #3 from Nick Burch <nick@...> 2009-11-03 17:30:03 UTC ---
Any chance you could upload a very small file containing a cell with this type?

That'll be useful for testing/fixing against

--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@...
For additional commands, e-mail: dev-help@...