Example usage for org.apache.poi.ss.usermodel CellValue getStringValue

Introduction

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

Prototype

public String getStringValue() 

Source Link

Usage

From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java

License:Open Source License

public Column buildBooleanColumn() {
    boolean[] colValues = new boolean[values.size()];
    boolean[] missing = new boolean[values.size()];
    Iterator<CellValue> it = values.iterator();
    int counter = 0;
    while (it.hasNext()) {
        CellValue cv = it.next();
        if (cv == null) {
            missing[counter] = true;//from w  w w .  java2 s.c o  m
        } else {
            switch (detectedTypes.get(counter)) {
            case Boolean:
                colValues[counter] = cv.getBooleanValue();
                break;
            case Numeric:
                if (forceConversion) {
                    colValues[counter] = cv.getNumberValue() > 0;
                } else {
                    missing[counter] = true;
                }
                break;
            case String:
                if (forceConversion) {
                    colValues[counter] = Boolean.parseBoolean(cv.getStringValue().toLowerCase());
                } else {
                    missing[counter] = true;
                }
                break;
            case DateTime:
                missing[counter] = true;
                addWarning("Cell " + CellUtils.formatAsString(cells.get(counter))
                        + " cannot be converted from DateTime to Boolean - returning NA");
                break;
            default:
                throw new IllegalArgumentException("Unknown data type detected!");
            }
        }
        ++counter;
    }
    return new Column(colValues, missing, DataType.Boolean);
}

---

From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java

License:Open Source License

public Column buildDateTimeColumn() {
    Date[] colValues = new Date[values.size()];
    boolean[] missing = new boolean[values.size()];
    Iterator<CellValue> it = values.iterator();
    Iterator<Cell> jt = cells.iterator();
    int counter = 0;
    while (it.hasNext()) {
        CellValue cv = it.next();
        Cell cell = jt.next();// ww w . j  a  va  2s  .c  om
        if (cv == null) {
            missing[counter] = true;
        } else {
            switch (detectedTypes.get(counter)) {
            case Boolean:
                missing[counter] = true;
                addWarning("Cell " + CellUtils.formatAsString(cells.get(counter))
                        + " cannot be converted from Boolean to DateTime - returning NA");
                break;
            case Numeric:
                if (forceConversion) {
                    if (DateUtil.isValidExcelDate(cv.getNumberValue())) {
                        colValues[counter] = DateUtil.getJavaDate(cv.getNumberValue());
                    } else {
                        missing[counter] = true;
                        addWarning("Cell " + CellUtils.formatAsString(cells.get(counter))
                                + " cannot be converted from Numeric to DateTime - returning NA");
                    }
                } else {
                    missing[counter] = true;
                }
                break;
            case String:
                if (forceConversion) {
                    try {
                        colValues[counter] = Workbook.dateTimeFormatter.parse(cv.getStringValue(),
                                dateTimeFormat);
                    } catch (Exception e) {
                        missing[counter] = true;
                        addWarning("Cell " + CellUtils.formatAsString(cells.get(counter))
                                + " cannot be converted from String to DateTime - returning NA");
                    }
                } else {
                    missing[counter] = true;
                }
                break;
            case DateTime:
                colValues[counter] = cell.getDateCellValue();
                break;
            default:
                throw new IllegalArgumentException("Unknown data type detected!");
            }
        }
        ++counter;
    }
    return new Column(colValues, missing, DataType.DateTime);
}

---

From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java

License:Open Source License

public Column buildNumericColumn() {
    double[] colValues = new double[values.size()];
    boolean[] missing = new boolean[values.size()];
    Iterator<CellValue> it = values.iterator();
    int counter = 0;
    while (it.hasNext()) {
        CellValue cv = it.next();
        if (cv == null) {
            missing[counter] = true;/*from   w w w . ja va 2  s  .c  o  m*/
        } else {
            switch (detectedTypes.get(counter)) {
            case Boolean:
                colValues[counter] = cv.getBooleanValue() ? 1.0 : 0.0;
                break;
            case Numeric:
                colValues[counter] = cv.getNumberValue();
                break;
            case String:
                if (forceConversion) {
                    try {
                        colValues[counter] = Double.parseDouble(cv.getStringValue());
                    } catch (NumberFormatException e) {
                        missing[counter] = true;
                        addWarning("Cell " + CellUtils.formatAsString(cells.get(counter))
                                + " cannot be converted from String to Numeric - returning NA");
                    }
                } else {
                    missing[counter] = true;
                }
                break;
            case DateTime:
                if (forceConversion) {
                    colValues[counter] = cv.getNumberValue();
                } else {
                    missing[counter] = true;
                }
                break;
            default:
                throw new IllegalArgumentException("Unknown data type detected!");
            }
        }
        ++counter;
    }
    return new Column(colValues, missing, DataType.Numeric);
}

---

From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java

License:Open Source License

public Column buildStringColumn() {
    String[] colValues = new String[values.size()];
    boolean[] missing = new boolean[values.size()];
    Iterator<CellValue> it = values.iterator();
    Iterator<Cell> jt = cells.iterator();
    DataFormatter fmt = new DataFormatter();
    int counter = 0;
    while (it.hasNext()) {
        CellValue cv = it.next();
        Cell cell = jt.next();/*from  w w w.  j  av a 2  s .c  om*/
        if (cv == null) {
            missing[counter] = true;
        } else {
            switch (detectedTypes.get(counter)) {
            case Boolean:
            case Numeric:
                // format according to Excel format
                colValues[counter] = fmt.formatCellValue(cell, this.evaluator);
                break;
            case DateTime:
                // format according to dateTimeFormatter
                colValues[counter] = Workbook.dateTimeFormatter
                        .format(DateUtil.getJavaDate(cv.getNumberValue()), dateTimeFormat);
                break;
            case String:
                colValues[counter] = cv.getStringValue();
                break;
            default:
                throw new IllegalArgumentException("Unknown data type detected!");
            }
        }
        ++counter;
    }
    return new Column(colValues, missing, DataType.String);
}

---

From source file:com.miraisolutions.xlconnect.data.DefaultColumnBuilder.java

License:Open Source License

protected void handleCell(Cell c, CellValue cv) {
    String msg;/*from w  ww.  ja v  a2 s.  c o m*/
    // Determine (evaluated) cell data type
    switch (cv.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        addMissing();
        return;
    case Cell.CELL_TYPE_BOOLEAN:
        addValue(c, cv, DataType.Boolean);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c))
            addValue(c, cv, DataType.DateTime);
        else {
            boolean missing = false;
            for (int i = 0; i < missingValueNumbers.length; i++) {
                if (cv.getNumberValue() == missingValueNumbers[i]) {
                    missing = true;
                    break;
                }
            }
            if (missing)
                addMissing();
            else
                addValue(c, cv, DataType.Numeric);
        }
        break;
    case Cell.CELL_TYPE_STRING:
        boolean missing = false;
        for (int i = 0; i < missingValueStrings.length; i++) {
            if (cv.getStringValue() == null || cv.getStringValue().equals(missingValueStrings[i])) {
                missing = true;
                break;
            }
        }
        if (missing)
            addMissing();
        else
            addValue(c, cv, DataType.String);
        break;
    case Cell.CELL_TYPE_FORMULA:
        msg = "Formula detected in already evaluated cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
        break;
    case Cell.CELL_TYPE_ERROR:
        msg = "Error detected in cell " + CellUtils.formatAsString(c) + " - "
                + CellUtils.getErrorMessage(cv.getErrorValue());
        cellError(msg);
        break;
    default:
        msg = "Unexpected cell type detected for cell " + CellUtils.formatAsString(c) + "!";
        cellError(msg);
    }
}

---

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

private DataFrame readData(Sheet sheet, int startRow, int startCol, int nrows, int ncols, boolean header,
        ReadStrategy readStrategy, DataType[] colTypes, boolean forceConversion, String dateTimeFormat,
        boolean takeCached, int[] subset) {

    DataFrame data = new DataFrame();
    int[] colset;

    // Formula evaluator - only if we don't want to take cached values
    FormulaEvaluator evaluator = null;//from w  w w.j a v a  2s .  c  om
    if (!takeCached) {
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.clearAllCachedResultValues();
    }

    if (subset == null) {
        colset = new int[ncols];
        for (int i = 0; i < ncols; i++) {
            colset[i] = i;
        }
    } else {
        colset = subset;
    }

    ColumnBuilder cb;
    switch (readStrategy) {
    case DEFAULT:
        cb = new DefaultColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, missingValue,
                dateTimeFormat);
        break;
    case FAST:
        cb = new FastColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, dateTimeFormat);
        break;
    default:
        throw new IllegalArgumentException("Unknown read strategy!");
    }

    // Loop over columns
    for (int col : colset) {
        int colIndex = startCol + col;
        // Determine column header
        String columnHeader = null;
        if (header) {
            Cell cell = getCell(sheet, startRow, colIndex, false);
            // Check if there actually is a cell ...
            if (cell != null) {
                if (!takeCached) {
                    CellValue cv = evaluator.evaluate(cell);
                    if (cv != null)
                        columnHeader = cv.getStringValue();
                } else {
                    columnHeader = cell.getStringCellValue();
                }
            }
        }
        // If it was specified that there is a header but an empty(/non-existing)
        // cell or cell value is found, then use a default column name
        if (columnHeader == null)
            columnHeader = "Col" + (col + 1);

        // Prepare column builder for new set of rows
        cb.clear();

        // Loop over rows
        Row r;
        for (int row = header ? 1 : 0; row < nrows; row++) {
            int rowIndex = startRow + row;

            // Cell cell = getCell(sheet, rowIndex, colIndex, false);
            Cell cell = ((r = sheet.getRow(rowIndex)) == null) ? null : r.getCell(colIndex);
            cb.addCell(cell);
        }

        DataType columnType = ((colTypes != null) && (colTypes.length > 0)) ? colTypes[col % colTypes.length]
                : cb.determineColumnType();
        switch (columnType) {
        case Boolean:
            data.addColumn(columnHeader, cb.buildBooleanColumn());
            break;
        case DateTime:
            data.addColumn(columnHeader, cb.buildDateTimeColumn());
            break;
        case Numeric:
            data.addColumn(columnHeader, cb.buildNumericColumn());
            break;
        case String:
            data.addColumn(columnHeader, cb.buildStringColumn());
            break;
        default:
            throw new IllegalArgumentException("Unknown data type detected!");

        }
        // ArrayList columnValues = cb.build(columnType);
        // data.addColumn(columnHeader, columnType, columnValues);
        // Copy warnings
        for (String w : cb.retrieveWarnings())
            this.addWarning(w);
    }

    return data;
}

---

From source file:com.phucdk.emailsender.utils.ExcelUtils.java

public static String getCellValueAsString(int row, int column, XSSFWorkbook myWorkBook) {
    XSSFSheet mySheet = myWorkBook.getSheetAt(1);
    Cell cell = getCell(row, column, mySheet);
    String strCellValue = "";
    FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
    if (cell != null) {
        CellValue cellValue = null;
        try {/*w w  w. j a v a2s.  c  o m*/
            cellValue = evaluator.evaluate(cell);
        } catch (Exception ex) {
            log.error("Error when evaluate cell value", ex);
        }

        if (cellValue != null) {
            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                strCellValue = String.valueOf(cellValue.getNumberValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCellValue = String.valueOf(cellValue.getBooleanValue());
                break;
            case Cell.CELL_TYPE_STRING:
                strCellValue = String.valueOf(cellValue.getStringValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                //strCellValue = String.valueOf(cellValue.get());
                break;
            }
        }

    }
    return strCellValue;
}

---

From source file:com.phucdk.emailsender.utils.ExcelUtils.java

public static Object getCellValue(int row, int column, XSSFWorkbook myWorkBook) {
    XSSFSheet mySheet = myWorkBook.getSheetAt(1);
    Cell cell = getCell(row, column, mySheet);
    Object cellValueObject = "";
    FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
    if (cell != null) {
        CellValue cellValue = null;
        try {/*from w w  w . j  a  v a 2s  .  c om*/
            cellValue = evaluator.evaluate(cell);
        } catch (Exception ex) {
            log.error("Error when evaluate cell value", ex);
        }

        if (cellValue != null) {
            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                cellValueObject = cellValue.getNumberValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValueObject = cellValue.getBooleanValue();
                break;
            case Cell.CELL_TYPE_STRING:
                cellValueObject = cellValue.getStringValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                //strCellValue = cellValue.getErrorValue();
                break;
            }
        }

    }
    return cellValueObject;
}

---

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Returns the evaluated cell content./*  ww  w. j  a  va 2s  . com*/
 * This assumes the cell contains a formula.
 * @param cell cell to evaluate
 * @return cell value
 */
public Object evaluateCellValue(Cell cell) {
    FormulaEvaluator evaluator = getFormulaEvaluator();
    CellValue value = evaluator.evaluate(cell);
    switch (value.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return value.getStringValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return DateUtil.getJavaDate(value.getNumberValue());
        } else {
            return value.getNumberValue();
        }
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return value.getBooleanValue();
    case Cell.CELL_TYPE_ERROR:
        return value.getErrorValue();
    default:
        System.out.println("type=" + cell.getCellType());
    }
    return cell.getCellFormula();
}

---

From source file:de.bund.bfr.knime.pmm.common.XLSReader.java

License:Open Source License

private String getData(Cell cell) {
    if (cell != null) {
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            CellValue value = evaluator.evaluate(cell);

            switch (value.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return value.getBooleanValue() + "";
            case Cell.CELL_TYPE_NUMERIC:
                return value.getNumberValue() + "";
            case Cell.CELL_TYPE_STRING:
                return value.getStringValue();
            default:
                return "";
            }/*from  w w w  .  ja  v a2 s  . c  o m*/
        } else {
            return cell.toString().trim();
        }
    }

    return null;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• »