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.talend.dataprep.transformation.format.XlsWriterTest.java

License:Open Source License

/**
 * Please have a look at <a href="https://jira.talendforge.org/browse/TDP-4571">TDP-4571</a>.
 *//*w  w  w.  j a  va  2 s.co m*/
@Test
public void export_bugfix() throws Exception {
    // given
    SchemaParser.Request request = createSchemaParser("export_bug_fix_xlsx.json");

    Workbook workbook = WorkbookFactory.create(request.getContent());
    assertThat(workbook).isNotNull();
    assertThat(workbook.getNumberOfSheets()).isEqualTo(1);

    Sheet sheet = workbook.getSheetAt(0);
    assertThat(sheet).isNotNull().isNotEmpty();
    assertThat(sheet.getFirstRowNum()).isEqualTo(0);
    assertThat(sheet.getLastRowNum()).isEqualTo(6);

    // assert header content
    Row row = sheet.getRow(0);
    /*
     * "columns": [ { "id": "id", "type": "string" }, { "id": "firstname", "type": "string" }, { "id": "lastname",
     * "type": "string" }, { "id": "age", "type": "integer" }, { "id": "date-of-birth", "type": "date" }, { "id":
     * "alive", "type": "boolean" }, { "id": "city", "type": "string" }, { "id": "7", "type": "float" } ]
     */
    assertThat(row.getCell(0).getRichStringCellValue().getString()).isEqualTo("id");
    assertThat(row.getCell(1).getRichStringCellValue().getString()).isEqualTo("firstname");
    assertThat(row.getCell(2).getRichStringCellValue().getString()).isEqualTo("lastname");
    assertThat(row.getCell(3).getRichStringCellValue().getString()).isEqualTo("age");
    assertThat(row.getCell(4).getRichStringCellValue().getString()).isEqualTo("date-of-birth");
    assertThat(row.getCell(5).getRichStringCellValue().getString()).isEqualTo("alive");
    assertThat(row.getCell(6).getRichStringCellValue().getString()).isEqualTo("city");
    assertThat(row.getCell(7).getRichStringCellValue().getString()).isEqualTo("phone-number");

    // assert first content
    row = sheet.getRow(1);
    /*
     * { "id" : "1", "firstname" : "Clark", "lastname" : "Kent", "age" : "42", "date-of-birth" : "10/09/1940",
     * "alive" : "false", "city" : "", "phone-number" : "" }
     */

    assertRowValues(row, 1, "Clark", "Kent", 42, "10/09/1940", //
            false, "Smallville", "");

    // assert second row content
    row = sheet.getRow(2);
    /*
     * { "id" : "2", "firstname" : "Bruce", "lastname" : "Wayne", "age" : "50", "date-of-birth" : "01/01/1947",
     * "alive" : "true", "city" : "Gotham city", "phone-number" : "null" }
     */
    assertRowValues(row, 2, "Bruce", "Wayne", 50, "01/01/1947", //
            true, "Gotham city", "null");

    // assert third row content
    row = sheet.getRow(3);
    /*
     * { "id" : "3", "firstname" : "Barry", "lastname" : "Allen", "age" : "67", "date-of-birth" : "01/02/1948",
     * "alive" : "true", "city" : "Central city", "phone-number" : "+33 6 89 46 55 34" }
     */
    assertRowValues(row, 3, "Barry", "Allen", 67, "01/02/1948", //
            true, "Central city", "+33 6 89 46 55 34");

    // assert last content
    row = sheet.getRow(sheet.getLastRowNum());
    /*
     * { "id" : "6", "firstname" : "Ray", "lastname" : "Palmer", "age" : "93", "date-of-birth" : "01/05/1951",
     * "alive" : "true", "city" : "Star city" }
     */
    assertRowValues(row, 6, "Ray", "Palmer", 93, "01/05/1951", //
            true, "Star city", "+33-6-89-46-55-34");
}

From source file:org.talend.mdm.webapp.browserecords.server.service.UploadServiceTest.java

License:Open Source License

public void testImportWithDefaultImportCount() throws Exception {
    fileType = "xls"; //$NON-NLS-1$
    multipleValueSeparator = "|"; //$NON-NLS-1$
    file = new File(this.getClass().getResource("Product_defalutImportCount.xls").getFile()); //$NON-NLS-1$
    String[] keys = { "Product/Id" }; //$NON-NLS-1$
    entityModel = getEntityModel("Product.xsd", "Product", "Product", keys); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    UploadService service = new TestUploadService(entityModel, fileType, isPartialUpdate, headersOnFirstLine,
            headerVisibleMap, inheritanceNodePathList, multipleValueSeparator, seperator, encoding,
            textDelimiter, language);/*ww  w. ja v  a 2 s.co  m*/
    List<WSPutItemWithReport> wsPutItemWithReportList = service.readUploadFile(file);

    FileInputStream fileInputStream = new FileInputStream(file);
    POIFSFileSystem poiFSFile = new POIFSFileSystem(fileInputStream);
    Workbook workBook = new HSSFWorkbook(poiFSFile);
    Sheet sheet = workBook.getSheetAt(0);

    assertEquals(15, sheet.getLastRowNum());
    assertEquals(10, wsPutItemWithReportList.size());

    fileType = "csv"; //$NON-NLS-1$
    file = new File(this.getClass().getResource("Product_defalutImportCount.csv").getFile()); //$NON-NLS-1$
    service = new TestUploadService(entityModel, fileType, isPartialUpdate, headersOnFirstLine,
            headerVisibleMap, inheritanceNodePathList, multipleValueSeparator, seperator, encoding,
            textDelimiter, language);
    wsPutItemWithReportList = service.readUploadFile(file);

    assertEquals(10, wsPutItemWithReportList.size());
}

From source file:org.teiid.translator.excel.ExcelUpdateExecution.java

License:Apache License

private void handleInsert() throws TranslatorException {
    Insert insert = (Insert) command;//from   w  w w .  j  a  v  a 2s .c o m
    ExpressionValueSource evs = (ExpressionValueSource) insert.getValueSource();
    Row row = nextRow();
    Sheet sheet = null;
    if (row == null) {
        sheet = workbook.getSheet(this.visitor.getSheetName());
    } else {
        sheet = row.getSheet();
    }
    int last = sheet.getLastRowNum();
    Row newRow = sheet.createRow(last + 1);
    List<Integer> cols = this.visitor.getProjectedColumns();
    for (int i = 0; i < cols.size(); i++) {
        int index = cols.get(i);
        setValue(newRow, index - 1, ((Literal) evs.getValues().get(i)).getValue());
    }
    GeneratedKeys keys = executionContext.getCommandContext().returnGeneratedKeys(
            new String[] { ExcelMetadataProcessor.ROW_ID },
            new Class<?>[] { TypeFacility.RUNTIME_TYPES.INTEGER });
    keys.addKey(Arrays.asList(last + 1));
    result++;
    writeXLSFile();
}

From source file:org.teiid.translator.excel.ExcelUpdateExecution.java

License:Apache License

private void handleDelete() throws TranslatorException {
    while (true) {
        Row row = nextRow();/*from   w  w  w .j  a  va 2  s .co m*/
        if (row == null) {
            break;
        }
        this.rowIterator = null;
        int start = row.getRowNum();
        Sheet sheet = row.getSheet();
        int end = sheet.getLastRowNum();
        //a different iteration style is needed, which will not perform as well for sparse documents
        for (int i = start; i <= end; i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (row.getFirstCellNum() == -1) {
                continue;
            }

            if (!this.visitor.allows(row.getRowNum())) {
                continue;
            }
            sheet.removeRow(row);
            result++;
            modified = true;
        }
    }
}

From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java

License:MIT License

/**
 * Gets the sheet configuration.//from ww w  . ja va2s. c o  m
 *
 * @param sheet
 *            the sheet
 * @param formName
 *            the form name
 * @param sheetRightCol
 *            the sheet right col
 * @return the sheet configuration
 */
private SheetConfiguration getSheetConfiguration(final Sheet sheet, final String formName,
        final int sheetRightCol) {

    SheetConfiguration sheetConfig = new SheetConfiguration();
    sheetConfig.setFormName(formName);
    sheetConfig.setSheetName(sheet.getSheetName());
    int leftCol = sheet.getLeftCol();
    int lastRow = sheet.getLastRowNum();
    int firstRow = sheet.getFirstRowNum();
    int rightCol = 0;
    int maxRow = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > TieConstants.TIE_WEB_SHEET_MAX_ROWS) {
            break;
        }
        maxRow = row.getRowNum();
        int firstCellNum = row.getFirstCellNum();
        if (firstCellNum >= 0 && firstCellNum < leftCol) {
            leftCol = firstCellNum;
        }
        if ((row.getLastCellNum() - 1) > rightCol) {
            int verifiedcol = verifyLastCell(row, rightCol, sheetRightCol);
            if (verifiedcol > rightCol) {
                rightCol = verifiedcol;
            }
        }
    }
    if (maxRow < lastRow) {
        lastRow = maxRow;
    }
    // header range row set to 0 while column set to first column to
    // max
    // column (FF) e.g. $A$0 : $FF$0
    String tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol)
            + TieConstants.CELL_ADDR_PRE_FIX + "0 : " + TieConstants.CELL_ADDR_PRE_FIX
            + WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + "0";
    sheetConfig.setFormHeaderRange(tempStr);
    sheetConfig.setHeaderCellRange(new CellRange(tempStr));
    // body range row set to first row to last row while column set
    // to
    // first column to max column (FF) e.g. $A$1 : $FF$1000
    tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol)
            + TieConstants.CELL_ADDR_PRE_FIX + (firstRow + 1) + " : " + TieConstants.CELL_ADDR_PRE_FIX
            + WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + (lastRow + 1);
    sheetConfig.setFormBodyRange(tempStr);
    sheetConfig.setBodyCellRange(new CellRange(tempStr));
    sheetConfig.setFormBodyType(org.tiefaces.common.TieConstants.FORM_TYPE_FREE);
    sheetConfig.setCellFormAttributes(new HashMap<String, List<CellFormAttributes>>());

    // check it's a hidden sheet
    int sheetIndex = parent.getWb().getSheetIndex(sheet);
    if (parent.getWb().isSheetHidden(sheetIndex) || parent.getWb().isSheetVeryHidden(sheetIndex)) {
        sheetConfig.setHidden(true);
    }

    return sheetConfig;

}

From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java

License:MIT License

/**
 * build a sheet for configuration map./*  ww  w .j a  va2  s . c om*/
 *
 * @param sheet
 *            sheet.
 * @param sheetConfigMap
 *            sheetConfiguration map.
 * @param cellAttributesMap
 *            the cell attributes map
 */
public final void buildSheet(final Sheet sheet, final Map<String, SheetConfiguration> sheetConfigMap,
        final CellAttributesMap cellAttributesMap) {

    if ((sheet.getLastRowNum() <= 0) && (sheet.getRow(0) == null)) {
        // this is a empty sheet. skip it.
        return;
    }
    checkAndRepairLastRow(sheet);

    int sheetRightCol = WebSheetUtility.getSheetRightCol(sheet);

    List<ConfigCommand> commandList = buildCommandListFromSheetComment((XSSFSheet) sheet, sheetRightCol,
            cellAttributesMap);

    boolean hasEachCommand = hasEachCommandInTheList(commandList);

    List<String> formList = new ArrayList<>();

    buildSheetConfigMapFromFormCommand(sheet, sheetConfigMap, commandList, formList, sheetRightCol);
    // match parent command
    matchParentCommand(commandList);
    // setup save attrs in hidden column in the sheet.
    // loop command list again to assemble other command list into sheet
    // configuration
    matchSheetConfigForm(sheetConfigMap, commandList, formList);
    initTemplateForCommand(sheet, sheetConfigMap, formList, hasEachCommand);
}

From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java

License:MIT License

/**
 * check and repair the sheet's lastrow. If the row is blank then remove it.
 *
 * @param sheet//  w  ww  .  ja  v  a 2s .  c o m
 *            the sheet
 */
private final void checkAndRepairLastRow(final Sheet sheet) {
    // repair last row if it's inserted in the configuration generation
    Row lastrow = sheet.getRow(sheet.getLastRowNum());
    // if it's lastrow and all the cells are blank. then remove the lastrow.
    if (lastrow != null) {
        for (Cell cell : lastrow) {
            if ((cell.getCellTypeEnum() != CellType._NONE) && (cell.getCellTypeEnum() != CellType.BLANK)) {
                return;
            }
        }
        sheet.removeRow(lastrow);
    }

}

From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java

License:MIT License

/**
 * build top level configuration map from command list. User can either put
 * tie:form command in the comments (which will transfer to sheetConfig), Or
 * just ignore it, then use whole sheet as one form.
 *
 * @param sheet/*from w w  w .  j  a  v  a  2s.  com*/
 *            sheet.
 * @param sheetConfigMap
 *            sheetConfigMap.
 * @param commandList
 *            command list.
 * @param formList
 *            form list.
 * @param sheetRightCol
 *            the sheet right col
 */
private void buildSheetConfigMapFromFormCommand(final Sheet sheet,
        final Map<String, SheetConfiguration> sheetConfigMap, final List<ConfigCommand> commandList,
        final List<String> formList, final int sheetRightCol) {
    boolean foundForm = false;
    int minRowNum = sheet.getLastRowNum();
    int maxRowNum = sheet.getFirstRowNum();
    for (Command command : commandList) {
        // check whether is form command
        if (command.getCommandTypeName().equalsIgnoreCase(TieConstants.COMMAND_FORM)) {
            foundForm = true;
            FormCommand fcommand = (FormCommand) command;
            sheetConfigMap.put(fcommand.getName(),
                    getSheetConfigurationFromConfigCommand(sheet, fcommand, sheetRightCol));
            formList.add(fcommand.getName());
            if (fcommand.getTopRow() < minRowNum) {
                minRowNum = fcommand.getTopRow();
            }
            if (fcommand.getLastRow() > maxRowNum) {
                maxRowNum = fcommand.getLastRow();
            }
        }
    }
    // if no form found, then use the whole sheet as form
    if (!foundForm) {
        WebSheetUtility.clearHiddenColumns(sheet);
        String formName = sheet.getSheetName();
        SheetConfiguration sheetConfig = getSheetConfiguration(sheet, formName, sheetRightCol);
        FormCommand fcommand = buildFormCommandFromSheetConfig(sheetConfig, sheet);
        commandList.add(fcommand);
        sheetConfig.setFormCommand(fcommand);
        sheetConfigMap.put(formName, sheetConfig);
        formList.add(formName);
        minRowNum = sheet.getFirstRowNum();
        maxRowNum = sheet.getLastRowNum();
    }

    // if skip config then return.
    if (parent.isSkipConfiguration()) {
        return;
    }
    SaveAttrsUtility.setSaveAttrsForSheet(sheet, minRowNum, maxRowNum,
            parent.getCellAttributesMap().getTemplateCommentMap().get(TieConstants.SAVE_COMMENT_KEY_IN_MAP));
}

From source file:org.tiefaces.components.websheet.utility.CellUtility.java

License:MIT License

/**
 * Copy rows./*from w  w w. j a va 2  s . co m*/
 *
 * @param srcSheet
 *            the src sheet
 * @param destSheet
 *            the dest sheet
 * @param srcRowStart
 *            the src row start
 * @param srcRowEnd
 *            the src row end
 * @param destRow
 *            the dest row
 * @param checkLock
 *            the check lock
 * @param setHiddenColumn
 *            the set hidden column
 */
public static void copyRows(final Sheet srcSheet, final Sheet destSheet, final int srcRowStart,
        final int srcRowEnd, final int destRow, final boolean checkLock, final boolean setHiddenColumn) {

    int length = srcRowEnd - srcRowStart + 1;
    if (length <= 0) {
        return;
    }
    destSheet.shiftRows(destRow, destSheet.getLastRowNum(), length, true, false);
    for (int i = 0; i < length; i++) {
        copySingleRow(srcSheet, destSheet, srcRowStart + i, destRow + i, checkLock, setHiddenColumn);
    }
    // If there are are any merged regions in the source row, copy to new
    // row
    for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i);
        if ((cellRangeAddress.getFirstRow() >= srcRowStart) && (cellRangeAddress.getLastRow() <= srcRowEnd)) {
            int targetRowFrom = cellRangeAddress.getFirstRow() - srcRowStart + destRow;
            int targetRowTo = cellRangeAddress.getLastRow() - srcRowStart + destRow;

            CellRangeAddress newCellRangeAddress = new CellRangeAddress(targetRowFrom, targetRowTo,
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            destSheet.addMergedRegion(newCellRangeAddress);
        }
    }
}

From source file:org.tiefaces.components.websheet.utility.CommandUtility.java

License:MIT License

/**
 * Remove the rows.//from   w w  w.  j a v a2s . com
 *
 * @param sheet
 *            the sheet
 * @param rowIndexStart
 *            start row index.
 * @param rowIndexEnd
 *            end row index.
 * @param cachedMap
 *            the cached map
 */
public static void removeRowsInSheet(final Sheet sheet, final int rowIndexStart, final int rowIndexEnd,
        final Map<Cell, String> cachedMap) {

    for (int irow = rowIndexStart; irow <= rowIndexEnd; irow++) {
        removeCachedCellForRow(sheet, irow, cachedMap);
    }
    int irows = rowIndexEnd - rowIndexStart + 1;
    if ((irows < 1) || (rowIndexStart < 0)) {
        return;
    }
    int lastRowNum = sheet.getLastRowNum();
    if (rowIndexEnd < lastRowNum) {
        sheet.shiftRows(rowIndexEnd + 1, lastRowNum, -irows);
    }
    if (rowIndexEnd == lastRowNum) {
        // reverse order to delete rows.
        for (int i = rowIndexEnd; i >= rowIndexStart; i--) {
            removeSingleRowInSheet(sheet, rowIndexStart);
        }
    }
}