Example usage for org.apache.poi.ss.usermodel Cell getCellFormula

List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getCellFormula

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getCellFormula.

Prototype

String getCellFormula();

Source Link

Document

Return a formula for the cell, for example, SUM(C4:E4)

Usage

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java

License:Open Source License

/**
 * get the attribute from the cell. Do basic type conversions.
 * //from   w  w w .j a va  2 s.  c  om
 * @param cell the cell to read
 * @param convertNumberToDate if true, AND if the cell type is numeric, convert the cell value to Date; use the 
 *        numeric value otherwise.
 * 
 * @return the value from the cell, or null if cell is empty.
 */
@SuppressWarnings({ "PMD.MissingBreakInSwitch", "boxing" })
public static Object getCellValue(Cell cell, boolean convertNumberToDate) {

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (convertNumberToDate) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getStringCellValue());
    case Cell.CELL_TYPE_ERROR:
        LOGGER.error("Error in cell {0}: contains Error", ExcelUtils.getFullCellName(cell));
        return null;
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    default: // impossible.
        LOGGER.error("Error in cell {0}: contains unknown cell type.", ExcelUtils.getFullCellName(cell));
        return null;
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java

License:Open Source License

/**
 * Returns cell content or cell content being referenced in a formula as String. Also reads
 * numeric cells, but these must not be referenced (a poi formulaCell). A cell being NULL or empty
 * returns "". For formulaCells that are not string Formulas, an empty String is returned, too.
 * Directly using poi's getRichStringCellValue() on a HSSFCell being NULL would throw an
 * exception. Cell in row is specified by the headline map and the key. Warning: Does not support
 * Dates (see currentRowCoreKeyToCell)// ww  w  .  ja  va  2  s.co m
 * 
 * @param cell
 *          a cell
 * @return a String from cell or an referenced cell, leading and ending white space removed
 */
public static String contentAsString(Cell cell, ProcessingLog processingLog) {
    if (cell == null) {
        return "";
    }
    switch (cell.getCellType()) {

    case Cell.CELL_TYPE_STRING: // for richStringsCells and formulaCells
        return cell.getRichStringCellValue().getString().trim();

    case Cell.CELL_TYPE_FORMULA: // for formulaCells / Hyperlinks(id)
        int type = cell.getCachedFormulaResultType();
        String result = "";
        switch (type) {
        case Cell.CELL_TYPE_BOOLEAN:
            result = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            result = String.valueOf(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            result = String.valueOf(cell.getRichStringCellValue());
            break;
        default:
            break;
        }
        if (result.isEmpty()) {
            processingLog.warn(
                    "Cell [{0}]  Cannot get a String from a numeric cell being referenced by a formula: {1}",
                    getCellRef(cell), cell.getCellFormula());
        }
        return result;

    case Cell.CELL_TYPE_NUMERIC:
        return getNumericCellContentAsString(cell, processingLog);

    default:
        return cell.toString().trim();
    }

}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ImportWorkbook.java

License:Open Source License

/**
 * Initially reads the complete Excel workbook and calculates all formulaCells. After that, we can
 * be sure that no cell contains formulas any longer, but they all contain values (or nothing).
 *///from  w  ww  . j av a  2  s  .  c o  m
protected void calculateAllFormulas() {
    FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) getWb());

    // iterate over all sheets, their rows, and all their cells
    for (int sheetNum = 0; sheetNum < getWb().getNumberOfSheets(); sheetNum++) {
        Sheet sheet = getWb().getSheetAt(sheetNum);

        for (Row row : sheet) {
            for (Cell cell : row) {

                if ((cell.getCellType() == Cell.CELL_TYPE_FORMULA) && (cell.getCellFormula() != null)) {
                    try {
                        evaluator.evaluateInCell(cell);
                    } catch (Exception e) {
                        getProcessingLog().error("Error calculating the formula in cell ["
                                + ExcelImportUtilities.getCellRef(cell) + "] on sheet '" + sheet.getSheetName()
                                + "', using cached value instead: " + e.getLocalizedMessage());
                    }
                }
            }
        }
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

public String getFormularCellValue(int columnIndex, boolean nullable) throws Exception {
    String value = null;/*from w ww  .j  a  v a  2 s .  co m*/
    Cell cell = getCell(columnIndex);
    if (cell == null) {
        if (nullable == false) {
            throw new Exception("Cell in column " + columnIndex + " has no value!");
        }
        return null;
    } else {
        value = cell.getCellFormula();
    }
    return value;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetReferencedCellInput.java

License:Apache License

private boolean fetchCurrentCellValue(Cell cell) {
    if (cell != null) {
        currentCell = cell;//w w  w .jav  a  2  s  .  c  om
        currentCellValueString = getStringCellValue(cell);
        Comment comment = cell.getCellComment();
        if (comment != null) {
            currentCellComment = comment.getString().getString();
            currentCellCommentAuthor = comment.getAuthor();
        }
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.BLANK) {
            currentCellValueClassName = "Object";
        } else if (cellType == CellType.STRING) {
            currentCellValueClassName = "String";
            currentCellValueObject = currentCellValueString;
        } else if (cellType == CellType.BOOLEAN) {
            currentCellValueClassName = "Boolean";
            currentCellValueBool = cell.getBooleanCellValue();
            currentCellValueObject = currentCellValueBool;
        } else if (cellType == CellType.ERROR) {
            currentCellValueClassName = "Byte";
            currentCellValueObject = cell.getErrorCellValue();
        } else if (cellType == CellType.FORMULA) {
            currentCellValueClassName = "String";
            currentCellFormula = cell.getCellFormula();
            currentCellValueString = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
            currentCellValueObject = currentCellValueString;
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                currentCellValueClassName = "java.util.Date";
                currentCellValueDate = cell.getDateCellValue();
                currentCellValueObject = currentCellValueDate;
            } else {
                currentCellValueClassName = "Double";
                currentCellValueNumber = cell.getNumericCellValue();
                currentCellValueObject = currentCellValueNumber;
            }
        }
        currentCellBgColor = getBgColor(cell);
        currentCellFgColor = getFgColor(cell);
        return currentCellValueObject != null;
    } else {
        return false;
    }
}

From source file:egovframework.rte.fdl.excel.util.EgovExcelUtil.java

License:Apache License

/**
 * ? ? String   ./*ww  w .  j a v  a2 s . c  om*/
 * 
 * @param cell <code>Cell</code>
 * @return  
 */
public static String getValue(Cell cell) {

    String result = "";

    if (null == cell || cell.equals(null)) {
        return "";
    }

    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        LOGGER.debug("### Cell.CELL_TYPE_BOOLEAN : {}", Cell.CELL_TYPE_BOOLEAN);
        result = String.valueOf(cell.getBooleanCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
        LOGGER.debug("### Cell.CELL_TYPE_ERROR : {}", Cell.CELL_TYPE_ERROR);
        // byte errorValue =
        // cell.getErrorCellValue();

    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        LOGGER.debug("### Cell.CELL_TYPE_FORMULA : {}", Cell.CELL_TYPE_FORMULA);

        String stringValue = null;
        String longValue = null;

        try {
            stringValue = cell.getRichStringCellValue().getString();
            longValue = doubleToString(cell.getNumericCellValue());
        } catch (Exception e) {
            LOGGER.debug("{}", e);
        }

        if (stringValue != null) {
            result = stringValue;
        } else if (longValue != null) {
            result = longValue;
        } else {
            result = cell.getCellFormula();
        }

    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        LOGGER.debug("### Cell.CELL_TYPE_NUMERIC : {}", Cell.CELL_TYPE_NUMERIC);

        result = DateUtil.isCellDateFormatted(cell)
                ? EgovDateUtil.toString(cell.getDateCellValue(), "yyyy/MM/dd", null)
                : doubleToString(cell.getNumericCellValue());

    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        LOGGER.debug("### Cell.CELL_TYPE_STRING : {}", Cell.CELL_TYPE_STRING);
        result = cell.getRichStringCellValue().getString();

    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        LOGGER.debug("### Cell.CELL_TYPE_BLANK : {}", Cell.CELL_TYPE_BLANK);
    }

    return result;
}

From source file:gov.nih.nci.cananolab.util.ExcelParser.java

License:BSD License

public void printSheet(Sheet sheet) {
    for (Row row : sheet) {
        for (Cell cell : row) {
            CellReference cellRef = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
            System.out.print(cellRef.formatAsString());
            System.out.print(" - ");

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                System.out.println(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.println(cell.getDateCellValue());
                } else {
                    System.out.println(cell.getNumericCellValue());
                }/*from   www  .j a  v  a  2  s.  co  m*/
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.println(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                System.out.println(cell.getCellFormula());
                break;
            default:
                System.out.println();
            }
        }
    }
}

From source file:gov.nih.nci.evs.app.neopl.XLStoXLSX.java

License:Open Source License

/**
 * @param args//from  ww w. j  a  v  a  2s. c  o  m
 * @throws InvalidFormatException
 * @throws IOException
 */

public static void run(String inputfile, String outputfile) throws IOException {
    InputStream in = new BufferedInputStream(new FileInputStream(inputfile));
    try {
        Workbook wbIn = new HSSFWorkbook(in);
        File outFn = new File(outputfile);
        if (outFn.exists()) {
            outFn.delete();
        }

        Workbook wbOut = new XSSFWorkbook();
        int sheetCnt = wbIn.getNumberOfSheets();
        for (int i = 0; i < sheetCnt; i++) {
            Sheet sIn = wbIn.getSheetAt(0);
            Sheet sOut = wbOut.createSheet(sIn.getSheetName());
            Iterator<Row> rowIt = sIn.rowIterator();
            while (rowIt.hasNext()) {
                Row rowIn = rowIt.next();
                Row rowOut = sOut.createRow(rowIn.getRowNum());

                Iterator<Cell> cellIt = rowIn.cellIterator();
                while (cellIt.hasNext()) {
                    Cell cellIn = cellIt.next();
                    Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType());

                    switch (cellIn.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;

                    case Cell.CELL_TYPE_BOOLEAN:
                        cellOut.setCellValue(cellIn.getBooleanCellValue());
                        break;

                    case Cell.CELL_TYPE_ERROR:
                        cellOut.setCellValue(cellIn.getErrorCellValue());
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        cellOut.setCellFormula(cellIn.getCellFormula());
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        cellOut.setCellValue(cellIn.getNumericCellValue());
                        break;

                    case Cell.CELL_TYPE_STRING:
                        cellOut.setCellValue(cellIn.getStringCellValue());
                        break;
                    }

                    {
                        CellStyle styleIn = cellIn.getCellStyle();
                        CellStyle styleOut = cellOut.getCellStyle();
                        styleOut.setDataFormat(styleIn.getDataFormat());
                    }
                    cellOut.setCellComment(cellIn.getCellComment());

                    // HSSFCellStyle cannot be cast to XSSFCellStyle
                    // cellOut.setCellStyle(cellIn.getCellStyle());
                }
            }
        }
        OutputStream out = new BufferedOutputStream(new FileOutputStream(outFn));
        try {
            wbOut.write(out);
        } finally {
            out.close();
        }
    } finally {
        in.close();
    }
}

From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java

License:Apache License

private static String toString(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() + "";

    case Cell.CELL_TYPE_NUMERIC:
        return cell.getNumericCellValue() + "";

    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

    case Cell.CELL_TYPE_ERROR:
        return "_ERROR_ " + cell.getErrorCellValue();

    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula() + "";
    default:/*from  w w w . j  av  a 2  s.co m*/
        throw new RuntimeException("No toString is available for the cell type!");
    }
}

From source file:graphbuilder.ExcelParser.java

private static Object loadCellData(Cell cell) {
    Object result = null;/*from w w w. j  a  v a  2s.  co m*/
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        result = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            result = cell.getDateCellValue();
        } else {
            result = cell.getNumericCellValue();
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;
    }
    return result;
}