Skip to Main Content

Java Programming

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

JExcel: Cannot get the re-calculated value from formula cell in Excel

807589Jul 30 2008
I use JExcel 2.6.8 to update a cell value in Excel and then use that cell value to calculate a number in a formula. I want to get out the calculated value in Java; however, even I have successfully changed the cell value, the calculated number that I get out from Java has not been updated yet. Here is the code that I did:

public static String file = "C:\\Sha Tin.xls"; // Original file
public static String file1 = "C:\\Sha Tin1.xls"; // program-generated file

public static void main(String[] args) throws IOException{

try{
// Write Data
Workbook workbook = Workbook.getWorkbook(new File(file));
WritableWorkbook copy = Workbook.createWorkbook(new File(file1), workbook);
WritableSheet sheet = copy.getSheet("Data Entry");
WritableCell cell = sheet.getWritableCell(4, 35); // Write E36

if (cell.getType() == CellType.NUMBER)
{
Number l = (Number) cell;
l.setValue(value);
}
copy.write();
copy.close();

// Read Data
Workbook workbook1 = Workbook.getWorkbook(new File(file1));
Sheet sheet1 = workbook1.getSheet("Data Entry");
Cell a1 = sheet1.getCell(4,36); // Get E37
double number1 = 0.0;
if (a1.getType() == CellType.NUMBER_FORMULA)
{
NumberFormulaCell nc = (NumberFormulaCell) a1;
number1 = nc.getValue();
}
System.out.println(number1);
workbook1.close();
}
catch(Exception e){
e.printStackTrace();
}

Cell E37 contains a formula which is E35-E36. However, even i have changed the value of E36, the value of E37 that I get from the program has not been re-calculated. The only way that I can get the re-calculated E37 value is: when i open the program-generated excel file (Sha Tin1.xls) in Excel 2003 program, the program asks me to save the changes to the file and show a message: "Microsoft Office Excel recalculates formulas when opening files last saved by an earlier version of Excel." After i have saved the changes the Excel program requests, i can get the re-calculated E37 value from the above Java program. But i dont want to open and save the excel file manually each time i make change to the excel file, how can i achieve that? Please help. Thank you
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 27 2008
Added on Jul 30 2008
0 comments
383 views