import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;

public class Formula { 

    public static void main(String [] args) throws IOException {
        InputStream is = new BufferedInputStream(new FileInputStream("formula.xls"));
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(wb.getFirstVisibleTab());

        // set the value in cell A1 so that the formula in cell B1 should work
        HSSFRow row = sheet.getRow(0);
        HSSFCell cell = row.getCell((int) row.getFirstCellNum());
        cell.setCellValue(2.5);

        convertAllFormulas(sheet, wb);

        OutputStream os = new BufferedOutputStream(new FileOutputStream("value.xls"));
        wb.write(os);
        os.close();
    }

    private static void convertAllFormulas(HSSFSheet sheet, HSSFWorkbook wb) {
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
        for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
            HSSFRow row = (HSSFRow) rit.next();
            for (Iterator cit = row.cellIterator(); cit.hasNext();) {
                HSSFCell cell = (HSSFCell) cit.next();
                if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
//                        cell.setCellType(evaluator.evaluateFormulaCell(cell));
                        evaluator.evaluateInCell(cell);
                    } catch (Exception e) {
                        System.out.println("Error evaluating cell of Excel sheet row=" + row.getRowNum() + " column=" + cell.getColumnIndex() + " cell value=" + cell.toString() + " exception=" + e);
                        e.printStackTrace();
                    }
                }
            }
        }
    }
}