CODEWITHSUNDEEP
excelapache-poiexcel-formula
Dharmen Panchal
Dharmen Panchal

Reputation: 53

How to remove a formula from an Excel sheet using the POI api?

Using cell.setCellFormula(null) method we can remove formula but it affects the cells which are having values depended on this cell. I want to remove all the formulas from the whole sheet without changing the values of any cell. Please suggest me some idea...

Upvotes: 1

Views: 5742

Answers (1)

Gagravarr
Gagravarr

Reputation: 48326

Your question is a bit ambiguous, but I'm going to take a guess at what you're after and answer what I think you meant...

Assuming an excel file that looks something like:

|   |      A      |     B   |    C    |
| 1 |      2      |     4   |    6    |
| 2 | =SUM(A1:C1) | =A2*3   | =A2*4   |

I believe what you're after is to remove the formula from A2, but leave in the currently calculated value (12) so that formulas B2 and C2 remain unaffected?

There are two ways to do this. If you have just loaded up the file, and not made any changes to it, then it's very easy. All you need to do is change the cell type from being a Formula cell to a Numeric cell. It'll keep the old calculated value.

sheet.getRow(1).getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC);

If you've made other changes to the file aready, then what you need to do is evaluate the cell, then replace the contents with the evaluated value. You can do this with the formula parser, using the evaluteInCell method.

Upvotes: 2

Related Questions