Apache | POI |
Formula EvaluationIntroductionThe POI formula evaluation code enables you to calculate the result of formulas in Excels sheets read-in, or created in POI. This document explains how to use the API to evaluate your formulas. Note
In versions of POI before 3.0.3, this code lived in the
scratchpad area of the POI SVN repository. If using an such an older
version of POI, ensure that you have the scratchpad jar or the
scratchpad build area in your classpath before experimenting with this
code. Users of all versions of POI may wish to make use of a recent
SVN checkout, as new functions are currently being added fairly frequently.
StatusThe code currently provides implementations for all the arithmatic operators. It also provides implementations for approx. 100 built in functions in Excel. The framework however makes is easy to add implementation of new functions. See the Formula evaluation development guide for details. Note that user-defined 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 How-TOThe following code demonstrates how to use the HSSFFormulaEvaluator in the context of other POI excel reading code. There are several ways in which you can use the HSSFFormulaEvalutator API. Using HSSFFormulaEvaluator.evaluate(HSSFCell cell)This evaluates a given cell, and returns the new value, without affecting the cell FileInputStream fis = new FileInputStream("c:/temp/test.xls"); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); HSSFRow row = sheet.getRow(cellReference.getRow()); HSSFCell cell = row.getCell(cellReference.getCol()); evaluator.setCurrentRow(row); HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: System.out.println(cellValue.getBooleanValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: System.out.println(cellValue.getNumberValue()); break; case HSSFCell.CELL_TYPE_STRING: System.out.println(cellValue.getStringValue()); break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: break; // CELL_TYPE_FORMULA will never happen case HSSFCell.CELL_TYPE_FORMULA: break; } Thus using the retrieved value (of type HSSFFormulaEvaluator.CellValue - a nested class) returned by HSSFFormulaEvaluator is similar to using a HSSFCell object containing the value of the formula evaluation. CellValue is a simple value object and does not maintain reference to the original cell. Using HSSFFormulaEvaluator.evaluateFormulaCell(HSSFCell cell)evaluateFormulaCell(HSSFCell 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 HSSFCell.CELL_TYPE_BOOLEAN FileInputStream fis = new FileInputStream("/somepath/test.xls"); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); HSSFRow row = sheet.getRow(cellReference.getRow()); HSSFCell cell = row.getCell(cellReference.getCol()); evaluator.setCurrentRow(row); if (cell!=null) { switch (evaluator.evaluateFormulaCell(cell)) { case HSSFCell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumberCellValue()); break; case HSSFCell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: System.out.println(cell.getErrorCellValue()); break; // CELL_TYPE_FORMULA will never occur case HSSFCell.CELL_TYPE_FORMULA: break; } } Using HSSFFormulaEvaluator.evaluateInCell(HSSFCell cell)evaluateInCell(HSSFCell 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"); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); // suppose your formula is in B3 CellReference cellReference = new CellReference("B3"); HSSFRow row = sheet.getRow(cellReference.getRow()); HSSFCell cell = row.getCell(cellReference.getCol()); evaluator.setCurrentRow(row); if (cell!=null) { switch (evaluator.evaluateInCell(cell).getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumberCellValue()); break; case HSSFCell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: System.out.println(cell.getErrorCellValue()); break; // CELL_TYPE_FORMULA will never occur case HSSFCell.CELL_TYPE_FORMULA: break; } } Re-calculating all formulas in a WorkbookFileInputStream fis = new FileInputStream("/somepath/test.xls"); HSSFWorkbook wb = new HSSFWorkbook(fis); for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = wb.getSheetAt(sheetNum); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); for(Iterator rit = sheet.rowIterator(); rit.hasNext();) { HSSFRow r = (HSSFRow)rit.next(); evaluator.setCurrentRow(r); for(Iterator cit = r.cellIterator(); cit.hasNext();) { HSSFCell c = (HSSFCell)cit.next(); if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); } } } } wb.write(new FileOutputStream("/somepath/changed.xls")); Performance Notes
|