Mailing-List: contact dev-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Developers List" <dev@poi.apache.org>
Received-SPF: pass (athena.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <28141315.post@talk.nabble.com>
Date: Mon, 5 Apr 2010 08:43:38 -0700 (PDT)
From: akothari <ar.kothari@gmail.com>
To: dev@poi.apache.org
Subject: POI 3.6 issue while calculating formulae
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org