Generating a new Excel while retaining old formula from template Excel file

2
votes

I have an Excel template with first 5 columns left blank (to be populated from an XML). The sixth column has a drop-down list such that depending on whatever value is selected from the list, a value appears in 7th column. This is done by using INDEX-MATCH formula for the 7th column. 

=INDEX(Sheet7!$B$1:$B$312,MATCH(F3,Sheet7!$A$1:$A$312,0))

The task is to take this template Excel, and using POI, populate the first 5 columns and generate a new Excel workbook. (Not changing in the existing sheet).

The problem is when I generate the new Excel workbook, the formula isn't copied over from the template. It is really important to retain the formula for the task I am working on. I read about

formulaEvaluator()

but I don't think it'll work in that case because I need the formula to be retained as it is. I cannot evaluate it before copying as there won't be any data in column 6 at that point to evaluate the formula and store the value.

I'd really appreciate if someone who has any experience in this area could guide me a bit.

1
javaexcelapache-poiexcel-formula

1 Answers

2
votes

If you have access to the Cell, simply call:

String formula = cell.getCellFormula();

Note though that this will only work if the cell is formula cell. IE: cell.getCellType() == Cell.CELL_TYPE_FORMULA. If the cell is not a formula cell, you're going to end up with an IllegalStateException as described in the API