package pwc.taxtech.atms.common; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.udf.AggregatingUDFFinder; import org.apache.poi.ss.formula.udf.DefaultUDFFinder; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellReference; import java.io.*; public class POITest { public static void main(String[] args) { File workbookFile = new File("C:\\source\\test - Copy.xlsx"); try { FileInputStream fis = new FileInputStream(workbookFile); Workbook workbook = WorkbookFactory.create(fis); String[] functionNames = {"TmpFunction"}; FreeRefFunction[] functionImpls = {new TmpFunction()}; UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls); UDFFinder udfToolpack = new AggregatingUDFFinder(udfs); workbook.addToolPack(udfToolpack); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); int sheetNum = workbook.getNumberOfSheets(); Sheet st1 = workbook.getSheetAt(0); st1.getRow(1).getCell(0).setCellType(CellType.NUMERIC); st1.getRow(1).getCell(0).setCellValue(15); evaluator.evaluateAll(); for (int i = 0; i < sheetNum; i++) { Sheet tmpSheet = workbook.getSheetAt(i); for (int r = tmpSheet.getFirstRowNum(); r <= tmpSheet.getLastRowNum(); r++) { Row row = tmpSheet.getRow(r); if (null == row) { continue; } for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) { System.out.println("row :" + r + " cell: " + c); Cell tmp = row.getCell(c); if (null != tmp && tmp.getCellTypeEnum().equals(CellType.FORMULA)) { // CellValue v = evaluator.evaluate(tmp); tmp.setCellType(CellType.NUMERIC); tmp.setCellValue(tmp.getNumericCellValue()); } } } } FileOutputStream excelFileOutPutStream = new FileOutputStream("C:\\source\\test - Copy.xlsx"); workbook.write(excelFileOutPutStream); excelFileOutPutStream.flush(); excelFileOutPutStream.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }