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 <[email protected]> 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 <[email protected]> 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:
>> [email protected].**org<[email protected]>
>> For additional commands, e-mail: [email protected]
>>
>>
>
