Cell formula and value

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!