Java Code Examples for org.apache.poi.ss.usermodel.FormulaEvaluator#evaluate()

The following examples show how to use org.apache.poi.ss.usermodel.FormulaEvaluator#evaluate() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: SpreadsheetGetCellValue.java    From openbd-core with GNU General Public License v3.0 4 votes vote down vote up 
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	int rowNo, columnNo;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet	= (cfSpreadSheetData)parameters.get(2);
rowNo				= parameters.get(1).getInt() - 1;
columnNo		= parameters.get(0).getInt() - 1;
		
if ( rowNo < 0 )
	throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
	throwException(_session, "column must be 1 or greater (" + columnNo + ")");


/*
 * Find the cell in question 
 */
Sheet	sheet = spreadsheet.getActiveSheet();
Row row	= sheet.getRow( rowNo );
if ( row == null )
	row	= sheet.createRow( rowNo );

Cell cell	= row.getCell( columnNo );
if ( cell == null )
	cell = row.createCell( columnNo );

FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();

if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN )
	return cfBooleanData.getcfBooleanData( cell.getBooleanCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
	return new cfNumberData( cell.getNumericCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_BLANK )
	return cfStringData.EMPTY_STRING;
else if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
	return new cfStringData( cell.getStringCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
	CellValue cellValue = evaluator.evaluate(cell);
	
	switch (cellValue.getCellType()) {
		case Cell.CELL_TYPE_BOOLEAN:
			return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
		case Cell.CELL_TYPE_NUMERIC:
			return new cfNumberData(cellValue.getNumberValue());
		case Cell.CELL_TYPE_STRING:
			return new cfStringData(cellValue.getStringValue());
		default:
			return cfStringData.EMPTY_STRING;
	}

}else
	return cfStringData.EMPTY_STRING;
}
Example 2
Source File: GetRowIndexByConditionService.java    From cs-actions with Apache License 2.0 4 votes vote down vote up 
private static void processFormulaColumn(final Workbook excelDoc,
                                         final Sheet worksheet,
                                         final int firstRow,
                                         final int columnIndex) {

    final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
    for (int i = firstRow; i <= worksheet.getLastRowNum(); i++) {
        final Row row = worksheet.getRow(i);
        if (row != null) {
            final Cell cell = row.getCell(columnIndex);
            if (cell != null && (cell.getCellType() != CellType.BLANK)) {
                //formula type
                if (cell.getCellType() == CellType.FORMULA) {
                    CellValue cellValue = evaluator.evaluate(cell);

                    switch (cellValue.getCellType()) {
                        case BOOLEAN:
                            cell.setCellType(CellType.STRING);
                            break;
                        case NUMERIC:
                            cell.setCellType(CellType.NUMERIC);
                            break;
                        case STRING:
                            if (StringUtils.isBlank(cell.getStringCellValue())) {
                                cell.setCellType(CellType.BLANK);
                            } else {
                                cell.setCellType(CellType.STRING);
                            }
                            break;
                        case BLANK:

                            break;
                        case ERROR:
                            break;

                        // CELL_TYPE_FORMULA will never happen
                        case FORMULA:
                            break;
                    }
                }
            }
        }
    }
}