Example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

Introduction

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

Prototype

@Override
public int getLastRowNum() 

Source Link

Document

Gets the number last row on the sheet.

Usage

From source file:org.lamsfoundation.lams.admin.service.ImportService.java

License:Open Source License

public List parseUserSpreadsheet(FormFile fileItem, String sessionId) throws IOException {
    results = new ArrayList<ArrayList>();
    HSSFSheet sheet = getSheet(fileItem);
    int startRow = sheet.getFirstRowNum();
    int endRow = sheet.getLastRowNum();

    setupImportStatus(sessionId, endRow - startRow);
    UserDTO userDTO = (UserDTO) SessionManager.getSession(sessionId).getAttribute(AttributeNames.USER);

    log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow);

    HSSFRow row;//from  w ww. ja v  a  2  s.  co m
    User user = null;
    int successful = 0;
    for (int i = startRow + 1; i < endRow + 1; i++) {
        emptyRow = true;
        hasError = false;
        rowResult = new ArrayList<String>();
        row = sheet.getRow(i);
        user = parseUser(row, i);

        if (emptyRow) {
            log.debug("Row " + i + " is empty.");
            break;
        }
        if (hasError) {
            log.debug("Row " + i + " has an error which has been sent to the browser.");
            results.add(rowResult);
            writeErrorsAuditLog(i + 1, rowResult, userDTO);
            updateImportStatus(sessionId, results.size());
            continue;
        } else {
            try {
                service.save(user);
                successful++;
                writeAuditLog(user, userDTO);
                log.debug("Row " + i + " saved user: " + user.getLogin());
            } catch (Exception e) {
                log.debug(e);
                rowResult.add(messageService.getMessage("error.fail.add"));
            }
            if (rowResult.size() > 0) {
                if (log.isDebugEnabled())
                    log.debug("Row " + i + " has " + rowResult.size() + " messages.");
                writeErrorsAuditLog(i + 1, rowResult, userDTO);
            }
            results.add(rowResult);
            updateImportStatus(sessionId, results.size());
        }
    }
    log.debug("Found " + results.size() + " users in spreadsheet.");
    writeSuccessAuditLog(successful, userDTO, "audit.successful.user.import");
    return results;
}

---

From source file:org.lamsfoundation.lams.admin.service.ImportService.java

License:Open Source License

public List parseRolesSpreadsheet(FormFile fileItem, String sessionId) throws IOException {
    results = new ArrayList<ArrayList>();
    HSSFSheet sheet = getSheet(fileItem);
    int startRow = sheet.getFirstRowNum();
    int endRow = sheet.getLastRowNum();

    log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow);

    setupImportStatus(sessionId, endRow - startRow);
    UserDTO userDTO = (UserDTO) SessionManager.getSession(sessionId).getAttribute(AttributeNames.USER);

    HSSFRow row;/*w w w.j  a  va 2  s  .  c o  m*/
    List<String> roles;
    int successful = 0;
    for (int i = startRow + 1; i < endRow + 1; i++) {
        emptyRow = true;
        hasError = false;
        rowResult = new ArrayList<String>();
        row = sheet.getRow(i);

        String login = parseStringCell(row.getCell(LOGIN));
        String orgId = parseStringCell(row.getCell(ORGANISATION));
        roles = parseRolesCell(row.getCell(ROLES));

        if (emptyRow) {
            log.debug("Row " + i + " is empty.");
            break;
        }
        if (hasError) {
            log.debug("Row " + i + " has an error which has been sent to the browser.");
            results.add(rowResult);
            writeErrorsAuditLog(i + 1, rowResult, userDTO);
            updateImportStatus(sessionId, results.size());
            continue;
        } else {
            try {
                saveUserRoles(isSysadmin(sessionId), login, orgId, roles, row);
                successful++;
            } catch (Exception e) {
                log.error("Unable to assign roles to user: " + login, e);
                rowResult.add(messageService.getMessage("error.fail.add"));
            }
            if (rowResult.size() > 0) {
                if (log.isDebugEnabled())
                    log.debug("Row " + i + " has " + rowResult.size() + " messages.");
                writeErrorsAuditLog(i + 1, rowResult, userDTO);
            }
            results.add(rowResult);
            updateImportStatus(sessionId, results.size());
        }
    }
    log.debug("Found " + results.size() + " users in spreadsheet.");
    writeSuccessAuditLog(successful, userDTO, "audit.successful.role.import");
    return results;
}

---

From source file:org.mili.core.text.MockFactory.java

License:Apache License

/**
 * Assert workbook./* ww  w  .jav a  2  s  .  com*/
 *
 * @param workbook the workbook
 */
public static void assertWorkbook(HSSFWorkbook workbook) {
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFRow row = sheet.getRow(0);
    assertEquals("unknown", workbook.getSheetName(0));
    assertEquals(1, sheet.getLastRowNum());
    assertEquals(11, row.getLastCellNum());
    assertEquals("byte", row.getCell((short) 0).getStringCellValue());
    assertEquals("short", row.getCell((short) 1).getStringCellValue());
    assertEquals("integer", row.getCell((short) 2).getStringCellValue());
    assertEquals("char", row.getCell((short) 3).getStringCellValue());
    assertEquals("long", row.getCell((short) 4).getStringCellValue());
    assertEquals("float", row.getCell((short) 5).getStringCellValue());
    assertEquals("double", row.getCell((short) 6).getStringCellValue());
    assertEquals("boolean", row.getCell((short) 7).getStringCellValue());
    assertEquals("string", row.getCell((short) 8).getStringCellValue());
    assertEquals("foo", row.getCell((short) 9).getStringCellValue());
    assertEquals("date", row.getCell((short) 10).getStringCellValue());
    assertEquals("null", row.getCell((short) 11).getStringCellValue());
    row = sheet.getRow(1);
    //        assertEquals("1", row.getCell((short) 0).getStringCellValue());
    //        assertEquals("1", row.getCell((short) 1).getStringCellValue());
    //        assertEquals("1", row.getCell((short) 2).getStringCellValue());
    assertEquals("c", row.getCell((short) 3).getStringCellValue());
    //        assertEquals("1", row.getCell((short) 4).getStringCellValue());
    assertEquals(1.0, row.getCell((short) 5).getNumericCellValue(), 0.0);
    //        assertEquals(1.0, row.getCell((short) 6).getNumericCellValue(), 0.0);
    assertEquals("true", row.getCell((short) 7).getStringCellValue());
    assertEquals("abbas", row.getCell((short) 8).getStringCellValue());
    assertEquals("Foo", row.getCell((short) 9).getStringCellValue());
    assertEquals("Thu Feb 01 00:00:00 CET 3900", row.getCell((short) 10).getStringCellValue());
    assertEquals("", row.getCell((short) 11).getStringCellValue());
}

---

From source file:org.mili.core.text.MockFactory.java

License:Apache License

/**
 * Assert empty workbook./* www .j  a  v  a2s  . c  o m*/
 *
 * @param workbook the workbook
 */
public static void assertEmptyWorkbook(HSSFWorkbook workbook) {
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFRow row = sheet.getRow(0);
    assertEquals("unknown", workbook.getSheetName(0));
    assertEquals(0, sheet.getLastRowNum());
    assertEquals(0, row.getLastCellNum());
    assertEquals("Keine Daten vorhanden !", row.getCell((short) 0).getStringCellValue());
}

---

From source file:org.modeshape.sequencer.msoffice.excel.ExcelMetadataReader.java

License:Apache License

public static ExcelMetadata instance(InputStream stream) throws IOException {
    ExcelMetadata metadata = new ExcelMetadata();
    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream));

    List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>();

    for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) {
        ExcelSheetMetadata meta = new ExcelSheetMetadata();
        meta.setName(wb.getSheetName(sheetInd));
        sheets.add(meta);//from w w  w  .  j a v a2s . c o m

        HSSFSheet worksheet = wb.getSheetAt(sheetInd);
        int lastRowNum = worksheet.getLastRowNum();

        StringBuilder buff = new StringBuilder();
        for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
            HSSFRow row = worksheet.getRow(rowNum);

            // Empty rows are returned as null
            if (row == null) {
                continue;
            }

            int lastCellNum = row.getLastCellNum();
            for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) {
                HSSFCell cell = row.getCell(cellNum);

                // Undefined cells are returned as null
                if (cell == null) {
                    continue;
                }

                /*
                 * Builds a string of body content from all string, numeric,
                 * and formula values in the body of each worksheet.
                 * 
                 *  This code currently duplicates the POI 3.1 ExcelExtractor behavior of
                 *  combining the body text from all worksheets into a single string.
                 */
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    buff.append(cell.getRichStringCellValue().getString());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    buff.append(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    buff.append(cell.getCellFormula());
                    break;
                }

                HSSFComment comment = cell.getCellComment();
                if (comment != null) {
                    // Filter out row delimiter characters from comment
                    String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' ');

                    buff.append(" [");
                    buff.append(commentText);
                    buff.append(" by ");
                    buff.append(comment.getAuthor());
                    buff.append(']');
                }

                if (cellNum < lastCellNum - 1) {
                    buff.append(CELL_DELIMITER_CHAR);
                } else {
                    buff.append(ROW_DELIMITER_CHAR);
                }
            }
        }
        meta.setText(buff.toString());
    }

    metadata.setSheets(sheets);
    metadata.setMetadata(wb.getSummaryInformation());
    return metadata;
}

---

From source file:org.neo4art.colour.write.WriteFileCsv.java

License:Apache License

public void savePixel(Color[] c) {
    try {/* www  .jav  a2  s . co m*/
        int i = 0;
        FileInputStream inp = new FileInputStream(csv);
        HSSFWorkbook workbook = new HSSFWorkbook(inp);
        HSSFSheet worksheet = workbook.getSheet("Pixel image");
        while (i < c.length) {
            HSSFRow row = worksheet.createRow(worksheet.getLastRowNum() + 1);
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

            HSSFCell cell1 = row.createCell(0);
            cell1.setCellStyle(cellStyle);
            HSSFCell cell2 = row.createCell(1);
            cell2.setCellStyle(cellStyle);
            HSSFCell cell3 = row.createCell(2);
            cell3.setCellStyle(cellStyle);

            cell1.setCellValue(c[i].getRed());
            cell2.setCellValue(c[i].getGreen());
            cell3.setCellValue(c[i].getBlue());
            FileOutputStream fileOut = new FileOutputStream(csv);
            workbook.write(fileOut);
            i++;
        }
        workbook.close();
    } catch (Exception e) {

    }
}

---

From source file:org.newcashel.meta.model.ElasticAlias.java

License:Apache License

public static void load(HSSFWorkbook wb, LaunchParms launchParm) throws Exception {

    // load the sheet
    HSSFSheet sheet = wb.getSheet("Aliases");
    if (sheet == null) {
        throw new Exception("The Aliases sheet was not found in BootBook, terminate load process");
    }/*from w  w w  .  ja  v a 2 s .  c  om*/

    NCClass ncCls = NCClass.getNCClass("ElasticAlias");
    Row row = null;

    try {
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            if ((row = sheet.getRow(i)) != null && POIUtil.getCellValue(row, 0) != null
                    && POIUtil.getCellValue(row, 0).length() > 0) {

                String mode = POIUtil.getCellValue(row, 0);
                if (mode == null || mode.length() < 1) {
                    String msg = "deploy mode must be specified for Alias entries ";
                    logger.error(msg);
                    throw new Exception(msg);
                }

                String version = POIUtil.getCellValue(row, 1);

                // if version is set but not the specified launch version, skip it
                if (version != null && version.length() > 0) {
                    if (!(launchParm.getVersion().equals(version))) {
                        continue;
                    }
                }

                ElasticAlias alias = new ElasticAlias(POIUtil.getCellValue(row, 2),
                        POIUtil.getCellValue(row, 3), POIUtil.getCellValue(row, 4),
                        POIUtil.getCellValue(row, 5), POIUtil.getCellValue(row, 6));
                elasticAliases.put(alias.getName(), alias);
            }
        }

    } catch (Exception e) {
        logger.error("exception in GroupAssign load " + e.toString());
    }
}

---

From source file:org.newcashel.meta.model.ElasticIndex.java

License:Apache License

public static void load(HSSFWorkbook wb, LaunchParms launchParm) throws Exception {

    // load the sheet
    HSSFSheet sheet = wb.getSheet("Indexes");
    if (sheet == null) {
        throw new Exception("The Indexes sheet was not found in BootBook, terminate load process");
    }//from   w  ww  .  java 2  s.co m

    NCClass ncCls = NCClass.getNCClass("ElasticIndex");
    Row row = null;

    try {
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            if ((row = sheet.getRow(i)) != null && POIUtil.getCellValue(row, 0) != null
                    && POIUtil.getCellValue(row, 0).length() > 0) {

                String mode = POIUtil.getCellValue(row, 0);
                if (mode == null || mode.length() < 1) {
                    String msg = "deploy mode must be specified for Index entries ";
                    logger.error(msg);
                    throw new Exception(msg);
                }

                String version = POIUtil.getCellValue(row, 1);

                // if version is set but not the specified launch version, skip it
                if (version != null && version.length() > 0) {
                    if (!(launchParm.getVersion().equals(version))) {
                        continue;
                    }
                }

                ElasticIndex index = new ElasticIndex(POIUtil.getCellValue(row, 2),
                        POIUtil.getCellValue(row, 3), POIUtil.getCellValue(row, 4),
                        POIUtil.getCellValue(row, 5), POIUtil.getCellValue(row, 6),
                        POIUtil.getCellValue(row, 7), POIUtil.getCellValue(row, 8),
                        POIUtil.getCellValue(row, 9), POIUtil.getCellValue(row, 10));
                elasticIndexes.put(index.getName(), index);
            }
        }

    } catch (Exception e) {
        logger.error("exception in GroupAssign load " + e.toString());
    }
}

---

From source file:org.newcashel.meta.model.River.java

License:Apache License

public static void load(HSSFWorkbook wb) throws Exception {

    // load the sheet
    HSSFSheet sheet = wb.getSheet("Rivers");
    if (sheet == null) {
        throw new Exception("The River sheet was not found in MetaBook, terminate load process");
    }// ww w  . j  av a  2s. co m

    NCClass ncCls = NCClass.getNCClass("River");
    Row row = null;

    try {
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            if ((row = sheet.getRow(i)) != null && POIUtil.getCellValue(row, 0) != null
                    && POIUtil.getCellValue(row, 0).length() > 0) {

                String mode = POIUtil.getCellValue(row, 0);
                if (mode == null || mode.length() < 1) {
                    String msg = "deploy mode must be specified for River entries ";
                    logger.error(msg);
                    throw new Exception(msg);
                }

                River river = new River(POIUtil.getCellValue(row, 0), POIUtil.getCellValue(row, 1),
                        POIUtil.getCellValue(row, 2));

                rivers.put(river.getName(), river);
            }
        }

    } catch (Exception e) {
        logger.error("exception in GroupAssign load " + e.toString());
    }
}

---

From source file:org.niord.importer.aton.batch.AbstractDkAtonImportReader.java

License:Open Source License

/**
 * Opens the Excel sheet, reads in the header row and build a map of the column indexes for the given header fields.
 * @param path a path to the the Excel sheet
 * @param colIndex the column index map//from  ww  w  .  j av  a 2s  .  c om
 * @param fields the fields to determine column indexes for
 * @return the Excel row iterator pointing to the first data row
 */
private Iterator<Row> parseHeaderRow(Path path, Map<String, Integer> colIndex, String[] fields)
        throws Exception {

    try (FileInputStream inputStream = new FileInputStream(path.toFile())) {
        // Create Workbook instance holding reference to .xls file
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // Get first/desired sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        totalRowNo = sheet.getLastRowNum();

        // Get row iterator
        Iterator<Row> rowIterator = sheet.iterator();
        Row headerRow = rowIterator.next();

        // Get the column indexes of the relevant columns
        Arrays.stream(fields).forEach(f -> updateColumnIndex(headerRow, colIndex, f));

        return rowIterator;
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• »