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