Firstly, you do not need to call the setForceFormulaRecalculation() method when you read a workbook; you only need to call this method when you are writing/creating a workbook using HSSF that the user may then open with Excel. Calling that method when you write the workbook will ensure that the user sees the correct values when they open the workbook.
You are quite correct to use the HSSFFormulaEvaluator class to calculate the values that should be contained within those workbook cells that contain formulae. To help illustrate how you could use the class, I have put together a bit of code - in the attached file - that opens a workbook, gets the first sheet and then steps through each cell in turn. The code tests the cells type and, if it contains a formula, evaluates the formula and obtains the result. You will need to re-factor this code to use it in a production environment but it should function as you require. --- On Wed, 7/9/08, Harold.Zhang <[EMAIL PROTECTED]> wrote: From: Harold.Zhang <[EMAIL PROTECTED]> Subject: Re: Only after open the .xls file and save it by manually,I can read the updated data. To: [email protected] Date: Wednesday, July 9, 2008, 1:10 AM part of my code: private void readformual2() throws FileNotFoundException, IOException { HSSFWorkbook workbook = new HSSFWorkbook( new FileInputStream(outputfile)); HSSFSheet sheet = workbook.getSheetAt(0); sheet.setForceFormulaRecalculation(true); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook); for (int i = 0; i < sheet.getLastRowNum(); i++) { if (sheet.getRow(i) != null) { HSSFRow row = sheet.getRow(i); evaluator.setCurrentRow(row); for (short j = 0; j < row.getLastCellNum(); j++) { if (row.getCell(j) != null) { HSSFCell cell = row.getCell(j); int cellType = cell.getCellType(); switch(cellType) { case 0: String strcell=String.valueOf(cell.getNumericCellValue()); System.out.println("strcell_0:"+strcell); break; case 1: strcell=cell.getStringCellValue(); System.out.println("strcell_1:"+strcell); break; case 2: // // strcell=String.valueOf(evaluator.evaluateFormulaCell(cell)); // System.out.println("strcell3:"+strcell); strcell=cell.getCellFormula(); Double numericCellvalue=cell.getNumericCellValue(); if(numericCellvalue.isNaN()) System.out.println("cell.getRichStringCellValue().toString():"+cell.getRichStringCellValue().toString()); else System.out.println("numericCellvalue:"+numericCellvalue); System.out.println("strcell_2:"+strcell); // evaluator.evaluateInCell(cell); break; default: strcell=cell.getStringCellValue(); System.out.println("strcell_default:"+strcell+"---读取其他格式数据,c"+i+","+j); } } } } } } Harold.Zhang wrote: > > hi,I added sheet.setForceFormulaRecalculation(true); > this time when I open the "001.xls" bu manully,I can see b8=300,but if I > close the "001.xls" without save, > I read the "001.xls" with java code the b8 is still equal to 30;and if I > close the "001.xls" with save,b8 will be 300. > > Anthony Andrews wrote: >> >> Excel has it's own rules that determine when and where it re-calculates >> formulae. Have a look at the setForceFormulaRecalculation(boolean value) >> >> of the HSSFSheet class. By calling that method and passing the boolean >> value 'true', you may get the results you require. >> >> --- On Tue, 7/8/08, Harold.Zhang <[EMAIL PROTECTED]> wrote: >> From: Harold.Zhang <[EMAIL PROTECTED]> >> Subject: Only after open the .xls file and save it by manually,I can read >> the updated data. >> To: [email protected] >> Date: Tuesday, July 8, 2008, 11:47 PM >> >> Hi,all. >> There is one file "001.xls",I write two datas into the file >> "001.xls" such >> as a1=10---->100,a2=20---->200. >> b8=SUM(a1,a2),then I read the "001.xls",but the value of b8 is still >> equal >> to 30,not equal to 300.But after I open the "001.xls" and close the >> "001.xls" with save option,then I can read b8 which value is 300. >> Is there any method to avoid open and save the "001.xls" by >> manually,and >> just direct read the "001.xls" which b8's value is 300. >> please help. >> -- >> View this message in context: >> http://www.nabble.com/Only-after-open-the-.xls-file-and-save-it-by-manually%2CI-can-read-the-updated-data.-tp18355465p18355465.html >> Sent from the POI - User mailing list archive at Nabble.com. >> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [EMAIL PROTECTED] >> For additional commands, e-mail: [EMAIL PROTECTED] >> >> >> >> > > -- View this message in context: http://www.nabble.com/Only-after-open-the-.xls-file-and-save-it-by-manually%2CI-can-read-the-updated-data.-tp18355465p18356502.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
