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.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private boolean isBlockEnd(Row row, int numCols2Check, String nextBlockIdentifier) {
    if (row == null)
        return true;
    for (int j = 0; j < numCols2Check; j++) {
        Cell cell = row.getCell(j);
        if (cell == null)
            continue;
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String s = cell.getStringCellValue().trim();
        if (j == 0 && nextBlockIdentifier != null && s.equals(nextBlockIdentifier))
            return true;
        if (!s.isEmpty())
            return false;
    }/*from w w w  . j  a  v a  2 s  .co m*/
    return true;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private int getNextBlockRowIndex(Sheet transactionSheet, int rowIndex, String nextBlockIdentifier) {
    int numRows = transactionSheet.getLastRowNum() + 1;
    for (; rowIndex < numRows; rowIndex++) {
        Row row = transactionSheet.getRow(rowIndex);
        if (row == null)
            continue;
        Cell cell = row.getCell(0);
        if (cell == null)
            continue;
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String s = cell.getStringCellValue().trim();
        if (s.equals(nextBlockIdentifier))
            return rowIndex;
    }/*from   ww  w. j  ava  2s  .  c o m*/
    return -100;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private static MetaInfo getMetaInfo(List<Exception> exceptions, Row row, Row rowBefore) {
    if (row == null)
        return null;
    boolean hasPartedDate = true;
    if (rowBefore != null) {
        Cell cell = rowBefore.getCell(1);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            if (getStr(cell.getStringCellValue()).equals("Reporting Date")) {
                cell = rowBefore.getCell(3);
                if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK
                        || getStr(cell.getStringCellValue()).trim().isEmpty()) {
                    hasPartedDate = false;
                }/*from www. j  a  v a  2 s .c o  m*/
            }
        }
    }
    MetaInfo result = new MetaInfo();
    Cell cell = row.getCell(0);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setReporter(getStr(cell.getStringCellValue()));
    }
    if (hasPartedDate) {
        cell = row.getCell(1);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setDateDay(getInt(cell.getStringCellValue()));
        }
        cell = row.getCell(2);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setDateMonth(getInt(cell.getStringCellValue()));
        }
        cell = row.getCell(3);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setDateYear(getInt(cell.getStringCellValue()));
        }
        /*
        if (result.getDateDay() == null) {
           if (exceptions != null) exceptions.add(new Exception("Reporting date is not defined correctly. This is mandatory! The Day is missing"));
        }
        if (result.getDateMonth() == null) {
           if (exceptions != null) exceptions.add(new Exception("Reporting date is not defined correctly. This is mandatory! The Month is missing"));
        }
        if (result.getDateYear() == null) {
           if (exceptions != null) exceptions.add(new Exception("Reporting date is not defined correctly. This is mandatory! The Year is missing"));
        }
        */
        cell = row.getCell(4);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setRemarks(getStr(cell.getStringCellValue()));
        }
    } else {
        cell = row.getCell(1);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setDate(getStr(cell.getStringCellValue()));
        }
        cell = row.getCell(2);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setRemarks(getStr(cell.getStringCellValue()));
        }
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private Station getStation(List<Exception> exceptions, Sheet businessSheet, String lookup, Row srcrow) {
    Station result = null;/*from  ww  w.j  a va  2s  .  c o m*/
    int numRows = businessSheet.getLastRowNum() + 1;
    for (int i = 0; i < numRows; i++) {
        Row row = businessSheet.getRow(i);
        if (row != null) {
            Cell cell = row.getCell(0);
            if (cell.getStringCellValue().equals(lookup)) {
                result = getStation(businessSheet.getRow(0), row);
                break;
            }
        }
    }
    if (result == null)
        exceptions.add(new Exception(
                "Station '" + lookup + "' is not correctly defined in Row " + (srcrow.getRowNum() + 1)));
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private Station getStation(Row titleRow, Row row) {
    if (row == null)
        return null;
    Station result = new Station();
    Cell cell = row.getCell(0);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String id = getStr(cell.getStringCellValue());
        if (id == null)
            return null;
        result.setId(id);/*w ww  .  j a va  2s . c o m*/
    } else
        return null;
    cell = row.getCell(1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setName(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(2);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setStreet(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(3);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setNumber(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(4);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setZip(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(5);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setCity(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(6);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDistrict(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(7);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setState(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(8);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setCountry(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(9);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setTypeOfBusiness(getStr(cell.getStringCellValue()));
    }
    //      cell = row.getCell(10); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {cell.setCellType(Cell.CELL_TYPE_STRING); result.setLookup(getStr(cell.getStringCellValue()));}

    // Further flexible cells
    for (int ii = 10; ii < 20; ii++) {
        Cell tCell = titleRow.getCell(ii);
        if (tCell != null && tCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            tCell.setCellType(Cell.CELL_TYPE_STRING);
            cell = row.getCell(ii);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                result.addFlexibleField(tCell.getStringCellValue(), cell.getStringCellValue());
            }
        }
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private D2D getD2D(List<Exception> exceptions, HashMap<String, Delivery> deliveries, Row titleRow, Row row,
        int rowNum) {
    if (row == null)
        return null;
    D2D result = new D2D();
    Cell cell = row.getCell(0);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String did = getStr(cell.getStringCellValue());
        Delivery d = deliveries.get(did);
        if (d == null)
            exceptions.add(new Exception(
                    "Delivery ID in sheet Deliveries2Deliveries not defined in deliveries sheet: '" + did
                            + "'; -> Row " + (rowNum + 1)));
        result.setIngredient(d);// w  w  w.  jav  a2  s . c o  m
    } else {
        return null;
    }
    cell = row.getCell(1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String did = getStr(cell.getStringCellValue());
        Delivery d = deliveries.get(did);
        if (d == null)
            exceptions.add(new Exception(
                    "Delivery ID in sheet Deliveries2Deliveries not defined in deliveries sheet: '" + did
                            + "'; -> Row " + (rowNum + 1)));
        result.setTargetDelivery(d);
    } else {
        return null;
    }
    if (result.getIngredient() != null && result.getTargetDelivery() != null) {
        if (!result.getIngredient().getReceiver().getId()
                .equals(result.getTargetDelivery().getLot().getProduct().getStation().getId())) {
            exceptions.add(new Exception("Recipient does not match Supplier; in sheet Deliveries2Deliveries: '"
                    + result.getIngredient().getId() + "' -> '" + result.getTargetDelivery().getId()
                    + "'; -> Row " + (rowNum + 1)));
        }
    }

    // Further flexible cells
    for (int i = 2; i < 10; i++) {
        Cell tCell = titleRow.getCell(i);
        if (tCell != null && tCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            tCell.setCellType(Cell.CELL_TYPE_STRING);
            cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                result.addFlexibleField(tCell.getStringCellValue(), cell.getStringCellValue());
            }
        }
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private Delivery getForwardDelivery(List<Exception> exceptions, Sheet stationSheet, HashMap<String, Lot> lots,
        Row titleRow, Row row, boolean isNewFormat_151105) {
    if (row == null)
        return null;
    Lot l = null;//  w  w  w .  ja  v  a 2s  . com
    Cell cell = row.getCell(0);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        l = lots.get(getStr(cell.getStringCellValue()));
    }
    if (l == null)
        return null;
    Delivery result = new Delivery();
    result.setLot(l);
    cell = row.getCell(1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureDay(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(2);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureMonth(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(3);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureYear(getInt(cell.getStringCellValue()));
    }
    int startCol = 4;
    if (isNewFormat_151105) {
        cell = row.getCell(4);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setArrivalDay(getInt(cell.getStringCellValue()));
        }
        cell = row.getCell(5);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setArrivalMonth(getInt(cell.getStringCellValue()));
        }
        cell = row.getCell(6);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result.setArrivalYear(getInt(cell.getStringCellValue()));
        }
        startCol = 7;
    }
    cell = row.getCell(startCol);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitNumber(getDbl(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitUnit(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 2);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String ss = getStr(cell.getStringCellValue());
        Station s = getStation(exceptions, stationSheet, ss, row);
        if (s == null)
            exceptions.add(new Exception("Recipient station '" + ss
                    + "' not correclty defined / not known in Forward Tracing sheet"));
        result.setReceiver(s);
    } else {
        exceptions.add(new Exception("No Recipient Station defined in Forward Tracing sheet"));
    }
    result.setId(getNewSerial(l, result));
    result.setNewlyGeneratedID(true);

    // Further flexible cells
    for (int i = startCol + 4; i < startCol + 21; i++) {
        Cell tCell = titleRow.getCell(i);
        if (tCell != null && tCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            tCell.setCellType(Cell.CELL_TYPE_STRING);
            cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                result.addFlexibleField(tCell.getStringCellValue(), cell.getStringCellValue());
            }
        }
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private String getStr(Cell cell) {
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
        return null;
    cell.setCellType(Cell.CELL_TYPE_STRING);
    String s = getStr(cell.getStringCellValue());
    return s;/*from  w  ww .  j a  v  a2  s. c om*/
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private Delivery getMultiOutDelivery(List<Exception> exceptions, HashMap<String, Station> stations,
        Row titleRow, Row row, HashMap<String, String> definedLots, int rowNum, String filename,
        boolean ignoreMissingLotnumbers) {
    if (row == null)
        return null;
    Delivery result = new Delivery();
    Cell cell = row.getCell(0);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setId(getStr(cell.getStringCellValue()));
    }//from w w w  .  j  av  a2  s  .c o m
    Product p = new Product();
    cell = row.getCell(1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String sid = getStr(cell.getStringCellValue());
        Station s = stations.get(sid);
        if (s == null)
            exceptions.add(new Exception("Station ID in Deliveries not defined in stations sheet: '" + sid
                    + "'; -> Row " + (rowNum + 1)));
        p.setStation(s);
    } else {
        return null;
    }
    cell = row.getCell(2);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        p.setName(getStr(cell.getStringCellValue()));
    }
    Lot l = new Lot();
    l.setProduct(p);
    cell = row.getCell(3);
    String str = getStr(cell);
    if (str != null) {
        l.setNumber(str);
    } else if (!ignoreMissingLotnumbers) {
        exceptions.add(new Exception("Please, do always provide a lot number as this is most helpful! -> Row "
                + (rowNum + 1) + " in '" + filename + "'\n"));
    }
    cell = row.getCell(4);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        l.setUnitNumber(getDbl(cell.getStringCellValue()));
    }
    cell = row.getCell(5);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        l.setUnitUnit(getStr(cell.getStringCellValue()));
    }
    String lotId = (p.getStation() == null) ? "_" + p.getName() + "_" + l.getNumber()
            : p.getStation().getId() + "_" + p.getName() + "_" + l.getNumber();
    String lotInfo = l.getUnitNumber() + "_" + l.getUnitUnit();
    if (definedLots.containsKey(lotId)) {
        if (!definedLots.get(lotId).equals(lotInfo))
            exceptions.add(new Exception("Lot has different quantities -> Lot number: '" + l.getNumber()
                    + "'; -> Row " + (rowNum + 1)));
    } else
        definedLots.put(lotId, lotInfo);

    result.setLot(l);
    cell = row.getCell(6);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureDay(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(7);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureMonth(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(8);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureYear(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(9);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalDay(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(10);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalMonth(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(11);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalYear(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(12);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitNumber(getDbl(cell.getStringCellValue()));
    }
    cell = row.getCell(13);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitUnit(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(14);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String sid = getStr(cell.getStringCellValue());
        Station s = stations.get(sid);
        if (s == null)
            exceptions.add(new Exception("Recipient ID in sheet Deliveries not defined in stations sheet: '"
                    + sid + "'; -> Row " + (rowNum + 1)));
        result.setReceiver(s);
    } else {
        if (result.getId() == null)
            return null;
        else
            exceptions
                    .add(new Exception("Recipient ID in sheet Deliveries not defined; -> Row " + (rowNum + 1)));
    }

    // Further flexible cells
    LinkedHashSet<String> le0 = new LinkedHashSet<>();
    le0.add("Production date".toLowerCase());
    le0.add("Best before date".toLowerCase());
    le0.add("Treatment of product during production".toLowerCase());
    le0.add("Sampling".toLowerCase());
    for (int i = 15; i < 25; i++) {
        Cell tCell = titleRow.getCell(i);
        if (tCell != null && tCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            tCell.setCellType(Cell.CELL_TYPE_STRING);
            String field = tCell.getStringCellValue();
            cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                if (le0.contains(field.toLowerCase()))
                    l.addFlexibleField(field, cell.getStringCellValue());
                else
                    result.addFlexibleField(field, cell.getStringCellValue());
            }
        }
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private boolean isCellEmpty(Cell cell) {
    return cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK
            || (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().isEmpty());
}