http://old.nabble.com/file/p28141315/1.xls 1.xls
I have a simple excel-2003 file. I write to one cell and then read another
formula cell which is based on the previously written cell. I am attaching
the file. In my program, I write to cell A1 and then read the value of the
cell D1.
Below are few lines of my code (I am using POI 3.6):
try{
//WRITE TO THE EXCEL
File file = new File("c:\\1.xls");
Workbook w = WorkbookFactory.create(new FileInputStream(file));
Sheet sheet = w.getSheetAt(0);
Cell cell = sheet.getRow(0).getCell(0);
cell.setCellValue(Double.parseDouble("310"));
//EVALUATE THE FORMULA
FormulaEvaluator evaluator =
w.getCreationHelper().createFormulaEvaluator();
for(int sheetNum = 0; sheetNum < w.getNumberOfSheets(); sheetNum++) {
Sheet s = w.getSheetAt(sheetNum);
for(Row r : s) {
for(Cell c : r) {
if(c.getCellType() ==
Cell.CELL_TYPE_FORMULA) {
CellValue cv = evaluator.evaluate(c);
System.out.println(">
"+cv.getNumberValue());
evaluator.evaluateFormulaCell(c);
System.out.println(">
"+c.getNumericCellValue());
}
}
}
}
OutputStream fileOut = new FileOutputStream(file.getAbsolutePath());
w.write(fileOut);
fileOut.close();
//READ THE EXCEL
Workbook workbook = WorkbookFactory.create(new FileInputStream( new
File("c:\\1.xls")));
Sheet sheet1 = workbook.getSheetAt(0);
Cell readCell = sheet1.getRow(3).getCell(0);
switch (readCell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(readCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(readCell)) {
SimpleDateFormat sd=new
SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
System.out.println(readCell.getDateCellValue().toString());
System.out.println(sd.format(readCell.getDateCellValue()));
} else {
System.out.println(readCell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
System.out.println(readCell.getStringCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
FormulaEvaluator evaluator1 =
workbook.getCreationHelper().createFormulaEvaluator();
CellValue cellEvaluated = evaluator1.evaluate(readCell);
System.out.println(cellEvaluated.getNumberValue());
System.out.println(readCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
break;
}
}catch(Exception e){
System.out.println(e);
}
After the program execution, the file has updated A1 contents, but not of
D1. D1 has a financial NPV formula/
Any help is appreciated. Thanks in advance
--
View this message in context:
http://old.nabble.com/POI-3.6-issue-while-calculating-formulae-tp28141315p28141315.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
