Updated excel file still returns old values

My java code is reading an excel file by using jxl library. It first copies the original excel file to create temp.xls. While creating that file it changes some values, and it successfully creates the temp.xls with the formula.

Original file:

A   1       Y   3 (has formula: A+B)
B   2

Copied File:

A   5       Y   13 (has formula: A+B)
B   8

However, even the copied file changes, the code still gives "3" as an output instead of "13". How can I make it give the correct output?

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;

public class Reader {

    public static void excelWriting() {
         try {
         Workbook workbook = Workbook.getWorkbook(new File(
         "D:\\parser\\calc.xls"));
         WritableWorkbook copy = Workbook.createWorkbook(new File(
         "D:\\parser\\temp.xls"), workbook);

         WritableSheet tempSheet = copy.getSheet(0);

         Number num1 = new Number(1, 2, 5);
         Number num2 = new Number(1, 3, 8);

         tempSheet.addCell(num1);
         tempSheet.addCell(num2);

         copy.write();
         copy.setProtected(false);
         copy.close();
         workbook.close();

         } catch (BiffException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         } catch (IOException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         } catch (RowsExceededException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         } catch (WriteException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
         }

    }

    public static void main(String[] args) {

        excelWriting();
        int value = 0;

        try {   
                Workbook wrk1 = Workbook.getWorkbook(new File(
                    "D:\\parser\\temp.xls"));

            // Obtain the reference to the first sheet in the workbook
            Sheet sheet1 = wrk1.getSheet(0);
            Cell cell = sheet1.getCell(4, 2);
            value = Integer.parseInt(cell.getContents());

        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println(value);

    }
}

in excelWriting() method num1 indicates "A" which equals to 1
in excelWriting() method num2 indicates "B" which equals to 2
in main() method sheet1.getCell(4, 2) is "Y" which equals to 3

Solution

I don't know the jxl library very well, and I couldn't get your example running (what import should I use for Number?), but:

The Excel file formats not only stores formulas, but also the calculated results in the file. So when you change the values A and B, you somehow need to re-evaluate the formulas in order for the new result to be stored. Most likely, the formula is not re-evaluated and still returns the old value.

Looking at the jxl API, there is a command setRecalculateFormulasBeforeSave() that might be of help when used at the right place (likely before your copy.write():

tempSheet.getSettings().setRecalculateFormulasBeforeSave(true);

Sadly, I couldn't try it out myself as I wasn't able to get your example running, but it might be worth a try.

UPDATE: okay, finally got your example working. My approach above does not help. My interpretation: As mentioned, the Excel file format does store formula results in the file. Changing cell values need re-evaluation of formulas. This re-evaluation/recalculation is only done by Excel itself, but not by JXL. Thinking a bit more about this, this makes sense: If JXL would need to recalculate the values, it would have to support all the formulas supported by Excel itself, including all the complex statistical and financial formulas, which is likely just too much. So all JXL can do is change the values of cells, but the actual calculation it cannot do. So, except opening the created file in Excel and re-saving it, there seems to be no other way to update the formula result.