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:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_/*  ww w.ja  v a  2 s. c om*/
 *
 * @param request _more_
 * @param entry _more_
 * @param suffix _more_
 * @param inputStream _more_
 * @param visitInfo _more_
 * @param visitor _more_
 *
 * @throws Exception _more_
 */
private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream,
        TextReader visitInfo, TabularVisitor visitor) throws Exception {
    //        System.err.println("visitXls: making workbook");
    Workbook wb = makeWorkbook(suffix, inputStream);
    //        System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets());
    int maxRows = visitInfo.getMaxRows();
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        if (!visitInfo.okToShowSheet(sheetIdx)) {
            continue;
        }
        Sheet sheet = wb.getSheetAt(sheetIdx);
        //            System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum());
        List<List<Object>> rows = new ArrayList<List<Object>>();
        int sheetSkip = visitInfo.getSkip();
        for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows)
                && (rowIdx <= sheet.getLastRowNum()); rowIdx++) {
            if (sheetSkip-- > 0) {
                continue;
            }

            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            List<Object> cols = new ArrayList<Object>();
            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                cols.add(value);
            }

            /**
             * ** TODO
             * org.ramadda.util.text.Row row = new Row(cols);
             *
             * if ( !visitInfo.rowOk(row)) {
             *   if (rows.size() == 0) {
             *       //todo: check for the header line
             *   } else {
             *       continue;
             *   }
             * }
             */
            rows.add(cols);
        }
        if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) {
            break;
        }
    }
}

From source file:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_/*  w w  w .  jav  a 2s.  c o  m*/
 *
 * @param args _more_
 *
 * @throws Exception _more_
 */
public static void main(String[] args) throws Exception {
    Workbook wb = makeWorkbook(IOUtil.getFileExtension(args[0]), new FileInputStream(args[0]));
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        Sheet sheet = wb.getSheetAt(sheetIdx);
        System.err.println(sheet.getSheetName());
        for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            short firstCol = row.getFirstCellNum();
            int colCnt = 0;
            for (short col = firstCol; col < row.getLastCellNum(); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                if (colCnt++ > 0)
                    System.out.print(",");
                System.out.print(value);
            }
            System.out.println("");
        }
    }
}

From source file:org.ramadda.plugins.media.TabularOutputHandler.java

License:Open Source License

/**
 * _more_//from  www . j  a  v  a2 s.  co m
 *
 * @param request _more_
 * @param entry _more_
 * @param suffix _more_
 * @param inputStream _more_
 * @param visitInfo _more_
 * @param visitor _more_
 *
 * @throws Exception _more_
 */
private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream, Visitor visitInfo,
        TabularVisitor visitor) throws Exception {
    //        System.err.println("visitXls: making workbook");
    Workbook wb = makeWorkbook(suffix, inputStream);
    //        System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets());
    int maxRows = visitInfo.getMaxRows();
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        if (!visitInfo.okToShowSheet(sheetIdx)) {
            continue;
        }
        Sheet sheet = wb.getSheetAt(sheetIdx);
        //            System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum());
        List<List<Object>> rows = new ArrayList<List<Object>>();
        int sheetSkip = visitInfo.getSkip();
        for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows)
                && (rowIdx <= sheet.getLastRowNum()); rowIdx++) {
            if (sheetSkip-- > 0) {
                continue;
            }

            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            List<Object> cols = new ArrayList<Object>();
            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                cols.add(value);
            }

            /**
             * ** TODO
             * org.ramadda.util.text.Row row = new Row(cols);
             *
             * if ( !visitInfo.rowOk(row)) {
             *   if (rows.size() == 0) {
             *       //todo: check for the header line
             *   } else {
             *       continue;
             *   }
             * }
             */
            rows.add(cols);
        }
        if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) {
            break;
        }
    }
}

From source file:org.seasar.fisshplate.core.parser.handler.CellParserHandler.java

License:Apache License

private String getCellValue(CellWrapper cell) {
    Cell hssfCell = cell.getHSSFCell();
    String value = null;/*from   w  w  w  .  ja v  a 2 s.com*/
    if (hssfCell.getCellType() == Cell.CELL_TYPE_STRING) {
        value = hssfCell.getRichStringCellValue().getString();
    } else if (hssfCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        value = hssfCell.getCellFormula();
    }
    return value;
}

From source file:org.seasar.fisshplate.util.FPPoiUtil.java

License:Apache License

/**
 * ?????????/*from w w  w .  j a v  a  2 s.c  o m*/
 * @param hssfCell
 * @return ?
 */
public static Object getCellValueAsObject(Cell hssfCell) {
    if (hssfCell == null) {
        return null;
    }
    int cellType = hssfCell.getCellType();
    Object ret = null;

    switch (cellType) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        ret = getValueFromNumericCell(hssfCell);
        break;
    case HSSFCell.CELL_TYPE_STRING:
        ret = hssfCell.getRichStringCellValue().getString();
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        ret = Boolean.valueOf(hssfCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        ret = hssfCell.getCellFormula();
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        ret = new Byte(hssfCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        break;
    default:
        return null;
    }

    return ret;
}

From source file:org.specrunner.source.excel.SourceFactoryExcel.java

License:Open Source License

/**
 * Extract the value of a given cell.//from w  ww.  j  a v  a 2s  .c om
 * 
 * @param cell
 *            The cell.
 * @return The corresponding object.
 */
protected Object extractVal(Cell cell) {
    if (cell == null) {
        return "";
    }
    Object val = null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        val = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        val = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_ERROR:
        val = cell.getErrorCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        val = cell.getCellFormula();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        double d = cell.getNumericCellValue();
        String tmp = String.valueOf(d);
        if (tmp.endsWith(".0")) {
            val = tmp.substring(0, tmp.lastIndexOf('.'));
        } else {
            val = d;
        }
        break;
    case Cell.CELL_TYPE_STRING:
        val = cell.getStringCellValue();
        break;
    default:
    }
    return val;
}

From source file:org.sysmodb.CellInfo.java

License:BSD License

private void readCellValueAndType(CellType cellType, Cell cell) {
    switch (cellType) {
    case BLANK:/*from w w w  .  j a v a  2s.co  m*/
        value = "";
        type = "blank";
        break;
    case BOOLEAN:
        value = String.valueOf(cell.getBooleanCellValue());
        type = "boolean";
        break;
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            type = "datetime";
            Date dateCellValue = cell.getDateCellValue();
            value = dateFormatter.format(dateCellValue);
        } else {
            double numericValue = cell.getNumericCellValue();
            int intValue = (int) numericValue;
            if (intValue == numericValue) {
                value = String.valueOf(intValue);
            } else {
                value = String.valueOf(numericValue);
            }
            type = "numeric";
        }
        break;
    case STRING:
        value = cell.getStringCellValue();
        type = "string";
        break;
    case FORMULA:
        try {
            formula = cell.getCellFormula();
        } catch (FormulaParseException e) {

        }
        CellType resultCellType = cell.getCachedFormulaResultTypeEnum();
        readCellValueAndType(resultCellType, cell);
        break;
    default:
        value = "";
        type = "none";
        break;
    }
}

From source file:org.talend.dataprep.schema.xls.XlsUtils.java

License:Open Source License

/**
 *
 * @param cell/*from w  w w  .  j  ava 2s . c o  m*/
 * @param formulaEvaluator
 * @return return the cell value as String (if needed evaluate the existing formula)
 */
public static String getCellValueAsString(Cell cell, FormulaEvaluator formulaEvaluator) {
    if (cell == null) {
        return StringUtils.EMPTY;
    }
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
    case Cell.CELL_TYPE_ERROR:
        return "Cell Error type";
    case Cell.CELL_TYPE_FORMULA:
        try {
            return getCellValueAsString(cell, formulaEvaluator.evaluate(cell));
        } catch (Exception e) {
            // log error message and the formula
            LOGGER.warn("Unable to evaluate cell (line: {}, col: {}) with formula '{}': {}", cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getCellFormula(), e.getMessage(), e);
            return StringUtils.EMPTY;
        }
    case Cell.CELL_TYPE_NUMERIC:
        return getNumericValue(cell, null, false);
    case Cell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getStringCellValue());
    default:
        return "Unknown Cell Type: " + cell.getCellType();
    }
}

From source file:org.talend.mdm.webapp.browserecords.server.service.UploadService.java

License:Open Source License

protected String getExcelFieldValue(Cell cell) throws Exception {
    String fieldValue = null;/*  w  w  w.  j a v  a2  s.co  m*/
    int cellType = cell.getCellType();
    switch (cellType) {
    case Cell.CELL_TYPE_NUMERIC: {
        double tmp = cell.getNumericCellValue();
        fieldValue = getStringRepresentation(tmp);
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        fieldValue = cell.getRichStringCellValue().getString();
        break;
    }
    case Cell.CELL_TYPE_BOOLEAN: {
        boolean tmp = cell.getBooleanCellValue();
        if (tmp) {
            fieldValue = "true"; //$NON-NLS-1$
        } else {
            fieldValue = "false";//$NON-NLS-1$
        }
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        fieldValue = cell.getCellFormula();
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        break;
    }
    case Cell.CELL_TYPE_BLANK: {
        fieldValue = ""; //$NON-NLS-1$
    }
    default: {
    }
    }
    return fieldValue;
}

From source file:org.tiefaces.components.websheet.configuration.ConfigRange.java

License:MIT License

/**
 * Builds the single cell./*from   www . j  av a  2  s. co m*/
 *
 * @param cell
 *            the cell
 * @param context
 *            the context
 * @param configBuildRef
 *            the config build ref
 * @param shiftFormulaRef
 *            the shift formula ref
 */
private void buildSingleCell(final Cell cell, final Map<String, Object> context,
        final ConfigBuildRef configBuildRef, final ShiftFormulaRef shiftFormulaRef) {
    try {
        CommandUtility.evaluate(context, cell, configBuildRef.getEngine());
        if (cell.getCellTypeEnum() == CellType.FORMULA) {
            // rebuild formula if necessary for dynamic row
            String originFormula = cell.getCellFormula();
            shiftFormulaRef.setFormulaChanged(0);
            ConfigurationUtility.buildCellFormulaForShiftedRows(configBuildRef.getSheet(),
                    configBuildRef.getWbWrapper(), shiftFormulaRef, cell, cell.getCellFormula());
            if (shiftFormulaRef.getFormulaChanged() > 0) {
                configBuildRef.getCachedCells().put(cell, originFormula);
            }
        }

    } catch (Exception ex) {
        LOG.log(Level.SEVERE, "build cell ( row = " + cell.getRowIndex() + " column = " + cell.getColumnIndex()
                + " error = " + ex.getLocalizedMessage(), ex);
    }
}