poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brian Nesbitt <br...@nesbot.com>
Subject Re: "Accounting" Format Option w/no Symbol still outputs a $ (or * w/XSSF) when read?
Date Fri, 01 Nov 2013 02:26:07 GMT
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 <Eric@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 <Eric@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 <Eric@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
>>>>
>>>>
>>>
>>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message