org.apache.poi.ss.formula.FormulaParseException Java Examples

protected void createName(HandlerState state, String bookmark, int row1, int col1, int row2, int col2 ) {
	CellReference crFirst = new CellReference( state.currentSheet.getSheetName(), row1, col1, true, true );
	CellReference crLast = new CellReference( row2, col2, true, true );
	String formula = crFirst.formatAsString() + ":" + crLast.formatAsString();

	Name name = state.currentSheet.getWorkbook().getName(bookmark);
	if( name == null ) {
		name = state.currentSheet.getWorkbook().createName();
		name.setNameName( bookmark ); 
		name.setRefersToFormula( formula );
	} else {
		String existingFormula = name.getRefersToFormula();
		try {
			name.setRefersToFormula(existingFormula + "," + formula);
		} catch( FormulaParseException ex ) {
			log.warn( 0, "Unable to add \"" + formula + "\" to name (\"" + bookmark + "\") with existing formula: " + existingFormula, ex );
		}
	}
}
 

/**
 * セルに数式を設定する
 * @param field フィールド情報
 * @param config システム情報
 * @param cell セル情報
 * @param targetBean 処理対象のフィールドが定義されているクラスのインスタンス。
 * @throws ConversionException 数式の解析に失敗した場合。
 */
public void handleFormula(final FieldAccessor field, final Configuration config, final Cell cell, final Object targetBean) {
    
    ArgUtils.notNull(field, "field");
    ArgUtils.notNull(config, "config");
    ArgUtils.notNull(cell, "cell");
    
    final String evaluatedFormula = createFormulaValue(config, cell, targetBean);
    if(Utils.isEmpty(evaluatedFormula)) {
        cell.setCellType(CellType.BLANK);
        return;
    }
    
    try {
        cell.setCellFormula(evaluatedFormula);
        cell.setCellType(CellType.FORMULA);
        
    } catch(FormulaParseException e) {
        // 数式の解析に失敗した場合
        String message = MessageBuilder.create("cell.failParseFormula")
                .var("property", field.getNameWithClass())
                .var("cellAddress", CellPosition.of(cell).toString())
                .var("formula", evaluatedFormula)
                .format();
        
        throw new ConversionException(message, e, field.getType());
    }
    
}
 

private static ValueEval evaluateIndirect(final OperationEvaluationContext ec, String text,
        boolean isA1style) {
    
    // Search backwards for '!' because sheet names can contain '!'
    int plingPos = text.lastIndexOf('!');

    String workbookName;
    String sheetName;
    String refText; // whitespace around this gets trimmed OK
    if (plingPos < 0) {
        workbookName = null;
        sheetName = null;
        refText = text;
    } else {
        String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos));
        if (parts == null) {
            return ErrorEval.REF_INVALID;
        }
        workbookName = parts[0];
        sheetName = parts[1];
        refText = text.substring(plingPos + 1);
    }

    if (Table.isStructuredReference.matcher(refText).matches()) {
        // The argument is structured reference
        Area3DPxg areaPtg = null;
        try {
            areaPtg = FormulaParser.parseStructuredReference(refText, (FormulaParsingWorkbook) ec.getWorkbook(), ec.getRowIndex());
        } catch (FormulaParseException e) {
            return ErrorEval.REF_INVALID;
        }
        return ec.getArea3DEval(areaPtg);
    } else {
        // The argument is regular reference
        String refStrPart1;
        String refStrPart2;
        int colonPos = refText.indexOf(':');
        if (colonPos < 0) {
             refStrPart1 = refText.trim();
             refStrPart2 = null;            
        } else {
            refStrPart1 = refText.substring(0, colonPos).trim();
            refStrPart2 = refText.substring(colonPos + 1).trim();
        }
        return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style);
    }
}
 

/**
 * Not supported
 */
@Override
public void setCellFormula(String formula) throws FormulaParseException {
  throw new NotSupportedException();
}
 

/**
 * Sets formula for this cell.
 * <p>
 * Note, this method only sets the formula string and does not calculate the formula value.
 * To set the precalculated value use {@link #setCellValue(double)} or {@link #setCellValue(String)}
 * </p>
 *
 * @param formula the formula to set, e.g. <code>"SUM(C4:E4)"</code>.
 *  If the argument is <code>null</code> then the current formula is removed.
 * @throws FormulaParseException if the formula has incorrect syntax or is otherwise invalid
 */
void setCellFormula(String formula) throws FormulaParseException;
 

/**
 * Convenience method for parsing cell formulas. see {@link #parse(String, HSSFWorkbook, FormulaType, int)}
 * @param formula   The formula to parse, excluding the leading equals sign
 * @param workbook  The parent workbook
 * @return the parsed formula tokens
 * @throws FormulaParseException if the formula has incorrect syntax or is otherwise invalid
 */
public static Ptg[] parse(String formula, HSSFWorkbook workbook) throws FormulaParseException {
    return parse(formula, workbook, FormulaType.CELL);
}
 

/**
 * @param formula     The formula to parse, excluding the leading equals sign
 * @param workbook    The parent workbook
 * @param formulaType The type of formula
 * @return The parsed formula tokens
 * @throws FormulaParseException if the formula has incorrect syntax or is otherwise invalid
 */
public static Ptg[] parse(String formula, HSSFWorkbook workbook, FormulaType formulaType) throws FormulaParseException {
    return parse(formula, workbook, formulaType, -1);
}
 

/**
 * @param formula     The formula to parse
 * @param workbook    The parent workbook
 * @param formulaType The type of formula
 * @param sheetIndex  The 0-based index of the sheet this formula belongs to.
 * The sheet index is required to resolve sheet-level names. <code>-1</code> means that
 * the scope of the name will be ignored and  the parser will match named ranges only by name
 *
 * @return the parsed formula tokens
 * @throws FormulaParseException if the formula has incorrect syntax or is otherwise invalid
 */
public static Ptg[] parse(String formula, HSSFWorkbook workbook, FormulaType formulaType, int sheetIndex) throws FormulaParseException {
    return FormulaParser.parse(formula, createParsingWorkbook(workbook), formulaType, sheetIndex);
}