Hi,
I have a Java calculation that mimics an excel sheet and want to
use POI to run and compare test cases. The java code uses BigDecimal
to calculate exact values.
But when I try to read a numerical excel cell using POI the HSSFCell
only supports double as return value. This will loose precision and
has side effects:
Easy to reproduce:
1. create new excel sheet
2. set cell A1 to "1,6"
3. set cell B1 to "=A1/12000", cell type is number with 25 decimal places
(Excel shows "0,0001333333333333330000000", -> internal precision is 18
decimal places)
4. when I read the cell value of HSSFCell#getNumericCellValue() I get
"0,000133333333333334"
5. when I try to compare my value (which is like excel) with the result read
by POI they are different caused by the lost precision using double values
Find a code snippet and output below.
To make things short: how can I get the excel value of a calculated
numeric excel cell using POI as a string to construct a BigDecimal
(without having to modify the excel sheet)
Thx
Torsten
----------------------------------------------------------------------
public class POITest {
public static void main(String[] args) throws Exception {
POITest test = new POITest();
test.run();
}
public void run() throws Exception {
InputStream input = this.getClass().getResourceAsStream("poitest.xls");
POIFSFileSystem fs = new POIFSFileSystem(new
BufferedInputStream(input));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
HSSFSheet sheet = workbook.getSheet("Tabelle1");
double poiValue =
sheet.getRow(0).getCell((short)1).getNumericCellValue();
System.out.println("Value POI : " + Double.toString(poiValue));
BigDecimal myExactValue = new BigDecimal("1.6").divide(new
BigDecimal("12000"),22, BigDecimal.ROUND_HALF_UP);
BigDecimal myValueRoundedLikeExcel =
myExactValue.setScale(18,BigDecimal.ROUND_HALF_UP);
System.out.println("My Value : " +
myValueRoundedLikeExcel.toString());
System.out.println("Excel value: 0,00013333333333333300");
}
}
-----------------------------------------------------------------------
Output is
Value POI : 1.3333333333333334E-4
My Value : 0.000133333333333333
Excel value: 0,00013333333333333300
--
"Feel free" - 5 GB Mailbox, 50 FreeSMS/Monat ...
Jetzt GMX ProMail testen: www.gmx.net/de/go/mailfooter/promail-out
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta POI Project: http://jakarta.apache.org/poi/
