Example usage for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows.

Prototype

int getPhysicalNumberOfRows();

Source Link

Document

Returns the number of physically defined rows (NOT the number of rows in the sheet)

Usage

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();
    }// w w  w . jav 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 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();
        }// w w  w  . j a va  2 s . c o  m

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

---

From source file:org.smart.migrate.dao.impl.ExcelImportDao.java

@Override
public List<String> findAllSourcePrimaryKeys(TableSetting tableSetting) {

    sourceData.clear();/*from   www  . jav  a 2s.c  o m*/

    List<String> pks = new ArrayList<String>();
    Sheet sheet = sourceWorkbook.getSheet(tableSetting.getSourceTable());
    if (sheet == null) {
        throw new RuntimeException("can not get sheet from " + tableSetting.getSourceTable());
    }
    Row headRow = sheet.getRow(sheet.getFirstRowNum());
    if (headRow != null) {
        int idColumn = ExcelUtils.cellIndexInRow("id", headRow);
        if (idColumn == -1) {
            throw new RuntimeException("sheet must have id column!");
        }

        //initialize header map (key: column index,value: fieldname)
        Map<Integer, String> header = new HashMap<Integer, String>();
        for (Cell cell : headRow) {
            header.put(cell.getColumnIndex(), cell.getStringCellValue());
        }

        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            Row row = sheet.getRow(i);
            String pk;
            if (row.getCell(idColumn).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                Double did = row.getCell(idColumn).getNumericCellValue();
                pk = String.valueOf(did.intValue());
            } else {
                pk = row.getCell(idColumn).getStringCellValue();
            }
            pks.add(pk);
            Map<String, Object> data = new HashMap<String, Object>();
            for (Cell cell : row) {
                String fieldname = header.get(cell.getColumnIndex());

                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    Double dvalue = cell.getNumericCellValue();
                    String s = String.valueOf(dvalue);
                    if (s.endsWith(".0")) {
                        data.put(fieldname, dvalue.intValue());
                    } else {
                        data.put(fieldname, dvalue);
                    }
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    data.put(fieldname, cell.getBooleanCellValue());
                } else {
                    data.put(fieldname, cell.getStringCellValue());
                }

            }
            sourceData.put(pk, data);
        }
    }
    return pks;
}

---

From source file:org.smart.migrate.dao.impl.MetaExcelDao.java

@Override
public List<String> getTables(Connection connection) {
    List<String> tables = new ArrayList<String>();
    try {/*from  ww  w.  j  av  a2  s.c o m*/
        InputStream inp = new FileInputStream(dbfile);
        Workbook wb = WorkbookFactory.create(inp);
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            if (sheet.getPhysicalNumberOfRows() > 0) {
                tables.add(sheet.getSheetName());
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    }
    return tables;
}

---

From source file:org.smart.migrate.util.ExcelUtils.java

/**
 * Add indendity column data for excel sheet
 * @param filename Excel name/*www  .j av a  2  s  . c o m*/
 * @param sheetName Sheet name
 */
public static void addIndendityColumnData(String filename, String sheetName) {
    try {
        InputStream inp = new FileInputStream(filename);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) {
            Row row = sheet.getRow(sheet.getFirstRowNum());

            if (row != null) {
                int idColumn = cellIndexInRow("id", row);
                if (idColumn == -1) {
                    idColumn = row.getLastCellNum();
                    row.createCell(idColumn).setCellValue("id");
                    for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                        Row r = sheet.getRow(i);
                        Cell cell = r.getCell(idColumn);
                        if (cell == null) {
                            cell = r.createCell(idColumn);
                        }
                        cell.setCellValue(i);
                    }
                    FileOutputStream fileOut = new FileOutputStream(filename);
                    wb.write(fileOut);
                    fileOut.close();
                }
                inp.close();
            }
        }

    } catch (IOException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    }
}

---

From source file:pruebaimportarexcel.excel.Excel.java

/**
* Devuelve el numero de filas que contiene la pagina actual.
*
* @return//from w w  w  .j  a v  a2 s  . c  o m
*/
public static int getNumRow(Sheet sheet) {
    return (sheet != null) ? sheet.getPhysicalNumberOfRows() : 0;
}

---

From source file:pruebaimportarexcel.excel.Excels.java

/**
 * Obtiene todos los datos de una pagina Excel, como un ArrayList de
 * Vectores.//  w  ww.j a va 2 s. c o m
 *
 * @param excel
 * @param sheetName
 * @param colunms
 * @return
 */
public static List<List<Object>> getDatasSheet(Excel excel, String sheetName, int colunms) {
    Sheet sheet;
    int numColumns = 0;
    List<List<Object>> datasheet = new ArrayList<>();

    Row row = null;
    if (excel != null) {
        sheet = excel.getSheet(sheetName);
        if (sheet != null) {
            numColumns = Excel.getNumCells(excel.getRow(sheet, 0));

            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                datasheet.add(Excels.getRowToList(sheet, i, colunms));
            }

        }
    }

    return datasheet;
}

---

From source file:se.sll.invoicedata.price.GeneratePriceList.java

License:Open Source License

private List<String> getGuidList(Sheet sheet, int startRow, int startColumn) {

    List<String> guids = new ArrayList<String>();

    for (int i = startRow; i < sheet.getPhysicalNumberOfRows(); i++) {
        Cell cell = sheet.getRow(i).getCell(startColumn);

        System.out.println("GUID: " + i + ": " + cell);
        if (!guids.contains(cell.getStringCellValue())) {
            guids.add(cell.getStringCellValue());
        } else {/*  ww w . j  a  v a  2  s. c  o  m*/
            throw new IllegalArgumentException(
                    "GUID data is corrupt, duplicate item: " + cell.getStringCellValue());
        }
    }
    return guids;
}

---

From source file:se.sll.invoicedata.price.GeneratePriceList.java

License:Open Source License

private List<String> getServicePrice(Sheet sheet, int startRow, int serviceType) {
    FormulaEvaluator formulaEval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    List<String> priceList = new ArrayList<String>();

    for (int i = startRow; i < sheet.getPhysicalNumberOfRows(); i++) {
        Cell cell = sheet.getRow(i).getCell(serviceType);

        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                priceList.add(DECIMAL_FORMAT.format(cell.getNumericCellValue()));
                break;
            case Cell.CELL_TYPE_FORMULA:
                double d = formulaEval.evaluate(cell).getNumberValue();
                priceList.add(DECIMAL_FORMAT.format(d));
                break;
            case Cell.CELL_TYPE_BLANK:
                priceList.add(DECIMAL_FORMAT.format(0));
                break;
            default:
                StringBuffer errorMsg = new StringBuffer("This type of cell is not handled by the program!");
                errorMsg.append(" cell type:").append(cell.getCellType());
                errorMsg.append(" cell row:").append(cell.getRowIndex());
                errorMsg.append(" cell column:").append(cell.getColumnIndex());
                errorMsg.append(" cell value:").append(cell.getStringCellValue());
                throw new IllegalStateException(errorMsg.toString());
            }// w ww. j a  va2s. c  om
        } else {
            priceList.add(DECIMAL_FORMAT.format(0));
        }
    }

    return priceList;
}

---

From source file:ternarymovies.TernaryMovies.java

private static void enterValues() throws IOException {
    //Reads the file name
    File file = new File("MoviesList.xls");

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    Sheet sheet = wb.getSheetAt(0);
    HSSFRow row;// ww w.j av a  2  s.  c om
    Cell cell;
    //Number of rows
    int rows;
    rows = sheet.getPhysicalNumberOfRows();
    //Sets the number of columns
    int cols = 0;
    //Use a for loop to insert each year
    for (int i = 1; i < 21; i++) {
        cell = sheet.getRow(i).getCell(2);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        insert(Integer.parseInt(cell.getRichStringCellValue().toString()));
    }

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• »