Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:invoiceapplication.CopyRowOriginal.java

public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exists in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true);
    } else {//from   w  w  w.j  a v a 2 s  .  c  om
        newRow = worksheet.createRow(destRowNum);
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
    // Loops through source column to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        //Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // if the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Use old cell style
        newCell.setCellStyle(oldCell.getCellStyle());

        // If there is a cell comment, copy
        if (newCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellValue(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        }
    }
}

From source file:IO.FILES.java

public static void removeRow(Sheet sheet, Row row) {
    int rowIndex = row.getRowNum();
    sheet.removeRow(row);// ww w . j a  v  a2s .c o m
    int lastRowNum = sheet.getLastRowNum();
    if ((rowIndex >= 0) && (rowIndex < lastRowNum))
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
}

From source file:io.github.jonestimd.finance.file.excel.SheetParser.java

License:Open Source License

public SheetParser(Sheet sheet, int headerRow) {
    final DataFormatter formatter = new DataFormatter();
    final Map<Integer, String> columnNames = new HashMap<>();
    final int lastRow = sheet.getLastRowNum();
    sheet.getRow(headerRow)/* ww  w. java  2 s .co m*/
            .forEach(cell -> columnNames.put(cell.getColumnIndex(), formatter.formatCellValue(cell)));
    for (int index = headerRow + 1; index <= lastRow; index++) {
        rows.add(getRow(sheet, index, columnNames, formatter));
    }
}

From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java

License:Apache License

private void visitSheet(WorkbookSheet bookSheet) throws SpreadsheetException {

    String sheetName = bookSheet.getSheet().getSheetName();
    if (settings.getIgnoreSheets().contains(sheetName)) {
        logger.debug("Ignoring Sheet ... {}", sheetName);
        return;//from w w w .java  2  s  . c  om
    }
    logger.debug("visitSheet({})", sheetName);
    List<SheetColumn> undeclaredColumns = new ArrayList<>();
    assignColumnIndexes(bookSheet, undeclaredColumns);
    SheetProcessor processor = bookSheet.getProcessor();
    Sheet sheet = bookSheet.getSheet();

    int rowSize = sheet.getLastRowNum() + 1;

    SheetColumn projectColumn = filterByProject(bookSheet);

    // Skip the first row since it is the column header row
    for (int i = sheet.getFirstRowNum() + 1; i < rowSize; i++) {
        Row row = sheet.getRow(i);
        if (row != null) {
            SheetRow sheetRow = new SheetRow(bookSheet, row);
            sheetRow.setUndeclaredColumns(undeclaredColumns);
            try {
                if (accept(sheetRow, processor, projectColumn)) {
                    processor.visit(sheetRow);
                }
            } catch (SpreadsheetException e) {
                handle(e);
            }
        }
    }

}

From source file:it.greenvulcano.excel.reader.BaseReader.java

License:Open Source License

public void processExcel(Workbook workbook) throws ExcelException {
    cleanUp();/*from   w ww. jav a2s.co  m*/
    try {
        startProcess();

        // Discover how many sheets there are in the workbook....
        int numSheets = workbook.getNumberOfSheets();

        // and then iterate through them.
        for (int i = 0; i < numSheets; i++) {

            // Get a reference to a sheet and check to see if it contains
            // any rows.
            Sheet sheet = workbook.getSheetAt(i);
            if (processSheet(sheet, i)) {
                // Note down the index number of the bottom-most row and
                // then iterate through all of the rows on the sheet starting
                // from the very first row - number 1 - even if it is missing.
                // Recover a reference to the row and then call another method
                // which will strip the data from the cells and build lines
                // for inclusion in the resulting object.
                int lastRowNum = sheet.getLastRowNum();
                for (int j = 0; j <= lastRowNum; j++) {
                    if (j % 10 == 0) {
                        ThreadUtils.checkInterrupted(getClass().getSimpleName(), "ExcelFile", null);
                    }
                    Row row = sheet.getRow(j);
                    processRow(row, i, j);
                }
            }
        }

        endProcess();
    } catch (Exception exc) {
        throw new ExcelException("Error parsing Excel", exc);
    }
}

From source file:it.redev.parco.job.AnagraficaParserJob.java

License:Open Source License

@Override
public void execute() throws DatabaseException, RemoteSafeException {
    try {//w ww.j  a v a2  s  .c o  m
        service = new ImportService(getEntityManager(), getUser());

        addInfoMessage("Caricamento file " + getFilePath());

        InputStream file = super.openStream();

        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // POSTAZIONI
        Integer parsed = 0;
        int rows, tot;
        Sheet sheet = workbook.getSheet(POSTAZIONI_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovate " + rows + " righe nel file postazioni");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parsePostazioni(row))
                    ;
                parsed++;
            }
            tot = saveOc();
            if (tot > 0)
                addInfoMessage("Create " + tot + " nuove postazioni e/o aree e/o province");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornate " + (parsed - tot) + " postazioni e/o aree e/o province");
            commit();
        }

        // PERSONE
        parsed = 0;
        sheet = workbook.getSheet(PERSONE_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovate " + rows + " persone nel file");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parsePersona(row))
                    ;
                parsed++;
            }
            tot = savePersone();
            if (tot > 0)
                addInfoMessage("Create " + tot + " nuove persone");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornate " + (parsed - tot) + " persone");
            commit();
        }

        // TIPO MEZZO
        parsed = 0;
        sheet = workbook.getSheet(TIPO_MEZZI_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovati " + rows + " tipi mezzo nel file");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parseTipoMezzo(row))
                    ;
                parsed++;
            }
            tot = saveTipoMezzo();
            if (tot > 0)
                addInfoMessage("Creati " + tot + " nuovi tipi mezzo");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornati " + (parsed - tot) + " tipi mezzo");
            commit();
        }

        // MEZZO
        parsed = 0;
        sheet = workbook.getSheet(MEZZI_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovati " + rows + " mezzi");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parseMezzo(row))
                    parsed++;
            }
            tot = saveMezzi();
            if (tot > 0)
                addInfoMessage("Creati " + tot + " nuovi mezzi");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornati " + (parsed - tot) + " mezzi");
            commit();
        }

        // CARTE
        parsed = 0;
        sheet = workbook.getSheet(CARTE_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovate " + rows + " carte");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parseCarta(row))
                    parsed++;
            }
            tot = saveCarte(false);
            if (tot > 0)
                addInfoMessage("Create " + tot + " nuove carte");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornate " + (parsed - tot) + " carte");
            commit();
        }

        // POLIZZE
        parsed = 0;
        sheet = workbook.getSheet(POLIZZE_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovate " + rows + " polizze");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parsePolizza(row))
                    parsed++;
            }
            saveCompagnie();
            tot = savePolizze();
            if (tot > 0)
                addInfoMessage("Create " + tot + " nuove polizze");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornate " + (parsed - tot) + " polizze");
            commit();
        }

        // DOTAZIONI
        parsed = 0;
        sheet = workbook.getSheet(DOTAZIONI_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovate " + rows + " dotazioni");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parseDotazione(row))
                    parsed++;
            }
            tot = saveDotazioni();
            if (tot > 0)
                addInfoMessage("Create " + tot + " nuove dotazioni");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornate " + (parsed - tot) + " dotazioni");
            commit();
        }

        // PIN CARD
        parsed = 0;
        sheet = workbook.getSheet(PIN_SHEET);
        if (sheet != null) {
            rows = sheet.getLastRowNum();
            addInfoMessage("Trovati " + rows + " pin card");
            for (int i = 1; i <= rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null && parsePincard(row))
                    parsed++;
            }
            tot = savePincard(true);
            if (tot > 0)
                addInfoMessage("Create " + tot + " nuove pin card");
            if ((parsed - tot) > 0)
                addInfoMessage("Aggiornate " + (parsed - tot) + " pin card");
            commit();
        }

        file.close();
    } catch (FileNotFoundException e) {
        throw new DatabaseException(e);
    } catch (IOException e) {
        throw new DatabaseException(e);
    } catch (Exception e) {
        throw new DatabaseException(e);
    } finally {
        super.removeFileQuietly();
    }
}

From source file:it.redev.parco.job.Q8ParserJob.java

License:Open Source License

@Override
public void execute() throws DatabaseException, RemoteSafeException {
    try {//from   w  w  w. j av a  2s  .com
        addInfoMessage("Caricamento file " + getFilePath());

        InputStream file = super.openStream();

        HSSFWorkbook workbook = new HSSFWorkbook(file);

        Sheet sheet = workbook.getSheetAt(0);

        int rows = sheet.getLastRowNum();

        for (int i = 10; i <= rows; i++) {
            Row row = sheet.getRow(i);
            Rifornimento rif = parse(row);
            if (rif != null) {
                super.addRifornimento(rif);
                super.nuovoScontrino(rif);
            }
        }

        super.save();

        file.close();
    } catch (FileNotFoundException e) {
        throw new DatabaseException(e);
    } catch (IOException e) {
        throw new DatabaseException(e);
    } catch (Exception e) {
        throw new DatabaseException(e);
    } finally {
        super.removeFileQuietly();
    }

    enqueueLinkedJob();
}

From source file:it.redev.parco.job.SoccorsiParserJob.java

License:Open Source License

@Override
public void execute() throws DatabaseException, RemoteSafeException {
    try {/*from  w w  w. j a  va  2  s .co m*/
        addInfoMessage("Caricamento file " + getFilePath());

        InputStream file = super.openStream();

        HSSFWorkbook workbook = new HSSFWorkbook(file);

        Sheet sheet = workbook.getSheetAt(0);

        int rows = sheet.getLastRowNum();

        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            super.addSoccorso(parse(row));
        }

        getInfo().setDataInizio(DateUtils.create(year, month - 1, 1));
        getInfo().setDataFine(DateUtils.lastDayOfMoth(getInfo().getDataInizio()));
        super.saveSoccorsi();

        file.close();
    } catch (FileNotFoundException e) {
        throw new DatabaseException(e);
    } catch (IOException e) {
        throw new DatabaseException(e);
    } catch (Exception e) {
        throw new DatabaseException(e);
    } finally {
        super.removeFileQuietly();
    }

    // Enqueue job soccorsi
    SoccorsiJob job = new SoccorsiJob(month, year, getUser());
    getEntityManager().persist(job.toJob());
}

From source file:it.smartcommunitylab.riciclo.app.importer.converter.DataImporter.java

License:Apache License

private List<Map<String, String>> getSheetMap(Sheet sheet) {
    Row row = sheet.getRow(0);/*  w w  w .java  2  s  .  c om*/
    List<String> keys = new ArrayList<String>();
    int firstRow = 1;
    if (row.getLastCellNum() != 1) {
        for (int j = 0; j < row.getLastCellNum(); j++) {
            String key = WordUtils
                    .capitalizeFully(" " + getCellValue(row.getCell(j)).replace(' ', '_'), new char[] { '_' })
                    .replace("_", "").trim();
            keys.add(key);
        }
    } else {
        firstRow = 1;
        keys.add("valore");
    }

    List<Map<String, String>> result = new ArrayList<Map<String, String>>();
    for (int i = firstRow; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        Map<String, String> map = new TreeMap<String, String>();
        boolean add = false;
        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (j >= keys.size()) {
                continue;
            }
            if (row.getCell(j) != null) {
                String value = getCellValue(row.getCell(j)).replace("_", " ").trim();
                if (!value.isEmpty()) {
                    add = true;
                }
                try {
                    map.put(keys.get(j), value);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else {
                map.put(keys.get(j), "");
            }
        }
        if (add) {
            result.add(map);
        }
    }

    return result;
}

From source file:it.smartcommunitylab.ungiorno.importer.Importer.java

License:Apache License

private List<Map<String, String>> getSheetMap(Sheet sheet) {
    System.err.println(sheet.getSheetName());
    Row row = sheet.getRow(0);//  w  ww  .j a v a  2  s.c o m
    List<String> keys = new ArrayList<String>();
    int firstRow = 2;
    if (row.getLastCellNum() != 1) {
        for (int j = 0; j < row.getLastCellNum(); j++) {
            String key = getCellValue(row.getCell(j), null).toUpperCase().replace(' ', '_').trim();
            keys.add(key);
        }
    } else {
        keys.add("valore");
    }

    List<Map<String, String>> result = new ArrayList<Map<String, String>>();
    for (int i = firstRow; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        Map<String, String> map = new TreeMap<String, String>();
        boolean add = false;
        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (j >= keys.size()) {
                continue;
            }
            if (row.getCell(j) != null) {
                String value = getCellValue(row.getCell(j), keys.get(j)).replace("_", " ").trim();
                if (!value.isEmpty()) {
                    add = true;
                }
                try {
                    map.put(keys.get(j), value);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else {
                map.put(keys.get(j), "");
            }
        }
        if (add) {
            result.add(map);
        }
    }

    return result;
}