Apache POI's FormulaEvaluator doesn't work after modifying a Cell's formula

I am using Apache POI to write to a spreadsheet some test case expectations...

These expectations, as of right now, are all in the form of string cell values. They contain some expected dollar amounts...

Here's example row:

I make sure that the dollar amounts have the Accounting Number Format.

I did notice a need to have Excel calculate an Expected Net Amount (one cell minus the sum of some others), instead of my code, by entering in a formula like:

=I34-(J34+K34)

This works in the spreadsheet: after hitting enter, the value is calculated. However, when I try to read it programmatically, I face some issue:

The util method:

	public static double GetDollarAmount(Cell cell) { 
		switch (cell.getCellType()) { 
			case CellType.NUMERIC.code:
				return cell.getNumericCellValue();
			case CellType.STRING.code:
				return SMDNumberUtils.parseDouble(cell.getStringCellValue());
			case CellType.FORMULA.code:
				return cell.getSheet().getWorkbook()
					.getCreationHelper()
					.createFormulaEvaluator()
					.evaluate(cell)
					.getNumberValue();
					
			default: 
				throw new IllegalStateException("Cell Type ${cell.getCellTypeEnum().name} not yet supported!")
		}
	}

fails for the formula-type cell, because it returns 0 instead of, in the case of that example row, 1919.09 . When I check the CellValue that the evaluate() call returns, I see a CellValue with #VALUE! (which typically gets thrown when you're tryna use a numeric formula on string values)

Even when I try to tweak my code to re-write the formula, by wrapping "VALUE()" around any cell references:

	public static double GetDollarAmount(Cell cell) { 
		switch (cell.getCellType()) { 
			case CellType.NUMERIC.code:
				return cell.getNumericCellValue();
			case CellType.STRING.code:
				return SMDNumberUtils.parseDouble(cell.getStringCellValue());
			case CellType.FORMULA.code:
				
				FormulaEvaluator evaluator = cell.getSheet().getWorkbook()
					.getCreationHelper()
					.createFormulaEvaluator();
			
				CellValue cellValue = evaluator.evaluate(cell);
					
				if (!cellValue.getCellTypeEnum().equals(CellType.ERROR))
					return cellValue.getNumberValue();
					
				cell.setCellFormula(cell.getCellFormula()
					.replaceAll(/[A-Z]+\d+/, { match ->
					    return "VALUE($match)"
					}))
				
				return evaluator.evaluate(cell)
					.getNumberValue();
					
			default: 
				throw new IllegalStateException("Cell Type ${cell.getCellTypeEnum().name} not yet supported!")
		}
	}

It still fails, with the same issue! Nothing changes, except we have a safe formula for next run!

How can I fix this, given that my testing framework, Katalon Studio, has me stuck on Apache POI v3.17 ?

I was able to fix this problem, with some tweaking of the code:

public static double GetDollarAmount(Cell cell) { 
	switch (cell.getCellType()) { 
		case CellType.NUMERIC.code:
			return cell.getNumericCellValue();
		case CellType.STRING.code:
			return SMDNumberUtils.parseDouble(cell.getStringCellValue());
		case CellType.FORMULA.code:
			CellValue cellValue = this.EvaluateCellFormula(cell);
				
			if (!cellValue.getCellTypeEnum().equals(CellType.ERROR))
				return cellValue.getNumberValue();
				
			cell.setCellFormula(cell.getCellFormula()
				.replaceAll(/[A-Z]+\d+/, { match ->
				    return "VALUE($match)"
				}))
			
			return this.EvaluateCellFormula(cell)
				.getNumberValue();
				
		default: 
			throw new IllegalStateException("Cell Type ${cell.getCellTypeEnum().name} not yet supported!")
	}
}

public static CellValue EvaluateCellFormula(Cell cell) { 
	return cell.getSheet().getWorkbook()
			.getCreationHelper()
			.createFormulaEvaluator()
			.evaluate(cell)

It looks like we need new FormulaEvaluator every time we evaluate()...