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

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

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

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:net.sf.excelutils.tags.SheetTag.java

License:Apache License

/**
 * Parse #sheet detail in list by sheetName
 *//*www  . ja  va 2s . c om*/
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {
    String sheetExpr = curCell.getStringCellValue();
    StringTokenizer st = new StringTokenizer(sheetExpr, " ");

    String properties = "";
    String property = "";
    String sheetName = "";
    // parse the collection an object
    int pos = 0;
    while (st.hasMoreTokens()) {
        String str = st.nextToken();
        if (pos == 1) {
            property = str;
        }
        if (pos == 3) {
            properties = str;
        }
        if (pos == 5) {
            sheetName = str;
        }
        pos++;
    }

    // get collection
    Object collection = ExcelParser.parseStr(context, properties);
    if (null == collection) {
        return new int[] { 0, 0, 1 };
    }

    // remove #sheet tag
    sheet.removeRow(curRow);

    // remove merged region in forstart & forend
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() >= curRow.getRowNum() && r.getLastRow() <= curRow.getRowNum()) {
            sheet.removeMergedRegion(i);
            i = i - 1;
        }
    }
    sheet.shiftRows(curRow.getRowNum() + 1, sheet.getLastRowNum(), -1, true, true);

    // get the iterator of collection
    Iterator iterator = ExcelParser.getIterator(collection);
    if (null != iterator) {
        // first obj, use parse method
        Object firstObj = null;
        if (iterator.hasNext()) {
            firstObj = iterator.next();
        }

        // next obj, clone sheet and use parseSheet method
        while (iterator.hasNext()) {
            Object obj = iterator.next();
            ExcelUtils.addValue(context, property, obj);
            try {
                int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet);

                // clone sheet
                Sheet cloneSheet = wb.cloneSheet(sheetIndex);

                // set cloneSheet name
                int cloneSheetIndex = WorkbookUtils.getSheetIndex(wb, cloneSheet);
                setSheetName(obj, wb, cloneSheetIndex, sheetName);

                // parse cloneSheet
                ExcelUtils.parseSheet(context, wb, cloneSheet);
            } catch (Exception e) {
                if (LOG.isErrorEnabled()) {
                    LOG.error("parse sheet error", e);
                }
            }
        }

        if (null != firstObj) {
            ExcelUtils.addValue(context, property, firstObj);
            // set sheet name
            int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet);
            setSheetName(firstObj, wb, sheetIndex, sheetName);
        }
    }

    return new int[] { 0, -1, 0 };
}

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) {

    if (shift == 0)
        return;/*from  w w w . ja v a2 s  .  co m*/

    // get the from & to row
    int fromRow = row.getRowNum();
    int toRow = row.getRowNum() + rowCount - 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() == row.getRowNum()) {
            if (r.getLastRow() > toRow) {
                toRow = r.getLastRow();
            }
            if (r.getFirstRow() < fromRow) {
                fromRow = r.getFirstRow();
            }
        }
    }

    for (int rownum = fromRow; rownum <= toRow; rownum++) {
        Row curRow = WorkbookUtils.getRow(rownum, sheet);
        int lastCellNum = curRow.getLastCellNum();
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);
            Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift);
            toCell.setCellType(fromCell.getCellType());
            toCell.setCellStyle(fromCell.getCellStyle());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                toCell.setCellErrorValue(fromCell.getErrorCellValue());
                break;
            }
            fromCell.setCellValue("");
            fromCell.setCellType(Cell.CELL_TYPE_BLANK);
            // Workbook wb = new Workbook();
            // CellStyle style = wb.createCellStyle();
            // fromCell.setCellStyle(style);
        }

        // process merged region
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);

            List shiftedRegions = new ArrayList();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) {
                    r.setFirstColumn((short) (r.getFirstColumn() + shift));
                    r.setLastColumn((short) (r.getLastColumn() + shift));
                    // have to remove/add it back
                    shiftedRegions.add(r);
                    sheet.removeMergedRegion(i);
                    // we have to back up now since we removed one
                    i = i - 1;
                }
            }

            // readd so it doesn't get shifted again
            Iterator iterator = shiftedRegions.iterator();
            while (iterator.hasNext()) {
                CellRangeAddress region = (CellRangeAddress) iterator.next();
                sheet.addMergedRegion(region);
            }
        }
    }
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readAdditionalMetadata(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Metadatos Adicionales");
    DateFormat dateFormatA = new SimpleDateFormat("MM/dd/yyyy");
    DateFormat dateFormatB = new SimpleDateFormat("yyyy-MM-dd");
    eml.setHierarchyLevel(readCellValue(sheet.getRow(5).getCell(1)));
    eml.setLogoUrl(readCellValue(sheet.getRow(7).getCell(1)));
    try {// w  ww  . j a v a  2 s .co  m
        if (readCellValue(sheet.getRow(5).getCell(4)).matches("\\d{4}-\\d{2}-\\d{2}")) {
            eml.setPubDate(dateFormatB.parse(readCellValue(sheet.getRow(5).getCell(4))));
        } else if (readCellValue(sheet.getRow(5).getCell(4)).matches("\\d{2}/\\d{2}/\\d{4}")) {
            eml.setPubDate(dateFormatA.parse(readCellValue(sheet.getRow(5).getCell(4))));
        } else {
            throw new InvalidFormatException("Error al procesar fecha inicial y final en cobertura temporal: ");
        }
    } catch (ParseException e) {
        throw new InvalidFormatException("Error al procesar fecha inicial y final en cobertura temporal: " + e);
    }
    eml.setPurpose(readCellValue(sheet.getRow(9).getCell(1)));
    switch (readCellValue(sheet.getRow(11).getCell(1))) {
    case "Ningna licencia seleccionada":
        eml.setIntellectualRights(readCellValue(sheet.getRow(12).getCell(1)));
        break;
    case "Creative Commons CCZero":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.cczero.text"));
        break;
    case "Open Data Commons Public Domain Dedication and Licence (PDDL)":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.pddl.text"));
        break;
    case "Open Data Commons Attribution License":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.odcby.text"));
        break;
    case "Open Data Commons Open Database License (ODbL)":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.odbl.text"));
        break;
    default:
        throw new InvalidFormatException("El tipo de licencia elegida es invlida.");
    }
    eml.setAdditionalInfo(readCellValue(sheet.getRow(14).getCell(1)));
    List<String> alternateIdentifiers = new ArrayList<String>();
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Identificador Alterno:")) {
            if (!readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)).isEmpty()) {
                alternateIdentifiers.add(readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)));
            }
        }
    }
    eml.setAlternateIdentifiers(alternateIdentifiers);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readAssociatedParties(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Partes Asociadas");
    Iterator<Row> rowIterator = sheet.rowIterator();
    Agent agent;//from ww w. j  ava2s  .  co m
    Address address;
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Nombre:")) {
            agent = new Agent();
            agent.setFirstName(
                    readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setLastName(
                    readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setPosition(
                    readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setOrganisation(
                    readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setPhone(
                    readCellValue(sheet.getRow(row.getRowNum() + 9).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setEmail(
                    readCellValue(sheet.getRow(row.getRowNum() + 11).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setHomepage(
                    readCellValue(sheet.getRow(row.getRowNum() + 11).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setRole(
                    readCellValue(sheet.getRow(row.getRowNum() + 13).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            address = new Address();
            address.setAddress(
                    readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            address.setCity(
                    readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            address.setProvince(
                    readCellValue(sheet.getRow(row.getRowNum() + 7).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            address.setCountry(
                    readCellValue(sheet.getRow(row.getRowNum() + 7).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            address.setPostalCode(
                    readCellValue(sheet.getRow(row.getRowNum() + 9).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setAddress(address);
            eml.addAssociatedParty(agent);
        }
    }
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readCitations(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Referencias");
    Citation citation = new Citation();
    citation.setIdentifier(readCellValue(sheet.getRow(5).getCell(1)));
    citation.setCitation(readCellValue(sheet.getRow(7).getCell(1)));
    eml.setCitation(citation);//from w w  w .j a va 2s.  c om
    BibliographicCitationSet val = new BibliographicCitationSet();
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Identificacin de la Referencia:")) {
            if (!readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)).isEmpty()
                    || !readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1)).isEmpty()) {
                val.add(readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1)),
                        readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)));
            }
        }
    }
    eml.setBibliographicCitationSet(val);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readCollectionData(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Datos de la Coleccin");
    eml.setCollectionName(readCellValue(sheet.getRow(5).getCell(1)));
    eml.setCollectionId(readCellValue(sheet.getRow(5).getCell(4)));
    eml.setParentCollectionId(readCellValue(sheet.getRow(7).getCell(1)));
    eml.setSpecimenPreservationMethod(readCellValue(sheet.getRow(7).getCell(4)));
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    List<JGTICuratorialUnit> jgtiCuratorialUnits = new ArrayList<JGTICuratorialUnit>();
    JGTICuratorialUnit jgtiCuratorialUnit;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();// w  ww.  ja v  a2s . c o  m
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Tipo de Mtodo:")) {
            switch (readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1))) {
            case "Rango de conteo":
                jgtiCuratorialUnit = new JGTICuratorialUnit();
                jgtiCuratorialUnit.setRangeStart(
                        (int) sheet.getRow(row.getRowNum() + 5).getCell(2).getNumericCellValue());
                jgtiCuratorialUnit
                        .setRangeEnd((int) sheet.getRow(row.getRowNum() + 5).getCell(4).getNumericCellValue());
                jgtiCuratorialUnit.setUnitType(readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(6)));
                jgtiCuratorialUnits.add(jgtiCuratorialUnit);
                break;
            case "Conteo con incertidumbre":
                jgtiCuratorialUnit = new JGTICuratorialUnit();
                jgtiCuratorialUnit
                        .setRangeMean((int) sheet.getRow(row.getRowNum() + 8).getCell(2).getNumericCellValue());
                jgtiCuratorialUnit.setUncertaintyMeasure(
                        (int) sheet.getRow(row.getRowNum() + 8).getCell(4).getNumericCellValue());
                jgtiCuratorialUnit.setUnitType(readCellValue(sheet.getRow(row.getRowNum() + 8).getCell(6)));
                jgtiCuratorialUnits.add(jgtiCuratorialUnit);
                break;
            }
        }
    }
    eml.setJgtiCuratorialUnits(jgtiCuratorialUnits);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readExternallinks(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Enlaces externos");
    eml.setDistributionUrl(readCellValue(sheet.getRow(5).getCell(1)));
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    List<PhysicalData> physicalDatas = new ArrayList<PhysicalData>();
    PhysicalData physicalData = null;//from w w w.  j  a v a  2  s  . c o  m
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Nombre:")) {
            if (!readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)).isEmpty()
                    || !readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(4)).isEmpty()
                    || !readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1)).isEmpty()
                    || !readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(1)).isEmpty()
                    || !readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(4)).isEmpty()) {
                physicalData = new PhysicalData();
                physicalData.setName(readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)));
                physicalData.setCharset(readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(4)));
                physicalData.setDistributionUrl(readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1)));
                physicalData.setFormat(readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(1)));
                physicalData.setFormatVersion(readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(4)));
                physicalDatas.add(physicalData);
            }
        }
    }
    eml.setPhysicalData(physicalDatas);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readKeywords(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Palabras Clave");
    List<KeywordSet> keywordsSet = new ArrayList<KeywordSet>();
    KeywordSet keywordSet = null;//  w  w  w  .  ja  v  a  2s  . com
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Tesauro *REQUERIDO:")) {
            row = rowIterator.next();
            if (!readCellValue(sheet.getRow(row.getRowNum()).getCell(1)).isEmpty()
                    && !readCellValue(sheet.getRow(row.getRowNum() + 2).getCell(1)).isEmpty()) {
                keywordSet = new KeywordSet();
                keywordSet.setKeywordThesaurus(readCellValue(sheet.getRow(row.getRowNum()).getCell(1)));
                keywordSet.setKeywordsString(readCellValue(sheet.getRow(row.getRowNum() + 2).getCell(1)));
                keywordsSet.add(keywordSet);
            }
        }
    }
    eml.setKeywords(keywordsSet);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readSamplingMethods(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Mtodos de Muestreo");
    eml.setStudyExtent(readCellValue(sheet.getRow(5).getCell(1)));
    eml.setSampleDescription(readCellValue(sheet.getRow(7).getCell(1)));
    eml.setQualityControl(readCellValue(sheet.getRow(9).getCell(1)));
    List<String> methodSteps = new ArrayList<String>();
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();/* w  ww .  j  a  va2s .  co m*/
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Descripcin del Paso Metodolgico:")) {
            if (!readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)).isEmpty()) {
                methodSteps.add(readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)));
            }
        }
    }
    eml.setMethodSteps(methodSteps);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readTaxonomicCoverage(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Cobertura Taxonmica");
    List<TaxonomicCoverage> taxonomicCoverages = new ArrayList<TaxonomicCoverage>();
    TaxonomicCoverage taxonomicCoverage = null;
    List<TaxonKeyword> keywords = null;
    TaxonKeyword taxonKeyword = null;/*from www.  j av  a  2  s.c  om*/
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Descripcin:")) {
            if (!readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)).isEmpty()) {
                taxonomicCoverage = new TaxonomicCoverage();
                taxonomicCoverage.setDescription(readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)));
                keywords = new ArrayList<TaxonKeyword>();
                row = rowIterator.next();
                row = rowIterator.next();
                while (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                        .equalsIgnoreCase("Nombre cientfico *REQUERIDO:")) {
                    row = rowIterator.next();
                    if (!readCellValue(sheet.getRow(row.getRowNum()).getCell(1)).isEmpty()) {
                        taxonKeyword = new TaxonKeyword();
                        taxonKeyword.setScientificName(readCellValue(sheet.getRow(row.getRowNum()).getCell(1)));
                        taxonKeyword.setCommonName(readCellValue(sheet.getRow(row.getRowNum()).getCell(4)));
                        taxonKeyword.setRank(readCellValue(sheet.getRow(row.getRowNum()).getCell(7)));
                        keywords.add(taxonKeyword);
                    }
                    row = rowIterator.next();
                }
                taxonomicCoverage.setTaxonKeywords(keywords);
                taxonomicCoverages.add(taxonomicCoverage);
            }
        }
    }
    eml.setTaxonomicCoverages(taxonomicCoverages);
}