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:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java

License:Apache License

private void createTextualAnswers(@NonNull List<Vote> answers, @NonNull List<String> questionTexts, Sheet sheet,
        Workbook wb) {/* w ww  .  j  av a2 s . com*/
    Row row;
    Cell cell;
    row = sheet.createRow(sheet.getLastRowNum() + 2);
    cell = row.createCell(1);
    CellStyle helpStyle = wb.createCellStyle();
    helpStyle.cloneStyleFrom(headerStyle);
    helpStyle.setBorderBottom(CellStyle.BORDER_NONE);
    helpStyle.setBorderTop(CellStyle.BORDER_NONE);
    helpStyle.setBorderLeft(CellStyle.BORDER_NONE);
    helpStyle.setBorderRight(CellStyle.BORDER_NONE);
    cell.setCellValue("Kommentare");
    cell.setCellStyle(helpStyle);

    //TODO used to determine style for current line -> its stupid. Think of something better
    int styleCounter = 0;
    for (String textualQuestion : questionTexts) {
        row = sheet.createRow(sheet.getLastRowNum() + 3);
        cell = row.createCell(1);
        cell.setCellValue(textualQuestion);
        setTextQuestionStyle(cell, styleCounter, true);

        //colorize horizontal neighbour cells of headline
        for (int i = 2; i < 5; i++) {
            cell = row.createCell(i);
            setTextQuestionStyle(cell, styleCounter, false);
        }

        int rowNum = sheet.getLastRowNum();
        int counter = 1;

        for (String comment : aggregateTextAnswers(answers, textualQuestion)) {
            row = sheet.createRow(rowNum + 1);
            cell = row.createCell(1, Cell.CELL_TYPE_STRING);

            //              introduces line breaks in long comments
            ArrayList<String> commentChunks = splitComment(comment);
            StringBuilder formattedComment = new StringBuilder();
            formattedComment.append(Integer.toString(counter));
            formattedComment.append(": ");

            int chunkCounter = 0;
            for (String chunk : commentChunks) {
                formattedComment.append(chunk);

                if ((chunkCounter + 1) < commentChunks.size()) {
                    formattedComment.append(System.lineSeparator());
                }
                chunkCounter++;
            }
            cell.setCellValue(formattedComment.toString());

            CellStyle style = setTextQuestionStyle(cell, styleCounter, false);

            // increase height of row based on font size, number of lines and line spacing
            // the origin of 140 % -> http://superuser.com/questions/337181/how-many-pts-is-1-5-line-spacing-in-microsoft-word-2007
            float pointsPerLine = (wb.getFontAt(style.getFontIndex()).getFontHeightInPoints() * 140) / 100;
            row.setHeightInPoints(pointsPerLine * commentChunks.size());

            //colorize horizontal neighbour cells of comment
            for (int i = 2; i < 17; i++) {
                cell = row.createCell(i);
                setTextQuestionStyle(cell, styleCounter, false);
            }
            sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 1, 17));

            rowNum++;
            counter++;
        }
        styleCounter++;
    }
}

From source file:de.topicmapslab.jexc.eXql.grammar.expression.FromExpression.java

License:Apache License

/**
 * {@inheritDoc}//from  w  w w.  j a v a 2 s . c  o  m
 */
public Collection<Row> interpret(Workbook workBook, Object... input) throws JeXcException {
    /*
     * get sheet by name
     */
    String sheetName = getTokens().get(1).token();
    if (sheetName.startsWith("\"")) {
        sheetName = sheetName.substring(1, sheetName.length() - 1);
    }
    Sheet sheet = workBook.getSheet(sheetName);
    if (sheet == null) {
        return Collections.emptyList();
    }
    Collection<Row> rows = new LinkedList<Row>();
    /*
     * is a TO b syntax
     */
    if (containsToken(ExqlTokens.tokenInstance(To.TOKEN))) {
        if (getNumberOfExpressions() != 2) {
            throw new JeXcException(
                    "Invalid number of numeric indexes by using the keyword 'TO'! Expected 2 but was "
                            + getNumberOfExpressions() + ".");
        }
        int from = Integer.parseInt(getExpressions().get(0).getTokens().get(0).token());
        int to;
        ExqlToken token = getExpressions().get(1).getTokens().get(0);
        if (token instanceof Last) {
            to = sheet.getLastRowNum();
        } else {
            to = Integer.parseInt(token.token());
        }
        for (int i = from; i <= to; i++) {
            Row row = sheet.getRow(i);
            /*
             * ignore non existing cells
             */
            if (row == null) {
                continue;
            }
            rows.add(row);
        }
    }
    /*
     * is index syntax
     */
    else {
        for (ExqlExpression e : getExpressions()) {
            int index = Integer.parseInt(e.getTokens().get(0).token());
            Row row = sheet.getRow(index);
            /*
             * ignore non existing cells
             */
            if (row == null) {
                continue;
            }
            rows.add(row);
        }
    }
    return rows;
}

From source file:de.topicmapslab.jexc.eXql.grammar.expression.FunctionExpression.java

License:Apache License

/**
 * Interpretation method for next function
 * /*from  w w  w  .j ava 2  s .co  m*/
 * @param workBook
 *            the workbook
 * @param row
 *            the row
 * @return the index of the next row satisfying the given property or in
 *         maximum the last row
 * @throws JeXcException
 *             thrown if operation fails
 */
private Object interpretNextFunction(Workbook workBook, Row row) throws JeXcException {
    ExqlExpression ex = getExpressions().get(0);

    Sheet sheet = row.getSheet();
    for (int i = row.getRowNum() + 1; i < sheet.getLastRowNum() + 1; i++) {
        Row r = sheet.getRow(i);
        Object result = ex.interpret(workBook, Arrays.asList(new Row[] { r }));
        if (result instanceof Collection && !((Collection<?>) result).isEmpty()) {
            return r.getRowNum();
        }
    }
    /*
     * get maximum rows
     */
    return sheet.getLastRowNum() + 1;
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

@Override
protected List<Event> parseEvents(Workbook workBook) throws IllegalStateException {
    evaluator = workBook.getCreationHelper().createFormulaEvaluator();
    List<Event> events = new CopyOnWriteArrayList<Event>();
    for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        Sheet sheet = workBook.getSheetAt(i);
        if (isValidResultSheet(sheet)) {
            log.debug("Parsing: {}", sheet.getSheetName());
            Row firstRow = sheet.getRow(4); // first row with event results
            if (firstRow != null) {
                Cell cell = firstRow.getCell(1); // first cell with event results
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // only parse sheet with content
                    Event event = parseEventDetails(sheet);
                    event.setLive(workBook.getActiveSheetIndex() == i);
                    List<Result> results = new CopyOnWriteArrayList<Result>();
                    for (int j = 4; j < sheet.getLastRowNum(); j++) {
                        Row row = sheet.getRow(j);
                        if (row != null) {
                            Result result = parseResultRow(row, event);
                            if (result.getFirstname() != null && result.getSurname() != null) {
                                results.add(result);
                            }/*from w  w w.j a  v  a  2 s . com*/
                        }
                    }
                    if (!results.isEmpty()) {
                        event.setResults(results);
                        events.add(event);
                    }
                }
            }
        }
    }
    return events;
}

From source file:edu.isi.karma.imp.excel.ToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into a
 * CSV file.//from   w  w w.  j  a v  a 2  s  .  c o  m
 * 
 * @param destination
 * @param excelFileName
 * @throws IOException
 * @throws FileNotFoundException
 */
private void convertToCSV(File destination, String excelFileName) throws FileNotFoundException, IOException {
    Sheet sheet;
    Row row;
    int lastRowNum;
    this.csvData = new ArrayList<>();

    logger.info("Converting files contents to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.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 = this.workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {

            // 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 resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }

            // Save to CSV
            String sheetName = sheet.getSheetName();
            String csvFileName = excelFileName.substring(0, excelFileName.lastIndexOf(".")) + "_" + sheetName;
            File csvFile = new File(destination, csvFileName);
            this.saveCSVFile(csvFile);
            csvFiles.add(csvFile);
            csvData.clear();
        }
    }
}

From source file:edu.isi.karma.imp.excel.ToCSV.java

License:Apache License

private void convertWorksheetToCSV(int wsIdx, Writer writer) throws IOException {
    Sheet sheet = this.workbook.getSheetAt(wsIdx);
    Row row;/*from w w w.j a v  a  2s  .c o m*/
    int lastRowNum;
    this.csvData = new ArrayList<>();
    this.maxRowWidth = -1;

    if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) {
        lastRowNum = sheet.getLastRowNum();
        for (int j = 0; j <= lastRowNum; j++) {
            row = sheet.getRow(j);
            this.rowToCSV(row);
        }
    }

    StringBuffer buffer;
    ArrayList<String> line;
    String csvLineElement;
    BufferedWriter bw = new BufferedWriter(writer);
    for (int i = 0; i < this.csvData.size(); i++) {
        buffer = new StringBuffer();
        line = this.csvData.get(i);
        for (int j = 0; j < this.maxRowWidth; j++) {
            if (line.size() > j) {
                csvLineElement = line.get(j);
                if (csvLineElement != null) {
                    buffer.append(this.escapeEmbeddedCharacters(csvLineElement));
                }
            }
            if (j < (this.maxRowWidth - 1)) {
                buffer.append(this.separator);
            }
        }

        // Once the line is built, write it away to the CSV file.
        bw.write(buffer.toString().trim());

        // Condition the inclusion of new line characters so as to
        // avoid an additional, superfluous, new line at the end of
        // the file.
        if (i < (this.csvData.size() - 1)) {
            bw.newLine();
        }
    }
    bw.flush();
}

From source file:edu.jhu.pha.vospace.process.tika.ExcelParser.java

License:Apache License

@Override
public void parse(InputStream stream, ContentHandler handler, Metadata metadata, ParseContext context)
        throws IOException, SAXException, TikaException {

    XHTMLContentHandler xhtml = new XHTMLContentHandler(handler, metadata);
    xhtml.startDocument();//w w w .java 2 s .  c o  m

    Workbook wb;

    try {
        wb = WorkbookFactory.create(stream);
    } catch (InvalidFormatException e) {
        throw new TikaException("Invalid format");
    }

    Sheet sheet = wb.getSheetAt(0);
    int nRows = sheet.getLastRowNum();
    int nCols = sheet.getRow(0).getLastCellNum();
    xhtml.startElement("section", "id", String.valueOf(1));

    AttributesImpl attributes = new AttributesImpl();
    //attributes.addAttribute("", "id", "id", "CDATA", String.valueOf(1));
    attributes.addAttribute("", "columns", "columns", "CDATA", String.valueOf(nCols));
    xhtml.startElement("table", attributes);

    Row headerRow = sheet.getRow(0);
    xhtml.startElement("th", "info", "columnNames");
    for (int j = 0; j < nCols; j++) {
        Cell cell = headerRow.getCell(j);
        String columnName = cell.getStringCellValue();
        xhtml.element("td", columnName);
    }
    xhtml.endElement("th");
    Row firstDataRow = sheet.getRow(1);
    xhtml.startElement("th", "info", "columnTypes");
    for (int j = 0; j < nCols; j++) {
        Cell cell = firstDataRow.getCell(j);
        int type = cell.getCellType();
        String columnType = null;
        switch (type) {
        case Cell.CELL_TYPE_NUMERIC:
            columnType = "D";
            break;
        case Cell.CELL_TYPE_STRING:
            columnType = "A";
            break;
        }
        xhtml.element("td", columnType);
    }
    xhtml.endElement("th");

    for (int i = 1; i <= nRows; i++) {
        Row row = sheet.getRow(i);
        xhtml.startElement("tr");
        for (int j = 0; j < nCols; j++) {
            Cell cell = row.getCell(j);
            int type = cell.getCellType();
            switch (type) {
            case Cell.CELL_TYPE_NUMERIC:
                xhtml.element("td", String.valueOf(cell.getNumericCellValue()));
                break;
            case Cell.CELL_TYPE_STRING:
                xhtml.element("td", cell.getStringCellValue());
                break;
            }
        }
        xhtml.endElement("tr");

    }
    xhtml.endElement("table");
    xhtml.endElement("section");
    xhtml.endDocument();

    metadata.add(TikaCoreProperties.TYPE, "EXCEL");
}

From source file:edu.si.services.beans.excel.ExcelToCSV.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file./*from   ww  w.ja  va2  s .co  m*/
 */
private void convertToCSV() {
    Sheet sheet = null;
    Row row = null;
    int lastRowNum = 0;
    this.csvData = new ArrayList<ArrayList<String>>();

    logger.debug("Converting stream content to CSV format.");

    // Discover how many sheets there are in the workbook....
    int numSheets = this.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 = this.workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {
            // 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 resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
    }
}

From source file:edu.vt.vbi.patric.common.ExcelHelper.java

License:Apache License

/**
 * Returns the width the Column should be (HSSF version)
 * @param sheet - sheet of workbook//from w w w .  j  a  v a 2  s  .c om
 * @param col - the column to work with
 * @return length (in characters) of that column
 */
private int decideColumnWidth(Sheet sheet, int col) {
    int titleLength = sheet.getRow(0).getCell(col).getStringCellValue().length();
    int longestString = titleLength;

    for (int i = 0; i < sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        Cell cell = row.getCell(col);
        int temp = cell.getStringCellValue().length();
        if (temp > titleLength * 2) {
            longestString = temp;
        }
    }

    if (longestString > titleLength * 4) {
        longestString = titleLength * 4;
    }

    return longestString;
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java

License:Mozilla Public License

@Override
public void writeContentToInstance(DD_XMLInstance instance) throws Exception {

    List<DDXmlElement> tables = instance.getTables();
    if (tables == null || wb == null) {
        readerLogger.logNoDefinitionsForTables();
        return;/*  w  w  w . java2s.  c  o m*/
    }

    for (int i = 0; i < tables.size(); i++) {
        DDXmlElement table = tables.get(i);
        String tblLocalName = table.getLocalName();
        if (tblLocalName != null && tblLocalName.length() > 31) {
            tblLocalName = tblLocalName.substring(0, 31);
        }
        String tblName = table.getName();
        String tblAttrs = table.getAttributes();

        readerLogger.logStartSheet(tblLocalName);
        readerLogger.logSheetSchema(instance.getInstanceUrl(), tblLocalName);
        if (!excelSheetNames.contains(tblLocalName)) {
            readerLogger.logSheetNotFound(tblLocalName);
        }
        Sheet sheet = getSheet(tblLocalName);
        Sheet metaSheet = getMetaSheet(tblLocalName);

        if (sheet == null) {
            readerLogger.logEmptySheet(tblLocalName);
            continue;
        }
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();
        Row row = sheet.getRow(firstRow);
        Row metaRow = null;

        List<DDXmlElement> elements = instance.getTblElements(tblName);

        setColumnMappings(row, elements, true);

        if (metaSheet != null) {
            metaRow = metaSheet.getRow(firstRow);
            setColumnMappings(metaRow, elements, false);
        }
        try {
            logColumnMappings(tblLocalName, row, metaRow, elements);
        } catch (Exception e) {
            e.printStackTrace();
            readerLogger.logSystemWarning(tblLocalName, "cannot write log about missing or ectra columns.");
        }
        instance.writeTableStart(tblName, tblAttrs);
        instance.setCurRow(tblName);

        Map<String, DDElement> elemDefs = instance.getElemDefs(tblLocalName);

        // read data
        // there are no data rows in the Excel file. We create empty table
        firstRow = (firstRow == lastRow) ? lastRow : firstRow + 1;
        int countRows = 0;

        for (int j = firstRow; j <= lastRow; j++) {
            row = (firstRow == 0) ? null : sheet.getRow(j);
            metaRow = (metaSheet != null && firstRow != 0) ? metaSheet.getRow(j) : null;
            // don't convert empty rows.
            if (isEmptyRow(row)) {
                continue;
            }
            countRows++;

            instance.writeRowStart();
            for (int k = 0; k < elements.size(); k++) {
                DDXmlElement elem = elements.get(k);
                String elemName = elem.getName();
                String elemLocalName = elem.getLocalName();
                String elemAttributes = elem.getAttributes();
                int colIdx = elem.getColIndex();
                boolean isMainTable = elem.isMainTable();
                String schemaType = null;
                boolean hasMultipleValues = false;
                String delim = null;

                // get element definition info
                if (elemDefs != null && elemDefs.containsKey(elemLocalName)) {
                    schemaType = elemDefs.get(elemLocalName).getSchemaDataType();
                    delim = elemDefs.get(elemLocalName).getDelimiter();
                    hasMultipleValues = elemDefs.get(elemLocalName).isHasMultipleValues();
                }

                String data = "";
                if (colIdx > -1) {
                    data = (isMainTable) ? getCellValue(row, colIdx, schemaType)
                            : getCellValue(metaRow, colIdx, null);
                }
                if (hasMultipleValues && !Utils.isNullStr(delim)) {
                    String[] values = data.split(delim);
                    for (String value : values) {
                        instance.writeElement(elemName, elemAttributes, value.trim());
                    }
                } else {
                    instance.writeElement(elemName, elemAttributes, data);
                }
            }
            instance.writeRowEnd();
        }
        instance.writeTableEnd(tblName);
        readerLogger.logNumberOfRows(countRows, tblLocalName);
        readerLogger.logEndSheet(tblLocalName);
    }
}