[Java] POI 사용시 formula cell의 결과값이 제대로 나오지 않을 때

POI를 이용하여 Excel 파일을 control할 때,

cell에 formula를 입력하였으나 결과가 제대로 나오지 않고 #REF! 와 같이 나오는 경우가 있다.

이 경우는... 각 cell의 formula를 강제로 실행하도록 한다.

Recalculation of Formulas

In certain cases you may want to force Excel to re-calculate formulas when the workbook is opened. Consider the following example:

Open Excel and create a new workbook. On the first sheet set A1=1, B1=1, C1=A1+B1. Excel automatically calculates formulas and the value in C1 is 2. So far so good.

Now modify the workbook with POI:

  Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));

  Sheet sh = wb.getSheetAt(0);
  sh.getRow(0).getCell(0).setCellValue(2);  // set A1=2

  FileOutputStream out = new FileOutputStream("workbook2.xls");
  wb.write(out);
  out.close();
      

Now open workbook2.xls in Excel and the value in C1 is still 2 while you expected 3. Wrong? No! The point is that Excel caches previously calculated results and you need to trigger recalculation to updated them. It is not an issue when you are creating new workbooks from scratch, but important to remember when you are modifing existing workbooks with formulas. This can be done in two ways:

1. Re-evaluate formulas with POI's FormulaEvaluator:

  Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));

  Sheet sh = wb.getSheetAt(0);
  sh.getRow(0).getCell(0).setCellValue(2);  // set A1=2

  wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
        

2. Delegate re-calculation to Excel. The application will perform a full recalculation when the workbook is opened:

  Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));

  Sheet sh = wb.getSheetAt(0);
  sh.getRow(0).getCell(0).setCellValue(2);  // set A1=2

  wb.setForceFormulaRecalculation(true);
        

위 내용의 전문은 http://poi.apache.org/spreadsheet/eval.html 에서 확인 가능하다.