You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Eric Peters <Er...@Peters.org> on 2013/10/31 18:22:07 UTC
"Accounting" Format Option w/no Symbol still outputs a $ (or *
w/XSSF) when read?
Running into a funny field we're reading in.
In Excel, you can create one by putting say "4.79" in a cell, then format
it with accounting, then go into the options and select "None" for the
symbol.
My HSSF code prints "$4.79", and my XSSF code prints "* 4.79"
I would expect this to print the simple "4.79" Thoughts on howto chase
this bug down?
When I unzip the XLSX file and dig into the worksheet XML I see:
<c r="L2" s="7"><v>4.79</v></c>
<c r="M2" s="6"><v>4.79</v></c>
<c r="L3" s="7"><v>5.79</v></c>
<c r="M3" s="6"><v>5.79</v></c>
The L2/L3 I believe is the Accounting w/format of No Symbol, and the M2 is
Accounting w/the $ as a symbol
Style ID 7 refers to:
<xf numFmtId="43" fontId="0" fillId="0" borderId="0" xfId="0"
applyNumberFormat="1"/>
Style ID 6 refers to:
<xf numFmtId="44" fontId="0" fillId="0" borderId="0" xfId="0"
applyNumberFormat="1"/>
43 in hex => 0x2B, 44 in hex => 0x2C
http://www.docjar.com/docs/api/org/apache/poi/ss/usermodel/BuiltinFormats.html
0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
So that's where the asterix comes in, but why is there an asterix?
Regards,
Eric
Re: "Accounting" Format Option w/no Symbol still outputs a $ (or *
w/XSSF) when read?
Posted by Eric Peters <Er...@Peters.org>.
I think I tracked down the bugs on this one:
Two issue
1) There is a code logic bug:
https://issues.apache.org/bugzilla/show_bug.cgi?id=55731
2) The actual format strings got swapped and don't match the javadoc
anymore: https://issues.apache.org/bugzilla/show_bug.cgi?id=55730
Would love to get some feedback from anyone else on these on bugzilla.
-Eric
On Thu, Oct 31, 2013 at 7:26 PM, Brian Nesbitt <br...@nesbot.com> wrote:
> I have seen this issue as well.
>
> As for the asterisk... its the difference between currency formatting and
> accounting formatting.
>
> http://office.microsoft.com/en-ca/excel-help/create-a-custom-number-format-HP010342372.aspx
>
> *Repeat characters: To repeat the next character in the format to fill the
> column width, include an asterisk (*) in the number format. For example,
> type 0*- to include enough dashes after a number to fill the cell, or type
> *0 before any format to include leading zeros.
> *
>
> The accounting format uses a space after the * so it fills the width with
> spaces getting the currency symbols to align left.
>
>
>
>
>
> On Thu, Oct 31, 2013 at 4:20 PM, Eric Peters <Er...@peters.org> wrote:
>
> > Oh, I should also say, if I completely just hack the the formatString for
> > "43" and completely remove the asterix, it seems to "format correctly" -
> > any idea what the asterix is suppose to do from a format standpoint?
> >
> > if(formatString == """_(* #,##0.00_);_(* \(#,##0.00\);_(*
> > "-"??_);_(@_)""" || formatString == """_("$"* #,##0.00_);_("$"*
> > \(#,##0.00\);_("$"* "-"??_);_(@_)""") formatString =
> > """_(#,##0.00_);_(\(#,##0.00\);_("-"??_);_(@_)"""
> >
> >
> >
> >
> > On Thu, Oct 31, 2013 at 1:18 PM, Eric Peters <Er...@peters.org> wrote:
> >
> >> On the HSSF Front:
> >>
> >> The xfindex appears to be correct, but
> formatListener.getFormatString(43)
> >> (FormatTrackingHSSFListener)
> >>
> >> HSSFDataFormat.getBuiltinFormat(43) => returns the format with $, when
> it
> >> should be *
> >> HSSFDataFormat.getBuiltinFormat(44) => returns the format with *, when
> it
> >> should be $
> >>
> >> Verified this at the command line:
> >>
> >> scala>
> >>
> org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("43".toShort)
> >> res2: String = _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
> >>
> >> scala>
> >>
> org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("44".toShort)
> >> res3: String = _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
> >>
> >>
> >>
> >>
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats
> >>
> >> 140 <
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#140
> >
> >>
> >> <
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#
> >
> >>
> >> putFormat <
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m,
> 0x2b, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)");
> >>
> >> 141 <
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#141
> >
> >>
> >> <
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#
> >
> >>
> >> putFormat <
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m,
> 0x2c, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)");
> >>
> >>
> >> I think these two lines basically need the formats swapped, the java
> docs
> >> actually have them in the other way:
> >>
> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html
> >>
> >> 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
> >> 0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
> >>
> >>
> >> Original Cell Records:
> >>
> >> 13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok
> >> numrec: [NUMBER]
> >> .row = 0x0007
> >> .col = 0x000B
> >> .xfindex= 0x0043
> >> .value= 9.79
> >> [/NUMBER]
> >> and formatListener.getFormatIndex(numrec): 43,
> >> formatListener.getFormatString(43): _("$"* #,##0.00_);_("$"*
> >> (#,##0.00);_("$"* "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(43):
> >> _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_),
> >> formatListener.formatNumberDateCell(numrec): $9.79
> >> 13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok
> >> numrec: [NUMBER]
> >> .row = 0x0007
> >> .col = 0x000C
> >> .xfindex= 0x0044
> >> .value= 9.79
> >> [/NUMBER]
> >> and formatListener.getFormatIndex(numrec): 44,
> >> formatListener.getFormatString(44): _(* #,##0.00_);_(* (#,##0.00);_(*
> >> "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(44): _(* #,##0.00_);_(*
> >> (#,##0.00);_(* "-"??_);_(@_),
> formatListener.formatNumberDateCell(numrec):
> >> * 9.79
> >>
> >> -Eric
> >>
> >> On Thu, Oct 31, 2013 at 11:07 AM, Eric Peters <Er...@peters.org> wrote:
> >>
> >>> Didn't look to change at all - I added a 2nd unit test in for of an
> >>> actual "Accounting" field with a $ symbol, and it appears the HSSF
> >>> formatter is bugged, it prints the * when it should be $, and it
> prints the
> >>> $ when it should be * (to at least match the XSSF pattern) I tripple
> >>> checked my test Excel files and they both look like:
> >>>
> >>> [image: Inline image 2][image: Inline image 1]
> >>>
> >>> Unit Test Output:
> >>>
> >>> (Unit Test Read Value) => (Expected Unit Test Value)
> >>>
> >>> Here's 3.9:
> >>>
> >>> HSSF:
> >>> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79,
> >>> $4.79)) (TestExcelFlatFileReaderCommon.scala:37)
> >>>
> >>> XSSF:
> >>> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79,
> $4.79))
> >>> (TestExcelFlatFileReaderCommon.scala:37)
> >>>
> >>> Heres's 3.10-beta2
> >>> & Just for reference, still on the old ooxml & xerces:
> >>> "org.apache.poi" % "poi" % "3.10-beta2", // XLS/XLSX
> Stream
> >>> Reader
> >>> "org.apache.poi" % "poi-ooxml" % "3.10-beta2", // XLS/XLSX
> Stream
> >>> Reader
> >>> "org.apache.poi" % "poi-scratchpad" % "3.10-beta2", // XLS/XLSX
> Stream
> >>> Reader
> >>> "org.apache.poi" % "ooxml-schemas" % "1.1", // XLS/XLSX Stream
> Reader
> >>> "xerces" % "xercesImpl" % "2.11.0", // XLS/XLSX Stream
> Reader
> >>>
> >>> HSSF:
> >>> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79,
> >>> $4.79)) (TestExcelFlatFileReaderCommon.scala:37)
> >>> XSSF:
> >>> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79,
> >>> $4.79)) (TestExcelFlatFileReaderCommon.scala:37)
> >>>
> >>> -Eric
> >>>
> >>>
> >>>
> >>> On Thu, Oct 31, 2013 at 10:33 AM, Nick Burch <apache@gagravarr.org
> >wrote:
> >>>
> >>>> On Thu, 31 Oct 2013, Eric Peters wrote:
> >>>>
> >>>>> In Excel, you can create one by putting say "4.79" in a cell, then
> >>>>> format
> >>>>> it with accounting, then go into the options and select "None" for
> the
> >>>>> symbol.
> >>>>>
> >>>>> My HSSF code prints "$4.79", and my XSSF code prints "* 4.79"
> >>>>>
> >>>>> I would expect this to print the simple "4.79" Thoughts on howto
> chase
> >>>>> this bug down?
> >>>>>
> >>>>
> >>>> Have you tried with 3.10 beta 2? I think there have been some fixes to
> >>>> that logic in the last year or so
> >>>>
> >>>> Nick
> >>>>
> >>>> ------------------------------**------------------------------**
> >>>> ---------
> >>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<
> user-unsubscribe@poi.apache.org>
> >>>> For additional commands, e-mail: user-help@poi.apache.org
> >>>>
> >>>>
> >>>
> >>
> >
>
Re: "Accounting" Format Option w/no Symbol still outputs a $ (or *
w/XSSF) when read?
Posted by Brian Nesbitt <br...@nesbot.com>.
I have seen this issue as well.
As for the asterisk... its the difference between currency formatting and
accounting formatting.
http://office.microsoft.com/en-ca/excel-help/create-a-custom-number-format-HP010342372.aspx
*Repeat characters: To repeat the next character in the format to fill the
column width, include an asterisk (*) in the number format. For example,
type 0*- to include enough dashes after a number to fill the cell, or type
*0 before any format to include leading zeros.
*
The accounting format uses a space after the * so it fills the width with
spaces getting the currency symbols to align left.
On Thu, Oct 31, 2013 at 4:20 PM, Eric Peters <Er...@peters.org> wrote:
> Oh, I should also say, if I completely just hack the the formatString for
> "43" and completely remove the asterix, it seems to "format correctly" -
> any idea what the asterix is suppose to do from a format standpoint?
>
> if(formatString == """_(* #,##0.00_);_(* \(#,##0.00\);_(*
> "-"??_);_(@_)""" || formatString == """_("$"* #,##0.00_);_("$"*
> \(#,##0.00\);_("$"* "-"??_);_(@_)""") formatString =
> """_(#,##0.00_);_(\(#,##0.00\);_("-"??_);_(@_)"""
>
>
>
>
> On Thu, Oct 31, 2013 at 1:18 PM, Eric Peters <Er...@peters.org> wrote:
>
>> On the HSSF Front:
>>
>> The xfindex appears to be correct, but formatListener.getFormatString(43)
>> (FormatTrackingHSSFListener)
>>
>> HSSFDataFormat.getBuiltinFormat(43) => returns the format with $, when it
>> should be *
>> HSSFDataFormat.getBuiltinFormat(44) => returns the format with *, when it
>> should be $
>>
>> Verified this at the command line:
>>
>> scala>
>> org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("43".toShort)
>> res2: String = _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
>>
>> scala>
>> org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("44".toShort)
>> res3: String = _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
>>
>>
>>
>> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats
>>
>> 140 <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#140>
>>
>> <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#>
>>
>> putFormat <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m, 0x2b, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)");
>>
>> 141 <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#141>
>>
>> <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#>
>>
>> putFormat <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m, 0x2c, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)");
>>
>>
>> I think these two lines basically need the formats swapped, the java docs
>> actually have them in the other way:
>> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html
>>
>> 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
>> 0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
>>
>>
>> Original Cell Records:
>>
>> 13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok
>> numrec: [NUMBER]
>> .row = 0x0007
>> .col = 0x000B
>> .xfindex= 0x0043
>> .value= 9.79
>> [/NUMBER]
>> and formatListener.getFormatIndex(numrec): 43,
>> formatListener.getFormatString(43): _("$"* #,##0.00_);_("$"*
>> (#,##0.00);_("$"* "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(43):
>> _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_),
>> formatListener.formatNumberDateCell(numrec): $9.79
>> 13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok
>> numrec: [NUMBER]
>> .row = 0x0007
>> .col = 0x000C
>> .xfindex= 0x0044
>> .value= 9.79
>> [/NUMBER]
>> and formatListener.getFormatIndex(numrec): 44,
>> formatListener.getFormatString(44): _(* #,##0.00_);_(* (#,##0.00);_(*
>> "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(44): _(* #,##0.00_);_(*
>> (#,##0.00);_(* "-"??_);_(@_), formatListener.formatNumberDateCell(numrec):
>> * 9.79
>>
>> -Eric
>>
>> On Thu, Oct 31, 2013 at 11:07 AM, Eric Peters <Er...@peters.org> wrote:
>>
>>> Didn't look to change at all - I added a 2nd unit test in for of an
>>> actual "Accounting" field with a $ symbol, and it appears the HSSF
>>> formatter is bugged, it prints the * when it should be $, and it prints the
>>> $ when it should be * (to at least match the XSSF pattern) I tripple
>>> checked my test Excel files and they both look like:
>>>
>>> [image: Inline image 2][image: Inline image 1]
>>>
>>> Unit Test Output:
>>>
>>> (Unit Test Read Value) => (Expected Unit Test Value)
>>>
>>> Here's 3.9:
>>>
>>> HSSF:
>>> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79,
>>> $4.79)) (TestExcelFlatFileReaderCommon.scala:37)
>>>
>>> XSSF:
>>> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79, $4.79))
>>> (TestExcelFlatFileReaderCommon.scala:37)
>>>
>>> Heres's 3.10-beta2
>>> & Just for reference, still on the old ooxml & xerces:
>>> "org.apache.poi" % "poi" % "3.10-beta2", // XLS/XLSX Stream
>>> Reader
>>> "org.apache.poi" % "poi-ooxml" % "3.10-beta2", // XLS/XLSX Stream
>>> Reader
>>> "org.apache.poi" % "poi-scratchpad" % "3.10-beta2", // XLS/XLSX Stream
>>> Reader
>>> "org.apache.poi" % "ooxml-schemas" % "1.1", // XLS/XLSX Stream Reader
>>> "xerces" % "xercesImpl" % "2.11.0", // XLS/XLSX Stream Reader
>>>
>>> HSSF:
>>> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79,
>>> $4.79)) (TestExcelFlatFileReaderCommon.scala:37)
>>> XSSF:
>>> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79,
>>> $4.79)) (TestExcelFlatFileReaderCommon.scala:37)
>>>
>>> -Eric
>>>
>>>
>>>
>>> On Thu, Oct 31, 2013 at 10:33 AM, Nick Burch <ap...@gagravarr.org>wrote:
>>>
>>>> On Thu, 31 Oct 2013, Eric Peters wrote:
>>>>
>>>>> In Excel, you can create one by putting say "4.79" in a cell, then
>>>>> format
>>>>> it with accounting, then go into the options and select "None" for the
>>>>> symbol.
>>>>>
>>>>> My HSSF code prints "$4.79", and my XSSF code prints "* 4.79"
>>>>>
>>>>> I would expect this to print the simple "4.79" Thoughts on howto chase
>>>>> this bug down?
>>>>>
>>>>
>>>> Have you tried with 3.10 beta 2? I think there have been some fixes to
>>>> that logic in the last year or so
>>>>
>>>> Nick
>>>>
>>>> ------------------------------**------------------------------**
>>>> ---------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>
>>
>
Re: "Accounting" Format Option w/no Symbol still outputs a $ (or *
w/XSSF) when read?
Posted by Eric Peters <Er...@Peters.org>.
Oh, I should also say, if I completely just hack the the formatString for
"43" and completely remove the asterix, it seems to "format correctly" -
any idea what the asterix is suppose to do from a format standpoint?
if(formatString == """_(* #,##0.00_);_(* \(#,##0.00\);_(*
"-"??_);_(@_)""" || formatString == """_("$"* #,##0.00_);_("$"*
\(#,##0.00\);_("$"* "-"??_);_(@_)""") formatString =
"""_(#,##0.00_);_(\(#,##0.00\);_("-"??_);_(@_)"""
On Thu, Oct 31, 2013 at 1:18 PM, Eric Peters <Er...@peters.org> wrote:
> On the HSSF Front:
>
> The xfindex appears to be correct, but formatListener.getFormatString(43)
> (FormatTrackingHSSFListener)
>
> HSSFDataFormat.getBuiltinFormat(43) => returns the format with $, when it
> should be *
> HSSFDataFormat.getBuiltinFormat(44) => returns the format with *, when it
> should be $
>
> Verified this at the command line:
>
> scala>
> org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("43".toShort)
> res2: String = _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
>
> scala>
> org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("44".toShort)
> res3: String = _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
>
>
>
> http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats
>
> 140 <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#140>
>
> <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#>
>
> putFormat <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m, 0x2b, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)");
>
> 141 <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#141>
>
> <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#>
>
> putFormat <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m, 0x2c, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)");
>
>
> I think these two lines basically need the formats swapped, the java docs
> actually have them in the other way:
> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html
>
> 0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
> 0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
>
>
> Original Cell Records:
>
> 13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok
> numrec: [NUMBER]
> .row = 0x0007
> .col = 0x000B
> .xfindex= 0x0043
> .value= 9.79
> [/NUMBER]
> and formatListener.getFormatIndex(numrec): 43,
> formatListener.getFormatString(43): _("$"* #,##0.00_);_("$"*
> (#,##0.00);_("$"* "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(43):
> _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_),
> formatListener.formatNumberDateCell(numrec): $9.79
> 13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok
> numrec: [NUMBER]
> .row = 0x0007
> .col = 0x000C
> .xfindex= 0x0044
> .value= 9.79
> [/NUMBER]
> and formatListener.getFormatIndex(numrec): 44,
> formatListener.getFormatString(44): _(* #,##0.00_);_(* (#,##0.00);_(*
> "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(44): _(* #,##0.00_);_(*
> (#,##0.00);_(* "-"??_);_(@_), formatListener.formatNumberDateCell(numrec):
> * 9.79
>
> -Eric
>
> On Thu, Oct 31, 2013 at 11:07 AM, Eric Peters <Er...@peters.org> wrote:
>
>> Didn't look to change at all - I added a 2nd unit test in for of an
>> actual "Accounting" field with a $ symbol, and it appears the HSSF
>> formatter is bugged, it prints the * when it should be $, and it prints the
>> $ when it should be * (to at least match the XSSF pattern) I tripple
>> checked my test Excel files and they both look like:
>>
>> [image: Inline image 2][image: Inline image 1]
>>
>> Unit Test Output:
>>
>> (Unit Test Read Value) => (Expected Unit Test Value)
>>
>> Here's 3.9:
>>
>> HSSF:
>> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79, $4.79))
>> (TestExcelFlatFileReaderCommon.scala:37)
>>
>> XSSF:
>> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79, $4.79))
>> (TestExcelFlatFileReaderCommon.scala:37)
>>
>> Heres's 3.10-beta2
>> & Just for reference, still on the old ooxml & xerces:
>> "org.apache.poi" % "poi" % "3.10-beta2", // XLS/XLSX Stream
>> Reader
>> "org.apache.poi" % "poi-ooxml" % "3.10-beta2", // XLS/XLSX Stream
>> Reader
>> "org.apache.poi" % "poi-scratchpad" % "3.10-beta2", // XLS/XLSX Stream
>> Reader
>> "org.apache.poi" % "ooxml-schemas" % "1.1", // XLS/XLSX Stream Reader
>> "xerces" % "xercesImpl" % "2.11.0", // XLS/XLSX Stream Reader
>>
>> HSSF:
>> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79, $4.79))
>> (TestExcelFlatFileReaderCommon.scala:37)
>> XSSF:
>> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79, $4.79))
>> (TestExcelFlatFileReaderCommon.scala:37)
>>
>> -Eric
>>
>>
>>
>> On Thu, Oct 31, 2013 at 10:33 AM, Nick Burch <ap...@gagravarr.org>wrote:
>>
>>> On Thu, 31 Oct 2013, Eric Peters wrote:
>>>
>>>> In Excel, you can create one by putting say "4.79" in a cell, then
>>>> format
>>>> it with accounting, then go into the options and select "None" for the
>>>> symbol.
>>>>
>>>> My HSSF code prints "$4.79", and my XSSF code prints "* 4.79"
>>>>
>>>> I would expect this to print the simple "4.79" Thoughts on howto chase
>>>> this bug down?
>>>>
>>>
>>> Have you tried with 3.10 beta 2? I think there have been some fixes to
>>> that logic in the last year or so
>>>
>>> Nick
>>>
>>> ------------------------------**------------------------------**
>>> ---------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>
>
Re: "Accounting" Format Option w/no Symbol still outputs a $ (or *
w/XSSF) when read?
Posted by Eric Peters <Er...@Peters.org>.
On the HSSF Front:
The xfindex appears to be correct, but formatListener.getFormatString(43)
(FormatTrackingHSSFListener)
HSSFDataFormat.getBuiltinFormat(43) => returns the format with $, when it
should be *
HSSFDataFormat.getBuiltinFormat(44) => returns the format with *, when it
should be $
Verified this at the command line:
scala>
org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("43".toShort)
res2: String = _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
scala>
org.apache.poi.hssf.usermodel.HSSFDataFormat.getBuiltinFormat("44".toShort)
res3: String = _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats
140 <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#140>
<http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#>
putFormat <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m,
0x2b, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"*
\"-\"??_);_(@_)");
141 <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#141>
<http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#>
putFormat <http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/poi/3.10-beta1/org/apache/poi/ss/usermodel/BuiltinFormats.java#BuiltinFormats.putFormat%28java.util.List%2Cint%2Cjava.lang.String%29>(m,
0x2c, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)");
I think these two lines basically need the formats swapped, the java docs
actually have them in the other way:
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html
0x2b, "_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
0x2c, "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
Original Cell Records:
13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok numrec:
[NUMBER]
.row = 0x0007
.col = 0x000B
.xfindex= 0x0043
.value= 9.79
[/NUMBER]
and formatListener.getFormatIndex(numrec): 43,
formatListener.getFormatString(43): _("$"* #,##0.00_);_("$"*
(#,##0.00);_("$"* "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(43):
_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_),
formatListener.formatNumberDateCell(numrec): $9.79
13:18:13.217 [main] WARN fm.flatfile.excel.XLSStreamProcessor - ok numrec:
[NUMBER]
.row = 0x0007
.col = 0x000C
.xfindex= 0x0044
.value= 9.79
[/NUMBER]
and formatListener.getFormatIndex(numrec): 44,
formatListener.getFormatString(44): _(* #,##0.00_);_(* (#,##0.00);_(*
"-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(44): _(* #,##0.00_);_(*
(#,##0.00);_(* "-"??_);_(@_), formatListener.formatNumberDateCell(numrec):
* 9.79
-Eric
On Thu, Oct 31, 2013 at 11:07 AM, Eric Peters <Er...@peters.org> wrote:
> Didn't look to change at all - I added a 2nd unit test in for of an actual
> "Accounting" field with a $ symbol, and it appears the HSSF formatter is
> bugged, it prints the * when it should be $, and it prints the $ when it
> should be * (to at least match the XSSF pattern) I tripple checked my test
> Excel files and they both look like:
>
> [image: Inline image 2][image: Inline image 1]
>
> Unit Test Output:
>
> (Unit Test Read Value) => (Expected Unit Test Value)
>
> Here's 3.9:
>
> HSSF:
> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79, $4.79))
> (TestExcelFlatFileReaderCommon.scala:37)
>
> XSSF:
> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79, $4.79))
> (TestExcelFlatFileReaderCommon.scala:37)
>
> Heres's 3.10-beta2
> & Just for reference, still on the old ooxml & xerces:
> "org.apache.poi" % "poi" % "3.10-beta2", // XLS/XLSX Stream
> Reader
> "org.apache.poi" % "poi-ooxml" % "3.10-beta2", // XLS/XLSX Stream
> Reader
> "org.apache.poi" % "poi-scratchpad" % "3.10-beta2", // XLS/XLSX Stream
> Reader
> "org.apache.poi" % "ooxml-schemas" % "1.1", // XLS/XLSX Stream Reader
> "xerces" % "xercesImpl" % "2.11.0", // XLS/XLSX Stream Reader
>
> HSSF:
> Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79, $4.79))
> (TestExcelFlatFileReaderCommon.scala:37)
> XSSF:
> Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79, $4.79))
> (TestExcelFlatFileReaderCommon.scala:37)
>
> -Eric
>
>
>
> On Thu, Oct 31, 2013 at 10:33 AM, Nick Burch <ap...@gagravarr.org> wrote:
>
>> On Thu, 31 Oct 2013, Eric Peters wrote:
>>
>>> In Excel, you can create one by putting say "4.79" in a cell, then format
>>> it with accounting, then go into the options and select "None" for the
>>> symbol.
>>>
>>> My HSSF code prints "$4.79", and my XSSF code prints "* 4.79"
>>>
>>> I would expect this to print the simple "4.79" Thoughts on howto chase
>>> this bug down?
>>>
>>
>> Have you tried with 3.10 beta 2? I think there have been some fixes to
>> that logic in the last year or so
>>
>> Nick
>>
>> ------------------------------**------------------------------**---------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: "Accounting" Format Option w/no Symbol still outputs a $ (or *
w/XSSF) when read?
Posted by Eric Peters <Er...@Peters.org>.
Didn't look to change at all - I added a 2nd unit test in for of an actual
"Accounting" field with a $ symbol, and it appears the HSSF formatter is
bugged, it prints the * when it should be $, and it prints the $ when it
should be * (to at least match the XSSF pattern) I tripple checked my test
Excel files and they both look like:
[image: Inline image 2][image: Inline image 1]
Unit Test Output:
(Unit Test Read Value) => (Expected Unit Test Value)
Here's 3.9:
HSSF:
Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79, $4.79))
(TestExcelFlatFileReaderCommon.scala:37)
XSSF:
Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79, $4.79))
(TestExcelFlatFileReaderCommon.scala:37)
Heres's 3.10-beta2
& Just for reference, still on the old ooxml & xerces:
"org.apache.poi" % "poi" % "3.10-beta2", // XLS/XLSX Stream
Reader
"org.apache.poi" % "poi-ooxml" % "3.10-beta2", // XLS/XLSX Stream
Reader
"org.apache.poi" % "poi-scratchpad" % "3.10-beta2", // XLS/XLSX Stream
Reader
"org.apache.poi" % "ooxml-schemas" % "1.1", // XLS/XLSX Stream Reader
"xerces" % "xercesImpl" % "2.11.0", // XLS/XLSX Stream Reader
HSSF:
Vector(Vector($4.79, * 4.79)) did not equal Vector(Vector(4.79, $4.79))
(TestExcelFlatFileReaderCommon.scala:37)
XSSF:
Vector(Vector(* 4.79, $4.79)) did not equal Vector(Vector(4.79, $4.79))
(TestExcelFlatFileReaderCommon.scala:37)
-Eric
On Thu, Oct 31, 2013 at 10:33 AM, Nick Burch <ap...@gagravarr.org> wrote:
> On Thu, 31 Oct 2013, Eric Peters wrote:
>
>> In Excel, you can create one by putting say "4.79" in a cell, then format
>> it with accounting, then go into the options and select "None" for the
>> symbol.
>>
>> My HSSF code prints "$4.79", and my XSSF code prints "* 4.79"
>>
>> I would expect this to print the simple "4.79" Thoughts on howto chase
>> this bug down?
>>
>
> Have you tried with 3.10 beta 2? I think there have been some fixes to
> that logic in the last year or so
>
> Nick
>
> ------------------------------**------------------------------**---------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: "Accounting" Format Option w/no Symbol still outputs a $ (or *
w/XSSF) when read?
Posted by Nick Burch <ap...@gagravarr.org>.
On Thu, 31 Oct 2013, Eric Peters wrote:
> In Excel, you can create one by putting say "4.79" in a cell, then format
> it with accounting, then go into the options and select "None" for the
> symbol.
>
> My HSSF code prints "$4.79", and my XSSF code prints "* 4.79"
>
> I would expect this to print the simple "4.79" Thoughts on howto chase
> this bug down?
Have you tried with 3.10 beta 2? I think there have been some fixes to
that logic in the last year or so
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org