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 <[email protected]> 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 <[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]
>>>
>>>
>>
>
