Andrew, Your suggestion worked! After opening and resaving the excel doc manually I was able to read the calculated data.
However, my application is required to use a specific excel spreadsheet in the background as a calculator. I can't get around that. In addition, it is never accessible to the user who will enter data via a browser. So this resaving step will have to be done with my application. Is there a way to do this programmatically? Thanks again for your assistance. Richard -----Original Message----- From: Andrew Kharchuk [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 8:21 AM To: POI Users List Subject: Re: Rookie formula problem! You are to open file and save it using Excel. Otherwise you have to implement calculations yourself. Best regards, Andrew ----- Original Message ----- From: "Elsberry, Richard C - Eagan, MN - Contractor" <[EMAIL PROTECTED]> To: "POI Users List" <[EMAIL PROTECTED]> Sent: Wednesday, December 22, 2004 4:10 PM Subject: RE: Rookie formula problem! Andrew, Any suggestions as to how I can read a cell after calculation. I've been testing for several days now and consistently get the same result. I can open the spreadsheet manually and see the calculation but reading that same cell in hssf I see the pre-calculation value. Thanks for your response, Richard -----Original Message----- From: Andrew Kharchuk [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 12:44 AM To: POI Users List Subject: Re: Rookie formula problem! Hi, POI doesn't calculate formula values. Excel does it. So all is right. Best regards, Andrew ----- Original Message ----- From: "Elsberry, Richard C - Eagan, MN - Contractor" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, December 21, 2004 11:55 PM Subject: Rookie formula problem! Hello, I've searched the archives and have come close but still need some assistance. I am working on a project that requires the use of an excel spreadsheet that contains formulas with some rather longstanding and complex business rules. I need to use this spreadsheet as a template that accepts user input to perform calculations to other areas of the spreadsheet. I need to then display the outcome from those other areas back to the user. I've created a simple template to test. A worksheet with three rows. Row three is the sum of rows one and two. (1+1=2) I've attached my code below. I first print out the values, then add a numeric value 5 to row two and save the result to a new .xls file. If I open the newly created spreadsheet manually all looks fine, row 3 equals 6 (1+5=6). However, as my output below indicates, the new .xls document always reflects the original .xls document's value of 2 (1+1=2) I can't read any calculated cells. In addition when I run this in my actual template I get this error: [WARNING] Unknown Ptg 18 (24) at cell (66,4) thanks in advance for any pointers and happy holidays..... public void updateSpreadSheet(String file, String newFile) { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); FileOutputStream stream = new FileOutputStream(newFile); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); HSSFCell cell = row.getCell(( short ) 0); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(5); wb.write(stream); stream.close(); System.err.println("update done"); } catch(IOException ioe) { System.out.println("ExcelDAO: error loading spreadsheet"); } } public void readSpreadSheet(String file, boolean b) { try { double value1 = 0.1; double value2 = 0.1; double value3 = 0.1; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row1 = sheet.getRow(0); HSSFRow row2 = sheet.getRow(1); HSSFRow row3 = sheet.getRow(2); HSSFCell cell1 = row1.getCell(( short ) 0); value1 = cell1.getNumericCellValue(); System.err.println("row1 = "+value1); HSSFCell cell2 = row2.getCell(( short ) 0); value2 = cell2.getNumericCellValue(); System.err.println("row2 = "+value2); HSSFCell cell3 = row3.getCell(( short ) 0); value3 = cell3.getNumericCellValue(); System.err.println("row3 = "+value3); } catch(IOException ioe) { System.out.println("ExcelDAO: error loading spreadsheet"); } } LOG OUTPUT BEFORE UPDATE (row3 is the sum of rows 1 and 2) row1 = 1.0 row2 = 1.0 row3 = 2.0 update done LOG OUTPUT AFTER UPDATE (row3 is the sum of rows 1 and 2) row1 = 1.0 row2 = 5.0 row3 = 2.0 --------------------------------------------------------------------- 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] --------------------------------------------------------------------- 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]
