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

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

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

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

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

License:Apache License

private Boolean getBooleanCellValue(Cell cell) throws Exception {
    Boolean value = null;//from   w w  w .  ja va  2s .c o m
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                value = toBool(s);
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = cell.getStringCellValue();
                        value = toBool(s);
                    } else if (cellType == CellType.NUMERIC) {
                        double s = cell.getNumericCellValue();
                        value = toBool(s);
                    } else if (cellType == CellType.BOOLEAN) {
                        value = cell.getBooleanCellValue();
                    }
                }
            }
        } else if (cellType == CellType.STRING) {
            String s = cell.getStringCellValue();
            value = toBool(s);
        } else if (cellType == CellType.NUMERIC) {
            double s = cell.getNumericCellValue();
            value = toBool(s);
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue();
        }
    }
    return value;
}

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

License:Apache License

private Date getDateCellValue(Cell cell, String pattern) throws Exception {
    Date value = null;//from w w  w. ja  va 2s  . c  o  m
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                return parseDate(s, pattern);
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = cell.getStringCellValue();
                        value = parseDate(s, pattern);
                    } else if (cellType == CellType.NUMERIC) {
                        value = cell.getDateCellValue();
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell) && parseDateFromVisibleString == false) {
                value = cell.getDateCellValue();
            } else {
                String s = getDataFormatter().formatCellValue(cell);
                value = parseDate(s, pattern);
            }
        } else if (cellType == CellType.STRING) {
            String s = getDataFormatter().formatCellValue(cell);
            value = parseDate(s, pattern);
        }
    }
    if (returnZeroDateAsNull && GenericDateUtil.isZeroDate(value)) {
        value = null;
    }
    return value;
}

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

License:Apache License

public void configColumnPositions() throws Exception {
    headerRow = sheet.getRow(headerRowIndex);
    int lastCellNum = headerRow.getLastCellNum();
    int firstCellNum = headerRow.getFirstCellNum();
    for (int i = firstCellNum; i <= lastCellNum; i++) {
        Cell cell = headerRow.getCell(i);
        if (cell != null) {
            CellType cellType = cell.getCellTypeEnum();
            if (cellType == CellType.STRING) {
                String name = cell.getStringCellValue();
                if (name != null && name.trim().isEmpty() == false) {
                    namesFromHeaderRow.put(name.trim().toLowerCase(), i);
                }/* w w  w  .  j av  a2  s. c om*/
            }
        }
    }
    for (Map.Entry<Integer, String> nameFromSchema : namesFromSchema.entrySet()) {
        Boolean ignoreMissing = ignoreMissingMap.get(nameFromSchema.getKey());
        if (ignoreMissing == null) {
            ignoreMissing = false;
        }
        Integer targetIndex = findPosition(nameFromSchema.getValue());
        if (targetIndex != null) {
            columnIndexes.put(nameFromSchema.getKey(), targetIndex);
            individualColumnMappingUsed = true;
        } else if (ignoreMissing) {
            missingColumns.add(nameFromSchema.getKey());
        } else {
            if (findHeaderPosByRegex) {
                throw new Exception(
                        "Column with pattern: " + nameFromSchema.getValue() + " does not exists in header!");
            } else {
                throw new Exception(
                        "Column with name: " + nameFromSchema.getValue() + " does not exists in header!");
            }
        }
    }
}

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

License:Apache License

private String getStringCellValue(Cell cell) {
    String value = null;/*  w w w  .  j  a va2s .com*/
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
        } else if (cellType == CellType.STRING) {
            if (returnURLInsteadOfName) {
                Hyperlink link = cell.getHyperlink();
                if (link != null) {
                    if (concatenateLabelUrl) {
                        String url = link.getAddress();
                        if (url == null) {
                            url = "";
                        }
                        String label = link.getLabel();
                        if (label == null) {
                            label = "";
                        }
                        value = label + "|" + url;
                    } else {
                        value = link.getAddress();
                    }
                } else {
                    value = cell.getStringCellValue();
                }
            } else {
                value = cell.getStringCellValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                Date d = cell.getDateCellValue();
                value = defaultDateFormat.format(d);
            } else {
                value = numberFormat.format(cell.getNumericCellValue());
            }
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue() ? "true" : "false";
        } else if (cellType == CellType.BLANK) {
            value = null;
        }
    }
    return value;
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportToManyRelation() throws Exception {
    File tempFile = File.createTempFile("taskExportTest", ".xlsx");
    EntityExportSource<Task> tasks = new EntityExportSource<>(PersistentWork.idsFrom(Task.class), Task.class);
    EntityExportSource<Tag> tags = new EntityExportSource<>(PersistentWork.idsFrom(Tag.class), Tag.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, tasks, tags);

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet taskSheet = wb.getSheet(Task.class.getName());
    Sheet tagSheet = wb.getSheet(Tag.class.getName());
    assertNotNull(taskSheet);// w  w  w  .  j  a v  a  2  s  .  co  m
    assertNotNull(tagSheet);

    Row firstRow = taskSheet.getRow(0);
    int pos = 0;
    Iterator<Cell> cellIterator = firstRow.cellIterator();

    String property = PropertyPath.property(Task.class, t -> t.getTags());
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        if (cell.getStringCellValue().equals(property)) {
            break;
        }
        pos++;
    }
    assertNotEquals(Task.class.getSimpleName() + "." + property + " not exported", firstRow.getLastCellNum(),
            pos);

    Cell cell = taskSheet.getRow(1).getCell(pos);
    String[] split = StringUtils.split(cell.getStringCellValue(), ToManyColumn.SEPARATOR);
    assertEquals(2, split.length);
    assertTrue(Arrays.asList(split).contains("tag" + ToManyColumn.SEPARATOR_REPLACEMENT + "1"));
    assertTrue(Arrays.asList(split).contains("tag2"));
}

From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java

License:Apache License

/** Calibrate the {@link MeasureEvaluation}s. */
private void importMeasureEvaluationCalibration(Sheet sheet, IProgressMonitor monitor) {
    Row row = sheet.getRow(5);/*from ww  w. j a va2  s  .  c om*/
    for (Iterator<Cell> i = row.cellIterator(); i.hasNext();) {
        Cell cell = i.next();
        int column = cell.getColumnIndex();
        if (column < 1) {
            continue;
        }
        String id = cell.getStringCellValue();
        EObject evaluation = getEvaluation(id);
        if (evaluation instanceof SingleMeasureEvaluation) {
            SingleMeasureEvaluation measureEvaluation = (SingleMeasureEvaluation) evaluation;
            importFunction(sheet, column, measureEvaluation);
        } else {
            WeightedSumMultiMeasureEvaluation measureEvaluation = (WeightedSumMultiMeasureEvaluation) evaluation;
            if (measureEvaluation == null) {
                System.err.println("Evaluation not found. Model and Excel file are inconsistent.");
            } else {
                String measureName = sheet.getRow(1).getCell(column).getStringCellValue();
                MeasureRanking measureRanking = getMeasureRanking(measureEvaluation, measureName);

                importFunction(sheet, column, measureRanking);
            }
        }
        monitor.worked(1);
    }
}

From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java

License:Apache License

/** Calibrate a {@link FactorAggregation}. */
private void importFactorAggregationCalibration(Sheet sheet, EAttribute attribute, IProgressMonitor monitor) {
    Row mainRow = sheet.getRow(0);//  w ww .  j av a  2 s. c o m
    for (Iterator<Cell> i = mainRow.cellIterator(); i.hasNext();) {
        Cell cell = i.next();
        int column = cell.getColumnIndex();
        if (column < 1) {
            continue;
        }
        String id = cell.getStringCellValue();
        EObject evaluation = getEvaluation(id);
        if (evaluation instanceof WeightedSumFactorAggregation) {
            WeightedSumFactorAggregation factorAggregation = (WeightedSumFactorAggregation) evaluation;
            importWeightedSumFactorAggregation(sheet, factorAggregation, attribute, column);
        } else {
            WeightedSumMultiMeasureEvaluation multiMeasureEvaluation = (WeightedSumMultiMeasureEvaluation) evaluation;
            importWeightedSumMultiMeasureEvaluation(sheet, multiMeasureEvaluation, attribute, column);
        }
        monitor.worked(1);
    }
}

From source file:de.topicmapslab.jexc.eXql.grammar.expression.FunctionExpression.java

License:Apache License

/**
 * Interpretation method for value function
 * /*from w  ww .  j a  v a  2 s.com*/
 * @param workBook
 *            the workbook
 * @param row
 *            the row
 * @return the result value of the cell addressed by internal value
 *         expressions
 * @throws JeXcException
 *             thrown if operation fails
 */
private Object interpretValueFunction(Workbook workBook, Row row) throws JeXcException {
    if (getExpressions().size() != 2) {
        throw new JeXcException("Invalid number of contained value expression, expects 2 but was "
                + getExpressions().size() + ".");
    }
    Object oRowIndex = getExpressions().get(0).interpret(workBook, row);
    Object oColumnIndex = getExpressions().get(1).interpret(workBook, row);
    BigInteger rowIndex = LiteralUtils.asInteger(oRowIndex.toString());
    BigInteger columnIndex = LiteralUtils.asInteger(oColumnIndex.toString());
    Sheet sheet = row.getSheet();
    Row r = sheet.getRow(rowIndex.intValue());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(columnIndex.intValue());
    if (c == null) {
        return null;
    }
    if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        Double d = c.getNumericCellValue();
        Long l = d.longValue();
        /*
         * check if long value represents the same numeric value then the
         * double origin
         */
        if (d.doubleValue() == l.longValue()) {
            return String.valueOf(l);
        }
        return String.valueOf(d);
    } else if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        return c.getStringCellValue();
    } else if (c.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        return c.getBooleanCellValue();
    }
    return c.getStringCellValue();
}

From source file:de.topicmapslab.jexc.eXql.grammar.expression.ValueExpression.java

License:Apache License

/**
 * Returns the cell value represent by the given token
 * //from   w  ww.  j ava  2s. c  om
 * @param cell
 *            the cell to extract the values from cell
 * @param token
 *            the token specifies the value to extract
 * @return the cell value
 * @throws JeXcException
 *             thrown if cell value token is unknown
 */
public Object getCellValue(final Cell cell, final String token) throws JeXcException {
    if (VALUE.equalsIgnoreCase(token) || VALUE_STRING.equalsIgnoreCase(token)) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            Double d = cell.getNumericCellValue();
            Long l = d.longValue();
            /*
             * check if long value represents the same numeric value then
             * the double origin
             */
            if (d.doubleValue() == l.longValue()) {
                return String.valueOf(l);
            }
            return String.valueOf(d);
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_STRING:
        default:
            return cell.getStringCellValue();
        }
    } else if (VALUE_DATE.equalsIgnoreCase(token)) {
        return cell.getDateCellValue();
    } else if (VALUE_NUMERICAL.equalsIgnoreCase(token)) {
        return cell.getNumericCellValue();
    } else if (STYLE_FOREGROUND.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? NULL : style.getFillForegroundColor();
    } else if (STYLE_BACKGROUND.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? NULL : style.getFillBackgroundColor();
    } else if (BORDER_TOP.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderTop();
    } else if (BORDER_BOTTOM.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderBottom();
    } else if (BORDER_LEFT.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderLeft();
    } else if (BORDER_RIGHT.equalsIgnoreCase(token)) {
        CellStyle style = cell.getCellStyle();
        return style == null ? 0 : style.getBorderRight();
    } else if (ADDRESS.equalsIgnoreCase(token)) {
        StringBuilder builder = new StringBuilder();
        builder.append(cell.getSheet().getSheetName());
        builder.append(SLASH);
        builder.append(cell.getRow().getRowNum());
        builder.append(COLON);
        builder.append(cell.getColumnIndex());
        return builder.toString();
    } else if (HEIGHT.equalsIgnoreCase(token)) {
        CellRangeAddress address = XlsxCellUtils.getCellRange(cell);
        if (address != null) {
            return address.getLastRow() - address.getFirstRow() + 1;
        }
        return 1;
    } else if (ROW.equalsIgnoreCase(token)) {
        return cell.getRowIndex();
    } else if (COLUMN.equalsIgnoreCase(token)) {
        return cell.getColumnIndex();
    }
    throw new JeXcException("Unknown constant '" + token + "'!");
}

From source file:de.topicmapslab.jexc.utility.XlsxCellUtils.java

License:Apache License

/**
 * Returns the string value of the given cell range
 * //  w w w.  ja  va  2  s.c  o  m
 * @param sheet
 *            the sheet
 * @param address
 *            the cell range address
 * @return the value
 */
public static String getCellRangeStringValue(Sheet sheet, CellRangeAddress address) {
    Cell c = sheet.getRow(address.getFirstRow()).getCell(address.getFirstColumn());
    return c.getStringCellValue();
}