java poi读取公式_POI读取公式的值

excel中的数据：

packagepoi;importjava.io.FileInputStream;importjava.io.IOException;importjava.io.InputStream;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellValue;importorg.apache.poi.ss.usermodel.FormulaEvaluator;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;public classTestReadFormula {private staticFormulaEvaluator evaluator;public static void main(String[] args) throwsIOException {

InputStream is=new FileInputStream("ReadFormula.xls");

HSSFWorkbook wb=newHSSFWorkbook(is);

Sheet sheet=wb.getSheetAt(0);

evaluator=wb.getCreationHelper().createFormulaEvaluator();for (int i = 1; i <4; i++) {

Row row=sheet.getRow(i);for(Cell cell : row) {

System.out.println(getCellValue(cell));

}

}

wb.close();

}private staticString getCellValue(Cell cell) {if (cell==null) {return "isNull";

}

System.out.println("rowIdx:"+cell.getRowIndex()+",colIdx:"+cell.getColumnIndex());

String cellValue= null;switch(cell.getCellType()) {caseCell.CELL_TYPE_STRING:

System.out.print("STRING :");

cellValue=cell.getStringCellValue();break;caseCell.CELL_TYPE_NUMERIC:

System.out.print("NUMERIC:");

cellValue=String.valueOf(cell.getNumericCellValue());break;caseCell.CELL_TYPE_FORMULA:

System.out.print("FORMULA:");

cellValue=getCellValue(evaluator.evaluate(cell));break;default:

System.out.println("Has Default.");break;

}returncellValue;

}private staticString getCellValue(CellValue cell) {

String cellValue= null;switch(cell.getCellType()) {caseCell.CELL_TYPE_STRING:

System.out.print("String :");

cellValue=cell.getStringValue();break;caseCell.CELL_TYPE_NUMERIC:

System.out.print("NUMERIC:");

cellValue=String.valueOf(cell.getNumberValue());break;caseCell.CELL_TYPE_FORMULA:

System.out.print("FORMULA:");break;default:break;

}returncellValue;

}

}

Output:

rowIdx:1,colIdx:0STRING :begin

rowIdx:1,colIdx:1STRING :end

rowIdx:1,colIdx:2FORMULA:String :beginend

rowIdx:2,colIdx:0NUMERIC:1.0rowIdx:2,colIdx:1NUMERIC:3.0rowIdx:2,colIdx:2FORMULA:String :13rowIdx:3,colIdx:0NUMERIC:1.0rowIdx:3,colIdx:1NUMERIC:3.0rowIdx:3,colIdx:2FORMULA:NUMERIC:4.0

Formula Evaluation:

User API How-TO

The 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;

}

}

Re-calculating all formulas in a Workbook

FileInputStream 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 staticevaluateAllFormulaCellsmethod on the appropriate HSSFFormulaEvaluator or XSSFFormulaEvaluator class.