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.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataList list, String facetType) {
    Map<String, UIComponent> map = list.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else {/*from   ww  w  .j  a  v  a  2 s.c o m*/
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                sheetRowIndex, // last row (0-based)
                0, // first column (0-based)
                1 // last column (0-based)
        ));

    }
}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from w  ww .j a  va 2 s  .c o  m*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {
                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellValue(value);
                                cell.setCellStyle(facetStyle);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                        sheetRowIndex + rowSpan - 1, // last row (0-based)
                                        i, // first column (0-based)
                                        i // last column (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);

                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellValue(value);
                            cell.setCellStyle(facetStyle);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                    sheetRowIndex, // last row (0-based)
                                    i, // first column (0-based)
                                    i + colSpan - 1 // last column (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);
                    }

                    i++;
                }
            }

        }

    }
}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }//from www . j  a v  a2s  .  c  om
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                        sheetRowIndex + rowSpan - 1, // last row (0-based)
                                        i, // first column (0-based)
                                        i // last column (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                    sheetRowIndex, // last row (0-based)
                                    i, // first column (0-based)
                                    i + colSpan - 1 // last column (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void exportCells(DataTable table, Sheet sheet, int rowIndex) {
    int sheetRowIndex = sheet.getLastRowNum() + 1;
    Row row = sheet.createRow(sheetRowIndex);

    for (UIColumn col : table.getColumns()) {
        if (!col.isRendered()) {
            continue;
        }/*  ww  w  . j av a2s. co  m*/

        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyModel();
        }

        if (col.isExportable()) {
            // Adding RowIndex for custom Export
            UIComponent component = (UIComponent) col;
            if (component.getId().equalsIgnoreCase("subject")) {

                Cell cell = row.createCell(0);
                String value = rowIndex + "";
                cell.setCellValue(new XSSFRichTextString(value));
            }
            addColumnValue(row, col.getChildren(), "content");
        }
    }
    FacesContext context = null;
    if (table.getRowIndex() == 0) {
        for (UIComponent component : table.getChildren()) {
            if (component instanceof RowExpansion) {
                RowExpansion rowExpansion = (RowExpansion) component;
                if (rowExpansion.getChildren() != null) {
                    if (rowExpansion.getChildren().get(0) instanceof DataTable) {
                        DataTable childTable = (DataTable) rowExpansion.getChildren().get(0);
                        childTable.setRowIndex(-1);
                    }
                    if (rowExpansion.getChildren().get(0) instanceof DataList) {
                        DataList childList = (DataList) rowExpansion.getChildren().get(0);
                        childList.setRowIndex(-1);
                    }
                }

            }
        }
    }
    table.setRowIndex(table.getRowIndex() + 1);
    for (UIComponent component : table.getChildren()) {
        if (component instanceof RowExpansion) {
            RowExpansion rowExpansion = (RowExpansion) component;
            if (rowExpansion.getChildren() != null) {
                if (rowExpansion.getChildren().get(0) instanceof DataList) {
                    DataList list = (DataList) rowExpansion.getChildren().get(0);
                    if (list.getHeader() != null) {
                        tableFacet(context, sheet, list, "header");
                    }
                    exportAll(context, list, sheet);
                }
                if (rowExpansion.getChildren().get(0) instanceof DataTable) {
                    DataTable childTable = (DataTable) rowExpansion.getChildren().get(0);
                    int columnsCount = getColumnsCount(childTable);

                    if (childTable.getHeader() != null) {
                        tableFacet(context, sheet, childTable, columnsCount, "header");

                    }
                    tableColumnGroup(sheet, childTable, "header");

                    addColumnFacets(childTable, sheet, ColumnType.HEADER);

                    exportAll(context, childTable, sheet, false);

                    if (childTable.hasFooterColumn()) {
                        addColumnFacets(childTable, sheet, ColumnType.FOOTER);
                    }
                    tableColumnGroup(sheet, childTable, "footer");
                    childTable.setRowIndex(-1);
                }

            }
        }
    }
}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

protected void addColumnFacets(DataTable table, Sheet sheet, ColumnType columnType) {

    int sheetRowIndex = sheet.getLastRowNum() + 1;
    Row rowHeader = sheet.createRow(sheetRowIndex);

    for (UIColumn col : table.getColumns()) {
        if (!col.isRendered()) {
            continue;
        }/*from   ww w.  j a va  2  s .co m*/

        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyModel();
        }

        if (col.isExportable()) {
            // Adding RowIndex for custom Export
            UIComponent component = (UIComponent) col;
            if (component.getId().equalsIgnoreCase("subject")) {

                Cell cell = rowHeader.createCell(0);
                String value = "Index";
                cell.setCellValue(new XSSFRichTextString(value));
            }
            // Adding RowIndex for custom Export
            addColumnValue(rowHeader, col.getFacet(columnType.facet()), "facet");
        }
    }

}

From source file:org.project.utilities.ExcelRead.java

public ArrayList readexl(File exfile, String exfilename) {
    ArrayList storvalues = new ArrayList();
    try {//www .j a v a  2s .c om
        // File file2 = new File("/home/asl/Desktop/html work/ProgramFile/test_template.xls");
        FileInputStream file = new FileInputStream(exfile);
        //Workbook workbook = null;
        // String name = file2.getName();
        String name = exfilename;
        Workbook workbook = null;
        if (name.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(file);

        } else if (name.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(file);
        }

        //  workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
        DataFormatter fmt = new DataFormatter();

        for (int sn = 0; sn < workbook.getNumberOfSheets(); sn++) {
            Sheet sheet = workbook.getSheetAt(sn);
            for (int rn = sheet.getFirstRowNum() + 1; rn <= sheet.getLastRowNum(); rn++) {
                Row row = sheet.getRow(rn);
                if (row == null) {
                    // There is no data in this row, handle as needed
                } else {
                    // Row "rn" has data
                    ArrayList storeval = new ArrayList();
                    // System.out.println("size " + row.getLastCellNum());
                    for (int cn = 0; cn < 17; cn++) {
                        // for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                        Cell cell = row.getCell(cn);
                        //  cell.setCellType(Cell.CELL_TYPE_STRING);
                        String val = "";
                        // String strCellValue = "";

                        if (cell == null) {
                            // This cell is empty/blank/un-used, handle as needed
                        } else {
                            String cellStr = fmt.formatCellValue(cell);
                            val = cellStr;
                            // Do something with the value
                        }
                        storeval.add(val);
                    }
                    storvalues.add(storeval);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return storvalues;
}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }/*from   w  w w.  j  a va 2s.  co  m*/

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    return newRow;
}

From source file:org.projectforge.export.ExportSheet.java

License:Open Source License

public ExportSheet(final ContentProvider contentProvider, final String name, final Sheet poiSheet) {
    this.contentProvider = contentProvider;
    this.name = name;
    this.poiSheet = poiSheet;
    this.rows = new ArrayList<ExportRow>();
    final int lastRowNum = poiSheet.getLastRowNum();
    if (lastRowNum > 0) {
        // poiSheet does already exists.
        for (int i = poiSheet.getFirstRowNum(); i < poiSheet.getLastRowNum(); i++) {
            final Row poiRow = poiSheet.getRow(i);
            final ExportRow row = new ExportRow(contentProvider, this, poiRow, i);
            rows.add(row);/*from   w  w  w.j a v  a  2 s  . c  om*/
        }
    }
    final PrintSetup printSetup = getPrintSetup();
    printSetup.setPaperSize(ConfigXml.getInstance().getDefaultPaperSize());
}

From source file:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_//from  w w w.jav  a2 s. c om
 *
 * @param request _more_
 * @param entry _more_
 * @param suffix _more_
 * @param inputStream _more_
 * @param visitInfo _more_
 * @param visitor _more_
 *
 * @throws Exception _more_
 */
private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream,
        TextReader visitInfo, TabularVisitor visitor) throws Exception {
    //        System.err.println("visitXls: making workbook");
    Workbook wb = makeWorkbook(suffix, inputStream);
    //        System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets());
    int maxRows = visitInfo.getMaxRows();
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        if (!visitInfo.okToShowSheet(sheetIdx)) {
            continue;
        }
        Sheet sheet = wb.getSheetAt(sheetIdx);
        //            System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum());
        List<List<Object>> rows = new ArrayList<List<Object>>();
        int sheetSkip = visitInfo.getSkip();
        for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows)
                && (rowIdx <= sheet.getLastRowNum()); rowIdx++) {
            if (sheetSkip-- > 0) {
                continue;
            }

            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            List<Object> cols = new ArrayList<Object>();
            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                cols.add(value);
            }

            /**
             * ** TODO
             * org.ramadda.util.text.Row row = new Row(cols);
             *
             * if ( !visitInfo.rowOk(row)) {
             *   if (rows.size() == 0) {
             *       //todo: check for the header line
             *   } else {
             *       continue;
             *   }
             * }
             */
            rows.add(cols);
        }
        if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) {
            break;
        }
    }
}

From source file:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_//from   w w w.  jav  a 2s. c om
 *
 * @param args _more_
 *
 * @throws Exception _more_
 */
public static void main(String[] args) throws Exception {
    Workbook wb = makeWorkbook(IOUtil.getFileExtension(args[0]), new FileInputStream(args[0]));
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        Sheet sheet = wb.getSheetAt(sheetIdx);
        System.err.println(sheet.getSheetName());
        for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            short firstCol = row.getFirstCellNum();
            int colCnt = 0;
            for (short col = firstCol; col < row.getLastCellNum(); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                if (colCnt++ > 0)
                    System.out.print(",");
                System.out.print(value);
            }
            System.out.println("");
        }
    }
}