Hi,

I could not find how to read a cells formula and value (if even possible).

My client does not only want to know what formula a cell has but also what value it has.

Is that possible?

Thanks,

Marcel

Hi Marcel,

Well, you need to call Workbook.calculateFormula() first and then you may use Cell.getValue() to get the calculated value. Also, you may use Cell.getFormula() method to get the formula a cell has.

See an example below:

Sample code:

//Instantiating a Workbook object

Workbook book =new Workbook(filepath);

//Obtaining the reference of the first worksheet

Worksheet worksheet = book.getWorksheets().get(0);

Cells cells = worksheet.getCells();

Cell cell = null;

//Adding a value to “A1” cell

cell = cells.get(“A1”);

cell.setValue(1);

//Adding a value to “A2” cell

cell = cells.get(“A2”);

cell.setValue(2);

//Adding a value to “A3” cell

cell = cells.get(“A3”);

cell.setValue(3);

//Adding a SUM formula to “A4” cell

cell = cells.get(“A4”);

cell.setFormula("=SUM(A1:A3)");

//Calculating the results of formulas

book.calculateFormula();

//get the calculated value of A4

Object cval = cell.getValue()

Brilliant thank you for your fast reply!