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:org.is.jxlpoi.JXLPOIWorkbook.java

License:Apache License

public String getCellContentAsString(Cell c) {

    if (c == null)
        return null;

    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        if (c.getBooleanCellValue())
            return "Y";
        else//from   w ww .j a v a2  s.c  om
            return "N";
    case Cell.CELL_TYPE_NUMERIC:
        String result = "";
        int datatype = c.getCellStyle().getDataFormat();

        String formatString = c.getCellStyle().getDataFormatString();
        if (datatype == 174 && "yyyy/mm/dd".equals(formatString)) {
            java.util.Date date = c.getDateCellValue();
            return fmter.format(date);
        } else if (datatype == 49 || datatype == 0) {
            int d = (int) c.getNumericCellValue();
            result = Integer.toString(d);
        } else {
            result = Double.toString(c.getNumericCellValue());
        }

        //return Double.toString(c.getNumericCellValue());
        //System.out.println(" number = "+c.getNumericCellValue()+" *** value ="+twoPlaces.format(c.getNumericCellValue())+"");

        //return twoPlaces.format(c.getNumericCellValue())+"";
        return result;
    case Cell.CELL_TYPE_STRING:
        return c.getStringCellValue();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return "#ERROR" + c.getErrorCellValue();
    case Cell.CELL_TYPE_FORMULA:

        String formulaCellValue;

        if (formulaEvaluator == null) {
            formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            //formulaEvaluator.setIgnoreFormulaException();
            //System.out.println(formulaEvaluator);
        }

        //formulaEvaluator.evaluateFormulaCell(c);
        //formulaEvaluator.evaluateInCell(c);

        CellValue cv = formulaEvaluator.evaluate(c);

        switch (cv.getCellType()) {
        //switch (formulaEvaluator.evaluateInCell(c).getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            if (cv.getBooleanValue())
                formulaCellValue = "Y";
            else
                formulaCellValue = "F";
            break;
        case Cell.CELL_TYPE_NUMERIC:
            formulaCellValue = Double.toString(cv.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            formulaCellValue = cv.getStringValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            formulaCellValue = "";
            break;
        case Cell.CELL_TYPE_ERROR:
            formulaCellValue = Byte.toString(cv.getErrorValue());
            break;
        default:
            formulaCellValue = "";
            break;
        }//switch

        return formulaCellValue;
    default:
        return "";
    }//switch

}

---

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static Boolean getBooleanValue(HSSFFormulaEvaluator evaluator, Cell cell) {
    Boolean rvalue = false;//from   w  ww. j  a va  2 s .  c  o m

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        evaluator.evaluate(cell);
        CellValue cellValue = evaluator.evaluate(cell);

        switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            rvalue = cellValue.getBooleanValue();
            break;
        case Cell.CELL_TYPE_STRING:
            rvalue = Boolean.parseBoolean(cellValue.getStringValue());
            break;
        }
    } else {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            rvalue = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_STRING:
            rvalue = Boolean.parseBoolean(cell.getRichStringCellValue().getString());
            break;
        }
    }

    if (rvalue == null) {
        throw new IllegalArgumentException("Unexpected cell type");
    }

    return rvalue;
}

---

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static String getTextValue(HSSFFormulaEvaluator evaluator, Cell cell) {
    String rvalue = null;//from   w w  w.  ja  v  a 2 s  .  c  o  m

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
            rvalue = cellValue.getStringValue();
        }
    } else {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            rvalue = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            // If the cell type is NUMERIC, cast the value as a long and return it as a String.
            rvalue = (Long.valueOf((long) cell.getNumericCellValue())).toString();
            break;
        case Cell.CELL_TYPE_BLANK:
            rvalue = "";
        }
    }

    if (rvalue == null) {
        throw new IllegalArgumentException("Unexpected cell type");
    }

    return rvalue;
}

---

From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java

License:Open Source License

public static boolean containsWhitespace(HSSFFormulaEvaluator evaluator, Cell cell) {
    boolean containsWhitespace = false;

    String textValue = null;/*from   w w  w  .  ja  va  2  s  . c om*/

    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        CellValue cellValue = evaluator.evaluate(cell);

        if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
            textValue = cellValue.getStringValue();
        }
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        textValue = cell.getRichStringCellValue().getString();
    }

    if (textValue != null) {
        if (textValue.trim().length() == 0) {
            containsWhitespace = true;
        }
    }

    return containsWhitespace;
}

---

From source file:org.openconcerto.erp.importer.DataImporter.java

License:Open Source License

public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException {
    final InputStream inputStream = new FileInputStream(xlsFile);
    final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream));
    final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
    final HSSFSheet sheet = workBook.getSheetAt(sheetNumber);
    Iterator<Row> rowsIterator = sheet.rowIterator();
    int columnCount = 0;
    int rowCount = 0;
    while (rowsIterator.hasNext()) {
        Row row = rowsIterator.next();//from   w w w.  j  a va2 s .  c  om
        int i = row.getPhysicalNumberOfCells();
        if (i > columnCount) {
            columnCount = i;
        }
        rowCount++;
    }
    // Extract data
    rowsIterator = sheet.rowIterator();
    int start = 0;
    if (skipFirstLine) {
        start = 1;
        rowsIterator.next();
    }
    final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
    FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();

    while (rowsIterator.hasNext()) {
        final Row row = rowsIterator.next();
        final List<Object> rowData = new ArrayList<Object>();
        for (int i = 0; i < columnCount; i++) {
            final Cell cell = row.getCell(i);

            if (cell == null) {
                rowData.add("");
            } else {
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                    rowData.add("");
                } else {
                    switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(Double.valueOf(cellValue.getNumberValue()));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cellValue.getStringValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        rowData.add(cell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        rowData.add("");
                        break;
                    default:
                        rowData.add(cellValue.getStringValue());
                        break;

                    }
                }
            }
        }

        rows.add(rowData);

    }
    inputStream.close();
    return new ArrayTableModel(rows);

}

---

From source file:org.openepics.conf.dl.DataStreamExcel.java

License:Open Source License

@Override
public String getColumn(int colNum) throws Exception {
    if (currentRow == null) {
        logger.log(Level.FINE, "current row is NULL or blank");
        return "";
    }/*  w  ww  .  j a  v  a2 s .c om*/
    Cell cell = currentRow.getCell(colNum);

    String columnValue = "";
    if (cell != null) {
        // cell.setCellType(Cell.CELL_TYPE_STRING);
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_FORMULA:
            CellValue cellValue = evaluator.evaluate(cell);
            if (cellValue != null) {
                columnValue = cellValue.getStringValue();
                if (columnValue == null) {
                    columnValue = Double.toString(cellValue.getNumberValue());
                }
            }
            break;
        case Cell.CELL_TYPE_NUMERIC:
            columnValue = Double.toString(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            columnValue = cell.getStringCellValue();
            break;
        default:
            columnValue = cell.getStringCellValue();
            break;
        }
    }
    logger.log(Level.FINER, "Cell value " + columnValue);
    return columnValue;
}

---

From source file:org.openepics.discs.ccdb.core.util.ExcelCell.java

License:Open Source License

/**
 * Creating a String from Excel file cell. If cell contains numeric value, this value is cast to String.
 * If there is no value for this cell, null is returned.
 *
 * @param cell the Excel {@link Cell}/*from w  ww  .  j  a va 2s  .c o m*/
 * @param workbook the Excel {@link Workbook}
 *
 * @return the {@link String} result
 */
public static String asStringOrNull(@Nullable Cell cell, Workbook workbook) {
    final String stringValue;
    if (cell != null) {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            final double numericCellValue = cell.getNumericCellValue();
            if (numericCellValue == (int) numericCellValue) {
                stringValue = String.valueOf((int) numericCellValue);
            } else {
                stringValue = String.valueOf(numericCellValue);
            }
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            stringValue = cell.getStringCellValue() != null ? cell.getStringCellValue() : null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            stringValue = null;
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            stringValue = String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            final CellValue cellValue = evaluator.evaluate(cell);
            if (cellValue != null) {
                final String columnValue = cellValue.getStringValue();
                if (columnValue == null) {
                    stringValue = Double.toString(cellValue.getNumberValue());
                } else {
                    stringValue = columnValue;
                }
            } else {
                stringValue = null;
            }
        } else {
            throw new UnhandledCaseException();
        }
    } else {
        stringValue = null;
    }
    return stringValue;
}

---

From source file:org.paxml.table.excel.ExcelFile.java

License:Open Source License

public Object getCellValue(Cell cell) {
    CellValue cellValue = evaluator.evaluate(cell);
    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return cellValue.getBooleanValue();
    case Cell.CELL_TYPE_NUMERIC:
        return cellValue.getNumberValue();
    case Cell.CELL_TYPE_STRING:
        return cellValue.getStringValue();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return cellValue.getError(cell.getErrorCellValue()).getStringValue();
    // CELL_TYPE_FORMULA will never happen
    case Cell.CELL_TYPE_FORMULA:
        throw new PaxmlRuntimeException("Internal error: invalid case");
    default://from   w  w w. ja  va 2  s .  c  o  m
        return null;
    }
}

---

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

License:Open Source License

@Test
public void testExternRef() {
    File file = getResourceFile("Excel1.xlsx");

    WorkbookSet set = new WorkbookSet(file, null, new LinkedWorkbookFileResolver() {
        @Override//w  ww  .  ja v a2 s .c  o  m
        public File resolve(String linkedFilename) {
            return getResourceFile(linkedFilename.substring(linkedFilename.lastIndexOf("/") + 1));
        }
    }, false, false);

    Sheet s = set.getMainWorkbook().getSheetAt(0);

    Cell c = s.getRow(1).getCell(0);

    CellValue v = set.getMainFormulaEvaluator().evaluate(c);

    Assert.assertEquals("Value", v.getStringValue());
}

---

From source file:xqt.adapters.csv.reader.RowBuilder.java

public static String[] createRowArray(Row row, FormulaEvaluator evaluator) {
    String[] cellValues = new String[row.getLastCellNum() + 1];
    for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
        CellValue cellValue = evaluator.evaluate(row.getCell(cellIndex));
        if (cellValue != null) {
            switch (cellValue.getCellType()) {
            // what about the DATE type
            case Cell.CELL_TYPE_NUMERIC:
                //System.out.print(cellValue.getNumberValue() + "\t");
                cellValues[cellIndex] = String.valueOf(cellValue.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                //System.out.print(cellValue.getStringValue()  + "\t");
                cellValues[cellIndex] = cellValue.getStringValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //System.out.println(cellValue.getBooleanValue()  + "\t");
                cellValues[cellIndex] = String.valueOf(cellValue.getBooleanValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // should not happen. It is evaluated by the evaluator
            case Cell.CELL_TYPE_BLANK:
            case Cell.CELL_TYPE_ERROR:
                cellValues[cellIndex] = "";
                break;
            }//from  w w w.  j a va 2s . co m
        }
    }
    return cellValues;
}

---

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