Example usage for org.apache.poi.ss.usermodel Row getFirstCellNum

List of usage examples for org.apache.poi.ss.usermodel Row getFirstCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getFirstCellNum

Introduction

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

Prototype

short getFirstCellNum();

Source Link

Document

Get the number of the first cell contained in this row.

Usage

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

private void updateRecordWithCellValues(Row row, Record record) throws DataParserException {
    LinkedHashMap<String, Field> output = new LinkedHashMap<>();
    String sheetName = row.getSheet().getSheetName();
    String columnHeader;//from  w  w w. jav  a2 s. c o m
    Set<String> unsupportedCellTypes = new HashSet<>();
    for (int columnNum = row.getFirstCellNum(); columnNum < row.getLastCellNum(); columnNum++) {
        if (headers.isEmpty()) {
            columnHeader = String.valueOf(columnNum);
        } else {
            if (columnNum >= headers.get(sheetName).size()) {
                columnHeader = String.valueOf(columnNum); // no header for this column.  mismatch
            } else {
                columnHeader = headers.get(sheetName).get(columnNum).getValueAsString();
            }
        }

        Cell cell = row.getCell(columnNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        try {
            output.put(columnHeader, Cells.parseCell(cell, this.evaluator));
        } catch (ExcelUnsupportedCellTypeException e) {
            output.put(columnHeader, Cells.parseCellAsString(cell));
            unsupportedCellTypes.add(e.getCellType().name());
        }
    }

    // Set interesting metadata about the row
    Record.Header hdr = record.getHeader();
    hdr.setAttribute("worksheet", row.getSheet().getSheetName());
    hdr.setAttribute("row", Integer.toString(row.getRowNum()));
    hdr.setAttribute("firstCol", Integer.toString(row.getFirstCellNum()));
    hdr.setAttribute("lastCol", Integer.toString(row.getLastCellNum()));
    record.set(Field.createListMap(output));
    if (unsupportedCellTypes.size() > 0) {
        throw new RecoverableDataParserException(record, Errors.EXCEL_PARSER_05,
                StringUtils.join(unsupportedCellTypes, ", "));
    }
}

From source file:com.tecacet.jflat.excel.PoiExcelReader.java

License:Apache License

protected String[] readRow(Row row) {
    List<String> tokens = new ArrayList<String>();
    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);//from ww w. ja  v a2s.  c o  m
        String cellValue = "";
        if (cell != null) {
            cellValue = getCellContentAsString(cell);
        }
        tokens.add(cellValue);
    }
    return tokens.toArray(new String[tokens.size()]);
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

private int getLastNonBlankRow(Sheet sheet) {
    for (int r = sheet.getLastRowNum(); r >= 0; r--) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (short c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    return r;
                }//  ww w .  j  a  v  a 2s  .c  om
            }
        }
    }
    return 0;
}

From source file:common.ReadExcelData.java

License:Apache License

public static boolean isRowEmpty(Row row) {
    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);/* w w w. j  ava2  s  .  c  o  m*/
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
            return false;
    }
    return true;
}

From source file:de.bund.bfr.knime.pmmlite.io.XlsReader.java

License:Open Source License

private Map<String, Integer> getColumns(Sheet sheet) {
    Map<String, Integer> columns = new LinkedHashMap<>();
    Row firstRow = sheet.getRow(sheet.getFirstRowNum());

    if (firstRow == null) {
        return columns;
    }/* w ww  .  j a  v  a2 s. c o  m*/

    for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
        String name = getData(firstRow.getCell(i));

        if (name != null) {
            columns.put(name, i);
        }
    }

    return columns;
}

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

License:Open Source License

/**
 * @param sbvrXls/*from  www.  j  a va 2s  . com*/
 * @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;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.workbookdata.SheetContext.java

License:Open Source License

public short getColumnFullParentNameColumnNumber() {
    Row row = getSheet().getRow(getHeaderRowNumber());
    for (short cellnum = row.getFirstCellNum(); cellnum <= row.getLastCellNum(); cellnum++) {
        Cell cell = row.getCell(cellnum);
        if (SheetContext.FULL_PARENT_NAME_COLUMN.equals(cell.getStringCellValue())) {
            return cellnum;
        }//from   w  w  w .  j a v a  2 s.c o  m
    }
    return -1;
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

/**
 * Read column header./*from   w  ww  .  java 2 s.c o m*/
 *
 * @param row       Excel row object
 * @param elements  List of DD table elements
 * @param mainTable true if the table is main table.
 */
private void setColumnMappings(Row row, List<DDXmlElement> elements, boolean mainTable) {

    if (row == null || elements == null) {
        return;
    }
    int firstCell = row.getFirstCellNum();
    int lastCell = row.getLastCellNum();

    for (int j = 0; j < elements.size(); j++) {
        DDXmlElement elem = elements.get(j);
        String elemLocalName = elem.getLocalName();
        for (int k = firstCell; k < lastCell; k++) {
            Cell cell = row.getCell(k);
            String colName = cellValueToString(cell, null);
            colName = colName != null ? colName.trim() : "";
            if (colName.equalsIgnoreCase(elemLocalName)) {
                elem.setColIndex(k);
                elem.setMainTable(mainTable);
                break;
            }
        }
    }

}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

/**
 * Goes through all columns and logs missing and redundant columns into conversion log.
 *
 * @param sheetName Excel sheet name./*from ww w.ja  va 2s.c  o m*/
 * @param row       Excel Row object
 * @param metaRow   Excel meta sheet row
 * @param elements  List of XML elements
 */
private void logColumnMappings(String sheetName, Row row, Row metaRow, List<DDXmlElement> elements) {

    int nofColumns = row.getLastCellNum() - row.getFirstCellNum();
    readerLogger.logNumberOfColumns(nofColumns, sheetName);
    if (metaRow != null) {
        int nofMetaColumns = row.getLastCellNum() - row.getFirstCellNum();
        readerLogger.logNumberOfColumns(nofMetaColumns, sheetName + DDXMLConverter.META_SHEET_NAME);
    }

    List<String> missingColumns = new ArrayList<String>();
    List<String> elemNames = new ArrayList<String>();
    for (DDXmlElement element : elements) {
        if (element.getColIndex() < 0) {
            missingColumns.add(element.getLocalName());
        }
        elemNames.add(element.getLocalName().toLowerCase());
    }
    if (missingColumns.size() > 0) {
        readerLogger.logMissingColumns(StringUtils.join(missingColumns, ", "), sheetName);
    }
    List<String> extraColumns = getExtraColumns(sheetName, row, elemNames);
    if (extraColumns.size() > 0) {
        readerLogger.logExtraColumns(StringUtils.join(extraColumns, ", "), sheetName);
    }

    if (metaRow != null) {
        List<String> extraMetaColumns = getExtraColumns(sheetName, metaRow, elemNames);
        if (extraMetaColumns.size() > 0) {
            readerLogger.logExtraColumns(StringUtils.join(extraColumns, ", "),
                    sheetName + DDXMLConverter.META_SHEET_NAME);
        }
    }

}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

/**
 * Find redundant columns from the list of columns.
 *
 * @param sheetName Excel sheet name./*from  ww w.  jav a2  s .c o m*/
 * @param row       Excel row.
 * @param elemNames DD element names.
 * @return List of extra columns added to sheet.
 */
private List<String> getExtraColumns(String sheetName, Row row, List<String> elemNames) {
    List<String> extraColumns = new ArrayList<String>();
    List<Integer> emptyColumns = new ArrayList<Integer>();
    for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
        Cell cell = row.getCell(k);
        String colName = (cell != null) ? cellValueToString(cell, null) : null;
        colName = colName != null ? colName.trim() : "";

        if (colName.equals("")) {
            emptyColumns.add(k);
        } else if (!Utils.isNullStr(colName) && !elemNames.contains(colName.toLowerCase())) {
            extraColumns.add(colName);
        }
    }
    if (emptyColumns.size() > 0) {
        readerLogger.logInfo(sheetName, "Found data from column(s): " + StringUtils.join(emptyColumns, ", ")
                + ", but no column heading is available. The column(s) will be ignored.");
    }

    return extraColumns;
}