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:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ?key,?????// w w  w  .  ja v a  2 s .  c o m
 *
 * @author Zerrion
 * @date 2013-11-21
 * @param cell
 * @return
 */
private String getKeyValue(Cell cell) {
    Object obj = null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        obj = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        obj = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        obj = cell.getNumericCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        obj = cell.getCellFormula();
        break;
    }
    return obj == null ? null : obj.toString().trim();
}

From source file:com.qihang.winter.poi.util.PoiSheetUtility.java

License:Apache License

private static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;//from ww w .  ja v a2  s. com
    }
    case Cell.CELL_TYPE_NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    }

}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

private void merge(Workbook excerptWB, Workbook fullWB, String[] sheetsToMerge, OutputStream output)
        throws IOException {
    // Identify the sheets in both workbooks
    List<Sheet> sourceSheets = identifySheets(sheetsToMerge, excerptWB);
    List<Sheet> destSheets = identifySheets(sheetsToMerge, fullWB);

    // Process each sheet from the excerpt in turn
    for (int i = 0; i < sheetsToMerge.length; i++) {
        Sheet source = sourceSheets.get(i);
        Sheet dest = destSheets.get(i);//from w ww.ja va  2s .  c o  m

        for (Row srcR : source) {
            for (Cell srcC : srcR) {
                if (srcC.getCellType() == Cell.CELL_TYPE_FORMULA
                        || srcC.getCellType() == Cell.CELL_TYPE_ERROR) {
                    // Don't merge these kinds of cells
                } else {
                    Row destR = dest.getRow(srcR.getRowNum());
                    if (destR == null) {
                        // Newly added row to the excerpt file, skip this
                    } else {
                        Cell destC = destR.getCell(srcC.getColumnIndex());
                        if (destC == null && srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                            // Both are empty, don't need to do anything
                        } else {
                            if (destC == null)
                                destC = destR.createCell(srcC.getColumnIndex(), srcC.getCellType());

                            // Sync contents
                            if (srcC.getCellType() == Cell.CELL_TYPE_BLANK) {
                                destC.setCellType(Cell.CELL_TYPE_BLANK);
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                                destC.setCellValue(srcC.getBooleanCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                                destC.setCellValue(srcC.getNumericCellValue());
                            } else if (srcC.getCellType() == Cell.CELL_TYPE_STRING) {
                                destC.setCellValue(srcC.getStringCellValue());
                            }

                            // Sync formatting rules
                            // TODO
                        }
                    }
                }
            }
        }
    }

    // Re-evaluate all the formulas in the destination workbook, now that
    //  we have updated cells in it
    FormulaEvaluator eval = fullWB.getCreationHelper().createFormulaEvaluator();
    eval.evaluateAll();

    // Save the new file
    fullWB.write(output);
}

From source file:com.radaee.excel.ToHtml.java

License:Apache License

private String getText(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_BOOLEAN:
        break;/*from  w  ww  .  ja va  2 s .  c  o m*/
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    case Cell.CELL_TYPE_NUMERIC:
        String phone = Double.toString(cell.getNumericCellValue());

        String str[] = phone.split("[.]");
        phone = str[0];
        if (str[1].contains("E")) {
            String str1[] = str[1].split("[E]");
            phone += str1[0];
        }

        return phone;
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    default:
        return "";
    }
    return null;
}

From source file:com.rapidminer.operator.nio.Excel2007SheetTableModel.java

License:Open Source License

@Override
public Object getValueAt(int rowIndex, int columnIndex) {
    Cell cell;
    if (config != null) {
        Row row = sheet.getRow(rowIndex + config.getRowOffset());
        if (row == null) {
            return null;
        }//from w  w  w.j  a  v a  2  s  .c o  m
        cell = row.getCell(columnIndex + config.getColumnOffset());
    } else {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            return null;
        }
        cell = row.getCell(columnIndex);
    }
    if (cell == null) {
        return null;
    }
    if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
        return cell.getBooleanCellValue();
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
        return cell.getErrorCellValue();
    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        return cell.getNumericCellValue();
    } else {
        // last resort, should not come to this
        // maybe return null?
        return "";
    }
}

From source file:com.rapidminer.operator.nio.model.Excel2007ResultSet.java

License:Open Source License

/**
 * The constructor to build an ExcelResultSet from the given configuration. The calling operator
 * might be null. It is only needed for error handling.
 *//*from   www.  j ava2 s .c  o m*/
public Excel2007ResultSet(Operator callingOperator, ExcelResultSetConfiguration configuration)
        throws OperatorException {
    // reading configuration
    columnOffset = configuration.getColumnOffset();
    rowOffset = configuration.getRowOffset();
    currentRow = configuration.getRowOffset() - 1;

    timeZone = configuration.getTimezone();
    dateFormat = configuration.getDatePattern();
    try {
        workbookInputStream = new FileInputStream(configuration.getFile());
        workbook = WorkbookFactory.create(workbookInputStream);
    } catch (IOException | InvalidFormatException e) {
        throw new UserError(callingOperator, "file_consumer.error_loading_file");
    }

    // check range
    if (columnOffset > configuration.getColumnLast() || rowOffset > configuration.getRowLast()
            || columnOffset < 0 || rowOffset < 0) {
        throw new UserError(callingOperator, 223, Tools.getExcelColumnName(columnOffset) + rowOffset + ":"
                + Tools.getExcelColumnName(configuration.getColumnLast()) + configuration.getRowLast());
    }

    // check file presence
    if (configuration.getFile() == null) {
        throw new UserError(callingOperator, "file_consumer.no_file_defined");
    }
    try {
        sheet = configuration.selectSheetFrom(workbook);
    } catch (IndexOutOfBoundsException | IllegalArgumentException e) {
        throw new UserError(callingOperator, 953, configuration.getSheet() + 1);
    } catch (ExcelSheetSelection.SheetNotFoundException e) {
        throw new UserError(callingOperator, 321, configuration.getFile(), e.getMessage());
    }
    Row row = sheet.getRow(sheet.getFirstRowNum());
    if (row == null) {
        totalNumberOfColumns = 0;
        totalNumberOfRows = 0;
    } else {
        totalNumberOfColumns = Math.min(configuration.getColumnLast(),
                sheet.getRow(sheet.getFirstRowNum()).getLastCellNum() - 1) - columnOffset + 1;
        totalNumberOfRows = Math.min(configuration.getRowLast(), sheet.getLastRowNum()) - rowOffset + 1;
    }

    if (totalNumberOfColumns < 0 || totalNumberOfRows < 0) {
        throw new UserError(callingOperator, 404);
    }

    emptyColumns = new boolean[totalNumberOfColumns];
    emptyRows = new boolean[totalNumberOfRows];

    // filling offsets
    Arrays.fill(emptyColumns, true);
    Arrays.fill(emptyRows, true);

    // determine offsets and emptiness
    boolean foundAny = false;
    for (int r = 0; r < totalNumberOfRows; r++) {
        for (int c = 0; c < totalNumberOfColumns; c++) {
            if (emptyRows[r] || emptyColumns[c]) {
                final Row rowItem = sheet.getRow(r + rowOffset);
                if (rowItem == null) {
                    continue;
                }
                final Cell cell = rowItem.getCell(c + columnOffset);
                if (cell == null) {
                    continue;
                }
                boolean empty;
                try {
                    empty = "".equals(cell.getStringCellValue().trim());
                } catch (IllegalStateException e) {
                    empty = false;
                }
                if (!empty) {
                    foundAny = true;
                    emptyRows[r] = false;
                    emptyColumns[c] = false;
                }
            }
        }
    }
    if (!foundAny) {
        throw new UserError(callingOperator, 302, configuration.getFile().getPath(),
                "spreadsheet seems to be empty");
    }

    // retrieve attribute names: first count columns
    int numberOfAttributes = 0;
    List<Integer> nonEmptyColumnsList = new LinkedList<>();
    for (int i = 0; i < totalNumberOfColumns; i++) {
        if (!emptyColumns[i]) {
            numberOfAttributes++;
            nonEmptyColumnsList.add(i);
        }
    }

    // retrieve or generate attribute names
    attributeNames = new String[nonEmptyColumnsList.size()];

    if (!configuration.isEmulatingOldNames()) {
        for (int i = 0; i < numberOfAttributes; i++) {
            attributeNames[i] = Tools.getExcelColumnName(nonEmptyColumnsList.get(i));
        }
    } else {
        // emulate old 5.0.x style
        for (int i = 0; i < numberOfAttributes; i++) {
            if (!emptyColumns[i]) {
                attributeNames[i] = "attribute_" + i;
            }
        }
    }
}

From source file:com.rapidminer.operator.nio.model.Excel2007ResultSet.java

License:Open Source License

@Override
public boolean isMissing(int columnIndex) {
    Cell cell = getCurrentCell(columnIndex);
    try {//w  w  w  .  j  a va  2 s . c  om
        return cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK
                || cell.getCellType() == Cell.CELL_TYPE_ERROR || "".equals(cell.getStringCellValue().trim());
    } catch (IllegalStateException e) {
        return false;
    }
}

From source file:com.rapidminer.operator.nio.model.Excel2007ResultSet.java

License:Open Source License

@Override
public Number getNumber(int columnIndex) throws ParseException {
    final Cell cell = getCurrentCell(columnIndex);
    if (cell == null) {
        return Double.NaN;
    }//w ww . ja v a  2 s  .  co  m
    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA
            && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
        final double value = cell.getNumericCellValue();
        return Double.valueOf(value);
    } else {
        String valueString = "";
        try {
            valueString = cell.getStringCellValue();
            return Double.valueOf(valueString);
        } catch (NumberFormatException e) {
            throw new ParseException(new ParsingError(currentRow, columnIndex,
                    ParsingError.ErrorCode.UNPARSEABLE_REAL, valueString));
        } catch (IllegalStateException e) {
            throw new ParseException(new ParsingError(currentRow, columnIndex,
                    ParsingError.ErrorCode.UNPARSEABLE_REAL, "CELL_NOT_NUMERIC"));
        }
    }
}

From source file:com.rapidminer.operator.nio.model.Excel2007ResultSet.java

License:Open Source License

@Override
public Date getDate(int columnIndex) throws ParseException {
    final Cell cell = getCurrentCell(columnIndex);
    if (cell == null) {
        return null;
    }/*from w ww  .j  a va2s .  c  om*/
    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        return cell.getDateCellValue();
    } else {
        try {
            String valueString = cell.getStringCellValue();
            try {
                SimpleDateFormat simpleDateFormat = ParameterTypeDateFormat.createCheckedDateFormat(dateFormat,
                        null);
                simpleDateFormat.setTimeZone(TimeZone.getTimeZone(this.timeZone));
                return simpleDateFormat.parse(valueString);
            } catch (java.text.ParseException e) {
                throw new ParseException(new ParsingError(currentRow, columnIndex,
                        ParsingError.ErrorCode.UNPARSEABLE_DATE, valueString));
            } catch (UserError userError) {
                throw new ParseException(new ParsingError(currentRow, columnIndex,
                        ParsingError.ErrorCode.UNPARSEABLE_DATE, userError.getMessage()));
            }
        } catch (IllegalStateException e) {
            throw new ParseException(new ParsingError(currentRow, columnIndex,
                    ParsingError.ErrorCode.UNPARSEABLE_DATE, "CELL_NEITHER_NUMERIC_NOR_NOMINAL"));
        }
    }
}

From source file:com.rapidminer.operator.nio.model.Excel2007ResultSet.java

License:Open Source License

@Override
public String getString(int columnIndex) {
    final Cell cell = getCurrentCell(columnIndex);
    if (cell == null) {
        return "";
    }/*from  w  ww .  java  2 s  .c  o  m*/
    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        return String.valueOf(cell.getNumericCellValue());
    } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        String value;
        if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC) {
            value = String.valueOf(cell.getNumericCellValue());
        } else {
            value = cell.getStringCellValue();
        }
        return value;
    } else {
        try {
            return cell.getStringCellValue();
        } catch (IllegalStateException e) {
            return "";
        }
    }
}