Workaround for using Apache POI to handle Excel formulas that are not updated

When you use a POI to update Excel, if cell a sets a formula, and when the other cells it depends on are populated with values, a in the exported Excel is still a formula instead of an auto-calculated value.

Paste_image.png

The score subtotal is not updated to the calculated results.

Workaround

This interface is provided in the POI to org.apache.poi.ss.usermodel.FormulaEvaluator enable updates to the formula. Where HSSFFormulaEvaluator XSSFFormulaEvaluator this interface is implemented.

Implementation ideas

We can iterate through all the cells in a row of cells, and if they are of type Cell.CELL_TYPE_FORMULA , prove that the cell is a formula that you can use to evaluator.evaluateFormulaCell(cell); update it. The sample code is as follows:

PrivateStaticvoid updateformula (Workbook WB, Sheet s,int row) {row r=s.getrow (row); Cell c=null; Formulaecaluator eval=null; if (WB instanceof Hssfworkbook) eval= new hssfformulaevaluator ((hssfworkbook) WB); else if (WB instanceof Xssfworkbook) Eval=new xssfformulaevaluator ((xssfworkbook) WB); for (int i=r.getfirstcellnum (); I<r.getlastcellnum (); i++ ) {C=r.getcell (i); if (C.getcelltype () ==cell.cell_type_formula) Eval.evaluateformulacell (c);}}   

If you think this article to you to help me, please me to eat a candy bar ~??

Devid
Links: https://www.jianshu.com/p/5d5265cafa06
Source: Pinterest
The copyright of the book is owned by the author, and any form of reprint should be contacted by the author for authorization and attribution.

Workaround for using Apache POI to handle Excel formulas that are not updated