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

Introduction

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

Prototype

RichTextString getRichStringCellValue();

Source Link

Document

Get the value of the cell as a XSSFRichTextString

For numeric cells we throw an exception.

Usage

From source file:Search.IDSSearchFunctionRunner.java

@Test
public void searchAction() throws FileNotFoundException, IOException, InterruptedException {

    FileInputStream file = new FileInputStream(new File("D:\\Book13.xlsx"));
    workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    CellStyle style = workbook.createCellStyle();
    Cell cell;
    Row row;/* ww w.j  a  v a2 s . com*/

    navigateToLogin();
    LoginAction loginAction = new LoginAction(driver);
    loginAction.enterUserName("maxval");
    loginAction.enterPassword("Qcom2015*");
    TermsAndCondition termsAndConditions = loginAction.loginSubmit();
    Dashboard dashboard = termsAndConditions.Accept();
    System.out.println("abcd");
    System.out.println("abcde");

    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());

    for (int rowNum = rowStart + 1; rowNum < rowEnd; rowNum++) {
        row = sheet.getRow(rowNum);
        if (row != null) {
            int columnNumber = 0;
            cell = row.getCell(columnNumber, Row.RETURN_BLANK_AS_NULL);
            try {
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    RichTextString fullTextQuery = cell.getRichStringCellValue();
                    String myQuery = fullTextQuery.toString();
                    System.out.println(myQuery);
                    SearchList searchList = new SearchList(driver);
                    if (rowNum == 1) {
                        dashboard.enterFullTextSearchQuery(myQuery);
                        searchList = dashboard.submitFullTextSearchQueryFromDashboard();
                    } else {
                        searchList.enterFullTextSearchQuery(myQuery);
                        searchList.submitFullTextSearchQueryFromSearchList();
                    }
                    String searchRecordList = searchList.getRecordIdInSearchList();
                    cell = row.createCell(2);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    style.setWrapText(true);
                    cell.setCellStyle(style);
                    cell.setCellValue(searchRecordList);
                    cell = row.getCell(1);
                    String expectedResult = cell.getStringCellValue();
                    if (expectedResult.equals(searchRecordList)) {
                        cell = row.createCell(3);
                        cell.setCellValue("Seach result matched with Expected");
                    } else {
                        cell = row.createCell(3);
                        cell.setCellValue("Seach result NOT matched with Expected");
                    }
                    try (FileOutputStream fileOut = new FileOutputStream("D:\\result.xlsx")) {
                        workbook.write(fileOut);
                    }
                }
            } catch (AssertionError Ae) {
            }
        }
    }
}

---

From source file:Servelt.ExcelReader.java

private String cellToString(Cell cell) throws Exception {
    String data = null;// w  ww  .jav a  2 s . c  o  m
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        data = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            data = cell.getDateCellValue().toString();
        } else {
            data = String.valueOf(cell.getNumericCellValue());
            while (data.endsWith("0") && data.contains(".")) {
                data = data.substring(0, data.length() - 1);
            }
            if (data.endsWith("."))
                data = data.substring(0, data.length() - 1);
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        data = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        data = cell.getCellFormula();
        break;
    case Cell.CELL_TYPE_BLANK:
        data = "";
        break;
    case Cell.CELL_TYPE_ERROR:
        throw new Exception("CELL_TYPE_ERROR");
    }
    return data;
}

---

From source file:step.datapool.excel.ExcelFunctions.java

License:Open Source License

/**
* Konvertiert unterschiedliche Formate in Strings.
* 
* @param cell Excel Zelle//from   w w  w  .ja  va2s .  c  o  m
* @param evaluator FormulaEvaluator
* @return Wert der Zelle als String
*/
public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator) {

    boolean isFormulaPatched = false;
    String initialFormula = null;

    int chkTyp = cell.getCellType();
    if (chkTyp == Cell.CELL_TYPE_FORMULA) {

        initialFormula = cell.getCellFormula();
        // Some formula have to be changed before they can be evaluated in POI
        String formula = FormulaPatch.patch(initialFormula);
        if (!formula.equals(initialFormula)) {
            isFormulaPatched = true;
            cell.setCellFormula(formula);
            evaluator.notifySetFormula(cell);
        }
    }

    try {
        int typ = evaluateFormulaCell(cell, evaluator);
        if (typ == -1)
            typ = cell.getCellType();
        switch (typ) {
        case Cell.CELL_TYPE_NUMERIC:
            /* Datum und Zeit (sind auch Zahlen) */
            if (DateUtil.isCellDateFormatted(cell)) {
                Date dat = cell.getDateCellValue();
                GregorianCalendar cal = new GregorianCalendar();
                cal.setTime(dat);
                /*
                 * In Excel beginnt die Zeitrechnung am 01.01.1900. Ein Datum ist immer als
                 * double gespeichert. Dabei ist der Teil vor dem Dezimalpunkt das Datum
                 * und der Teil nach dem Dezimalpunkt die Zeit (z.B. 1.5 entspricht 01.01.1900 12:00:00).
                 * Falls der Tag 0 angegeben ist wird der Datumsanteil mit 31.12.1899 zurueck-
                 * gegeben. Erhalten wir also ein Jahr kleiner als 1900, dann haben wir eine
                 * Zeit.
                 */
                if (cal.get(Calendar.YEAR) < 1900) { // Zeitformat
                    SimpleDateFormat STD_TIM = new SimpleDateFormat("kk:mm:ss");
                    return STD_TIM.format(dat);
                }

                SimpleDateFormat STD_DAT = new SimpleDateFormat("dd.MM.yyyy");
                return STD_DAT.format(dat); // Datumsformat
            } else {
                /* int, long, double Formate */
                double dbl = cell.getNumericCellValue();
                int tryInt = (int) dbl;
                long tryLong = (long) dbl;
                if (tryInt == dbl) {
                    return new Integer(tryInt).toString(); // int-Format
                } else if (tryLong == dbl) {
                    return new Long(tryLong).toString(); // long-Format
                }

                // return new Double(dbl).toString(); // double-Format
                String numberValueString = new Double(dbl).toString(); // double-Format

                // always use decimal format
                try {
                    // scale 14 to solve problem like value 0.22 --> 0.219999999999997
                    BigDecimal roundedBigDecimal = new BigDecimal(numberValueString).setScale(14,
                            RoundingMode.HALF_UP); // use constructor BigDecimal(String)!

                    String customValueString = getCustomDecimalFormat().format(roundedBigDecimal);
                    if (!customValueString.equals(numberValueString)) {
                        logger.debug("getCellValusAsString: Changing string value of double '{}' to '{}'",
                                numberValueString, customValueString);
                        numberValueString = customValueString; // bigdecimal-format

                    }
                } catch (Exception e) {
                    logger.error("An error occurred trying to convert the cell value number to decimal format "
                            + numberValueString, e);
                }

                return numberValueString;
            }

        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());

        case Cell.CELL_TYPE_FORMULA:
            /* Dieser Fall wird jetzt nie eintreffen, da im Falle einer Formel neu die
             * Berechnung zurueckgegeben wurde, die dann einen eigenen Typ hat.
             */
            return cell.getCellFormula();

        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();

        case Cell.CELL_TYPE_BLANK:
            return "";

        case Cell.CELL_TYPE_ERROR:
            switch (cell.getErrorCellValue()) {
            case 1:
                return "#NULL!";
            case 2:
                return "#DIV/0!";
            case 3:
                return "#VALUE!";
            case 4:
                return "#REF!";
            case 5:
                return "#NAME?";
            case 6:
                return "#NUM!";
            case 7:
                return "#N/A";
            default:
                return "#ERR!";
            }

        default:
            return "ERROR: unknown Format";
        }
    } finally {
        if (isFormulaPatched) {
            cell.setCellFormula(initialFormula);
            evaluator.notifySetFormula(cell);
        }
    }

}

---

From source file:tech.tablesaw.io.xlsx.XlsxReader.java

License:Apache License

private Boolean isBlank(Cell cell) {
    switch (cell.getCellType()) {
    case STRING:/*from  w ww.  ja v a  2s.c  om*/
        if (cell.getRichStringCellValue().length() > 0) {
            return false;
        }
        break;
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue() != null
                : cell.getNumericCellValue() != 0) {
            return false;
        }
        break;
    case BOOLEAN:
        if (cell.getBooleanCellValue()) {
            return false;
        }
        break;
    case BLANK:
        return true;
    default:
        break;
    }
    return null;
}

---

From source file:tech.tablesaw.io.xlsx.XlsxReader.java

License:Apache License

private Table createTable(Sheet sheet, TableRange tableArea, XlsxReadOptions options) {
    // assume header row if all cells are of type String
    Row row = sheet.getRow(tableArea.startRow);
    List<String> headerNames = new ArrayList<>();
    for (Cell cell : row) {
        if (cell.getCellType() == CellType.STRING) {
            headerNames.add(cell.getRichStringCellValue().getString());
        } else {/* w  w w  . j av a  2 s .  co  m*/
            break;
        }
    }
    if (headerNames.size() == tableArea.endColumn - tableArea.startColumn + 1) {
        tableArea.startRow++;
    } else {
        headerNames.clear();
        for (int col = tableArea.startColumn; col <= tableArea.endColumn; col++) {
            headerNames.add("col" + col);
        }
    }
    Table table = Table.create(options.tableName());
    List<Column<?>> columns = new ArrayList<>(Collections.nCopies(headerNames.size(), null));
    for (int rowNum = tableArea.startRow; rowNum <= tableArea.endRow; rowNum++) {
        row = sheet.getRow(rowNum);
        for (int colNum = 0; colNum < headerNames.size(); colNum++) {
            Cell cell = row.getCell(colNum + tableArea.startColumn, MissingCellPolicy.RETURN_BLANK_AS_NULL);
            Column<?> column = columns.get(colNum);
            if (cell != null) {
                if (column == null) {
                    column = createColumn(headerNames.get(colNum), cell);
                    columns.set(colNum, column);
                    while (column.size() < rowNum - tableArea.startRow) {
                        column.appendMissing();
                    }
                }
                Column<?> altColumn = appendValue(column, cell);
                if (altColumn != null && altColumn != column) {
                    column = altColumn;
                    columns.set(colNum, column);
                }
            }
            if (column != null) {
                while (column.size() <= rowNum - tableArea.startRow) {
                    column.appendMissing();
                }
            }
        }
    }
    columns.removeAll(Collections.singleton(null));
    table.addColumns(columns.toArray(new Column<?>[columns.size()]));
    return table;
}

---

From source file:tech.tablesaw.io.xlsx.XlsxReader.java

License:Apache License

@SuppressWarnings("unchecked")
private Column<?> appendValue(Column<?> column, Cell cell) {
    switch (cell.getCellType()) {
    case STRING:/*from  w  w  w  .j av  a 2  s . c om*/
        column.appendCell(cell.getRichStringCellValue().getString());
        return null;
    case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            // This will return inconsistent results across time zones, but that matches Excel's behavior
            LocalDateTime localDate = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
            column.appendCell(localDate.toString());
            return null;
        } else {
            double num = cell.getNumericCellValue();
            if (column.type() == ColumnType.INTEGER) {
                Column<Integer> intColumn = (Column<Integer>) column;
                if ((int) num == num) {
                    intColumn.append((int) num);
                    return null;
                } else if ((long) num == num) {
                    Column<Long> altColumn = LongColumn.create(column.name(), column.size());
                    altColumn = intColumn.mapInto(s -> (long) s, altColumn);
                    altColumn.append((long) num);
                    return altColumn;
                } else {
                    Column<Double> altColumn = DoubleColumn.create(column.name(), column.size());
                    altColumn = intColumn.mapInto(s -> (double) s, altColumn);
                    altColumn.append(num);
                    return altColumn;
                }
            } else if (column.type() == ColumnType.LONG) {
                Column<Long> longColumn = (Column<Long>) column;
                if ((long) num == num) {
                    longColumn.append((long) num);
                    return null;
                } else {
                    Column<Double> altColumn = DoubleColumn.create(column.name(), column.size());
                    altColumn = longColumn.mapInto(s -> (double) s, altColumn);
                    altColumn.append(num);
                    return altColumn;
                }
            } else if (column.type() == ColumnType.DOUBLE) {
                Column<Double> doubleColumn = (Column<Double>) column;
                doubleColumn.append(num);
                return null;
            }
        }
        break;
    case BOOLEAN:
        if (column.type() == ColumnType.BOOLEAN) {
            Column<Boolean> booleanColumn = (Column<Boolean>) column;
            booleanColumn.append(cell.getBooleanCellValue());
            return null;
        }
    default:
        break;
    }
    return null;
}

---

From source file:ternarymovies.TernaryMovies.java

/**
 * @param args the command line arguments
 * @throws java.io.IOException// ww  w  .  j  av  a2s  .c om
 */
public static void main(String[] args) throws IOException {
    //Calls the years from the XML table to populate the tree
    enterValues();
    //Create a link to the xml file
    File file = new File("MoviesList.xls");
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    Cell cell;
    Sheet sheet = wb.getSheetAt(0);
    //"Try" and "catch" used to catch any invalid entries for year
    try {
        //Prompt the user to search for a movie year
        Scanner scan = new Scanner(System.in);
        System.out.print("Enter a year to search for: ");
        int searchYear = scan.nextInt();
        //Once entered, the system checks every year in the column for a match
        if (searchNodes(root, searchYear) == true) {
            for (int i = 1; i < 21; i++) {
                cell = sheet.getRow(i).getCell(2);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                if (searchYear == Integer.parseInt(cell.getRichStringCellValue().toString())) {
                    cell = sheet.getRow(i).getCell(0);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    System.out.println("Movie Title: " + cell.getRichStringCellValue().toString());
                    cell = sheet.getRow(i).getCell(1);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    System.out.println("Director: " + cell.getRichStringCellValue().toString());
                    cell = sheet.getRow(i).getCell(2);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    System.out.println("Year Filmed: " + cell.getRichStringCellValue().toString() + "\n");
                }
            }
        } else {
            System.out.println("Not Found");
        }
    } catch (Exception e) {
        System.err.print("Invalid entry" + "\n");
    }
}

---

From source file:ternarymovies.TernaryMovies.java

private static void enterValues() throws IOException {
    //Reads the file name
    File file = new File("MoviesList.xls");

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    Sheet sheet = wb.getSheetAt(0);//from   ww  w  .j ava 2  s  .c  o m
    HSSFRow row;
    Cell cell;
    //Number of rows
    int rows;
    rows = sheet.getPhysicalNumberOfRows();
    //Sets the number of columns
    int cols = 0;
    //Use a for loop to insert each year
    for (int i = 1; i < 21; i++) {
        cell = sheet.getRow(i).getCell(2);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        insert(Integer.parseInt(cell.getRichStringCellValue().toString()));
    }

}

---

From source file:util.POIUtils.java

License:LGPL

/**
 * Gets the string value of a cell.// w  ww .  j a v  a2s  .c om
 *
 * @param cell the cell to get the string value of
 * @return the string value of the specified cell
 */
private static String getStringValue(Cell cell) {

    if (cell != null) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            return formatNumber(cell.getNumericCellValue());
        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        default:
            return StringUtils.stripToNull(cell.getRichStringCellValue().getString());
        }
    }
    return null;
}

---

From source file:Utilities.ExcelTools.java

public static int[] getColumnIndices(int colStart, int colEnd, Row row) {
    int[] indices = new int[4];
    Tools.setArray(indices, -1);//from   w w  w .j a v a 2s  . co m

    for (int i = colStart; i < colEnd; i++) {
        Cell cell = row.getCell(i);
        if (cell == null || cell.getCellType() != Cell.CELL_TYPE_STRING)
            continue;
        String colName = cell.getRichStringCellValue().toString().toLowerCase();

        if (colName.contains(FILENAME))
            indices[FILENAME_COL_INDEX] = i;
        else if (colName.contains(DSLR_CELLSCOPE))
            indices[DSLR_CELLSCOPE_COL_INDEX] = i;
        else if (colName.contains(HDR))
            indices[HDR_COL_INDEX] = i;
        else if (colName.contains(PLUS_ONE))
            indices[PLUS_ONE_COL_INDEX] = i;
    }

    return indices;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• »