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 2019/02/26 23:59:04 UTC

[Bug 63211] New: DataFormatter incorrectly formats data formats with escaped percent character

https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

            Bug ID: 63211
           Summary: DataFormatter incorrectly formats data formats with
                    escaped percent character
           Product: POI
           Version: unspecified
          Hardware: PC
            Status: NEW
          Severity: major
          Priority: P2
         Component: SS Common
          Assignee: dev@poi.apache.org
          Reporter: spdooley@us.ibm.com
  Target Milestone: ---

When a format contains an escaped percent character (i.e. \% or "%"), the
escaping is ignored and the resultant formatted value is 100 times larger than
it should be. Some examples to illustrate the problem:

// Should be 12.5% but is 1250.0%
new DataFormatter(Locale.US).formatRawCellContents(12.5, -1,
"0.0\\%;\\-0.0\\%");
// Should be 12.5% but is 1250.0%
new DataFormatter(Locale.US).formatRawCellContents(12.5, -1,
"0.0\"%\";\\-0.0\"%\"");
// Should be -12.5% but is -1250.0%
new DataFormatter(Locale.US).formatRawCellContents(-12.5, -1,
"0.0\\%;\\-0.0\\%");
// Should be -12.5% but is -1250.0%
new DataFormatter(Locale.US).formatRawCellContents(-12.5, -1,
"0.0\\%;\\-0.0\\%");

-- 
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 63211] DataFormatter incorrectly formats data formats with escaped percent character

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

--- Comment #5 from PJ Fanning <fa...@yahoo.com> ---
added r1895312

-- 
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 63211] DataFormatter incorrectly formats data formats with escaped percent character

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

PJ Fanning <fa...@yahoo.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |FIXED

-- 
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 63211] DataFormatter incorrectly formats data formats with escaped percent character

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

--- Comment #4 from spdooley@us.ibm.com ---
Additionally, see the Excel documentation regarding format code guidelines:
https://support.office.com/en-us/article/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68.
From the text and spacing section:

To display both text and numbers in a cell, enclose the text characters in
double quotation marks (" ") or precede a single character with a backslash
(\). Include the characters in the appropriate section of the format codes. For
example, you could type the format $0.00" Surplus";$–0.00" Shortage" to display
a positive amount as "$125.74 Surplus" and a negative amount as "$–125.74
Shortage."

-- 
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 63211] DataFormatter incorrectly formats data formats with escaped percent character

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

PJ Fanning <fa...@yahoo.com> changed:

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

-- 
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 63211] DataFormatter incorrectly formats data formats with escaped percent character

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

spdooley@us.ibm.com changed:

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

--- Comment #3 from spdooley@us.ibm.com ---
See attachment for details on how this is handled in Excel. It seems the
problem is related to DataFormatter#cleanFormatForNumber. At line 675, there is
a check for a quote in backslash and these characters are removed from the
format. This does not seem correct. Instead, it seems that when there is a
backslash or a quoted value, it should be converted to quoted value in the Java
format (e.g. \% -> '%' and "%" -> '%').

-- 
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 63211] DataFormatter incorrectly formats data formats with escaped percent character

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

PJ Fanning <fa...@yahoo.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--- Comment #1 from PJ Fanning <fa...@yahoo.com> ---
12.5% is 0.125 in decimal. formatRawCellContents should assume you are
providing a decimal.

-- 
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 63211] DataFormatter incorrectly formats data formats with escaped percent character

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=63211

--- Comment #2 from spdooley@us.ibm.com ---
Created attachment 36470
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=36470&action=edit
Cell format & value in Excel

In Excel, when the percent sign is escaped with a backslash, it does not treat
the value as a percentage and merely uses the decimal value as is without any
adjustment.

-- 
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