Example usage for org.apache.poi.ss.usermodel Sheet 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:com.example.poi.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  va2  s.c o m*/
 */
private void convertToCSV() {
    Sheet sheet = null;
    Row row = null;
    int lastRowNum = 0;
    this.csvData = new ArrayList<ArrayList>();

    System.out.println("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);
            }
        }
    }
}

---

From source file:com.exilant.exility.core.XLSReader.java

License:Open Source License

/**
 * Purpose of this method to read rows from given Excel Sheet.
 * //from  www .j a  va 2 s.  com
 * @param sheet
 *            an Instance of .ss.usermodel.Sheet class from POI apache.
 * @return -1 if fail to read sheet else number of columns read successfully
 *         from the sheet.
 * @throws ExilityException
 */

public int readASheet(Sheet sheet) throws ExilityException {
    int nonEmptyFirstRowIdx = 0;
    int lastRowIdx = 0;

    int nbrPhysicalRows = sheet.getPhysicalNumberOfRows();
    String sheetName = sheet.getSheetName();

    if (nbrPhysicalRows < 2) {
        Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS);
        return -1;
    }

    try {
        nonEmptyFirstRowIdx = sheet.getFirstRowNum();
        lastRowIdx = sheet.getLastRowNum();

        /*
         * For checking to valid header.First row must be header.
         */

        Row headerRow = sheet.getRow(nonEmptyFirstRowIdx);
        int nbrCol = headerRow.getPhysicalNumberOfCells();

        for (int colIdx = 0; colIdx < nbrCol; colIdx++) {
            Cell hCell = headerRow.getCell(colIdx);

            if (hCell == null || hCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                Spit.out("Error--->Found blank column " + (colIdx + 1) + " in Sheet " + sheetName
                        + XLSReader.INVALID_HEADER);
                this.columnsData.clear();
                return -1;
            }

            String columnName = hCell.getStringCellValue();
            this.setDataType(columnName, colIdx);
        }

    } catch (Exception e) {
        Spit.out(sheetName + XLSReader.INVALID_HEADER);
        Spit.out(e);
        return -1;
    }

    int nbrColumnsInARow = this.columnsData.size();

    /*
     * Loop starts with second data row that is first row(header as column
     * name) excluded.
     */
    Spit.out(sheetName + ":\n");
    for (int rowIdx = (nonEmptyFirstRowIdx + 1); rowIdx <= lastRowIdx; rowIdx++) {
        Row row = sheet.getRow(rowIdx);
        if (row == null) {
            Spit.out(XLSReader.SKIP_BLANK_ROW + rowIdx);
            continue;
        }
        /**
         * readARow() will throws ExilityException if something goes wrong.
         */
        this.readARow(row, nbrColumnsInARow);
    }

    return this.columnsData.size();

}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * Get contents of a sheet into text rows and columns
 * /*from w  ww .  j  av  a2 s .co  m*/
 * @param sheet
 * @return
 */
private String[][] getRawData(Sheet sheet, boolean expectValueInFirstColumn) {

    // let us get a normalized rows/columns out of this sheet.
    int firstRowIdx = sheet.getFirstRowNum();
    Row firstRow = sheet.getRow(firstRowIdx);
    int firstCellIdx = firstRow.getFirstCellNum();
    int lastCellAt = firstRow.getLastCellNum();
    int nbrCells = lastCellAt - firstCellIdx;

    int lastRow = sheet.getLastRowNum();

    List<String[]> rawData = new ArrayList<String[]>();
    for (int rowNbr = firstRowIdx; rowNbr <= lastRow; rowNbr++) {
        Row row = sheet.getRow(rowNbr);
        if (row == null || row.getPhysicalNumberOfCells() == 0) {
            Spit.out(
                    "row at " + rowNbr + "is empty. while this is not an error, we certianly discourage this.");
            continue;
        }

        String[] rowData = this.getTextValues(row, firstCellIdx, nbrCells);
        if (rowData == null) {
            continue;
        }
        if (expectValueInFirstColumn) {
            String firstData = rowData[0];
            if (firstData == null || firstData.length() == 0) {
                Spit.out("row at" + rowNbr + " has its first column empty, and hence the row is ignored");
                continue;
            }
        }
        rawData.add(rowData);
    }

    if (rawData.size() > 0) {
        return rawData.toArray(new String[0][0]);
    }
    return null;
}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * extract fields from spread sheet into dc
 * //from   w w w .java 2s  .  co m
 * @param table
 *            table element of spread sheet
 * @param dc
 *            dc
 * @param useDictionaryForDataType
 *            refer to data dictionary or use DataType as present in spread
 *            sheet
 */
private void extractValues(Sheet sheet, DataCollection dc, boolean useDictionaryForDataType) {
    int n = sheet.getLastRowNum();

    // if there are no values, following for loop will not execute..
    for (int i = 1; i <= n; i++) // first row is header
    {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        // value row should have just two cells in it
        int nbrCells = row.getLastCellNum();
        if (nbrCells < 1) {
            continue;
        }

        String fieldName = row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue();
        if (fieldName.length() == 0) {
            continue; // no name
        }

        Cell dataCell = null;
        String fieldValue = EMPTY_STRING;
        if (nbrCells > 1) // value is present
        {
            dataCell = row.getCell(1, Row.CREATE_NULL_AS_BLANK);
            fieldValue = this.getTextValue(dataCell);
        }

        if (useDictionaryForDataType) {
            dc.addValueAfterCheckingInDictionary(fieldName, fieldValue);
        } else {
            dc.addValue(fieldName, fieldValue, this.getExilityType(dataCell));
        }
    }
}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/***
 * get data types of column based on actual values in the sheet
 * /*  w  w w .j  a v  a2 s.co  m*/
 * @param sheet
 * @param nbrCells
 * @param rowStart
 * @param rowEnd
 * @return
 */
private DataValueType[] getExilityTypes(Sheet sheet, int nbrCells) {
    DataValueType[] types = new DataValueType[nbrCells];

    // though NULL is default (as of now that is the first one in ENUM) let
    // us explicitly populate it
    for (int i = 0; i < nbrCells; i++) {
        types[i] = DataValueType.NULL;
    }

    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    int nbrFound = 0;

    // which cell to start? We will go by the first cell of the first
    // physucal row
    Row firstRow = sheet.getRow(sheet.getFirstRowNum());
    int startingCellIdx = firstRow.getFirstCellNum();
    int endCellIdx = startingCellIdx + nbrCells;
    for (int i = rowStart; i <= rowEnd; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }

        for (int j = startingCellIdx; j < endCellIdx; j++) {
            // do we already know this type?
            if (types[j] != DataValueType.NULL) {
                continue;
            }

            Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                continue;
            }
            types[j] = this.getExilityType(cell);
            nbrFound++;
            if (nbrFound == nbrCells) {
                return types;
            }
        }
    }

    // we will treat unknown ones as text
    for (int i = 0; i < nbrCells; i++) {
        if (types[i] == DataValueType.NULL) {
            types[i] = DataValueType.TEXT;
        }
    }

    return types;
}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

/**
 * Very specific requirement for saving labels. If the file exists, append
 * only missing labels/*from   w  ww .  j a  va 2  s.  co m*/
 * 
 * @param fileName
 * @param rows
 * @return true if we are able to save the file
 */
public boolean appendMissingOnes(String fileName, String[][] rows) {
    File file = new File(fileName);
    Workbook workbook;
    Sheet sheet;
    if (file.exists()) {
        /**
         * read spreadsheet
         */
        try {
            InputStream is = new FileInputStream(file);
            workbook = WorkbookFactory.create(is);
            is.close();
            Spit.out(fileName + " read into a workbook.");
        } catch (Exception e) {
            Spit.out(fileName + " is not saved because of an error while reading existing contents. "
                    + e.getMessage());
            Spit.out(e);
            return false;
        }
        sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            sheet = workbook.createSheet();
        }

    } else {
        Spit.out(fileName + " does not exist. New file will be created.");
        /**
         * first time this is being saved.
         */
        workbook = this.getWorkbookForFile(fileName);
        sheet = workbook.createSheet();
    }
    if (sheet.getLastRowNum() > 0) {
        this.addMissingRows(sheet, rows);
    } else {
        this.addRows(sheet, rows);
    }
    return this.save(workbook, fileName);
}

---

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private void addMissingRows(Sheet sheet, String[][] rows) {
    /**//  w w  w.  j av  a 2  s  .c  o  m
     * create a set of existing labels
     */
    Set<String> existingEntries = new HashSet<String>();
    int lastRow = sheet.getLastRowNum();
    for (int i = 0; i <= lastRow; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        Cell cell = row.getCell(0);
        if (cell == null) {
            continue;
        }
        existingEntries.add(cell.getStringCellValue());
    }
    /**
     * now, add rows, only if they are not there already
     */
    for (String[] row : rows) {
        if (existingEntries.contains(row[0])) {
            continue;
        }
        lastRow++;
        Row xlRow = sheet.createRow(lastRow);
        int colIdx = 0;
        for (String columnValue : row) {
            xlRow.createCell(colIdx).setCellValue(columnValue);
            colIdx++;
        }
    }
}

---

From source file:com.eyeq.pivot4j.export.poi.ExcelExporterIT.java

License:Common Public License

/**
 * @param format//from  w  w w .j  ava 2s  . c o m
 * @param showParentMember
 * @param showDimensionTitle
 * @param hideSpans
 * @param rows
 * @param mergedRegions
 * @throws IOException
 * @throws InvalidFormatException
 */
protected void testExport(Format format, boolean showParentMember, boolean showDimensionTitle,
        boolean hideSpans, int rows, int mergedRegions) throws IOException, InvalidFormatException {
    OutputStream out = null;

    File file = File.createTempFile("pivot4j-", "." + format.getExtension());

    if (deleteTestFile) {
        file.deleteOnExit();
    }

    try {
        out = new FileOutputStream(file);
        ExcelExporter exporter = new ExcelExporter(out);

        exporter.setFormat(format);
        exporter.setShowParentMembers(showParentMember);
        exporter.setShowDimensionTitle(showDimensionTitle);
        exporter.setHideSpans(hideSpans);

        exporter.render(getPivotModel());
    } finally {
        out.flush();
        IOUtils.closeQuietly(out);
    }

    Workbook workbook = WorkbookFactory.create(file);

    assertThat("Workbook cannot be null.", workbook, is(notNullValue()));

    Sheet sheet = workbook.getSheetAt(0);
    assertThat("Worksheet cannot be null.", sheet, is(notNullValue()));

    assertThat("Invalid worksheet name.", sheet.getSheetName(), is(equalTo("Sales")));

    assertThat("Wrong number of rows.", sheet.getLastRowNum(), is(equalTo(rows)));
    assertThat("Wrong number of merged regions.", sheet.getNumMergedRegions(), is(equalTo(mergedRegions)));
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ??//w  w  w  .  j av  a  2 s  .  c o m
 *
 * @param sheet
 * @param clazz
 *            ???java bean
 * @param fieldnames
 *            java bean???ExeclHeader
 * @param list
 *            ?
 * @return
 */
@SuppressWarnings("unused")
public static Sheet addDataToSheet(Sheet sheet, Class clazz, List<String> fieldnames, List<?> list) {
    // ?
    Row row = null;
    Cell cell = null;
    int lastRowNum = sheet.getLastRowNum();
    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow(lastRowNum + 1 + i);
        for (int j = 0; j < fieldnames.size(); j++) {
            try {
                Field field = clazz.getDeclaredField(fieldnames.get(j));
                field.setAccessible(true);
                cell = row.createCell(j);
                Class fieldType = field.getType();
                // cell.setCellType(Cell.);
                Object value = field.get(list.get(i));
                setCellValue(cell, value);

            } catch (Exception ex) {
                if (logger.isEnabledFor(Priority.ERROR)) {
                    logger.error("" + (i + 1) + "" + j + "", ex);
                }
            }
        }
    }

    return sheet;
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ExcelUtil.java

License:Apache License

/**
 * ??/*from w  ww  .  ja  va2s .c  o m*/
 *
 * @param sheet
 * @param columnIndex
 * @param style
 * @return
 */
public static boolean setColumnStyle(Sheet sheet, short columnIndex, int rowFirstIndex, int rowLastIndex,
        CellStyle style) {
    if (sheet == null)
        return false;

    int rowNum = sheet.getLastRowNum();
    CellStyle newCellStyle = sheet.getWorkbook().createCellStyle();
    // ??
    if (rowFirstIndex < rowLastIndex) {
        int temp = rowFirstIndex;
        rowFirstIndex = rowLastIndex;
        rowLastIndex = temp;
    }
    // TODO 
    if (rowNum < rowFirstIndex) {// ?
        return false;
    }
    // 
    for (int i = rowFirstIndex; i <= rowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null)
            return false;
        Cell cell = row.getCell(columnIndex);
        if (cell == null)
            return false;
        newCellStyle.cloneStyleFrom(cell.getCellStyle());// ??
        newCellStyle.cloneStyleFrom(style); // ??
        cell.setCellStyle(newCellStyle);

    }

    return true;
}

---

• «
• 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
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »