Apache  POI 
Home 

Formula EvaluationIntroductionThe POI formula evaluation code enables you to calculate the result of formulas in Excels sheets readin, or created in POI. This document explains how to use the API to evaluate your formulas. Why do I need to evaluate formulas?The Excel file format (both .xls and .xlsx) stores a "cached" result for every formula along with the formula itself. This means that when the file is opened, it can be quickly displayed, without needing to spend a long time calculating all of the formula results. It also means that when reading a file through Apache POI, the result is quickly available to you too! After making changes with Apache POI to either Formula Cells themselves, or those that they depend on, you should normally perform a Formula Evaluation to have these "cached" results updated. This is normally done after all changes have been performed, but before you write the file out. If you don't do this, there's a good chance that when you open the file in Excel, until you go to the cell and hit enter or F9, you will either see the old value or '#VALUE!' for the cell. (Sometimes Excel will notice itself, and trigger a recalculation on load, but unless you know you are using volatile functions it's generally best to trigger a Recalulation through POI) StatusThe code currently provides implementations for all the arithmatic operators. It also provides implementations for approx. 140 built in functions in Excel. The framework however makes it easy to add implementation of new functions. See the Formula evaluation development guide and javadocs for details. Both HSSFWorkbook and XSSFWorkbook are supported, so you can evaluate formulas on both .xls and .xlsx files. Note that userdefined functions are not supported, and is not likely to done any time soon... at least, not till there is a VB implementation in Java! User API HowTOThe following code demonstrates how to use the FormulaEvaluator in the context of other POI excel reading code. There are several ways in which you can use the FormulaEvalutator API. Using FormulaEvaluator.evaluate(Cell cell)This evaluates a given cell, and returns the new value, without affecting the cell FileInputStream fis = new FileInputStream("c:/temp/test.xls"); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("c:/temp/test.xls") Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.println(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cellValue.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; // CELL_TYPE_FORMULA will never happen case Cell.CELL_TYPE_FORMULA: break; } Thus using the retrieved value (of type FormulaEvaluator.CellValue  a nested class) returned by FormulaEvaluator is similar to using a Cell object containing the value of the formula evaluation. CellValue is a simple value object and does not maintain reference to the original cell. Using FormulaEvaluator.evaluateFormulaCell(Cell cell)evaluateFormulaCell(Cell cell) will check to see if the supplied cell is a formula cell. If it isn't, then no changes will be made to it. If it is, then the formula is evaluated. The value for the formula is saved alongside it, to be displayed in excel. The formula remains in the cell, just with a new value The return of the function is the type of the formula result, such as Cell.CELL_TYPE_BOOLEAN FileInputStream fis = new FileInputStream("/somepath/test.xls"); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); if (cell!=null) { switch (evaluator.evaluateFormulaCell(cell)) { case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: System.out.println(cell.getErrorCellValue()); break; // CELL_TYPE_FORMULA will never occur case Cell.CELL_TYPE_FORMULA: break; } } Using FormulaEvaluator.evaluateInCell(Cell cell)evaluateInCell(Cell cell) will check to see if the supplied cell is a formula cell. If it isn't, then no changes will be made to it. If it is, then the formula is evaluated, and the new value saved into the cell, in place of the old formula. FileInputStream fis = new FileInputStream("/somepath/test.xls"); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); if (cell!=null) { switch (evaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: System.out.println(cell.getErrorCellValue()); break; // CELL_TYPE_FORMULA will never occur case Cell.CELL_TYPE_FORMULA: break; } } Recalculating all formulas in a WorkbookFileInputStream fis = new FileInputStream("/somepath/test.xls"); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { Sheet sheet = wb.getSheetAt(sheetNum); for(Row r : sheet) { for(Cell c : r) { if(c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); } } } } Alternately, if you know which of HSSF or XSSF you're working with, then you can call the static evaluateAllFormulaCells method on the appropriate HSSFFormulaEvaluator or XSSFFormulaEvaluator class. Recalculation of FormulasIn certain cases you may want to force Excel to recalculate 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. Reevaluate 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 recalculation 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); External (CrossWorkbook) referencesIt is possible for a formula in an Excel spreadsheet to refer to a Named Range or Cell in a different workbook. These crossworkbook references are normally called External References. These are formulas which look something like: =SUM([Finances.xlsx]Numbers!D10:D25) =SUM('C:\Data\[Finances.xlsx]Numbers'!D10:D25) =SUM([Finances.xlsx]Range20) If you don't have access to these other workbooks, then you should call setIgnoreMissingWorkbooks(true) to tell the Formula Evaluator to skip evaluating any external references it can't look up. In order for POI to be able to evaluate external references, it needs access to the workbooks in question. As these don't necessarily have the same names on your system as in the workbook, you need to give POI a map of external references to open workbooks, through the setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks) method. You should normally do something like: // Create a FormulaEvaluator to use FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); // Track the workbook references Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>(); // Add this workbook workbooks.put("report.xlsx", mainWorkbookEvaluator); // Add two others workbooks.put("input.xls", WorkbookFactory.create("c:\temp\input22.xls").getCreationHelper().createFormulaEvaluator()); workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmplookups.xlsx").getCreationHelper().createFormulaEvaluator()); // Attach them mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks); // Evaluate mainWorkbookEvaluator.evaluateAll(); Performance Notes
Formula Evaluation DebuggingPOI is not perfect and you may stumble across formula evaluation problems (Java exceptions or just different results) in your special use case. To support an easy detailed analysis, a special logging of the full evaluation is provided. The output of this logging may be very large (depends on your EXCEL), so this logging has to be explicitly enabled for each single formula evaluation. Should not be used in production  only for specific development use. Example use: // activate logging to console System.setProperty("org.apache.poi.util.POILogger", "org.apache.poi.util.SystemOutLogger"); System.setProperty("poi.log.level", POILogger.INFO + ""); // open your file Workbook wb = new HSSFWorkbook(new FileInputStream("foobar.xls")); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // get your cell Cell cell = wb.getSheet(0).getRow(0).getCell(0); // just a dummy example // perform debug output for the next evaluatecall only evaluator.setDebugEvaluationOutputForNextEval(true); evaluator.evaluateFormulaCell(cell); evaluator.evaluateFormulaCell(cell); // no logging performed for this next evaluatecall The special Logger called "POI.FormulaEval" is used (useful if you use the CommonsLogger and a detailed logging configuration). The used log levels are WARN and INFO (for detailed parameter info and results)  the level are so high to allow this special logging without beeing disturbed by the bunch of DEBUG log entries from other classes. Formula Evaluation and SXSSFFor versions before 3.13 beta 2, no formula evaluation is possible with SXSSF. If using POI 3.13 beta 2 or newer, formula evaluation is possible with SXSSF, but with some caveats. The biggest restriction is that, since evaluating a cell needs that cell in memory and any others it depends on, only purefunction formulas and formulas referencing nearby cells can be evaluated with SXSSF. If a formula references a cell that hasn't yet been written, or one which has already been flushed to disk, then it won't be possible to evaluate it. Because of this, a call to wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); will very rarely work on SXSSF, as it's very rare that all the cells wil be available and in memory at any time! Instead, it is suggested to evaluate formula cells just after writing them, or shortly after when cells they depend on are added. Just make sure that all cells needing or needed for evaluation are inside the window. 