Java Code Examples for org.apache.poi.ss.usermodel.CellType#FORMULA

/**
 * @return  whether cell at rowIndex and columnIndex is a subtotal
 * @see org.apache.poi.ss.formula.functions.Subtotal
 */
public boolean isSubTotal(int rowIndex, int columnIndex){
    boolean subtotal = false;
    EvaluationCell cell = getSheet().getCell(rowIndex, columnIndex);
    if(cell != null && cell.getCellTypeEnum() == CellType.FORMULA){
        EvaluationWorkbook wb = _bookEvaluator.getWorkbook();
        for(Ptg ptg : wb.getFormulaTokens(cell)){
            if(ptg instanceof FuncVarPtg){
                FuncVarPtg f = (FuncVarPtg)ptg;
                if("SUBTOTAL".equals(f.getName())) {
                    subtotal = true;
                    break;
                }
            }
        }
    }
    return subtotal;
}
 

/**
 * used internally -- given a cell value record, figure out its type
 */
private static CellType determineType(CellValueRecordInterface cval) {
    if (cval instanceof FormulaRecordAggregate) {
        return CellType.FORMULA;
    }
    // all others are plain BIFF records
    Record record = ( Record ) cval;
    switch (record.getSid()) {

        case NumberRecord.sid :   return CellType.NUMERIC;
        case BlankRecord.sid :    return CellType.BLANK;
        case LabelSSTRecord.sid : return CellType.STRING;
        case BoolErrRecord.sid :
            BoolErrRecord boolErrRecord = ( BoolErrRecord ) record;

            return boolErrRecord.isBoolean()
                     ? CellType.BOOLEAN
                     : CellType.ERROR;
    }
    throw new RuntimeException("Bad cell value rec (" + cval.getClass().getName() + ")");
}
 

protected void updateFormulas(Area templateArea, Area dependentResultArea) {
    HSSFSheet templateSheet = getTemplateSheetForRangeName(templateWorkbook, templateArea.getName());
    HSSFSheet resultSheet = templateToResultSheetsMapping.get(templateSheet);

    AreaReference area = dependentResultArea.toAreaReference();
    for (CellReference cell : area.getAllReferencedCells()) {
        HSSFCell resultCell = getCellFromReference(cell, resultSheet);

        if (resultCell.getCellType() == CellType.FORMULA) {
            Ptg[] ptgs = HSSFFormulaParser.parse(resultCell.getCellFormula(), resultWorkbook);

            for (Ptg ptg : ptgs) {
                if (ptg instanceof AreaPtg) {
                    areaDependencyManager.updateAreaPtg(templateArea, dependentResultArea, (AreaPtg) ptg);
                } else if (ptg instanceof RefPtg) {
                    areaDependencyManager.updateRefPtg(templateArea, dependentResultArea, (RefPtg) ptg);
                }
            }

            String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs);
            resultCell.setCellFormula(calculatedFormula);
        }
    }
}
 

/**
     * <p>
     * Returns the formatted value of a cell as a <tt>String</tt> regardless of the cell type. If the Excel number
     * format pattern cannot be parsed then the cell value will be formatted using a default format.
     * </p>
     * <p>
     * When passed a null or blank cell, this method will return an empty String (""). Formula cells will be evaluated
     * using the given {@link FormulaEvaluator} if the evaluator is non-null. If the evaluator is null, then the formula
     * String will be returned. The caller is responsible for setting the currentRow on the evaluator
     * </p>
     * <p>
     * When a ConditionalFormattingEvaluator is present, it is checked first to see if there is a number format to
     * apply. If multiple rules apply, the last one is used. If no ConditionalFormattingEvaluator is present, no rules
     * apply, or the applied rules do not define a format, the cell's style format is used.
     * </p>
     * <p>
     * The two evaluators should be from the same context, to avoid inconsistencies in cached values.
     * </p>
     *
     * @param cell
     *            The cell (can be null)
     * @param evaluator
     *            The FormulaEvaluator (can be null)
     * @param cfEvaluator
     *            ConditionalFormattingEvaluator (can be null)
     * @return a string value of the cell
     */
    public String formatCellValue(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator) {
        localeChangedObservable.checkForLocaleChange();

        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            if (evaluator == null) {
                return cell.getCellFormula();
            }
            cellType = evaluator.evaluateFormulaCellEnum(cell);
        }
        switch (cellType) {
            case NUMERIC:

//                if (DateUtil.isCellDateFormatted(cell, cfEvaluator)) {
                    return getFormattedDateString(cell, cfEvaluator);
//                }
//                return getFormattedNumberString(cell, cfEvaluator);

            case STRING:
                return cell.getRichStringCellValue().getString();

            case BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case BLANK:
                return "";
            case ERROR:
                return FormulaError.forInt(cell.getErrorCellValue()).getString();
            default:
                throw new RuntimeException("Unexpected celltype (" + cellType + ")");
        }
    }
 

/**
 * copies template cell to result row into result column. Fills this cell with data from band
 *
 * @param templateCell - template cell
 * @param resultRow    - result row
 * @param resultColumn - result column
 * @param band         - band
 */
private HSSFCell copyCellFromTemplate(HSSFCell templateCell, HSSFRow resultRow, int resultColumn, BandData band) {
    checkThreadInterrupted();
    if (templateCell == null) return null;

    HSSFCell resultCell = resultRow.createCell(resultColumn);

    HSSFCellStyle templateStyle = templateCell.getCellStyle();
    HSSFCellStyle resultStyle = copyCellStyle(templateStyle);
    resultCell.setCellStyle(resultStyle);

    String templateCellValue = "";
    CellType cellType = templateCell.getCellType();

    if (cellType != CellType.FORMULA && cellType != CellType.NUMERIC) {
        HSSFRichTextString richStringCellValue = templateCell.getRichStringCellValue();
        templateCellValue = richStringCellValue != null ? richStringCellValue.getString() : "";

        templateCellValue = extractStyles(templateCell, resultCell, templateCellValue, band);
    }

    if (cellType == CellType.STRING && containsJustOneAlias(templateCellValue)) {
        updateValueCell(rootBand, band, templateCellValue, resultCell,
                drawingPatriarchsMap.get(resultCell.getSheet()));
    } else {
        String cellValue = inlineBandDataToCellString(templateCell, templateCellValue, band);
        setValueToCell(resultCell, cellValue, cellType);
    }

    return resultCell;
}
 

protected static void evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator) {
    for(int i=0; i<wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for(Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellTypeEnum() == CellType.FORMULA) {
                    evaluator.evaluateFormulaCellEnum(c);
                }
            }
        }
    }
}
 

/**
* Note that this assumes the cell cached value is up to date and in sync with data edits
* @param cell
* @param type
* @return true if the cell or cached cell formula result type match the given type
*/
public static boolean isType(Cell cell, CellType type) {
    final CellType cellType = cell.getCellTypeEnum();
    return cellType == type 
          || (cellType == CellType.FORMULA 
              && cell.getCachedFormulaResultTypeEnum() == type
             );
}
 

private ValueAndFormat getCellValue(Cell cell) {
    if (cell != null) {
        final CellType type = cell.getCellTypeEnum();
        if (type == CellType.NUMERIC || (type == CellType.FORMULA && cell.getCachedFormulaResultTypeEnum() == CellType.NUMERIC) ) {
            return new ValueAndFormat(new Double(cell.getNumericCellValue()), cell.getCellStyle().getDataFormatString());
        } else if (type == CellType.STRING || (type == CellType.FORMULA && cell.getCachedFormulaResultTypeEnum() == CellType.STRING) ) {
            return new ValueAndFormat(cell.getStringCellValue(), cell.getCellStyle().getDataFormatString());
        } else if (type == CellType.BOOLEAN || (type == CellType.FORMULA && cell.getCachedFormulaResultTypeEnum() == CellType.BOOLEAN) ) {
            return new ValueAndFormat(cell.getStringCellValue(), cell.getCellStyle().getDataFormatString());
        }
    }
    return new ValueAndFormat("", "");
}
 

public static Map<String, String> deleteCell(@NotNull final DeleteCellInputs deleteCellInputs) {
    try {
        final String excelFileName = deleteCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, deleteCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int firstColumnIndex = 0;
        final int lastRowIndex = worksheet.getLastRowNum();
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(deleteCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(deleteCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        if (rowIndexList.size() != 0 && columnIndexList.size() != 0) {
            final int deleteCellResult = deleteCell(worksheet, rowIndexList, columnIndexList);
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row r : worksheet) {
                for (Cell c : r) {
                    if (c.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
            return getSuccessResultsMap(String.valueOf(deleteCellResult));

        } else {
            return getSuccessResultsMap("0");
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}
 

public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (cell.getCachedFormulaResultType()) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

public CellRangeAddress getArrayFormulaRange() {
    if (_cellType != CellType.FORMULA) {
        String ref = new CellReference(this).formatAsString();
        throw new IllegalStateException("Cell " + ref
                + " is not part of an array formula.");
    }
    return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
}
 

private static int modifyCellData(final Sheet worksheet,
                                  final List<Integer> rowIndexList,
                                  final List<Integer> columnIndexList,
                                  final List<String> dataList) {
    int rowCount = 0;

    for (Integer rowIndex : rowIndexList) {
        boolean isModified = false;
        int i = 0;
        Row row = worksheet.getRow(rowIndex);
        //if the specified row does not exist
        if (row == null) {
            row = worksheet.createRow(rowIndex);

        }
        for (Integer columnIndex : columnIndexList) {
            Cell cell = row.getCell(columnIndex);
            //if the specified cell does not exist
            if (cell == null) {
                cell = row.createCell(columnIndex);
            }
            //the cell is a merged cell, cannot modify it
            if (isMergedCell(worksheet, rowIndex, columnIndex)) {
                i++;
                incompleted = true;
            } else {
                //if the cell needs to be modified is in formula type,
                if (cell.getCellType() == CellType.FORMULA) {
                    cell.setCellType(CellType.STRING);
                }
                try {
                    double valueNumeric = Double.parseDouble(dataList.get(i).trim());
                    cell.setCellValue(valueNumeric);
                }
                //for non-numeric value
                catch (Exception e) {
                    try {
                        Date date = new Date(dataList.get(i).trim());
                        cell.setCellValue(date);
                    } catch (Exception e1) {
                        cell.setCellValue(dataList.get(i).trim());
                    }
                }
                i++;
                isModified = true;
            }
        }
        if (isModified) rowCount++;
    }

    return rowCount;
}
 

/**
 * We store (cell types per row) per column.
 *
 * @param sheet key is the column number, value is a Map with key row number and value Type
 * @return A Map<colId, Map<rowId, type>>
 */
private SortedMap<Integer, SortedMap<Integer, String>> collectSheetTypeMatrix(Sheet sheet,
        FormulaEvaluator formulaEvaluator) {

    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    LOGGER.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum);

    SortedMap<Integer, SortedMap<Integer, String>> cellsTypeMatrix = new TreeMap<>();

    // we start analysing rows
    for (int rowCounter = firstRowNum; rowCounter <= lastRowNum; rowCounter++) {

        int cellCounter = 0;

        Row row = sheet.getRow(rowCounter);
        if (row == null) {
            continue;
        }

        Iterator<Cell> cellIterator = row.cellIterator();

        String currentType;

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            CellType xlsType = CellType.STRING;

            try {
                xlsType = cell.getCellType() == CellType.FORMULA ? //
                        formulaEvaluator.evaluate(cell).getCellType() : cell.getCellType();
            } catch (Exception e) {
                // ignore formula error evaluation get as a String with the formula
            }
            switch (xlsType) {
            case BOOLEAN:
                currentType = BOOLEAN.getName();
                break;
            case NUMERIC:
                currentType = getTypeFromNumericCell(cell);
                break;
            case BLANK:
                currentType = BLANK;
                break;
            case FORMULA:
            case STRING:
                currentType = STRING.getName();
                break;
            case ERROR:
                // we cannot really do anything with an error
            default:
                currentType = ANY.getName();
            }

            SortedMap<Integer, String> cellInfo = cellsTypeMatrix.get(cellCounter);

            if (cellInfo == null) {
                cellInfo = new TreeMap<>();
            }
            cellInfo.put(rowCounter, currentType);

            cellsTypeMatrix.put(cellCounter, cellInfo);
            cellCounter++;
        }
    }

    LOGGER.trace("cellsTypeMatrix: {}", cellsTypeMatrix);
    return cellsTypeMatrix;
}
 

@NotNull
public static Map<String, String> modifyCell(@NotNull final ModifyCellInputs modifyCellInputs) {
    try {
        final String excelFileName = modifyCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, modifyCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int lastRowIndex = worksheet.getLastRowNum();
        final int firstColumnIndex = 0;
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);
        final String columnDelimiter = modifyCellInputs.getColumnDelimiter();
        final String newValue = modifyCellInputs.getNewValue();

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(modifyCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(modifyCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        final List<String> dataList = getDataList(newValue, columnIndexList, columnDelimiter);

        incompleted = false;
        final int modifyCellDataResult = modifyCellData(worksheet, rowIndexList, columnIndexList, dataList);

        if (modifyCellDataResult != 0) {
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row row : worksheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(cell);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
        }

        if (modifyCellDataResult == rowIndexList.size() && !incompleted) {
            return getSuccessResultsMap(String.valueOf(modifyCellDataResult));
        } else {
            return getFailureResultsMap(String.valueOf(modifyCellDataResult));
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}
 

public boolean isPartOfArrayFormulaGroup() {
    if (_cellType != CellType.FORMULA) {
        return false;
    }
    return ((FormulaRecordAggregate)_record).isPartOfArrayFormula();
}
 

/**
 * Set a string value for the cell.
 *
 * @param value  value to set the cell to.  For formulas we'll set the formula
 * string, for String cells we'll set its value.  For other types we will
 * change the cell to a string cell and set its value.
 * If value is <code>null</code> then we will change the cell to a Blank cell.
 */

public void setCellValue(RichTextString value)
{
    int row=_record.getRow();
    short col=_record.getColumn();
    short styleIndex=_record.getXFIndex();
    if (value == null)
    {
        notifyFormulaChanging();
        setCellType(CellType.BLANK, false, row, col, styleIndex);
        return;
    }

    if(value.length() > SpreadsheetVersion.EXCEL97.getMaxTextLength()){
        throw new IllegalArgumentException("The maximum length of cell contents (text) is 32,767 characters");
    }

    if (_cellType == CellType.FORMULA) {
        // Set the 'pre-evaluated result' for the formula
        // note - formulas do not preserve text formatting.
        FormulaRecordAggregate fr = (FormulaRecordAggregate) _record;
        fr.setCachedStringResult(value.getString());
        // Update our local cache to the un-formatted version
        _stringValue = new HSSFRichTextString(value.getString());

        // All done
        return;
    }

    // If we get here, we're not dealing with a formula,
    //  so handle things as a normal rich text cell

    if (_cellType != CellType.STRING) {
        setCellType(CellType.STRING, false, row, col, styleIndex);
    }
    int index = 0;

    HSSFRichTextString hvalue = (HSSFRichTextString) value;
    UnicodeString str = hvalue.getUnicodeString();
    index = _book.getWorkbook().addSSTString(str);
    (( LabelSSTRecord ) _record).setSSTIndex(index);
    _stringValue = hvalue;
    _stringValue.setWorkbookReferences(_book.getWorkbook(), (( LabelSSTRecord ) _record));
    _stringValue.setUnicodeString(_book.getWorkbook().getSSTString(index));
}
 

@Override
public Object[] getNext() {

	SpreadSheetCellDAO[] result=null;
	// all sheets?
	if (this.sheets==null) { //  go on with all sheets
		if (!nextAllSheets()) {
			return result;
		}
	} else { // go on with specified sheets
		if (!nextSpecificSheets()) {
			return result;
		}
	}
	// read row from the sheet currently to be processed
	Sheet rSheet = this.currentWorkbook.getSheetAt(this.currentSheet);
	Row rRow = rSheet.getRow(this.currentRow);
	if ((rRow==null) || (rRow.getLastCellNum()<0)) {
		this.currentRow++;
		return new SpreadSheetCellDAO[0]; // emtpy row
	}
	result = new SpreadSheetCellDAO[rRow.getLastCellNum()];
	for (int i=0;i<rRow.getLastCellNum();i++) {
		Cell currentCell=rRow.getCell(i);
		if (currentCell==null) {
			result[i]=null;
		} else {	
			String formattedValue=useDataFormatter.formatCellValue(currentCell,this.formulaEvaluator);
			String formula = "";
			if (currentCell.getCellType()==CellType.FORMULA)  {
				formula = currentCell.getCellFormula();
			}
			Comment currentCellComment = currentCell.getCellComment();
			String comment = "";
			if (currentCellComment!=null) {
				comment = currentCellComment.getString().getString();
			}
			String address = currentCell.getAddress().toString();
			String sheetName = currentCell.getSheet().getSheetName();
			SpreadSheetCellDAO mySpreadSheetCellDAO = new SpreadSheetCellDAO(formattedValue,comment,formula,address,sheetName);
			
			result[i]=mySpreadSheetCellDAO;
		}
	}
	
	// increase rows
	this.currentRow++;
	return result;
}
 

public void notifyUpdateCell(int bookIndex, int sheetIndex, EvaluationCell cell) {
	FormulaCellCacheEntry fcce = _formulaCellCache.get(cell);

	int rowIndex = cell.getRowIndex();
	int columnIndex = cell.getColumnIndex();
	Loc loc = new Loc(bookIndex, sheetIndex, rowIndex, columnIndex);
	PlainValueCellCacheEntry pcce = _plainCellCache.get(loc);

	if (cell.getCellTypeEnum() == CellType.FORMULA) {
		if (fcce == null) {
			fcce = new FormulaCellCacheEntry();
			if (pcce == null) {
				if (_evaluationListener != null) {
					_evaluationListener.onChangeFromBlankValue(sheetIndex, rowIndex,
							columnIndex, cell, fcce);
				}
				updateAnyBlankReferencingFormulas(bookIndex, sheetIndex, rowIndex,
						columnIndex);
			}
			_formulaCellCache.put(cell, fcce);
		} else {
			fcce.recurseClearCachedFormulaResults(_evaluationListener);
			fcce.clearFormulaEntry();
		}
		if (pcce == null) {
			// was formula cell before - no change of type
		} else {
			// changing from plain cell to formula cell
			pcce.recurseClearCachedFormulaResults(_evaluationListener);
			_plainCellCache.remove(loc);
		}
	} else {
		ValueEval value = WorkbookEvaluator.getValueFromNonFormulaCell(cell);
		if (pcce == null) {
			if (value != BlankEval.instance) {
				// only cache non-blank values in the plain cell cache
				// (dependencies on blank cells are managed by
				// FormulaCellCacheEntry._usedBlankCellGroup)
				pcce = new PlainValueCellCacheEntry(value);
				if (fcce == null) {
					if (_evaluationListener != null) {
						_evaluationListener.onChangeFromBlankValue(sheetIndex, rowIndex, columnIndex, cell, pcce);
					}
					updateAnyBlankReferencingFormulas(bookIndex, sheetIndex,
							rowIndex, columnIndex);
				}
				_plainCellCache.put(loc, pcce);
			}
		} else {
			if (pcce.updateValue(value)) {
				pcce.recurseClearCachedFormulaResults(_evaluationListener);
			}
			if (value == BlankEval.instance) {
				_plainCellCache.remove(loc);
			}
		}
		if (fcce == null) {
			// was plain cell before - no change of type
		} else {
			// was formula cell before - now a plain value
			_formulaCellCache.remove(cell);
			fcce.setSensitiveInputCells(null);
			fcce.recurseClearCachedFormulaResults(_evaluationListener);
		}
	}
}
 

/**
 * 获取单元格值
 * 
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column)
{
    if (row == null)
    {
        return row;
    }
    Object val = "";
    try
    {
        Cell cell = row.getCell(column);
        if (StringUtils.isNotNull(cell))
        {
            if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
            {
                val = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell))
                {
                    val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                }
                else
                {
                    if ((Double) val % 1 > 0)
                    {
                        val = new DecimalFormat("0.00").format(val);
                    }
                    else
                    {
                        val = new DecimalFormat("0").format(val);
                    }
                }
            }
            else if (cell.getCellTypeEnum() == CellType.STRING)
            {
                val = cell.getStringCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
            {
                val = cell.getBooleanCellValue();
            }
            else if (cell.getCellTypeEnum() == CellType.ERROR)
            {
                val = cell.getErrorCellValue();
            }

        }
    }
    catch (Exception e)
    {
        return val;
    }
    return val;
}
 

/**
 * If cell contains formula, it evaluates the formula,
 *  and saves the result of the formula. The cell
 *  remains as a formula cell.
 * Else if cell does not contain formula, this method leaves
 *  the cell unchanged.
 * Note that the type of the formula result is returned,
 *  so you know what kind of value is also stored with
 *  the formula.
 * <pre>
 * CellType evaluatedCellType = evaluator.evaluateFormulaCellEnum(cell);
 * </pre>
 * Be aware that your cell will hold both the formula,
 *  and the result. If you want the cell replaced with
 *  the result of the formula, use {@link #evaluate(org.apache.poi.ss.usermodel.Cell)} }
 * @param cell The cell to evaluate
 * @return The type of the formula result (the cell's type remains as CellType.FORMULA however)
 *         If cell is not a formula cell, returns {@link CellType#_NONE} rather than throwing an exception.
 * @since POI 3.15 beta 3
 */
@Override
public CellType evaluateFormulaCellEnum(Cell cell) {
    if (cell == null || cell.getCellTypeEnum() != CellType.FORMULA) {
        return CellType._NONE;
    }
    CellValue cv = evaluateFormulaCellValue(cell);
    // cell remains a formula cell, but the cached value is changed
    setCellValue(cell, cv);
    return cv.getCellTypeEnum();
}