Created attachment 30989 [details] Strip *<character> from Format String From: http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx "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." This is a hack, but all of the tests currently pass with it (probably bad tests), it also fixes the issue I'm running into.
Here's some additional debug information: Basically, DataFormatter.cleanFormatForNumber("$"* #,##0.00_);_("$"* (#,##0.00)) => $#,##0.00;$(#,##0.00) strips the *<space> But DataFormatter.cleanFormatForNumber((_(* #,##0.00_);_(* (#,##0.00)) => * #,##0.00;* (#,##0.00) Doesn't & java.text.DecimalFormat will just reprint the *<space> out: scala> val format = new java.text.DecimalFormat("* #,##0.00;* (#,##0.00)") format: java.text.DecimalFormat = java.text.DecimalFormat@67a12 scala> format.format(4.79d) res2: String = * 4.79 A psuedo stack trace/debug statements: 10:51:36.477 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - Enter DataFormatter.getFormat(4.79, 43, _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)) 10:51:36.477 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - DataFormatter.getFormat(4.79, +43, _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)), Target formatStr: _(* #,##0.00_);_(* (#,##0.00) 10:51:36.477 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - Enter DataFormatter.createFormat(4.79, +43, _(* #,##0.00_);_(* (#,##0.00)) 10:51:36.477 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - Enter DataFormatter.createNumberFormat(_(* #,##0.00_);_(* (#,##0.00), +4.79) 10:51:36.477 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - DataFormatter.createNumberFormat(_(* #,##0.00_);_(* (#,##0.00), +4.79): cleanFormatForNumber(formatStr) => * #,##0.00;* (#,##0.00) 10:51:36.482 [main] TRACE fm.flatfile.excel.XLSStreamProcessor - ok numrec: [NUMBER] .row = 0x0000 .col = 0x0000 .xfindex= 0x003E .value= 4.79 [/NUMBER] and formatListener.getFormatIndex(numrec): 43, formatListener.getFormatString(43): _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(43): _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_), formatListener.formatNumberDateCell(numrec): * 4.79 10:51:36.483 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - Enter DataFormatter.getFormat(4.79, 43, _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)) 10:51:36.483 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - DataFormatter.getFormat(4.79, +43, _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)), Target formatStr: _(* #,##0.00_);_(* (#,##0.00) 10:51:36.484 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - Enter DataFormatter.getFormat(5.79, 44, _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)) 10:51:36.484 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - DataFormatter.getFormat(5.79, +44, _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)), Target formatStr: _("$"* #,##0.00_);_("$"* (#,##0.00) 10:51:36.484 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - Enter DataFormatter.createFormat(5.79, +44, _("$"* #,##0.00_);_("$"* (#,##0.00)) 10:51:36.484 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - Enter DataFormatter.createNumberFormat(_("$"* #,##0.00_);_("$"* (#,##0.00), +5.79) 10:51:36.485 [main] DEBUG o.a.poi.ss.usermodel.DataFormatter - DataFormatter.createNumberFormat(_("$"* #,##0.00_);_("$"* (#,##0.00), +5.79): cleanFormatForNumber(formatStr) => $#,##0.00;$(#,##0.00) 10:51:36.485 [main] TRACE fm.flatfile.excel.XLSStreamProcessor - ok numrec: [NUMBER] .row = 0x0000 .col = 0x0001 .xfindex= 0x003F .value= 5.79 [/NUMBER] and formatListener.getFormatIndex(numrec): 44, formatListener.getFormatString(44): _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_), HSSFDataFormat.getBuiltinFormat(44): _("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_), formatListener.formatNumberDateCell(numrec): $5.79 scala> val format = new java.text.DecimalFormat("* #,##0.00;* (#,##0.00)") format: java.text.DecimalFormat = java.text.DecimalFormat@67a12 scala> format.format(4.79d) res2: String = * 4.79 scala> scala> val format = new java.text.DecimalFormat("$#,##0.00;$(#,##0.00") format: java.text.DecimalFormat = java.text.DecimalFormat@67500 scala> format.format(5.79d) res3: String = $5.79 scala>
Created attachment 30991 [details] Fix StringBuilder logic in DataFormatter.cleanFormatForNumber
Created attachment 30992 [details] Fix StringBuilder logic in DataFormatter.cleanFormatForNumber Removed the logger for simplicity, and the sb.length() tracking I was trying to do (it gets evaluated already for each for statement)
Created attachment 31149 [details] Fix StringBuilder logic in DataFormatter.cleanFormatForNumber Same patch, but this time marked as "patch" attachment so its easier to read/view changes in bugzilla
Thanks for the patch, applied in r1553248 Yegor