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 Delivery getDelivery(List<Exception> exceptions, Sheet businessSheet, Station sif, Row row,
        boolean outbound, Row titleRow, String filename, boolean isForTracing, HashMap<String, Lot> outLots,
        HashMap<String, Delivery> existingDeliveries, boolean ignoreMissingLotnumbers,
        boolean isNewFormat_151105) {
    Cell cell;
    if (isNewFormat_151105) {
        cell = row.getCell(0);//w ww. j  a v a2  s  .c om
        if (isCellEmpty(cell)) {
            Cell cell10 = row.getCell(10);
            if (!isForTracing || !isCellEmpty(cell10)) {
                exceptions.add(
                        new Exception("It is essential to choose the associated Lot number ('Lot Number of "
                                + (isForTracing ? "" : " \"") + "Product" + (isForTracing ? "" : " Out\"")
                                + "') to the delivery in Row number " + (classRowIndex + 1)));
            }
            return null;
        }
        cell = row.getCell(12);
    } else {
        cell = row.getCell(12);
        if (isCellEmpty(cell)) {
            Cell cell10 = row.getCell(10);
            Cell cell0 = row.getCell(0);
            if ((!isForTracing && !isCellEmpty(cell0)) || !isCellEmpty(cell10)) {
                exceptions.add(
                        new Exception("It is essential to choose the associated Lot number ('Lot Number of "
                                + (isForTracing ? "" : " \"") + "Product" + (isForTracing ? "" : " Out\"")
                                + "') to the delivery in Row number " + (classRowIndex + 1)));
            }
            return null;
        }
    }
    Delivery result = new Delivery();
    String lotDelNumber = null;
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        lotDelNumber = getStr(cell.getStringCellValue());
        //if (isForTracing && outbound) {cell.setCellType(Cell.CELL_TYPE_STRING); result.setTargetLotId(getStr(cell.getStringCellValue()));}
        if (isNewFormat_151105) {
            result.setId(lotDelNumber);
        } else {
            if (!isForTracing && outbound) {
                result.setId(lotDelNumber);
            }
            if (isForTracing && !outbound) {
                result.setId(lotDelNumber);
            }
            if (!isForTracing && !outbound) {
                result.addTargetLotId(lotDelNumber);
            }
        }
    }
    Lot l;
    if (isForTracing && outbound) {
        cell = row.getCell(0);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            lotDelNumber = getStr(cell.getStringCellValue());
        }
        if (lotDelNumber == null && !ignoreMissingLotnumbers) {
            exceptions
                    .add(new Exception("Please, do always provide a lot number as this is most helpful! -> Row "
                            + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
        }
        l = outLots.get(lotDelNumber);
    } else {
        Product p = new Product();
        if (outbound)
            p.setStation(sif);
        l = new Lot();
        l.setProduct(p);
        String lotNumber = null;
        if (isNewFormat_151105) {
            if (outbound) {
                cell = row.getCell(0);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    lotNumber = getStr(cell.getStringCellValue());
                }
                if (lotNumber == null && !ignoreMissingLotnumbers) {
                    exceptions.add(new Exception(
                            "Please, do always provide a lot number as this is most helpful! -> Row "
                                    + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
                }
                l.setNumber(lotNumber);
                if (outLots.containsKey(lotNumber)) {
                    l = outLots.get(lotNumber);
                }
            } else {
                cell = row.getCell(0);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    lotNumber = getStr(cell.getStringCellValue());
                }
                if (lotNumber != null)
                    result.addTargetLotId(lotNumber);

                cell = row.getCell(1);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    p.setName(getStr(cell.getStringCellValue()));
                }
                cell = row.getCell(2);
                lotNumber = null;
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    lotNumber = getStr(cell.getStringCellValue());
                }
                if (lotNumber == null && !ignoreMissingLotnumbers) {
                    exceptions.add(new Exception(
                            "Please, do always provide a lot number as this is most helpful! -> Row "
                                    + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
                }
                l.setNumber(lotNumber);
                if (lotNumber == null && p.getName() == null) {
                    exceptions.add(new Exception("Lot number undefined in Row number " + (classRowIndex + 1)));
                }
            }
        } else {
            cell = row.getCell(0);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                p.setName(getStr(cell.getStringCellValue()));
            }
            cell = row.getCell(1);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                lotNumber = getStr(cell.getStringCellValue());
            } else if (!ignoreMissingLotnumbers) {
                exceptions.add(
                        new Exception("Please, do always provide a lot number as this is most helpful! -> Row "
                                + (row.getRowNum() + 1) + " in '" + filename + "'\n"));
            }
            l.setNumber(lotNumber);
            if (lotNumber == null && p.getName() == null) {
                exceptions.add(new Exception(
                        "Lot number and product name undefined in Row number " + (classRowIndex + 1)));
            }
        }
    }
    //cell = row.getCell(1); if (cell != null) {cell.setCellType(Cell.CELL_TYPE_STRING); l.setNumber(getStr(cell.getStringCellValue()));}
    result.setLot(l);
    if (!outbound)
        result.setReceiver(sif);
    int startCol = isNewFormat_151105 ? 3 : 2;
    cell = row.getCell(startCol);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureDay(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureMonth(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 2);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setDepartureYear(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 3);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalDay(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 4);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalMonth(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 5);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setArrivalYear(getInt(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 6);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitNumber(getDbl(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 7);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setUnitUnit(getStr(cell.getStringCellValue()));
    }
    cell = row.getCell(startCol + 8);
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK
            || (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().isEmpty()))
        return null;
    if (outbound && cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        result.setReceiver(getStation(exceptions, businessSheet, cell.getStringCellValue(), row));
    }
    if (!outbound && cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        l.getProduct().setStation(getStation(exceptions, businessSheet, cell.getStringCellValue(), row));
        l.setNumber(l.getNumber());
    }

    if (!isForTracing && !outbound || isForTracing && outbound) {
        if (!isNewFormat_151105 || result.getId() == null) {
            result.setId(getNewSerial(l, result));
            result.setNewlyGeneratedID(true);
        }
        if (existingDeliveries != null && existingDeliveries.containsKey(result.getId())) {
            result.getTargetLotIds().addAll(existingDeliveries.get(result.getId()).getTargetLotIds());
        }
    }

    // Further flexible cells
    for (int i = 13; i < 20; 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 boolean fillLot(List<Exception> exceptions, Row row, Station sif, HashMap<String, Lot> outLots,
        Row titleRow, HashMap<String, Delivery> outDeliveries, int rowIndex, boolean isNewFormat_151105) {
    Lot l = null;/* www. ja v  a 2  s  . co  m*/
    String lotNumber = null;
    Cell cell = row.getCell(0);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        lotNumber = getStr(cell.getStringCellValue());
    }
    l = outLots.get(lotNumber);
    if (l == null) {
        if (outDeliveries != null) {
            l = new Lot();
            l.setNumber(lotNumber);
            outLots.put(l.getNumber(), l);
        } else
            return false;
    }
    cell = row.getCell(1);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        Double dbl = getDbl(cell.getStringCellValue());
        if (l.getUnitNumber() == null)
            l.setUnitNumber(dbl);
        else if (l.getUnitNumber().doubleValue() != dbl) {
            exceptions.add(new Exception(
                    "Lot information defines same lot number with different quantities -> Row " + rowIndex));
        }
    }
    cell = row.getCell(2);
    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String str = getStr(cell.getStringCellValue());
        if (l.getUnitUnit() == null)
            l.setUnitUnit(str);
        else if (!l.getUnitUnit().equals(str)) {
            exceptions.add(new Exception(
                    "Lot information defines same lot number with different units -> Row " + rowIndex));
        }
    }
    if (isNewFormat_151105 || outDeliveries != null) {
        cell = row.getCell(3);
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String pr = getStr(cell.getStringCellValue());
            if (l.getProduct() == null) {
                Product p = new Product();
                p.setName(pr);
                l.setProduct(p);
                p.setStation(sif);
            } else if (l.getProduct().getName() == null) {
                l.getProduct().setName(pr);
            } else if (!l.getProduct().getName().equals(pr)) {
                exceptions.add(new Exception(
                        "Lot information defines same lot number with different product names -> Row "
                                + rowIndex));
            }
        }
        if (!isNewFormat_151105) {
            cell = row.getCell(4);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                Delivery d = outDeliveries.get(getStr(cell.getStringCellValue()));
                if (d == null)
                    return false;
                d.addTargetLotId(l.getNumber());
            }
        }
    }

    // Further flexible cells
    for (int i = 12; i < 20; 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);
                l.addFlexibleField(tCell.getStringCellValue(), cell.getStringCellValue());
            }
        }
    }
    return true;
}

From source file:de.chott.jfreechartsample.reader.FileReaderService.java

/**
 * Liest mit Hilfe der Apache-Poi-Library ein XLS-File aus und gibt die Daten darin als Liste von PieChartData-Objekten zurck. 
 * /*from   ww  w. j  a v  a2 s  . c o  m*/
 * @param stream Das File als Resource-InputStream
 * @return eine Liste der PieChartData
 * @throws IOException 
 */
public List<PieChartData> readPieChartDataFromXls(InputStream stream) throws IOException {
    List<PieChartData> retVal = new ArrayList<>();
    HSSFWorkbook workbook = new HSSFWorkbook(stream);

    HSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();
    rowIterator.next();

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        PieChartData data = new PieChartData();

        Cell countryCell = row.getCell(0);
        Cell weightCell = row.getCell(1);

        if (countryCell != null && weightCell != null) {
            data.setCountry(countryCell.getStringCellValue());
            data.setWeight(weightCell.getNumericCellValue());

            retVal.add(data);
        }
    }

    return retVal;
}

From source file:de.enerko.reports2.engine.CellDefinition.java

License:Apache License

protected CellValue parse_string(Cell in) {
    return new CellValue("string", in.getStringCellValue());
}

From source file:de.enerko.reports2.engine.CellDefinition.java

License:Apache License

protected CellValue parse_number(Cell in) {
    CellValue rv = null;//from   w  w  w . j av a 2s .  co  m

    try {
        if (HSSFDateUtil.isCellDateFormatted(in)) {
            rv = new CellValue("datetime", Report.DATEFORMAT_OUT.format(in.getDateCellValue()));
        } else {
            rv = new CellValue("number", Double.toString(in.getNumericCellValue()));
        }
    } catch (IllegalStateException e) {
        // Siehe Dokumentation getNumericCellValue
        rv = new CellValue("string", in.getStringCellValue());
    }

    return rv;
}

From source file:de.hk.exceldemo.business.processor.AbstractRowProcessor.java

protected void validateStringCell(Cell cell, String value)
        throws FileFormatException, IllegalArgumentException {
    if (cell == null || cell.getStringCellValue() == null) {
        throw new IllegalArgumentException("Zelle '" + value + "' konnte nicht gefunden werden");
    } else if (cell.getCellType() != XSSFCell.CELL_TYPE_STRING) {
        throw new FileFormatException("Ungueltiger CellType " + cell.getCellType() + " fuer " + value);
    } else if (!cell.getStringCellValue().trim().equalsIgnoreCase(value.trim())) {
        throw new FileFormatException(value + " erwartet statt " + cell.getStringCellValue());
    }//w w  w  .j  a  v  a2  s  .c o  m
}

From source file:de.hk.exceldemo.business.processor.AbstractRowProcessor.java

protected void checkNumericCell(Cell cell, String value) throws FileFormatException {
    if (cell == null || cell.getStringCellValue() == null) {
        throw new IllegalArgumentException("Zelle '" + value + "' konnte nicht gefunden werden");
    } else if (cell.getCellType() != XSSFCell.CELL_TYPE_NUMERIC) {
        throw new FileFormatException("Ungueltiger CellType " + cell.getCellType() + " fuer " + value);
    } else if (!cell.getStringCellValue().trim().equalsIgnoreCase(value.trim())) {
        throw new FileFormatException(value + " erwartet statt " + cell.getStringCellValue());
    }//from  w w w .j av  a 2  s  . c  o  m
}

From source file:de.ingrid.iplug.excel.service.SheetsService.java

License:EUPL

/**
 * Create sheets.// w ww. ja va2s. c  o m
 * 
 * @param inputStream
 * @return Created sheets.
 * @throws IOException
 */
public static Sheets createSheets(final InputStream inputStream) throws IOException {
    // sheets
    final Sheets sheets = new Sheets();
    // create workbook
    final Workbook workbook = new HSSFWorkbook(inputStream);
    final FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        final org.apache.poi.ss.usermodel.Sheet poiSheet = workbook.getSheetAt(sheetNum);
        // ingrid sheet
        final Sheet sheet = new Sheet();
        sheet.setSheetIndex(sheetNum);
        sheets.addSheet(sheet);
        final Values values = new Values();
        sheet.setValues(values);
        for (final org.apache.poi.ss.usermodel.Row poiRow : poiSheet) {
            boolean hasValues = false;
            final Map<Point, Comparable<? extends Object>> valuesInCell = new HashMap<Point, Comparable<? extends Object>>();
            for (final Cell poiCell : poiRow) {

                Comparable<? extends Object> value = null;
                switch (poiCell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = new Boolean(poiCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(poiCell)) {
                        value = getFormattedDateString(poiCell);
                    } else {
                        value = new Double(poiCell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = poiCell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value = calculateFormula(poiCell, eval);
                    break;
                default:
                    value = "";
                    break;
                }
                // trim strings
                if (value instanceof String) {
                    value = ((String) value).trim();
                }
                // only add if at least one value does exist in row
                if (!value.equals("")) {
                    hasValues = true;
                    // ingrid column
                    if (sheet.getColumn(poiCell.getColumnIndex()) == null) {
                        final Column column = new Column(poiCell.getColumnIndex());
                        sheet.addColumn(column);
                    }
                }

                // ingrid point and value
                final Point point = new Point(poiCell.getColumnIndex(), poiCell.getRowIndex());
                valuesInCell.put(point, value);
            }
            // ingrid row
            // ! only add if at least one value does exist
            if (hasValues) {
                final Row row = new Row(poiRow.getRowNum());
                sheet.addRow(row);
                for (final Point point : valuesInCell.keySet()) {
                    //
                    if (sheet.getColumn(point.getX()) != null) {
                        values.addValue(point, valuesInCell.get(point));
                    }
                }
            }
        }
    }

    return sheets;
}

From source file:de.ingrid.iplug.excel.service.SheetsService.java

License:EUPL

private static Comparable<? extends Object> calculateFormula(final Cell poiCell, final FormulaEvaluator eval) {
    Comparable<? extends Object> ret = null;
    final int type = eval.evaluateFormulaCell(poiCell);
    switch (type) {
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(poiCell)) {
            ret = getFormattedDateString(poiCell);
        } else {// w w  w  .j a v  a 2  s . c om
            ret = poiCell.getNumericCellValue();
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        ret = poiCell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR:
    case Cell.CELL_TYPE_FORMULA:
    case Cell.CELL_TYPE_STRING:
    default:
        ret = poiCell.getStringCellValue();
    }
    return ret;
}

From source file:de.interactive_instruments.ShapeChange.SBVR.SbvrRuleLoader.java

License:Open Source License

/**
 * @param sbvrXls//from ww w  .jav a2  s  .  c  om
 * @return mapping of schema package name to SBVR rules that apply to
 *         classes in this schema *
 *         <ul>
 *         <li>key: class name</li>
 *         <li>value: mapping of schema package name to SBVR rule info
 *         <ul>
 *         <li>key: schema package name (
 *         {@value #UNSPECIFIED_SCHEMA_PACKAGE_NAME} if no schema package
 *         name has been provided)</li>
 *         <li>value: list of SBVR rules that apply to classes in that
 *         schema (the list is sorted according to lexical order on a) the
 *         class name and b) the rule text)</li>
 *         </ul>
 *         </ul>
 */
private TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> parseSBVRRuleInfos(Workbook sbvrXls) {

    TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> rules = new TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>>();

    if (sbvrXls == null)
        return null;

    Sheet rulesSheet = null;

    for (int i = 0; i < sbvrXls.getNumberOfSheets(); i++) {

        String sheetName = sbvrXls.getSheetName(i);

        if (sheetName.equalsIgnoreCase("Constraints")) {
            rulesSheet = sbvrXls.getSheetAt(i);
            break;
        }
    }

    if (rulesSheet == null) {

        result.addError(this, 3);
        return null;
    }

    // read header row to determine which columns contain relevant
    // information
    Map<String, Integer> fieldIndexes = new HashMap<String, Integer>();

    Row header = rulesSheet.getRow(rulesSheet.getFirstRowNum());

    if (header == null) {
        result.addError(this, 4);
        return null;
    }

    boolean classNameFound = false;
    boolean commentsFound = false;
    boolean ruleNameFound = false;
    boolean ruleTextFound = false;
    boolean schemaPackageFound = false;

    for (short i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {

        Cell c = header.getCell(i, Row.RETURN_BLANK_AS_NULL);

        if (c == null) {
            // this is allowed
        } else {

            String value = c.getStringCellValue();

            if (value.equalsIgnoreCase(SbvrRuleInfo.CLASS_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.CLASS_COLUMN_NAME, (int) i);
                classNameFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.COMMENT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.COMMENT_COLUMN_NAME, (int) i);
                commentsFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME, (int) i);
                schemaPackageFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME, (int) i);
                ruleTextFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_NAME_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_NAME_COLUMN_NAME, (int) i);
                ruleNameFound = true;
            }
        }
    }

    // if (fieldIndexes.size() != 5) {
    if (!ruleNameFound && !ruleTextFound) {
        // log message that required fields were not found
        result.addError(this, 5);
        return null;
    }

    /*
     * Read rule content
     */
    for (int i = rulesSheet.getFirstRowNum() + 1; i <= rulesSheet.getLastRowNum(); i++) {

        Row r = rulesSheet.getRow(i);
        int rowNumber = i + 1;

        if (r == null) {
            // ignore empty rows
            continue;
        }

        SbvrRuleInfo sri = new SbvrRuleInfo();

        // get rule name (required)
        Cell c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_NAME_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 6, "" + rowNumber);
            continue;
        } else {
            String cellValue = c.getStringCellValue();
            if (cellValue != null) {
                if (cellValue.contains(":")) {
                    sri.setName(cellValue.substring(cellValue.lastIndexOf(":") + 1));
                } else {
                    sri.setName(cellValue);
                }
            }
        }

        // get rule text (required)
        c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 7, "" + rowNumber);
            continue;
        } else {
            sri.setText(c.getStringCellValue());
        }

        // get comment (optional)
        if (commentsFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.COMMENT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c != null) {
                sri.setComment(c.getStringCellValue());
            }
        }

        // get schema package (optional)
        if (schemaPackageFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                sri.setSchemaPackageName(UNSPECIFIED_SCHEMA_PACKAGE_NAME);
            } else {
                sri.setSchemaPackageName(c.getStringCellValue());
            }
        }

        /*
         * get class name (optional when loading from excel because later we
         * can still try parsing it from the rule text)
         */
        if (classNameFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.CLASS_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                /*
                 * then after this we'll try to parse the class name from
                 * the rule text
                 */
            } else {
                sri.setClassName(c.getStringCellValue());
            }
        }

        if (sri.getClassName() == null) {

            /*
             * try parsing the main class name from the rule text
             */
            result.addInfo(this, 10, sri.getName());

            String mainClassName = parseClassNameFromRuleText(sri.getText());

            if (mainClassName == null) {
                result.addWarning(this, 8, sri.getName());
                continue;
            } else {
                sri.setClassName(mainClassName);
            }
        }

        List<SbvrRuleInfo> rulesList;
        TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName;

        if (rules.containsKey(sri.getClassName())) {

            rulesBySchemaPackageName = rules.get(sri.getClassName());

            if (rulesBySchemaPackageName.containsKey(sri.getSchemaPackageName())) {
                rulesList = rulesBySchemaPackageName.get(sri.getSchemaPackageName());
            } else {
                rulesList = new ArrayList<SbvrRuleInfo>();
                rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
            }

        } else {

            rulesBySchemaPackageName = new TreeMap<String, List<SbvrRuleInfo>>();
            rules.put(sri.getClassName(), rulesBySchemaPackageName);

            rulesList = new ArrayList<SbvrRuleInfo>();
            rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
        }

        rulesList.add(sri);
    }

    // now sort all lists contained in the map
    for (TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName : rules.values()) {
        for (List<SbvrRuleInfo> rulesList : rulesBySchemaPackageName.values()) {

            Collections.sort(rulesList, new Comparator<SbvrRuleInfo>() {

                @Override
                public int compare(SbvrRuleInfo o1, SbvrRuleInfo o2) {

                    int classNameComparison = o1.getClassName().compareTo(o2.getClassName());

                    if (classNameComparison != 0) {
                        return classNameComparison;
                    } else {
                        return o1.getText().compareTo(o2.getText());
                    }
                }
            });
        }
    }

    return rules;
}