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.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readTemporalCoverage(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Cobertura Temporal");
    List<TemporalCoverage> temporalCoverages = new ArrayList<TemporalCoverage>();
    TemporalCoverage temporalCoverage = null;
    DateFormat dateFormatA = new SimpleDateFormat("MM/dd/yyyy");
    DateFormat dateFormatB = new SimpleDateFormat("yyyy-MM-dd");
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();/*from   ww  w. j a va  2 s. c o m*/
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Tipo de cobertura temporal:")) {
            switch (readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1))) {
            case "Fecha Simple":
                try {
                    temporalCoverage = new TemporalCoverage();
                    if (readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(2))
                            .matches("\\d{4}-\\d{2}-\\d{2}")) {
                        temporalCoverage.setStartDate(dateFormatB
                                .parse(readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(2))));
                    } else if (readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(2))
                            .matches("\\d{2}/\\d{2}/\\d{4}")) {
                        temporalCoverage.setStartDate(dateFormatA
                                .parse(readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(2))));
                    } else {
                        throw new InvalidFormatException(
                                "Error al procesar fecha inicial y final en cobertura temporal: ");
                    }
                    temporalCoverages.add(temporalCoverage);
                } catch (ParseException e) {
                    throw new InvalidFormatException(
                            "Error al procesar fecha inicial y final en cobertura temporal: " + e);
                }
                break;
            case "Perodo de Tiempo de Vida":
                temporalCoverage = new TemporalCoverage();
                temporalCoverage
                        .setLivingTimePeriod(readCellValue(sheet.getRow(row.getRowNum() + 8).getCell(2)));
                temporalCoverages.add(temporalCoverage);
                break;
            case "Perodo de Formacin":
                temporalCoverage = new TemporalCoverage();
                temporalCoverage
                        .setFormationPeriod(readCellValue(sheet.getRow(row.getRowNum() + 11).getCell(2)));
                temporalCoverages.add(temporalCoverage);
                break;
            case "Rango de Fechas":
                try {
                    temporalCoverage = new TemporalCoverage();
                    if (readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(2))
                            .matches("\\d{4}-\\d{2}-\\d{2}")) {
                        temporalCoverage.setStartDate(dateFormatB
                                .parse(readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(2))));
                    } else if (readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(2))
                            .matches("\\d{2}/\\d{2}/\\d{4}")) {
                        temporalCoverage.setStartDate(dateFormatA
                                .parse(readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(2))));
                    } else {
                        throw new InvalidFormatException(
                                "Error al procesar fecha inicial y final en cobertura temporal: ");
                    }
                    if (readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(5))
                            .matches("\\d{4}-\\d{2}-\\d{2}")) {
                        temporalCoverage.setEndDate(dateFormatB
                                .parse(readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(5))));
                    } else if (readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(5))
                            .matches("\\d{2}/\\d{2}/\\d{4}")) {
                        temporalCoverage.setEndDate(dateFormatA
                                .parse(readCellValue(sheet.getRow(row.getRowNum() + 14).getCell(5))));
                    } else {
                        throw new InvalidFormatException(
                                "Error al procesar fecha inicial y final en cobertura temporal: ");
                    }
                    temporalCoverages.add(temporalCoverage);
                } catch (ParseException e) {
                    throw new InvalidFormatException(
                            "Error al procesar fecha inicial y final en cobertura temporal: " + e);
                }
                break;
            default:
                break;
            }
        }
    }
    eml.setTemporalCoverages(temporalCoverages);
}

From source file:nl.detoren.ijsco.io.ExcelExport.java

License:Open Source License

/**
 * Sorts (A-Z) rows by String column// w  w w . jav  a  2  s  .  c  o  m
 * @param sheet - sheet to sort
 * @param column - String column to sort by
 * @param rowStart - sorting from this row down
 */

private void sortSheet(XSSFSheet sheet, int column, int rowStart, int rowEnd) {
    try {
        FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
        logger.log(Level.INFO, "sorting sheet: " + sheet.getSheetName());
        boolean sorting = true;
        //int lastRow = sheet.getLastRowNum();
        while (sorting == true) {
            sorting = false;
            for (Row row : sheet) {
                // skip if this row is before first to sort
                if (row.getRowNum() < rowStart)
                    continue;
                // end if this is last row
                if (rowEnd == row.getRowNum())
                    break;
                Row row2 = sheet.getRow(row.getRowNum() + 1);
                if (row2 == null)
                    continue;
                int rownum1 = row.getRowNum();
                int rownum2 = row2.getRowNum();
                CellValue firstValue;
                CellValue secondValue;
                firstValue = evaluator.evaluate(row.getCell(column));
                secondValue = evaluator.evaluate(row2.getCell(column));
                //compare cell from current row and next row - and switch if secondValue should be before first
                if (secondValue.toString().compareToIgnoreCase(firstValue.toString()) < 0) {
                    logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2);
                    sheet.shiftRows(row2.getRowNum(), row2.getRowNum(), -1);
                    logger.log(Level.INFO, "Shifting rows" + sheet.getSheetName() + rownum1 + " - " + rownum2);
                    sheet.shiftRows(row.getRowNum(), row.getRowNum(), 1);
                    sorting = true;
                }
            }
        }
    } catch (Exception ex) {
        logger.log(Level.WARNING, "Failing Shifting rows" + sheet.getSheetName() + "Error " + ex.getMessage());
    }
}

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void processUpdates(Sheet sheet) {
    System.out.println("Process updates on sheet: " + sheet.getSheetName());
    if (hasPrevious()) {
        FileInputStream previousStream = null;
        HSSFWorkbook prevWorkbook = null;
        try {// w  w  w  .j  av a 2  s .c om
            previousStream = new FileInputStream(previous);
            //Get the workbook instance for XLS file
            prevWorkbook = new HSSFWorkbook(previousStream);
            Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL);
            if (prevSheet != null) {
                // Bestaande mensen: eventuele updates
                for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {
                    Row row = it.next();
                    if (row.getRowNum() > 0) {
                        String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue();
                        Row previousRow = getLidFromSheet(lidnummer, prevSheet);
                        processPersonUpdates(row, previousRow);
                    }

                }
            }
        } catch (FileNotFoundException ex) {
            System.out.println("Could not locate file: " + ex.getLocalizedMessage());
        } catch (IOException ex) {
            System.out.println("Problems reading file: " + ex.getLocalizedMessage());
        } finally {
            try {
                if (previousStream != null) {
                    previousStream.close();
                    if (prevWorkbook != null) {
                        FileOutputStream out = new FileOutputStream(previous);

                        prevWorkbook.write(out);
                        out.close();
                    }
                }
            } catch (IOException ex) {
                System.out.println("Problems closing file: " + ex.getLocalizedMessage());
            }
        }
    }
}

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void processQuitters() {
    if (hasPrevious()) {
        Sheet sheet = workbook.getSheet(SorterFactory.GROUP_NAME_ALL);
        List<Row> quitters = new ArrayList<Row>();
        FileInputStream previousStream = null;
        try {/*from  www. j a va2s  .  c o m*/
            previousStream = new FileInputStream(previous);
            //Get the workbook instance for XLS file
            HSSFWorkbook prevWorkbook = new HSSFWorkbook(previousStream);
            Sheet prevSheet = prevWorkbook.getSheet(SorterFactory.GROUP_NAME_ALL);
            if (prevSheet == null) {
                return;
            }
            // Check of er mensen vertrokken zijn
            for (Row row : prevSheet) {
                if (row.getRowNum() > 0) {
                    String lidnummer = row.getCell(NUM_LIDNUMMER_CELL).getStringCellValue();
                    Row currentRow = getLidFromSheet(lidnummer, sheet);
                    if (currentRow == null) {
                        quitters.add(row);
                    }
                }
            }
        } catch (IOException ex) {
            System.out.println("Error Reading the previous file: " + ex.getLocalizedMessage());
            return;
        } finally {
            try {
                if (previousStream != null) {
                    previousStream.close();
                }
            } catch (IOException ex) {
                System.out.println("Problems closing file: " + ex.getLocalizedMessage());
            }
        }
        if (quitters.isEmpty()) {
            return;
        }
        Sheet removedSheet = workbook.createSheet(SHEET_REMOVED_PERSONS);

        // Create header
        Row header = removedSheet.createRow(0);
        //Lidnummer   Achternaam   Tussenvoegsel   Voornaam   Geslacht    Telefoonnummer   Mobiel  Geboortedatum

        Cell lidnummer = header.createCell(0);
        lidnummer.setCellValue("Lidnummer");
        lidnummer.setCellStyle(headingStyle);
        Cell achternaam = header.createCell(1);
        achternaam.setCellValue("Achternaam");
        achternaam.setCellStyle(headingStyle);
        Cell tussenvoegsel = header.createCell(2);
        tussenvoegsel.setCellValue("Tussenvoegsel");
        tussenvoegsel.setCellStyle(headingStyle);
        Cell voornaam = header.createCell(3);
        voornaam.setCellValue("Voornaam");
        voornaam.setCellStyle(headingStyle);
        Cell geslacht = header.createCell(4);
        geslacht.setCellValue("Geslacht");
        geslacht.setCellStyle(headingStyle);
        Cell telefoonnummer = header.createCell(5);
        telefoonnummer.setCellValue("Telefoonnummer");
        telefoonnummer.setCellStyle(headingStyle);
        Cell geboortedatum = header.createCell(6);
        geboortedatum.setCellValue("Geboortedatum");
        geboortedatum.setCellStyle(headingStyle);

        // Iterate over quitters
        int index = 1;
        for (Row quitter : quitters) {
            Row r = removedSheet.createRow(index);
            r.createCell(0).setCellValue(quitter.getCell(0).getStringCellValue());
            r.createCell(1).setCellValue(quitter.getCell(1).getStringCellValue());
            r.createCell(2).setCellValue(quitter.getCell(2).getStringCellValue());
            r.createCell(3).setCellValue(quitter.getCell(3).getStringCellValue());
            r.createCell(4).setCellValue(quitter.getCell(5).getStringCellValue());
            r.createCell(5).setCellValue(quitter.getCell(10).getStringCellValue());
            r.createCell(6).setCellValue(quitter.getCell(21).getStringCellValue());
            index++;
        }

        removedSheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 6));
        int numcells = removedSheet.getRow(0).getLastCellNum();
        for (int i = 0; i < numcells; i++) {
            removedSheet.autoSizeColumn(i);
        }
    }
}

From source file:npv.importer.XlsImporter.java

private void findTag(HSSFSheet sheet, String searchTag) {
    //looking for '#Ri' tag
    HSSFRow row = sheet.getRow(0);//from   ww w.  java  2s.c  o m
    Iterator<Row> rowIterator = sheet.iterator();
    Iterator<Cell> cellIterator;
    boolean isFound = false;

    while (rowIterator.hasNext()) {
        if (!isFound) {
            Row rRow = rowIterator.next();
            cellIterator = rRow.cellIterator();
            while (cellIterator.hasNext()) {
                Cell rCell = cellIterator.next();
                if (rCell.getCellType() == Cell.CELL_TYPE_STRING && rCell.getStringCellValue().equals(tag)) {
                    rPosition[0] = rRow.getRowNum();
                    rPosition[1] = rCell.getColumnIndex();
                    isFound = true;
                    break;
                }
            }
        } else {
            break;
        }
    }
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellComment.java

License:Open Source License

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
    Collections.reverse(parameters);

    if (parameters.size() == 2) {
        throwException(_session, "please specify both a row and a column");
    }/*from   w  w  w  . j  ava 2  s  .  c om*/

    cfSpreadSheetData spreadsheet = (cfSpreadSheetData) parameters.get(0);
    Sheet sheet = spreadsheet.getActiveSheet();

    if (parameters.size() == 3) {
        int rowNo = parameters.get(1).getInt() - 1;
        int columnNo = parameters.get(0).getInt() - 1;

        if (rowNo < 0)
            throwException(_session, "row must be 1 or greater (" + rowNo + ")");
        if (columnNo < 0)
            throwException(_session, "column must be 1 or greater (" + columnNo + ")");

        cfStructData sd = new cfStructData();

        Row row = sheet.getRow(rowNo);
        if (row != null) {
            Cell cell = row.getCell(columnNo);
            if (cell != null) {
                Comment comment = cell.getCellComment();
                if (comment != null) {
                    sd.setData("column", new cfNumberData(columnNo));
                    sd.setData("row", new cfNumberData(rowNo));
                    sd.setData("author", new cfStringData(comment.getAuthor()));
                    sd.setData("comment", new cfStringData(comment.getString().getString()));
                }
            }
        }

        return sd;
    } else {
        cfArrayData arr = cfArrayData.createArray(1);

        Iterator<Row> rowIT = sheet.rowIterator();
        while (rowIT.hasNext()) {
            Row row = rowIT.next();

            Iterator<Cell> cellIT = row.cellIterator();
            while (cellIT.hasNext()) {
                Cell cell = cellIT.next();
                Comment comment = cell.getCellComment();
                if (comment != null) {
                    cfStructData sd = new cfStructData();
                    sd.setData("column", new cfNumberData(cell.getColumnIndex() + 1));
                    sd.setData("row", new cfNumberData(row.getRowNum() + 1));
                    sd.setData("author", new cfStringData(comment.getAuthor()));
                    sd.setData("comment", new cfStringData(comment.getString().getString()));
                    arr.addElement(sd);
                }
            }
        }

        return arr;
    }

}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellFormula.java

License:Open Source License

private cfData getAllFormulaForSheet(cfSession _session, cfSpreadSheetData spreadsheet)
        throws cfmRunTimeException {
    cfArrayData array = cfArrayListData.createArray(1);

    Iterator<Row> rowIt = spreadsheet.getActiveSheet().rowIterator();
    while (rowIt.hasNext()) {
        Row row = rowIt.next();

        Iterator<Cell> cellIt = row.cellIterator();
        while (cellIt.hasNext()) {
            Cell cell = cellIt.next();// w w w.  j  a v  a  2s .  com

            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                cfStructData s = new cfStructData();
                s.setData("formula", new cfStringData(cell.getCellFormula()));
                s.setData("row", new cfNumberData(row.getRowNum() + 1));
                s.setData("column", new cfNumberData(cell.getColumnIndex() + 1));
                array.addElement(s);
            }
        }
    }

    return array;
}

From source file:org.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body.//from   w ww  .  j ava 2s .com
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");
    String delimiterParam = req.getParameter(PARAM_REQ_DELIMITER);
    CSVStrategy reqCSVstrategy = null;
    if (delimiterParam != null && !delimiterParam.isEmpty()) {
        reqCSVstrategy = new CSVStrategy(delimiterParam.charAt(0), '"', CSVStrategy.COMMENTS_DISABLED);
    }
    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, reqCSVstrategy != null ? reqCSVstrategy : getCsvStrategy());
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:org.apache.any23.plugin.officescraper.ExcelExtractor.java

License:Apache License

private URI getRowURI(URI sheetURI, Row row) {
    return RDFUtils.uri(sheetURI.toString() + "/" + row.getRowNum());
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ??//from  w w  w .j av a2s . c  om
 * 
 * @param sheet 
 * @param rangeAddress 
 */
public static void clearCell(Sheet sheet, CellRangeAddress rangeAddress) {
    int fromRowIndex = rangeAddress.getFirstRow();
    int fromColumnIndex = rangeAddress.getFirstColumn();

    int toRowIndex = rangeAddress.getLastRow();
    int toColumnIndex = rangeAddress.getLastColumn();

    // ???
    List<Row> removeRowList = new ArrayList<Row>();
    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (fromRowIndex <= row.getRowNum() && row.getRowNum() <= toRowIndex) {
            Set<Cell> removeCellSet = new HashSet<Cell>();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                if (fromColumnIndex <= cell.getColumnIndex() && cell.getColumnIndex() <= toColumnIndex) {
                    removeCellSet.add(cell);
                }
            }
            for (Cell cell : removeCellSet) {
                row.removeCell(cell);
            }
        }
        if (row.getLastCellNum() == -1) {
            removeRowList.add(row);
        }
    }
    for (Row row : removeRowList) {
        sheet.removeRow(row);
    }
}