Hi Folks,I've written a little proggy to demonstrate how to get the Raw Cell Value into a BigDecimal.
Please find attached, together with a (new!!) Excel with Testdata. This seems to workaround the problem fairly well. Best regards, DaveLaw On 18/10/2019 04:34, Andreas Reichel wrote:
Dear All, compliments of the day. We face some challenges with reading values from Spread Sheets. Example: the numeric cell has the value = 0.1066913 and when reading that value with POI we receive the double = 0.10669129999999999. This turns into a problem, when writing these figures into a database, when the Precision/Scale of the field can't hold that double value, e.g. JdbcSQLDataException: Value too long for column """BASE_RATE"" VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement: insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?) [22001-199]+ Excel itself does not seem to have a problem, because multiplying the value = 0.1066913 with a large multiplicant 1E15 gives the correct amount: 0.1066913 x 1E15 = 106691300000000 Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and will fit. My question is: How does Excel know the correct value, but POI does not? And would it not be very very usefull to have a method returning cell values as BigDecimals instead of Doubles. At the moment, we work around by advising the users to provide numbers as Text/String. Although that is counter intuitive and always yields in having egg on the face. Thank you already for advise and best regards Andreas
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Arrays;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ApachePoiExcelReadManticoreScaledTest {
private static final String FILE_NAME =
"./ifrsbox_DataCaptureExample.xlsx";
private static final int DECIMALS_07 = 7;
public static void main(final String[] args) {
try(final FileInputStream excelFile = new
FileInputStream(new File(FILE_NAME));
final Workbook workbook = new
XSSFWorkbook(excelFile)
) {
final Sheet datatypeSheet =
workbook.getSheetAt(0);
System.out.print("Sheet.: " +
datatypeSheet.getSheetName());
datatypeSheet .forEach(currentRow -> {
if (currentRow.getRowNum() <= 2) {
return;
}
currentRow.forEach(currentCell -> {
final XSSFCell xssfCell = (XSSFCell)
currentCell;
final CellType cellType =
xssfCell.getCellType();
final String rawValue =
xssfCell.getRawValue();
if (xssfCell.getColumnIndex() != 5) {
return;
}
System.out.print("type=" +
rPad(cellType.toString(), 9));
/*
* TODO Warning: use rawValue, NOT
cellValue.
*
* TODO Warning: use new
BigDecimal(String) constructor.
* ............: not new
BigDecimal(double) (see Javadoc)
*
* TODO Learn how to use BigDecimal
Precision & Scale!!!!
*/
switch (cellType) {
case NUMERIC : final String
cellValue = Double.valueOf(xssfCell.getNumericCellValue()).toString();
/**/ final BigDecimal
rawBig = new BigDecimal(rawValue).setScale(DECIMALS_07,
RoundingMode.HALF_EVEN);
/**/ final String
rawString = rawBig.toString();
/**/
System.out.print("poiValue=" + rPad(cellValue, 25));
/**/
System.out.print("rawValue=" + rPad(rawValue, 25));
/**/ if
(cellValue.equals(rawString)) {
/**/ break;
/**/ }
/**/
System.out.print("rawBig=" + rPad(rawString, 25));
/**/
System.out.print("scale=" + rPad(String.valueOf(rawBig.scale()), 8));
/**/
System.out.print("precision=" + rPad(String.valueOf(rawBig.precision()), 8));
/**/ break;
case FORMULA :
System.out.print("formula=" + rPad(rawValue, 25));
/**/ break;
default : throw new
NullPointerException("Exception of your choice...");
}
});
System.out.println();
});
} catch (final IOException e) {
e.printStackTrace();
}
}
public static final String rPad(final CharSequence value, final int
padLength) {
final char[] padChars = new char[Math.max(0, padLength -
value.length())];
Arrays.fill (padChars, ' ');
return value.toString().concat(String.valueOf(padChars));
}
}
ifrsbox_DataCaptureExample.xlsx
Description: MS-Excel 2007 spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
