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.component.exporter.ExcelExporter.java

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataTable table, int columnCount,
        String facetType) {/* w w  w  .j  a  va  2  s .  com*/
    Map<String, UIComponent> map = table.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 if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            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)
                columnCount //last column  (0-based)
        ));

    }
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.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  ww w  . ja v a  2  s. co 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.getPhysicalNumberOfRows() > 0 ? sheet.getLastRowNum() + 1 : 0;
                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.component.exporter.ExcelExporter.java

License:Apache License

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

    facetStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    facetStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    facetStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);
    cellStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    cellStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    cellStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);

    for (UIColumn col : table.getColumns()) {

        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyStatelessModel();
        }/*from  w  w w . ja v a 2 s  . c om*/

        if (col.isRendered() && col.isExportable()) {
            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);
                    }
                }

            }
        }
    }
    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.component.exporter.ExcelExporter.java

License:Apache License

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

    facetStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    facetStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    facetStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);
    cellStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    cellStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    cellStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);

    for (UIColumn col : table.getColumns()) {

        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyStatelessModel();
        }//from   w w  w. ja v  a 2 s  .c o  m

        if (col.isRendered() && col.isExportable()) {
            addColumnValue(row, col.getChildren(), "content");
        }
    }
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

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

    facetStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    facetStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    facetStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);
    cellStyleLeftAlign.setAlignment((short) CellStyle.ALIGN_LEFT);
    cellStyleCenterAlign.setAlignment((short) CellStyle.ALIGN_CENTER);
    cellStyleRightAlign.setAlignment((short) CellStyle.ALIGN_RIGHT);

    for (UIComponent component : list.getChildren()) {
        if (component instanceof Column) {
            UIColumn column = (UIColumn) component;
            for (UIComponent childComponent : column.getChildren()) {
                int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
                Cell cell = row.createCell(cellIndex);
                if (component.isRendered()) {
                    String value = component == null ? ""
                            : exportValue(FacesContext.getCurrentInstance(), childComponent);
                    cell.setCellValue(new XSSFRichTextString(value));
                    cell.setCellStyle(cellStyle);
                }/*  w w  w. j  av a2s  .  c o m*/
            }

        } else {
            int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
            Cell cell = row.createCell(cellIndex);
            if (component.isRendered()) {
                String value = component == null ? ""
                        : exportValue(FacesContext.getCurrentInstance(), component);
                cell.setCellValue(new XSSFRichTextString(value));
                cell.setCellStyle(cellStyle);
            }
        }
    }

}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.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 instanceof DynamicColumn) {
            ((DynamicColumn) col).applyStatelessModel();
        }/*from www.j a v a2 s.c  o m*/

        if (col.isRendered() && col.isExportable()) {
            addColumnValue(rowHeader, col.getFacet(columnType.facet()), "facet");
        }
    }

}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

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

    int sheetRowIndex = sheet.getPhysicalNumberOfRows() > 0 ? sheet.getLastRowNum() + 1 : 0;
    Row rowHeader = sheet.createRow(sheetRowIndex);

    for (UIColumn col : table.getColumns()) {

        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyStatelessModel();
        }//from ww  w . ja  v  a 2 s. com

        if (col.isRendered() && col.isExportable()) {
            addColumnValue(rowHeader, col.getFacet(columnType.facet()), "facet");
        }
    }
}

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

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();//from w  w w  .jav  a 2s .c o  m
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();
    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBold(true);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

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

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable) {
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }
    }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}

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

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataTable table, int columnCount,
        String facetType) {/*from   w  w w.j  a v a 2 s.  co m*/
    Map<String, UIComponent> map = table.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 if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            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)
                columnCount + 1 // last column (0-based)
        ));

    }
}

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

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, SubTable table, int columnCount,
        String facetType) {//from w ww . jav a2s .co  m
    Map<String, UIComponent> map = table.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 if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            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)
                columnCount // last column (0-based)
        ));

    }
}