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.helger.poi.excel.ExcelReadUtilsTest.java

License:Apache License

/**
 * Validate reference sheets/* w w w .j a  va 2 s.c o  m*/
 * 
 * @param aWB
 *        Workbook to use
 */
private void _validateWorkbook(@Nonnull final Workbook aWB) {
    final Sheet aSheet1 = aWB.getSheet("Sheet1");
    assertNotNull(aSheet1);
    assertNotNull(aWB.getSheet("Sheet2"));
    final Sheet aSheet3 = aWB.getSheet("Sheet3");
    assertNotNull(aSheet3);
    assertNull(aWB.getSheet("Sheet4"));

    Cell aCell = aSheet1.getRow(0).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("A1", aCell.getStringCellValue());

    aCell = aSheet1.getRow(1).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("B2", aCell.getStringCellValue());

    aCell = aSheet1.getRow(2).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("C\n3", aCell.getStringCellValue());

    aCell = aSheet1.getRow(3).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
    assertEquals(0.00001, 4.4, aCell.getNumericCellValue());

    for (int i = 0; i < 6; ++i) {
        aCell = aSheet3.getRow(i).getCell(i);
        assertNotNull(aCell);
        assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
        assertEquals(0.00001, i + 1, aCell.getNumericCellValue());
    }

    // ="abc"
    aCell = aSheet1.getRow(4).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("\"abc\"", aCell.getCellFormula());
    assertEquals("abc", aCell.getStringCellValue());
    CellValue aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE)
            .evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aEvaluated.getCellType());
    assertEquals("abc", aEvaluated.getStringValue());

    // =4711
    aCell = aSheet1.getRow(5).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("4711", aCell.getCellFormula());
    assertEquals(0.00001, 4711, aCell.getNumericCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aEvaluated.getCellType());
    assertEquals(0.00001, 4711, aEvaluated.getNumberValue());

    // =TRUE
    aCell = aSheet1.getRow(6).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("TRUE", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());

    // Refers to cell at 6/2
    aCell = aSheet1.getRow(7).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("C7", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());
}

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * ,//from   www .  jav  a2 s . c om
 * 
 * @param fileName
 *            excel, getModelExcel
 * @return 
 * @throws FileNotFoundException 
 */
public Map<String, String> readModelExcel(File file, String modelId) {
    if (modelId == null)
        return null;
    Map<String, String> m = new HashMap<String, String>();
    try {
        InputStream in = new FileInputStream(file);
        Workbook wb;
        try {
            wb = new HSSFWorkbook(in);
        } catch (IllegalArgumentException e) {
            wb = new XSSFWorkbook(in);
        }
        Sheet sheet = wb.getSheetAt(0);
        int total = sheet.getLastRowNum();
        Row row0 = sheet.getRow(0);
        String[] head = new String[row0.getLastCellNum()];
        for (int j = 0; j < row0.getLastCellNum(); j++) {
            String[] str = row0.getCell(j).getStringCellValue().split("/");
            if (str.length == 2) {
                head[j] = str[1];
            } else {
                head[j] = "";
            }
            System.out.println(head[j]);
        }
        Row row = null;
        Cell cell = null;
        for (int i = 1; i < total; i++) {
            m.clear();
            row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                m.put(head[j], cell.getStringCellValue());
                System.out.println(head[j] + "--" + cell.getStringCellValue());
            }
            // ServiceManager.getResourceUpdateService().addResource(modelId,
            // m, 1);
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        file.delete();
    }
    return m;
}

From source file:com.hurence.logisland.processor.excel.ExcelExtract.java

License:Apache License

/**
 * Handle row content and transform it into a {@link Record}
 *
 * @param row the {@link Row}// www .  ja  v  a2s . co  m
 * @return the transformed {@link Record}
 */
private Record handleRow(Row row, List<String> header) {
    Record ret = new StandardRecord().setTime(new Date());
    int index = 0;
    for (Cell cell : row) {
        if (configuration.getFieldNames() != null && index >= configuration.getFieldNames().size()) {
            //we've reached the end of mapping. Go to next row.
            break;
        }
        if (configuration.getColumnsToSkip().contains(cell.getColumnIndex())) {
            //skip this cell.
            continue;
        }
        String fieldName = header != null ? header.get(cell.getColumnIndex())
                : configuration.getFieldNames().get(index++);
        Field field;
        // Alternatively, get the value and format it yourself
        switch (cell.getCellTypeEnum()) {
        case STRING:
            field = new Field(fieldName, FieldType.STRING, cell.getStringCellValue());
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                field = new Field(fieldName, FieldType.LONG, cell.getDateCellValue().getTime());
            } else {
                field = new Field(fieldName, FieldType.DOUBLE, cell.getNumericCellValue());
            }
            break;
        case BOOLEAN:
            field = new Field(fieldName, FieldType.BOOLEAN, cell.getBooleanCellValue());
            break;
        case FORMULA:
            field = new Field(fieldName, FieldType.STRING, cell.getCellFormula());
            break;
        default:
            //blank or unknown
            field = new Field(fieldName, FieldType.NULL, null);
            break;
        }
        ret.setField(field);
    }
    return ret;
}

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

private static Object getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {//www . ja  va 2 s .  c om
            return String.valueOf(cell.getNumericCellValue());
        }
    case Cell.CELL_TYPE_STRING:
    default:
        return cell.getStringCellValue().trim();
    }
}

From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java

License:Open Source License

private List<Entry<String, ExcelType>> extractTypes(Sheet sheet) {

    final ArrayList<Entry<String, ExcelType>> columns = new ArrayList<Entry<String, ExcelType>>();
    int rowCount = 0;
    for (final Row row : new IteratorWrapper<Row>(sheet.iterator())) {
        if (rowCount < 1) {
            int cellCount = 0;
            //First row - get column names
            final Iterator<Cell> cellIterator = row.cellIterator();
            for (final Cell cell : new IteratorWrapper<Cell>(cellIterator)) {
                final String columnName = Utils.cleanUp(cell.getStringCellValue());
                addColumnName(columns, cellCount, columnName, sheet);
                cellCount++;//from   www. j  av a 2s  .  co  m
            }
        } else if (rowCount < 2) {
            int cellCount = 0;
            //Second row - work out column type based on these values
            final Iterator<Cell> cellIterator = row.cellIterator();
            for (final Cell cell : new IteratorWrapper<Cell>(cellIterator)) {
                Entry<String, ExcelType> type = columns.get(cellCount);
                if (type != null) {
                    type.setValue(Utils.excelTypeToMySql(cell));
                }
                cellCount++;
            }
        }
        rowCount++;
    }
    return columns;

}

From source file:com.hust.zsuper.DealWithPatent.WorkhseetToMySQL.java

License:Open Source License

private String getStringValue(ExcelType type, Cell cell) {
    switch (type) {
    case DATE://from  w w  w  . j a v  a 2 s . c  o  m
        return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(cell.getDateCellValue()) + "'";
    case NUMERIC:
        return String.valueOf(cell.getNumericCellValue());
    case BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case STRING:
        return "'" + cell.getStringCellValue().replaceAll("'", "\\\\'") + "'";
    default:
        return null;
    }
}

From source file:com.hust.zsuper.DealWithPatent.WorkhseetToMySQL.java

License:Open Source License

private void extractTypes(Sheet sheet) {

    {// w  ww  .  j  a  v  a2  s.c  o  m
        int cellCount = columnOffset;
        //First row - get column names
        final Iterator<Cell> cellIterator = sheet.getRow(rowOffset).cellIterator();

        for (final Cell cell : new IteratorWrapper<Cell>(cellIterator)) {
            final String columnName = Utils.cleanUp(cell.getStringCellValue());
            addColumnName(cellCount, columnName, sheet);
            cellCount++;
        }
    }
    {
        int cellCount = columnOffset;
        //Second row - work out column type based on these values
        final Iterator<Cell> cellIterator = sheet.getRow(rowOffset + 1).cellIterator();
        for (final Cell cell : new IteratorWrapper<Cell>(cellIterator)) {
            final Entry<String, ExcelType> type = types.get(cellCount);
            if (type != null) {
                type.setValue(Utils.excelTypeToMySql(cell));
            }
            cellCount++;
        }
    }
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Put the next row in the dvd row given in parameter.
 * Return SCAN_COMPLETED if there is no more row in the spreadsheet, or GOT_ROW if a row was successfully put in the dvd row.
 * //from  w  w  w .ja va  2 s.  c o m
 * Uses the attribute currentRow to save the previous row fetched.
 * 
 * @param sheet
 * @param dvdr
 * @param numberOfLogicalColumnsInvolved
 * @param columnIndexes
 * @return SCAN_COMPLETED or GOT_ROW
 * @throws SQLException
 */
private int createNextRow(Sheet sheet, DataValueDescriptor[] dvdr) {
    boolean gotData = false;

    /*
     * Find the next row to return.
     * 
     * currentRow should currently point to the last row returned.
     * If that's null, then start from first row.
     * Else, search for the next non-empty row (until we hit the end of the prescribed range).
     */
    if (currentRow == null)
        currentRow = sheet.getRow(firstRowIndex + (firstRowIsMetaData ? 1 : 0));
    else {
        int nextRowIndex = currentRow.getRowNum() + 1;
        currentRow = null;

        if (stopScanOnFirstEmptyRow) {
            currentRow = sheet.getRow(nextRowIndex);
        } else {
            while (currentRow == null && nextRowIndex <= lastRowIndex) {
                currentRow = sheet.getRow(nextRowIndex);
                nextRowIndex++;
            }
        }
    }

    /*
     * If we've run out of spreadsheet (currentRow == null) or gone out of the prescribed range,
     * then scan complete - return that.
     */
    if (currentRow == null || currentRow.getRowNum() > lastRowIndex) {
        return SCAN_COMPLETED;
    }

    /*
     * Get the offset of the first column in the spreadsheet.
     * Note: this is used when iterating below, so that we can correctly relate 
     * the actual column in the spreadsheet to the correct 'column' in the 
     * DataValueDescriptor [] representing the row.
     */
    int columnOffset = firstColumnIndex;

    //Figure out how many columns there are
    int numberOfColumns = lastColumnIndex - firstColumnIndex + 1;

    for (int i = 0; i < numberOfColumns; i++) {
        /*
         * Note: i is used to refer to the index of the DataValueDescriptor which represents
         * the actual spreadsheet column (at i + columnOffset) in the DataValueDescriptor[] 
         * representing this row. 
         */

        Cell cell = currentRow.getCell(i + columnOffset);

        if (cell == null) {
            dvdr[i].setToNull();
        } else {
            try {
                int cellValueType = cell.getCellType();

                if (cellValueType == Cell.CELL_TYPE_FORMULA)
                    cellValueType = cell.getCachedFormulaResultType();

                switch (cellValueType) {

                case Cell.CELL_TYPE_STRING:
                    dvdr[i].setValue(cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                        dvdr[i].setValue(new java.sql.Date(cell.getDateCellValue().getTime()));
                    else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        dvdr[i].setValue(cell.getStringCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_BOOLEAN:
                    dvdr[i].setValue(cell.getBooleanCellValue());
                    break;

                default:
                    dvdr[i].setToNull();
                    break;
                }

                //If a cell has data that is not null - then flag that we actually have data to return
                if (!dvdr[i].isNull())
                    gotData = true;

            } catch (Exception e) {
                dvdr[i].setToNull();
                logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_MAP_LT_ERROR, "Excel cell [spreadsheet "
                        + sheet.getSheetName() + "; row " + cell.getRow().getRowNum() + "; column "
                        + cell.getColumnIndex() + "; value " + cell
                        + "] could not be mapped into the logical table because of the column logical type: "
                        + e);
            }
        }
    }

    if (!gotData && stopScanOnFirstEmptyRow) {
        logger.logInfo(
                "Ending GExcel table scan on first empty row (as no row limit was specified in the ending cell config constraint)");
        return SCAN_COMPLETED;
    }

    return GOT_ROW;
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * looks for the column definition and initializes the following attributes :
 * //from  w  ww  .j a  v a2  s.  co  m
 * - numberOfColumns
 * - columnIndexes
 * - columnNames
 *
 * If a column which contains no values is ignored.
 * 
 * If firstRowIsMetaData is true, the column names will be extract from the first row of the spreadsheet.
 * Else, they will be automatically generated : COLUMN1, COLUMN2...
 * 
 * @param sheet
 */
private void findColumns(Sheet sheet) {
    numberOfColumns = 0;

    columnIndexes = new ArrayList<Integer>();
    columnNames = new ArrayList<String>();

    Row firstRow = sheet.getRow(firstRowIndex);

    int columnLabelIndex = 1;

    if (firstRowIsMetaData) {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());

            int cellType = cell.getCellType();
            if (Cell.CELL_TYPE_FORMULA == cellType) {
                cellType = cell.getCachedFormulaResultType();
                //                System.out.println("cell type is now getCachedFormulaResultType() = " + cellType );
            }

            //Build the column names depending on it's type
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //                case Cell.CELL_TYPE_FORMULA: // DO NOT USE: getCellFormula() !!!

                //                   System.out.println("cell type string" );

                // Note: Javadoc on method getStringCellValue() states:
                // "get the value of the cell as a string - for numeric cells we throw an exception. For blank cells we return an empty string. 
                // For formulaCells that are not string Formulas, we throw an exception"

                ++numberOfColumns;
                columnNames.add(cell.getStringCellValue().replaceAll("[\\ ]", "_")); // Note we should not have to do this in future... once defect is fixed
                break;

            case Cell.CELL_TYPE_NUMERIC:

                //                   System.out.println("cell type numeric " + 
                //                         ( DateUtil.isCellDateFormatted( cell ) ? "date: " + cell.getDateCellValue().toString() : "num: " + cell.getNumericCellValue() ) );

                ++numberOfColumns;
                columnNames.add(DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue().toString()
                        : "" + cell.getNumericCellValue());
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                //                   System.out.println("cell type boolean" );

                ++numberOfColumns;
                columnNames.add("" + cell.getBooleanCellValue());
                break;

            default:

                //                   System.out.println("cell type default" );

                ++numberOfColumns;
                columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex);
                break;
            }

            columnLabelIndex++;
        }
    } else {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());
            columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex++);
        }
    }
}

From source file:com.ifeng.vdn.ip.repository.app.IPCheckApp.java

License:Apache License

public static void main(String[] args) {
    Workbook wb = null;/*from  w  w w  .  j a  va2  s.c o m*/
    PrintWriter pw = null;
    try {

        pw = new PrintWriter(new FileOutputStream("src/test/resources/data/CDN_BAD.txt"), true);

        AliIPAddressChecker ipChecker = new AliIPAddressChecker();

        wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/CDN_BAD.xlsx"));
        Sheet sheet = wb.getSheetAt(0);

        int totalRows = sheet.getPhysicalNumberOfRows();
        Cell ipCell = null;
        //Cell locCell = null;

        List<String> ips = new ArrayList<String>();

        for (int i = 1; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            ipCell = row.getCell(0);

            ips.add(ipCell.getStringCellValue());
        }

        for (String ip : ips) {
            AliIPBean bean = (AliIPBean) ipChecker.ipcheck(ip);
            pw.println(ip + "-" + bean.getIpString());
        }

    } catch (InvalidFormatException | IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        if (pw != null) {
            pw.flush();
            pw.close();
        }
    }

}