How to set formulas in cells using Apache POI?

java apache-poi
vamsi picture vamsi · Feb 26, 2010 · Viewed 84.8k times · Source

I am currently using Apache POI for Java to set formulas in cells.

But after I run the program and open the Excel file that I created and processed, the cells with the formula include the formula as a string, rather than the value the formula should have returned.

Answer

user257111 picture user257111 · Feb 26, 2010

The HSSFCell object has methods .setCellType and .setCellFormula which you need to call like this:

// "cell" object previously created or looked up
String strFormula= "SUM(A1:A10)";
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(strFormula);

Related questions

How can I read numeric strings in Excel cells as string (not numbers)?

I have excel file with such contents: A1: SomeString A2: 2 All fields are set to String format. When I read the file in java using POI, it tells that A2 is in numeric cell format. The problem is that the …

Read More
Apache POI Excel - how to configure columns to be expanded?

I am using Apache POI API to generate excel spreadsheet to output some data. The problem I am facing is when the spreadsheet is created and opened, columns are not expanded so that some long text like Date formatted text …

Read More
How do I set cell value to Date and apply default Excel date format?

I've been using Apache POI for some time to read existing Excel 2003 files programmatically. Now I have a new requirement to create entire .xls files in-memory (still using Apache POI) and then write them to a file at the end. …

Read More