XlsTable creates BigDecimals using BigDecimal(double).
This will create a BigDecimal with an unexpected scale
(see
http://java.sun.com/j2se/1.4.2/docs/api/java/math/BigDecimal.html#BigDecimal\(double))
It should use the BigDecimal(String) constructor instead.
The fix (against version 2.1) is to change
XlsTable.java line 111 from
return new BigDecimal(ell.getNumericCellValue());
to
return new
BigDecimal(String.valueOf(cell.getNumericCellValue()));
Logged In: YES
user_id=716212
To fully fix this, you should use:
double val = cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(val);
{
bd = new BigDecimal(String.valueOf(val));
}
return bd;
This will prevent the bugfix from incorrectly giving decimal
places where none are needed (i.e.: number(1) columns).
Logged In: YES
user_id=716212
Also, for a quick and dirty hack to include decimal formatting
replace the switch with
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA:
return getNumericAsString(cell);
and add the following method:
public static final Object getNumericAsString(final
HSSFCell cell)
{
if (HSSFDateUtil.isCellDateFormatted(cell))
{
return cell.getDateCellValue();
}
String format = HSSFDataFormat.getBuiltinFormat
(cell.getCellStyle().getDataFormat());
double val = cell.getNumericCellValue();
if((format != null) && (format != "General"))
{
DecimalFormat nf = new DecimalFormat(format);
String s = nf.format(val);
if(s.indexOf(".") >= 0)
{
while(s.endsWith("0"))
{
s = s.substring(0, s.lastIndexOf("0"));
}
}
if(s.endsWith("."))
{
return s.substring(0, s.length() - 1);
}
return s;
}
BigDecimal bd = new BigDecimal(val);
{
bd = new BigDecimal(String.valueOf(val));
}
return String.valueOf(bd);
}
Logged In: YES
user_id=716212
I've since further enhanced the method posted... This
contains more formula support, as well as better date
support. This has not been 100% tested however, as I've only
run about 5 4-6mb files through it.
Logged In: YES
user_id=716212
oops, forgot the method.
Logged In: YES
user_id=716212
public static final Object getNumericAsString(final
HSSFCell cell)
{
short cellFormat = cell.getCellStyle().getDataFormat();
if((HSSFDateUtil.isCellDateFormatted(cell)) ||
((cellFormat >= 169) && (cellFormat <= 176)))
{
return cell.getDateCellValue();
}
else
{
System.out.println("Notta date: " + cellFormat);
}
String format = null;
try
{
format = HSSFDataFormat.getBuiltinFormat
(cell.getCellStyle().getDataFormat());
if((format != null) && ("@".equals(format)))
{
System.out.println(String.valueOf
(cell.getNumericCellValue()));
return String.valueOf(cell.getNumericCellValue());
}
}
catch(ArrayIndexOutOfBoundsException e)
{
; // not a big deal, return the normal number
}
double val = cell.getNumericCellValue();
if((format != null) && (format != "General"))
{
DecimalFormat nf = new DecimalFormat(format);
String s = nf.format(val);
if(s.indexOf(".") >= 0)
{
while(s.endsWith("0"))
{
s = s.substring(0, s.lastIndexOf("0"));
}
}
if(s.endsWith("."))
{
return s.substring(0, s.length() - 1);
}
return s;
}
BigDecimal bd = new BigDecimal(val);
{
bd = new BigDecimal(String.valueOf(val));
}
return String.valueOf(bd);
}
Logged In: YES
user_id=57511
Originator: NO
Can you please attach a patch for the latest development trunk?
I committed the change for the next release (rev. 822 trunk).
Thanks and regards,
mat