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:org.eclipse.jubula.client.core.businessprocess.importfilter.ExcelImportFilter.java

License:Open Source License

/**
 * parses a file and returns the data as DataTable structure
 * //from ww  w.  ja  va2s .  co m
 * @param dataDir
 *      directory for data files
 * @param file
 *      data source File
 * @return
 *      filled TestDataManager with new data
 * @throws IOException
 *      error occurred while reading data source
 */
public DataTable parse(File dataDir, String file) throws IOException, DataReadException {

    DataTable filledDataTable;
    final FileInputStream inStream = findDataFile(dataDir, file);
    try {
        Workbook wb;
        if (file.endsWith(".xls")) { //$NON-NLS-1$
            POIFSFileSystem fs = new POIFSFileSystem(inStream);
            wb = new HSSFWorkbook(fs);
        } else {
            wb = new XSSFWorkbook(inStream);
        }
        // Open the first sheet
        Sheet sheet = wb.getSheetAt(0);
        final int lastRowNum = sheet.getLastRowNum();
        final int firstRowNum = sheet.getFirstRowNum();
        // iterate over rows
        if (sheet.getRow(firstRowNum) == null) {
            return new DataTable(0, 0);
        }
        final int height = lastRowNum - firstRowNum + 1;
        final int width = sheet.getRow(firstRowNum).getLastCellNum()
                - sheet.getRow(firstRowNum).getFirstCellNum();
        filledDataTable = new DataTable(height, width);
        for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            final short lastCellNum = row.getLastCellNum();
            final short firstCellNum = row.getFirstCellNum();
            for (int cellNr = firstCellNum; cellNr < lastCellNum; cellNr++) {
                Cell cell = row.getCell(cellNr);
                String cellString = getExcelCellString(cell);
                filledDataTable.updateDataEntry(rowNum, cellNr, cellString);
            }
        }
    } catch (IOException e) {
        throw e; // just pass on, don't fall through to Throwable
    } catch (Throwable t) {
        throw new DataReadException(t);
    } finally {
        inStream.close();
    }

    /* fix issues with documents saved via open office 
     * if the document has been saved via open office it contains one ore many
     * "null" columns at the end of the data table; these columns are truncated 
     */
    while ((filledDataTable.getColumnCount() > 0)
            && (StringUtils.isBlank(filledDataTable.getData(0, filledDataTable.getColumnCount() - 1)))) {
        int newHeight = filledDataTable.getRowCount();
        int newWidth = filledDataTable.getColumnCount() - 1;
        DataTable cleanedFilledDataTable = new DataTable(newHeight, newWidth);
        for (int i = 0; i < newHeight; i++) {
            for (int j = 0; j < newWidth; j++) {
                cleanedFilledDataTable.updateDataEntry(i, j, filledDataTable.getData(i, j));
            }
        }
        filledDataTable = cleanedFilledDataTable;
    }

    return filledDataTable;
}

From source file:org.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java

License:Open Source License

public Model parseFile(String fileName) {
    if (relationshipUri == null) {
        return null;
    }//from   w  ww  .  jav  a2 s  . co  m

    FileInputStream in = null;
    Workbook wb = null;

    try {
        in = new FileInputStream(fileName);
        wb = WorkbookFactory.create(in);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            in.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    Model model = ModelFactory.createDefaultModel();
    model.setNsPrefixes(ConfigSingleton.getInstance().getNsPrefixes());

    HashMap<Sheet, Object[]> sheetResourceMap = new HashMap<Sheet, Object[]>();

    // Loop for Resources defined in Mapper file
    for (String en : mapperTable.getNameList()) {
        MapperEntry e = mapperTable.getEntry(en);
        String type = e.getType();
        String line = e.getLine();
        String uri = e.getUri();

        // parse line definition in Mapper file
        String[] ls = line.split(",");
        if (ls.length < 3) {
            System.err.println("line must has at least sheet, start row, and end row information");
            continue;
        }
        String ssheet = ls[0].trim();
        String sstart = ls[1].trim();
        String send = ls[2].trim();
        Sheet sheet = null;
        try {
            sheet = wb.getSheetAt(Integer.parseInt(ssheet));
        } catch (NumberFormatException ex) {
            sheet = wb.getSheet(ssheet);
        }
        if (sheet == null) {
            System.err.println("target sheet is not found");
            continue;
        }

        int start = Integer.parseInt(sstart);
        int end = sheet.getLastRowNum();
        if (!send.equals("*")) {
            end = Integer.parseInt(send);
        }
        String cond_cellstring = null;
        boolean exist = true;
        if (ls.length > 3) {
            String scond = ls[3].trim();
            if (scond.startsWith("exist")) {
                cond_cellstring = scond.substring(6, scond.length() - 1).trim();
            } else if (scond.startsWith("notexist")) {
                exist = false;
                cond_cellstring = scond.substring(9, scond.length() - 1).trim();
            }
        }

        // map to find referenced resource later
        Object[] resourceMap = sheetResourceMap.get(sheet);
        if (resourceMap == null) {
            resourceMap = new Object[sheet.getLastRowNum() + 1];
            Arrays.fill(resourceMap, null);
            sheetResourceMap.put(sheet, resourceMap);
        }

        // Loop of excel table rows to find the resource 
        for (int j = start; j <= end; j++) {
            if (sheet.getRow(j) == null) {
                continue;
            }
            if (cond_cellstring != null) {
                Cell cell = getCell(sheet, cond_cellstring, j);
                String value = getCellValue(cell);
                if (value == null && exist || value != null && !exist) {
                    continue;
                }
            }
            // generate URI for this resource
            String[] uris = uri.split(",");
            String format = uris[0].trim();
            String uriString = format;
            if (uris.length == 3) {
                Cell cell = getCell(sheet, uris[1].trim(), j);
                String value1 = getCellValue(cell);
                cell = getCell(sheet, uris[2].trim(), j);
                String value2 = getCellValue(cell);
                uriString = String.format(format, value1, value2);
            } else if (uris.length == 2) {
                Cell cell = getCell(sheet, uris[1].trim(), j);
                String value = getCellValue(cell);
                uriString = String.format(format, value);
            }

            // create a Resource in RDF model with URI and resource type defined in Mapper file
            Resource resource = null;
            try {
                resource = model.createResource(relationshipUri + URLEncoder.encode(uriString, "UTF-8"));

                type = getNameUri(type.trim(), model);
                resource.addProperty(RDF.type, model.createResource(type));
            } catch (UnsupportedEncodingException e1) {
                e1.printStackTrace();
            }
            if (resource == null) {
                continue;
            }

            // Keep resource map for current row which will be used to generate reference URI later
            Map<String, Resource> curResMap = (Map<String, Resource>) resourceMap[j];
            if (curResMap == null) {
                curResMap = new HashMap<String, Resource>();
                resourceMap[j] = curResMap;
            }
            curResMap.put(en, resource);

            // Loop for Properties for this resource defined in Mapper file
            for (String propName : e.getPropertyNameList()) {
                MapperEntry.Property prop = e.getProperty(propName);
                if (prop == null) {
                    continue;
                }
                String propType = prop.getType();
                if (propType == null) {
                    continue;
                }
                if (propType.equalsIgnoreCase("resource")) {
                    // assume that prop contains "reference" information in Mapper file
                    String reference = prop.getReference();
                    if (reference != null) {
                        processReference(model, resource, propName, reference, resourceMap, j);
                    }
                } else {
                    // assume that prop contains "column" information in Mapper file
                    String[] tokens = prop.getColumn().trim().split(",");
                    String fmt = null;
                    String column = tokens[0];
                    if (tokens.length > 1) {
                        fmt = tokens[0];
                        column = tokens[1];
                    }
                    Cell cell = getCell(sheet, column, j);
                    if (cell != null) {
                        String value = getCellValue(cell);
                        if (value != null) {
                            if (fmt != null) {
                                value = String.format(fmt, value);
                            }
                            String qpname = propName.trim();
                            qpname = getNameUri(qpname, model);
                            Property property = model.createProperty(qpname);
                            Literal literal = model.createLiteral(value);
                            resource.addLiteral(property, literal);
                        }
                    }
                }
            }
        }
    }
    return model;
}

From source file:org.eclipse.lyo.samples.excel.adapter.MapperTable.java

License:Open Source License

public void initialize(String fileName) {
    FileInputStream in = null;/*from  ww  w . j  av a2 s.c  o m*/
    Workbook wb = null;

    try {
        in = new FileInputStream(fileName);
        wb = WorkbookFactory.create(in);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            in.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    String lastName = null;
    Sheet sheet = wb.getSheetAt(0); //wb.getFirstVisibleTab() + 1);
    int start = 2; // skip row 0 and 1
    for (int j = start; j <= sheet.getLastRowNum(); j++) {
        Row row = sheet.getRow(j);
        if (row == null) {
            continue;
        }

        /* 0,    1,    2,    3   */
        /* name, type, line, uri */
        String name = getCellValue(row, 0);
        if (name != null) {
            MapperEntry entry = new MapperEntry(name);
            entry.setType(getCellValue(row, 1)); /* type */
            entry.setLine(getCellValue(row, 2)); /* line */
            entry.setUri(getCellValue(row, 3)); /* uri */
            entryMap.put(name, entry);
            nameList.add(name);
            lastName = name;
            System.out.println("entry " + j + " : name=" + entry.getName() + ", type=" + entry.getType()
                    + ", line=" + entry.getLine() + ", uri=" + entry.getUri());
            continue;
        }

        if (lastName != null) {
            /* 4,         5,         6,        7        */
            /* prop:name, prop:type, prop:col, prop:ref */
            String propName = getCellValue(row, 4);
            if (propName != null) {
                MapperEntry entry = entryMap.get(lastName);
                MapperEntry.Property prop = entry.addProperty(propName);
                prop.setType(getCellValue(row, 5)); /* prop:type */
                prop.setColumn(getCellValue(row, 6)); /* prop:col */
                prop.setReference(getCellValue(row, 7)); /* prop:ref */
                System.out.println("property : name=" + prop.getName() + ", type=" + prop.getType()
                        + ", column=" + prop.getColumn() + ", reference=" + prop.getReference());
            }
        }
    }
}

From source file:org.eclipse.rcptt.ecl.data.apache.poi.impl.internal.commands.ReadExcelFileService.java

License:Open Source License

private void readRows(Table table, Sheet sheet) {
    int maxRownum = sheet.getLastRowNum() + 1;
    for (int rownum = 0; rownum < maxRownum; rownum++) {
        Row row = sheet.getRow(rownum);/*www .  j a v  a  2 s.  com*/
        readRow(table, row);
    }
}

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy sheets./*from w  ww  .  j  a  va 2s  .c o m*/
 *
 * @param _newSheet the new sheet
 * @param _sheet the sheet
 * @param _copyStyle the copy style
 */
protected void copySheets(final Sheet _newSheet, final Sheet _sheet, final boolean _copyStyle) {
    int maxColumnNum = 0;
    final Map<Integer, CellStyle> styleMap = _copyStyle ? new HashMap<>() : null;
    for (int i = _sheet.getFirstRowNum(); i <= _sheet.getLastRowNum(); i++) {
        final Row srcRow = _sheet.getRow(i);
        final Row destRow = _newSheet.createRow(i);
        if (srcRow != null) {
            copyRow(_sheet, _newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        _newSheet.setColumnWidth(i, _sheet.getColumnWidth(i));
    }
}

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

/**
 * @return false if all cells in a column is not empty or null true if one or more cells in the column is empty or null.
 *//*  w  ww .  j a  va2  s.  c  o  m*/
public static Boolean columnHasEmpty(final Sheet sheet, final int columnIndex) {
    int index = 0;
    try {
        Row row = sheet.getRow(index);
        if (row == null) {
            return true;
        }
        final int lastRowNo = sheet.getLastRowNum();
        while (index <= lastRowNo) {
            if (row == null) {
                return true;
            }
            if (PoiUtil.getCellValue(row.getCell(columnIndex)) == null
                    || "".equalsIgnoreCase(PoiUtil.getCellValue(row.getCell(columnIndex)).toString())) {
                return true;
            }
            index++;
            row = sheet.getRow(index);
        }
    } catch (final Exception e) {
        PoiUtil.LOG.error(e.getMessage(), e);
    }
    return false;
}

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

public static Integer getLastRowNum(final Sheet sheet) {
    Integer lastRowNum = sheet.getLastRowNum() + 1;

    if (lastRowNum == 1) {
        return 0;
    }//from  w  w  w. j  a v a2 s. com

    Row row = null;
    int start = 0;
    int end = 0;

    do {
        lastRowNum--;

        row = sheet.getRow(lastRowNum);
        if (row == null) {
            continue;
        }
        start = row.getFirstCellNum();
        end = row.getLastCellNum() - 1;

    } while (PoiUtil.rowIsEmpty(sheet, lastRowNum, start, end) && lastRowNum > 0);

    return lastRowNum;
}

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 *
 * Note, this method will not update any formula references.
 *
 * @param sheet/*from   w  w w  . j a v a2  s  .com*/
 * @param column
 */
public static void deleteColumn(final Sheet sheet, final int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        final Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        // if the row doesn't have this many columns then we are good; next!
        final int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            final Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            final Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                final Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

/**
 * parse excel file data to java object/*from w ww.  j av a 2  s .  c o  m*/
 * 
 * @param workbookInputStream
 * @param sheetProcessors
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public static void read(InputStream workbookInputStream, ExcelReadSheetProcessor<?>... sheetProcessors) {
    Assert.isTrue(workbookInputStream != null, "workbookInputStream can't be null");
    Assert.isTrue(sheetProcessors != null && sheetProcessors.length != 0, "sheetProcessor can't be null");
    try {
        Workbook workbook = WorkbookFactory.create(workbookInputStream);
        for (ExcelReadSheetProcessor<?> sheetProcessor : sheetProcessors) {
            ExcelReadContext context = new ExcelReadContext();
            try {
                Class clazz = sheetProcessor.getTargetClass();
                Integer sheetIndex = sheetProcessor.getSheetIndex();
                String sheetName = sheetProcessor.getSheetName();
                context.setCurSheetIndex(sheetIndex);
                context.setCurSheetName(sheetName);

                Sheet sheet = null;
                if (sheetName != null) {
                    try {
                        sheet = workbook.getSheet(sheetName);
                    } catch (IllegalArgumentException e) {
                        // ignore
                    }
                    if (sheet != null && sheetIndex != null
                            && !sheetIndex.equals(workbook.getSheetIndex(sheet))) {
                        throw new IllegalArgumentException(
                                "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match.");
                    }
                } else if (sheetIndex != null) {
                    try {
                        sheet = workbook.getSheetAt(sheetIndex);
                    } catch (IllegalArgumentException e) {
                        // ignore
                    }
                } else {
                    throw new IllegalArgumentException("sheetName or sheetIndex can't be null");
                }
                if (sheet == null) {
                    ExcelReadException e = new ExcelReadException(
                            "Sheet Not Found Exception. for sheet name:" + sheetName);
                    e.setCode(ExcelReadException.CODE_OF_SHEET_NOT_EXSIT);
                    throw e;
                }

                if (sheetIndex == null) {
                    sheetIndex = workbook.getSheetIndex(sheet);
                }
                if (sheetName == null) {
                    sheetName = sheet.getSheetName();
                }
                // do check
                Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMapping = new HashMap<Integer, Map<String, ExcelReadFieldMappingAttribute>>();
                Map<String, Map<String, ExcelReadFieldMappingAttribute>> src = null;
                if (sheetProcessor.getFieldMapping() != null) {
                    src = sheetProcessor.getFieldMapping().export();
                }
                convertFieldMapping(sheet, sheetProcessor, src, fieldMapping);
                if (sheetProcessor.getTargetClass() != null && sheetProcessor.getFieldMapping() != null
                        && !Map.class.isAssignableFrom(sheetProcessor.getTargetClass())) {
                    readConfigParamVerify(sheetProcessor, fieldMapping);
                }

                // proc sheet
                context.setCurSheet(sheet);
                context.setCurSheetIndex(sheetIndex);
                context.setCurSheetName(sheet.getSheetName());
                context.setCurRow(null);
                context.setCurRowData(null);
                context.setCurRowIndex(null);
                context.setCurColIndex(null);
                context.setCurColIndex(null);
                // beforeProcess
                sheetProcessor.beforeProcess(context);

                if (sheetProcessor.getPageSize() != null) {
                    context.setDataList(new ArrayList(sheetProcessor.getPageSize()));
                } else {
                    context.setDataList(new ArrayList());
                }

                Integer pageSize = sheetProcessor.getPageSize();
                int startRow = sheetProcessor.getStartRowIndex();
                Integer rowEndIndex = sheetProcessor.getEndRowIndex();
                int actLastRow = sheet.getLastRowNum();
                if (rowEndIndex != null) {
                    if (rowEndIndex > actLastRow) {
                        rowEndIndex = actLastRow;
                    }
                } else {
                    rowEndIndex = actLastRow;
                }

                ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl();
                if (pageSize != null) {
                    int total = rowEndIndex - startRow + 1;
                    int pageCount = (total + pageSize - 1) / pageSize;
                    for (int i = 0; i < pageCount; i++) {
                        int start = startRow + pageSize * i;
                        int size = pageSize;
                        if (i == pageCount - 1) {
                            size = rowEndIndex - start + 1;
                        }
                        read(controller, context, sheet, start, size, fieldMapping, clazz,
                                sheetProcessor.getRowProcessor(), sheetProcessor.isTrimSpace());
                        sheetProcessor.process(context, context.getDataList());
                        context.getDataList().clear();
                        if (controller.isDoBreak()) {
                            controller.reset();
                            break;
                        }
                    }
                } else {
                    read(controller, context, sheet, startRow, rowEndIndex - startRow + 1, fieldMapping, clazz,
                            sheetProcessor.getRowProcessor(), sheetProcessor.isTrimSpace());
                    sheetProcessor.process(context, context.getDataList());
                    context.getDataList().clear();
                }
            } catch (RuntimeException e) {
                sheetProcessor.onException(context, e);
            } finally {
                sheetProcessor.afterProcess(context);
            }
        }
    } catch (Exception e) {
        if (e instanceof RuntimeException) {
            throw (RuntimeException) e;
        } else {
            throw new RuntimeException(e);
        }
    }
}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

private static <T> void read(ExcelProcessControllerImpl controller, ExcelReadContext<T> context, Sheet sheet,
        int startRow, Integer pageSize, Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> fieldMapping,
        Class<T> targetClass, ExcelReadRowProcessor<T> processor, boolean isTrimSpace) {
    Assert.isTrue(sheet != null, "sheet can't be null");
    Assert.isTrue(startRow >= 0, "startRow must greater than or equal to 0");
    Assert.isTrue(pageSize == null || pageSize >= 1, "pageSize == null || pageSize >= 1");
    Assert.isTrue(fieldMapping != null, "fieldMapping can't be null");
    // Assert.isTrue(targetClass != null, "clazz can't be null");

    List<T> list = context.getDataList();
    if (sheet.getPhysicalNumberOfRows() == 0) {
        return;//from w ww. ja  v  a 2  s. co  m
    }
    //
    int endRow = sheet.getLastRowNum();
    if (pageSize != null) {
        endRow = startRow + pageSize - 1;
    }
    for (int i = startRow; i <= endRow; i++) {
        Row row = sheet.getRow(i);
        // proc row
        context.setCurRow(row);
        context.setCurRowIndex(i);
        context.setCurCell(null);
        context.setCurColIndex(null);

        T t = null;
        if (!fieldMapping.isEmpty()) {
            t = readRow(context, row, fieldMapping, targetClass, processor, isTrimSpace);
        }
        if (processor != null) {
            try {
                controller.reset();
                t = processor.process(controller, context, row, t);
            } catch (RuntimeException re) {
                if (re instanceof ExcelReadException) {
                    ExcelReadException ere = (ExcelReadException) re;
                    ere.setRowIndex(row.getRowNum());
                    // ere.setColIndex();
                    throw ere;
                } else {
                    ExcelReadException e = new ExcelReadException(re);
                    e.setRowIndex(row.getRowNum());
                    e.setColIndex(null);
                    e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION);
                    throw e;
                }
            }
        }
        if (!controller.isDoSkip()) {
            list.add(t);
        }
        if (controller.isDoBreak()) {
            break;
        }
    }
}