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:com.crm.webapp.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   w w w. j a  va2s . 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.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:com.crm.webapp.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;
        }//from   w  w w . j  a  v  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:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

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

    for (UIColumn col : table.getColumns()) {
        if (!col.isRendered()) {
            continue;
        }//from w  ww  .  java 2 s  .  c om

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

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

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

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

    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);
                }//from w ww .j  a  v a  2  s  . 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:com.crm.webapp.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   w ww . j ava 2s  .c  om*/

        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:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void addColumnFacets(SubTable 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  w  w  w  .  jav  a  2  s.  c  om

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

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

From source file:com.cx.test.FromHowTo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Class[] clazz = new Class[] { String.class, String.class, String.class, String.class, Integer.class,
            String.class, String.class };
    InputStream stream = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\menu.xlsx"));
    Workbook wb = new XSSFWorkbook(stream);
    Sheet sheet = wb.getSheetAt(0);
    int rows = sheet.getLastRowNum();
    int cells = sheet.getRow(0).getPhysicalNumberOfCells();
    for (int i = 0; i < rows; i++) {
        Row row = sheet.getRow(i + 1);//  w ww.  j  av a  2 s  .  co  m
        for (int j = 0; j < cells; j++) {
            Cell cell = row.getCell(j);
            Object obj = null;
            if (cell != null) {
                obj = getCellValue(cell, clazz[j]);
            }
            switch (j) {
            case 0:
                System.out.println("000000000-----" + obj);
                break;
            case 1:
                System.out.println("1111111111111" + obj);
                break;
            default:
                break;
            }
        }
    }
}

From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java

License:Apache License

/**
 * Gets an instance of a Workbook ({@link ConverterUtils#newWorkbook(InputStream)}, creates copy of original file, 
 * clears all the cell values, but preserves formatting.
 *//*from  w  w w .  j  a v a2  s.  c  om*/
static Workbook clearContent(final Workbook book) {
    ByteArrayOutputStream originalOut = new ByteArrayOutputStream();

    try {
        book.write(originalOut);
    } catch (IOException e) {
        throw new CalculationEngineException(e);
    }

    InputStream originalIn = new ByteArrayInputStream(copyOf(originalOut.toByteArray(), originalOut.size()));

    Workbook w = ConverterUtils.newWorkbook(originalIn);
    Sheet s = w.getSheetAt(0); //TODO: only one sheet is supported

    for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) {
        Row r = s.getRow(i);
        if (r == null) {
            continue;
        }

        for (int j = r.getFirstCellNum(); j <= r.getLastCellNum(); j++) {
            Cell c = r.getCell(j);
            if (c == null) {
                continue;
            }

            c.setCellType(CELL_TYPE_BLANK);
        }
    }

    return w;
}

From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java

License:Apache License

/**
 * For given {@link Workbook} does convert everything to new {@link DataModel} structure.
 * Does copy all supported fields (for supported fields see {@link DataModel} class.
 *///from   ww  w.  jav  a 2s .c  om
static IDataModel toDataModel(final Workbook workbook) {
    if (workbook == null) {
        return null;
    }

    //add custom functions information
    workbook.addToolPack(getUdfFinder());

    Sheet s = workbook.getSheetAt(0); //TODO: only one sheet is supported
    if (s == null) {
        return null;
    }

    IDataModel dm = new DataModel(s.getSheetName());

    for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) {
        Row r = s.getRow(i);
        if (r == null) {
            continue;
        }

        DmRow row = new DmRow(i);
        dm.setRow(i, row);

        for (int j = r.getFirstCellNum(); j < r.getLastCellNum(); j++) {
            Cell c = r.getCell(j);
            if (c == null) {
                continue;
            }

            DmCell cell = new DmCell();
            row.setCell(j, cell);

            cell.setAddress(new CellAddress(dm.getDataModelId(), A1Address.fromRowColumn(i, j)));
            cell.setContent(ConverterUtils.resolveCellValue(c));
        }
    }

    EvaluationWorkbook evaluationWbook = ConverterUtils.newEvaluationWorkbook(workbook);

    for (int nIdx = 0; nIdx < workbook.getNumberOfNames(); nIdx++) {
        Name name = workbook.getNameAt(nIdx);

        String reference = name.getRefersToFormula();
        if (reference == null) {
            continue;
        }

        if (A1Address.isAddress(removeSheetFromNameRef(reference))) {
            dm.setNamedAddress(name.getNameName(), A1Address.fromA1Address(removeSheetFromNameRef(reference)));
        } else if (isFormula(reference, evaluationWbook)) {
            dm.setNamedValue(name.getNameName(), new CellValue(FORMULA_PREFIX + reference));
        } else {
            dm.setNamedValue(name.getNameName(), CellValue.from(reference));
        }
    }

    return dm;
}

From source file:com.dataart.spreadsheetanalytics.engine.DataSetConverters.java

License:Apache License

/**
 * Converts a {@link Workbook} to new {@link IDataSet}.
 * Ignores empty rows./*ww  w .  j a v  a2  s  . c o m*/
 * 
 * @throws {@link CalculationEngineException} if {@link Workbook} contains formulas or Cell references.
 */
static IDataSet toDataSet(final Workbook workbook) {
    Sheet sheet = workbook.getSheetAt(0); //TODO: this works only for single sheet documents
    DataSet dataSet = new DataSet(sheet.getSheetName());

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        IDsRow dsRow = dataSet.addRow();
        Row row = sheet.getRow(i);
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            Cell wbCell = row.getCell(j);
            if (wbCell != null && wbCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                throw new CalculationEngineException("DataSet should not contain formulas");
            }
            IDsCell cell = dsRow.addCell();
            cell.setValue(ConverterUtils.resolveCellValue(wbCell));
        }
    }
    return dataSet;
}