#86 BigDecimal values read by XlsDataSet have wrong precision

[Srinivasan Ranganthan]

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()));

[seraph1m]

Logged In: YES
user_id=716212

To fully fix this, you should use:

double val = cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(val);

   if\(bd.scale\(\) > 0\)

{
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).

[seraph1m]

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);

   if\(bd.scale\(\) > 0\)

{
bd = new BigDecimal(String.valueOf(val));
}

return String.valueOf(bd);
}

[seraph1m]

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.

[seraph1m]

Logged In: YES
user_id=716212

oops, forgot the method.

[seraph1m]

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);

   if\(bd.scale\(\) > 0\)

{
bd = new BigDecimal(String.valueOf(val));
}

return String.valueOf(bd);
}

[Roberto Lo Giacco]

Logged In: YES
user_id=57511
Originator: NO

Can you please attach a patch for the latest development trunk?

[matthias g]

I committed the change for the next release (rev. 822 trunk).

Thanks and regards,
mat