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