I using Apache POI for Java to set formulas in Excel cells. I'm using the
following code to make the formulas appear as formulas rather than as Strings:
// "cell" object previously created or looked upString StrFormula =
"SUM(\"A1:A10\")";cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);cell.setCellFormula(strFormula);The
setCellType(HSSFCell.CELL_TYPE_FORMULA) makes the trick for most formulas but
not all. There are some functions (introduced in Excel 2007) like SUMIFS,
COUNTIFS. IFERROR, AVERAGEIFS that cause the cell to contain a String. It is
necessary to click+enter every such cell in Excel for the formula to get
recognized. Is there any trick to make those functions work properly?
PS. Why don't you make the setCellFormula() implicitly call the
setCellType(HSSFCell.CELL_TYPE_FORMULA)? This cost me some time to figure out
this was need and made bad impression.
Kind regards,
Jacek
